Using combo box in a two table form

[lcamara]

I have a form based in two tables, and I want to add a combo box that will lookup data from one of this tables, but when I tried to do this with the wizard the option look a record based on the selection doesn't apear, if I make a form based in one table only, I don't have any trouble, because with the wizard I select the option look up the record based on selection, it works fine. But I don't know how to do that without the wizard. I would apreciate it if you could help me. I am a novice in access, so simplicity will be apreciate.
Thanks

 

[OutbackInvestments]

Just before I can post anything significant (if at all, im an experienced novice, if that makes any sense) I need to know how the two tables are set out. Could you perhaps, give an example of what you are trying to do, and what the two tables are, so I can understand the problem better? Also, are the tables linked in a relationship?

 

[lcamara]

One table is paper, and it has data for paper, (price, size, etc.), the other data is for estimates, I need to use the paper table to get the price of the paper to make an estimate, but I also need to save the estimate, So, the combo box that I need is to lookup in the paper table the price and size, but I need to have both tables in the same form. I put two id, one in the estimate table making reference to the paper table. and the paper table has a principal key id. I made a relationship one to many (one in the estimate table, many paper). Please tell me if there is something wrong with this.
Thanks

 

[Richie]

Don't you need a main form and a sub form,
main form based on table 1, sub on 2 linked by primID.?

 

[OutbackInvestments]

Ok, sounds a bit like my stock table. Let me just write this out as mine, with Stock Listing, and I'll transefer it to yours.
So, Stock has Price, Listing, etc. Transactions get the price of the stock to make a purchase. 1 stock is used in many transactions, 1 transaction has many stocks. Many to Many. Therefore, new Table.
Create IndividualPurchaseTable With StockID, TransID. Create autoform with combo boxes. save as IndividualSubAuto. New form for transaction. Add subform. Link master/child fields to TransID. Test. Should work.

So, as I see it. You have a many to many releationship. 1 paper is used in many estimates (right?) and 1 estimate can user many papers. Create a new linking table, with the ID fields. Make a subform for it, and link to the EstimateID. Wizards should take care of all this for you.
If you dont understand this, or it doesnt work, drop me a line.

 

[lcamara]

Ok, Thanks, but I just use 1 kind of paper for each estimate, the problem is that I don't know how to recall the data based on the selection, I can show a combo box that has all the papers, and I can select the paper that I want, but I don't know how to call the other data (size, price), I think there's a way to do that if you have the position on the record, but I don't know the sintax, I copied the one that the wizard generates when I use the combo box to locate a record in a form with 1 record, and it doesn't seems to work. Please let me know how to call the other data in a record if I the person chose one kind of paper.

 

[bill crumpton]

You need to create a query with the two tables that you want to display in your form. After creating the query, create a foem based on that query and then insert your combobox. This should solve your problems.

 

[OutbackInvestments]

So from that, it sounds like, when PaperID (im making up the names) is equal to something, display the price?
Ok. easy enough.
Link the combo box to PaperID.
Now, two ways to do this.
Easiest way is to create a text box, unlinked. Go to control source.
Now, since it is in a subform (as I understand it) I'll give the code for that.

Control Source =
DLookup("[Price]","PaperTable","[PriceID]=" & Forms!MainForm!Subform!PriceID)

Which says
Lookup Price field, from Paper Table, when PriceID is equal to what is displayed in the form.

Hope that helps.

 

[lcamara]

thanks, I will try this, it sounds like it will work, I will let you know.