northwind orders form & Access XP

[phillip9]

Hello,

The northwinds sample database has a Orders form, where the combo box
changes the customerID/name, the address, city, etc...

I don't see any VBA code that does this, how are all 5 of those fields
linked together to update after the customerID changes?

I tried created another form to do the same thing, and can only get the
customerID to change on my form.


thanks

phill

 

[Graham Mandeno]

Hi Phillip

There certainly is VBA code there. I just checked my copy of Northwind, and
the CustomerID combo has the following AfterUpdate event procedure:

Private Sub CustomerID_AfterUpdate()
' Update ShipTo controls based on value selected in CustomerID combo box.
Me!ShipName = Me![CustomerID].Column(1)
Me!ShipAddress = Me!Address
Me!ShipCity = Me!City
Me!ShipRegion = Me!Region
Me!ShipPostalCode = Me!PostalCode
Me!ShipCountry = Me!Country
End Sub

This is done this way because it's possible for the order to be shipped
somewhere other than the customer's default address, so the fields need to
be duplicated in the order where they can be changed if required.

If you don't need to duplicate information from the customer table, but you
just want it displayed on your form, then you could use one of the following
techniques:

1) Bind your form to a query which includes the related customer record (as
in Northwind) and bind the customer fields directly to locked textboxes on
your form.

2) Include the other customer fields as hidden columns (ColumWidth=0) in
your combo box, and bind your textboxes to the required column - for
example:
ControlSource: =cboCustomer.Column(4)