Get data from linked db

[Monica Lemaire]

I have a form that I would like to have some fields filled (product & vendor) in manually and a corresponding department populate (from linked ODBC table) automatically. How do I go about getting this field looked up without creating a drop down box?

 

[dcx693]

There are a few ways to get data from another table (or query), and they all apply whether or not the table is local to your Access database or belonging to another linked db.

Why not link the other table to your existing table and bind the field in question so it gets populated automatically? That will work if you join the table to the table/query feeding your form. Otherwise, use the Dlookup function to do it. I suspect you'll want to avoid that route since it tends to be very slow (and it will be slower still with linked tables).

 

[Monica Lemaire]

Need more help

I am still having problems.....here is my setup
ODBC table
Vendor (key)
Product (key)
Department

Local Table
Rec # (key)
Vendor (added)
Product (added)
Department (I want this field to automatically populate (and be stored) from ODBC table based on what is keyed into the added fields above)

There are a few ways to get data from another table (or query), and they all apply whether or not the table is local to your Access database or belonging to another linked db.

Why not link the other table to your existing table and bind the field in question so it gets populated automatically? That will work if you join the table to the table/query feeding your form. Otherwise, use the Dlookup function to do it. I suspect you'll want to avoid that route since it tends to be very slow (and it will be slower still with linked tables).

 

[dcx693]

Are you saying that you'd like to update the local table's Department field to the same values that are current in the ODBC table's Department field? I thought from your initial description that you just wanted to display the Department field from the ODBC table on your form.

 

[Monica Lemaire]

Yes, when someone enters the Vendor & product in the local table I want the department to automatically update the department field and store the data in the local record. All records are in the ODBC db (and in some instances there are multiple products with different vendors so I need to meet both criteria). Any help would be greatly appreciated...

 

[dcx693]

First I'd ask why you'd want to store the Department name in the local table since do have the data already stored (albeit in an external ODBC table).

when someone enters the Vendor & product in the local table I want the department to automatically update the department field and store the data in the local record

This is possible, but you'll need some way of triggering the update. Tables in Access do not support events. You'll need to use a form to detect when the vendor and product are entered into the form. (It's good to use combo boxes for those entries, to ensure valid choices.) You can use some code in the After Update event of those boxes to check if both have been filled in. If they have, you can then do a Dlookup and place the Department name from the ODBC table into the field that is bound to the Department name from the local table.