If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
simple address db question
i know in the back of my mind i know how to do this but...
when i select the suburb in the lookup field i need the table to automatically update the postcode and state field from the lookup table - like a vlookup function in excel help -- deb |
#2
|
|||
|
|||
simple address db question
On Mon, 26 Oct 2009 23:16:01 -0700, deb
wrote: The way I typically solve this is by having the additional data sit in hidden columns of the dropdown list. So rather than suburbID, suburbName, I might have two addl columns Postcode, State. This assumes that one suburb only has one postcode, which in the USA certainly is not true but perhaps in your country it is. Then I can simply have an expression in the ControlSource of my Postcode control: =mySuburbControl.Column(2) and for State: =mySuburbControl.Column(3) (of course you replace myObjectNames with yours) -Tom. Microsoft Access MVP i know in the back of my mind i know how to do this but... when i select the suburb in the lookup field i need the table to automatically update the postcode and state field from the lookup table - like a vlookup function in excel help |
#3
|
|||
|
|||
simple address db question
Deb
Perhaps it is only a matter of semantics ... You definitely do NOT want to do this directly in the table. If you are saying that you have a table with a field defined as a lookup field, stop now! Lookup fields in tables have two strikes against them ... first, lookup fields store the ID, but display the looked up value. This causes considerable confusion. Second, working directly in tables is a common approach ... if you know spreadsheets. But in Access, tables store data, and forms display it. Use the forms. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "deb" wrote in message ... i know in the back of my mind i know how to do this but... when i select the suburb in the lookup field i need the table to automatically update the postcode and state field from the lookup table - like a vlookup function in excel help -- deb |
#4
|
|||
|
|||
simple address db question
i agree with both of you - back to the drawing board
-- deb "Jeff Boyce" wrote: Deb Perhaps it is only a matter of semantics ... You definitely do NOT want to do this directly in the table. If you are saying that you have a table with a field defined as a lookup field, stop now! Lookup fields in tables have two strikes against them ... first, lookup fields store the ID, but display the looked up value. This causes considerable confusion. Second, working directly in tables is a common approach ... if you know spreadsheets. But in Access, tables store data, and forms display it. Use the forms. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "deb" wrote in message ... i know in the back of my mind i know how to do this but... when i select the suburb in the lookup field i need the table to automatically update the postcode and state field from the lookup table - like a vlookup function in excel help -- deb . |
#5
|
|||
|
|||
simple address db question
oh and thanks for the help - much appreciated
-- deb "deb" wrote: i agree with both of you - back to the drawing board -- deb "Jeff Boyce" wrote: Deb Perhaps it is only a matter of semantics ... You definitely do NOT want to do this directly in the table. If you are saying that you have a table with a field defined as a lookup field, stop now! Lookup fields in tables have two strikes against them ... first, lookup fields store the ID, but display the looked up value. This causes considerable confusion. Second, working directly in tables is a common approach ... if you know spreadsheets. But in Access, tables store data, and forms display it. Use the forms. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "deb" wrote in message ... i know in the back of my mind i know how to do this but... when i select the suburb in the lookup field i need the table to automatically update the postcode and state field from the lookup table - like a vlookup function in excel help -- deb . |
Thread Tools | |
Display Modes | |
|
|