Using Multiple Queries to Populate Table from Form

[cbramsey]

Here it goes, I have been working on this and it seems like every step forward results in two steps backwards so I thought I would come here for help.



1. I have a table to track property trades between oil and gas partners - the table is called trades and it has the following fields:
Owner Number
Owner Name
Well Number
Well Name
Effective Date

2. I have a query called active owners, this contains the Owner Number and Owner name for all active owners
3. I have a query called active wells, this contains Well Number, Well Name and is cross referenced through five other tables and queries. I put owner number into query 1 and it runs through 4 other queries to give me the tie to the owner numbers and names for each well

4. I have created a form to populate the Trades table, what I would like to do is to be able to put an owner number in the form and have this auto populate the owner name field. After that I want the Well Number and Well Names from the
active wells query to show up in a drop down allowing me to pick which wells are to be included. It sounds simple and I am sure I am just missing a simple step.

Thank you all for your help on this

[Homegrownandy]

make a select query that references your criteria. Use this as the record source for your drop down. Store the info wherever you like (the wizard will ask).

the main thing is getting that select query to only show what you want to see.

[cbramsey]

So, in the form, I have created a list box in the Owner number field and tied it to the active owners query and this acts as needed, it gives me a drop down of owner numbers, when I select the owner number, I can't get the name to populate in the owner name field on the form. I have the field as a text box which pulls the owner name from the query and the owner number from the form feeds into the query. This part works because if I have the form open with an owner number in and then run the query, I get the name. I just can't get that name to feed into the form automatically.

[Homegrownandy]

when you make the drop down, include the ID field for the record and the owner numbers. Access will hide the ID field its just for reference.

This ID field is what's actually stored. So in your forms record source query you need to include where you are saving this ID. then when you create your drop down save to that field.

if your relationships are right and your forms query then this should display the name.