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?

Thanks in advance.

 

[BoxHead]

If the dealer names are unique, you could use the dlookup function to set the values of the other fields.

Take a look at MS help for the specifics.


HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[DantheDbaseMan]

TO: BOXHEAD

The DEALER NAMES are not unique. There may be a case where there are three of the same DEALER NAMES but with different STORE_NO and REP_NO and REP_NAME in each record.

 

[GingerR]

Make a unique ID (YES YES YES DO THIS!!!) called DealerCodeID or whatever, make it an AUTONUM.

Here's what you want to do:

Table, name = DealerIDs
Fields = DealerID, DealerName, DealerCode

Table, name = Reps
Fields = RepID, RepName, RepNumber, etc

Table, name = DealerCodes (like above)
Fields = DealerCodeID[AUTONUM], DealerID[from table DealerIDs], StoreNo, RepID[From table REPS], etc

ok? so no info is repeated, but later you make relationships between the tables on all those ID's. Then say when a REP's telephone number changes, you only have to change it in one place....ok??

The key is this: once you start repeating data, it's time for a new table.

g

 

[DantheDbaseMan]

To GINGERR:

Thanks Ginger. This makes alot of sense. Now, once I create all these table how do I auto populate the DATA ENTRY table after the end user selects the DEALER NAME to autopopulate the DEALER CODE, STORE_NO, REP_NAME and REP_NO?

I'm not really sure whether to use VBA CODE or the dLookup function. In either case, I'm not familiar with VBA or the dLookup function. I can figure it out if I had an example code.

The end user selects the DEALER NAME from a COMBO BOX, then I want the other fields to auto-populate the remaing fields such as STORE_NO, REP_NAME and REP_NO, from the other tables that you recommend that create.

Dan.

 

[GingerR]

dude, if you use the word AUTOPOPULATE once more i'm gonna reach thru this machine...... )

ok, the only thing you are going to do is put the DealerCodeID (which is an Autonum in the table DealerCodes) into a field in the DataEntry table called DealerCodeID (integer). On the form, we'll popluate (for viewing only) the remainder of the field from the DealerCodeID, DealerIDs and Reps tables (all joined together in a query) by referencing the columns in the combo box that the user chooses from. i know it's a little confusing, but you'll see.

using my table names and names you gave above:
Make a form with the RecordSource being the table DataEntry.
make a combo box.
Name: cboDealerCodeID
ControlSource: DealerCodeID the ID the user chooses will be stored in the DealerCodeID field in the DataEntry table
Row Source Type: Table/Query
RowSource: SELECT DISTINCTROW DealerCodes.DealerCodeID, DealerIDs.DealerName, DealerCodes.StoreNum, DealerIDs.DealerCode, Reps.RepName, Reps.RepPhone FROM (DealerCodes INNER JOIN DealerIDs ON DealerCodes.DealerID = DealerIDs.DealerID) INNER JOIN Reps ON DealerCodes.RepID = Reps.RepID;
Column Count: 6
Column Heads: YES
Column Widths:0";1.5";0.25";0";0";0"
Bound Column:1means that the data in the first column of the combo box (in this case the DealerCodeID) will be saved in the DataEntry table in the RowSource we specified above

ok i think that's it. you can adjust the column widths and look up other properties to adjust as you see fit.

So this will make it so a person can choose which dealership they want. from what you say, the combination of Dealer + Store Number seems to be the unique thing, so those are the two columns i made visible in the combo box when it drops down.

now, to see the other info we'll reference the columns that are in the query behind the combo box. this matches exactly how i did it when i tested it, you can fiddle around with which column has which info in it later if you want. you should just be able to get the gist of it doing this:

make a text box. label="Dealer ID".
Control Source:=[cboDealerCodeID].[Column](3)

make another text box. label = "Store Number".
Control Source:=[cboDealerCodeID].[Column](2)

make another text box. label = "Rep".
Control Source:=[cboDealerCodeID].[Column](4)

make another text box. label = "Rep Phone".
Control Source:=[cboDealerCodeID].[Column](5)

ok?


g

 

[DantheDbaseMan]

TO: GingerR

I'm sorry.. I will not post anymore. I will print your last e-mail and will try to figure this out based by the info that you provided. Thank you for being patient with me.

I bought a book that cost $54.00 and sometimes these books don't give the answers that I need. This forum is the last straw where I get my answers. I appreciate your responses.

Happy New Year!

Dan.

 

[GingerR]

dan you can post as much as you want. i'm being sarcastic. i didn't mean to mess you up. sorry.

i know books are hard it just takes a lot of experience and trial and error and fiddling around. Essentially what we are all telling you is that the reason for relational databases is to not duplicate data...you have each piece of data in one place, and relate all the places to each other some how. I think you've gotten that by now.

Then the other piece you need is just to know how to make the info you want, from different places, appear where you want it to appear. hence the query that underlies the combo box and the .column(x) to show the other info. you could also use dlookup but in my experience that is slow ('....calculating....' appears in the status bar for 10 seconds while it looks up all the dlookups) or you could set the OnChange event of the combo box to fill those other 4 text boxes......and there are other ways, there are ALWAYS other ways.....

anyhow, don't quit posting. that's what this is for. honestly if i was really irritated i just wouldn't answer at all and go on to another post. i applaud your tenacity.

g