updating table info in a form from related info in another table

[gavrd1]

hi everyone



am an access newbie so still finding my way around things.

i have a database with two related tables "suppliers" and "purchases". suppliers has a "supplier id" primary key with another field called "supplier name" in it. purchases has a "purchase id" primary key with another field called "supplier id" which is one-to-many related to the the supplier id field in suppliers with referential integrity enforced and cascade update related fields.

i now have a form for the purchases that displays the information and where users can update the data. the form is set up to include all the info from the purchases table and the supplier id and supplier name from the supplier table as the record source.

when i display all the fields, i would like to be able to list in a combo box the suppliers names, which when each particular one is selected updates the appropriate "supplier id" in the purchases table.

at the minute, all i can do is get it to either display the "supplier id" numbers which when selected will update the supplier id field in purchases, or if i set it up to display the names that can be selected, when i select a name all it does is update the suppliers table, and not the suppliers id keys in the purchases table!

any ideas? thanks!

[NTC]

Access allows one to approach this using a 'lookup' field type. You would make your Supplier field in the Purchases table to be a look up field of the Supplier table itself.

get yourself an Access textbook easily found at Amazon or any large book store - you will find it very helpful.

[gavrd1]

thanks NTC, i have bought myself an access book although not found anything specific about that inside it yet.

my actual table set up at the minute is:

PURCHASING
Purchase ID - Primary key, autonumber
Supplier ID - Number, DC - combo box, RST - Table/Query, RS - SELECT Suppliers.[Supplier ID], Suppliers.[Supplier Name] FROM Suppliers; BC - 1, CC - 1.

SUPPLIERS
Supplier ID - Primary Key, autonumber
Supplier Name - Text, 255, Text Box

Relationship between the tables is One (from Supplier ID in the Supplier table) to Many (Supplier ID in the Purchasing table), EFI enforced, Cascade fields updated.

My form setup (called Purchases):
Record source = SELECT Purchasing.[Purchase ID], Purchasing.[Supplier ID] AS [Purchasing_Supplier ID], Suppliers.[Supplier ID] AS [Suppliers_Supplier ID], Suppliers.[Supplier Name]
FROM Suppliers INNER JOIN Purchasing ON Suppliers.[Supplier ID] = Purchasing.[Supplier ID];
"Purchase ID text" field - CS = Purchase ID, Not enabled
"Purchasing_Suppliers ID" text field - CS = Purchasing_Supplier ID, Not enabled
"Suppliers_Suppliers ID" text field - CS = Suppliers_Supplier ID, Not enabled
"Supplier Name" combo box field - CS = Purchasing_Supplier ID, RS = SELECT Suppliers.[Supplier ID], Suppliers.[Supplier Name] FROM Suppliers; RST = Table/Query, BC = 1, LTL = Yes

I have only got the Purchasing_Suppliers ID and Suppliers_Suppliers ID text fields on the form just so i can see what is happening with those values as i change information.

The Suppliers Name combo box is the one I am interested in as that is what I would like to drop down displaying the suppliers names rather than IDs which it currently does which can then be chosen from which will it turn update the Suppliers ID field in the Purchasing table.

At the minute, I can only get it to list the Suppliers ID values from the Suppliers table to list in that combo box so that if I chose one, it automatically updates the Purchasing_Suppliers ID and Suppliers_Suppliers ID values and in turn updates them appropriately in the Purchasing table which is what I want.

However, I would like it to display the Supplier Name value is in the combo box instead so that in turn it updates the Purchasing_Suppliers ID and Suppliers_Suppliers ID values from the corresponding Supplier ID for that Name.

I have a feeling that its somewhere due to the setup of my control source/field configurations within the form that could be causing the problems? I think once I've found out what it is I'll be well away!

Thanks

[NTC]

I haven't looked at the details of your post. But in general - it is very common to have a 'sales' table (in your case Purchase) in which some fields i.e. customer, part, even the State field are a 'look up' of another table(s). It is a convenience feature so one doesn't have to retype the same data over and over.

Lookup field types will definitely be found in your text book. Hopefully with examples similar to your situation.

One needs to take care as to what is actually 'bound' into the Sales/Purchase table. If it is just an ID from the looked up table - then that ID may show in a report/form where you actually want the full text. Sometimes it is better to bind the full text, but sometimes it is better to bind the ID and then join the looked up table at the time of display.

Hope this helps.