dlookup

[Guest]

I consider myself an expert Excel user and an above average Access user. I
am having considerable trouble understanding the syntax and parts of the
dlookup function. Also would like some info on the appropriate use of the
quotes, brackets, etc. I know that if I can get some help with one dlookup
that relates to my database that I can figure it out.

I have 2 tables:

Table A name: customers Table B name: roll_data
Table A fields: customer_id Table B fields: job_number
customer
project_number
address
customer_id
city
customer
state
zip

I have a form that pulls from Table B. I want to have the customer field in
table b to be poplulated with the appropriate customer field from Table A
based on the customer_id that is entered on Table/Form B.

Any help is greatly appreciated.

 

[Larry Linson]

I consider myself an expert Excel user and an above average Access user. I
am having considerable trouble understanding the syntax and parts of the
dlookup function. Also would like some info on the appropriate use of the
quotes, brackets, etc. I know that if I can get some help with one
dlookup
that relates to my database that I can figure it out.

I have 2 tables:

Table A name: customers Table B name: roll_data
Table A fields: customer_id Table B fields: job_number
customer
project_number
address
customer_id
city
customer
state
zip

I have a form that pulls from Table B. I want to have the customer field
in
table b to be poplulated with the appropriate customer field from Table A
based on the customer_id that is entered on Table/Form B.

Any help is greatly appreciated.

 

[Larry Linson]

Your no-doubt-carefully-formatted two-column list of data did not render
cleanly in my newsreader.

I see no reason to store redundant information in the two Tables -- a
relational database is neither just a larger spreadsheet, nor should it be
used as a giant flatfile.

You can join the two Tables in a Query, and display the Customer information
if (as it appears to me, Customer_ID, is the common Field) when you need it.
Alternatively, you could include a Form embedded in a Subform Control, with
MasterLinkFields and ChildLinkFields of Customer_ID, to display the Customer
information in your Form (and, in Reports, too).

If this does not seem to address your issue, could you clarify: (1) what is
the common Field between Tables A and B, (2) why it seems needful to store
duplicate (redundant) information?

Larry Linson
Microsoft Access MVP

 

[Guest]

Table A should contain constant info for the most part. Table B contains
transactions or orders of a sort. I am trying to populate the customer
fields in Table B with the custoemr info from Table A as it was when the
order/transaction was entered. In other words, once the order/transaction is
entered and customer info captured, the customer fields do not need to update
later if the data changes in table A. For example; if a customer changes
address or name, the info in the old order will remain as it was when it was
entered.

I hope this helps explain what I am looking to do and thanks for such a
quick response.

 

[Larry Linson]

Table A should contain constant info for the most part.
Table B contains transactions or orders of a sort. I am
trying to populate the customer fields in Table B with the
custoemr info from Table A as it was when the order/
transaction was entered. In other words, once the order/
transaction is entered and customer info captured, the
customer fields do not need to update later if the data
changes in table A. For example; if a customer changes
address or name, the info in the old order will remain as
it was when it was entered.

Thanks for the explanation. A "snapshot" of information at the time of the
order does make sense. (Perhaps I should have realized from your description
of your experience that you were past the "big spreadsheet" view.)

I'd suggest using a multi-column ComboBox to select the Customer, and VBA
code in its AfterUpdate event that uses its Column properties to obtain the
required Customer information to store in Controls on your Form, with those
controls bound to Fields in the other Table. You can set the Properties of
those Controls to either allow, or prohibit, the user from changing the data
you retrieve from the Customers Table.

Larry Linson
Microsoft Access MVP