Autofill table by using data from another table

[DantheDbaseMan]

Here is my two tables (exmples only)

TABLE NAME=DEALER CODES
DEALER NAME:
DEALER CODE:
STORE NUMBER:
REP_NAME
REP_NO

TABLE NAME=DATA ENTRY
DEALER NAME:
DEALER CODE:
STORE NUMBER:
REP_NAME
REP_NO

I would like the end user to Enter the Dealer Name in the Data Entry table by selecting the Dealer Name in a combo box displaying all the data in the Dealer Codes table.
After the selection, I would like the remaining fields to populate in the Data Entry table in their own individual fields.

How can this be done using VBA?

Thnaks in advance.

 

[ToeShot]

Not to sound harsh or stupid but why would you want the same data in 2 tables? This is the reason behind relationale databases so we wouldn't have to update all the same data in many different tables?

 

[DantheDbaseMan]

To ToeShot:

This is just an "example" as posted in my original message. The DATA ENTRY table actually contains 35 fields. Instead of actually posting the entire data structure, I just wanted to convey only those fields that I want to auto populate.

Can you help?

 

[GingerR]

I agree with ToeShot, even if you only are interested in the data from ONE field out of 35....there is no reason to put the same data into two different places. instead, you may want to put the ID number of the record of the already-populated table into the second table into a field called DealerCodeID or something like that. Then in queries and so on you make a relationship between the two tables using that DealerCodeID.

Think of this: Dealer Name = Joe's Dealership, Rep Name = Jonny Jones. so 'Jonny Jones' gets 'auto-populated' into your Data Entry table 300 times. The Jonny gets the axe, and now the rep for Joe's is Betsy Nimrod. Are you gonna go change each and every place it says Jonny Jones to Betsy Nimrod? Have fun......

if what you're looking for is when someone chooses Joe's Dealership on a Data Entry form, the Dealer Info from the Dealer Code table gets displayed, that's one thing. that's not writing the same data again into another table, it's just displaying it for the user to see. Then they can enter in all the other info they need into the second table. Then, like i said, if you make reports, forms, queries...you just put that data on the report from the first table, and the data from the second table, linked by that DealerCodeID.

get it?

 

[DantheDbaseMan]

I'm not interested in only one field. Ok.. Combo box, the user selects ABC RV Center. Then the other fields associated with ABC RV CENTER gets filled in like ABC RV CENTER code number, and ABC RV CENTER REP_NO, and RV CENTER code number....

The "other" fields are from another table.

If you're frustrated with me.. that's ok. I'm frustated too.

 

[ToeShot]

You could make a query combining all the fields you need from both tables and have that as your underlying recordset of a form created for that query. Then add an unbound combobox to the form and have it find a record based on your selection. The wizard is good at guiding you through this. If this is not a viable solution for you maybe we can brainstorm something else.

HTH
Dave

P.S. Not fustrated just trying to understand. Understanding Is The Key To Everything.