I have an address field containing "City", Street Name, and street Address. How do I link these fields together on a form so that when I enter data into the "City" field the rest of the fields making up the address fills in?
I have an address field containing "City", Street Name, and street Address. How do I link these fields together on a form so that when I enter data into the "City" field the rest of the fields making up the address fills in?
let's say the form / table you are working in is the Orders form/table - - the Form table's City field needs to be set up as a LookUp type to another table: Addresses
when one selects a City - the other fields of the Address table are available to be written into your other Orders fields
in the After Update event of the City field you add the vba to write in the other fields i.e.
me.Street = me.City.column(2)
me.StreetNumber = me.City.column(3)
Why save the additional data into Orders (this is duplication of data)? All you need to save is the city or its record ID. As Paul's link demonstrates, expressions in textbox can reference the combobox column index (index begins with 0).
=[cbxCity].[Column](2)
No VBA needed.
If you need better understanding of multi-column combobox, here is another tutorial http://datapigtechnologies.com/flash...combobox3.html
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Addressed in the link that was posted yesterday.
the reason to write the address into Orders - is to record the actual Order address; people & organizations move - thus if you rely on a link to the Address table, then when it gets changed/updated all orders display the current address even if that isn't accurate for a past order...
the correct approach is only resolved by knowing what the business requires; in some cases a link is very adequate and preferable - - in other cases losing the old information is definitely a bad idea.... I once worked on a db for a client that used the linked method for the sales tax % rate - - and when they changed taxed rate all their past invoices had differing amounts that didn't agree with their accounting info... but in other cases a link is totally preferable. So it can go either way.
Alternative is to create a new record and 'deactivate' the old one.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
I used the = [cbxcity].[Column](2) method you directed and it worked. No VBA as advised.