[Newbie] Replace filter values in query

[Loranga]

Aloha!
Sorry for beeng a newie, maybe this is the wrong category for this post..

I'm just starting to try and learn Access 2007.
I have a table named Pricelist wit the fields [Company], [Resource] and [Price].

In another table named [Orders] I also have these fields but when I type in the Company and Resource I would like to get the price automaticy from the Price table.

So far I've created this question:

SELECT Pricelist.Price, Pricelist.Company, Pricelist.Resource 
FROM Pricelist
WHERE (((Pricelist.Company)="ACME") AND ((Pricelist.Resource)="1281"));

What I would like to to is somehow exange "ACME" and "1281" (wich I typed manually in the query) with the coresponding values from the field Company and Resource from the same record row in the Orders table?

Does this make any sense at all?
Sorry for my bad english.

 

[KenHigg]

How do you intend to use the sql statement to populate the price field in the orders table?

 

[Loranga]

Thanks for the qiuck answer.

I was thinking of a combobox that will show the right price based on selection of Company and resource. Right now my combobox only shows the price for the company "ACEM" where the resource is "1281". (since I manually typed that criteria)
But since I'm a real newbie on Access I might be totaly wrong here.

 

[KenHigg]

You may want to consider using the dlookup() function. If you are building the sql statement in a vba module you can simply replace the "ACME" with the name of the textbox. Something like me.myTextBoxName.

 

[Loranga]

I have now got to the point where my textbox is populated with a fixed value whenever I change the combobox.

Private Sub Resurs_Click()
Text34.Value = 999
End Sub

Now I only have to dig deaper in how to use the dlookup.

Edit: Since I'm trying to learn this I tried a different approach and came up with this:

SELECT Pricelist.Price, Pricelista.Resource, Pricelist.Company
FROM Pricelist
WHERE (((Pricelist.Resource)=[Forms]![Verifikat]![Resurs]) AND ((Pricelist.Company)=[Forms]![Verifikat]![Text34])

This acctually works if I write the company name in the temporary textbox called Text34, now I have to figure out whats wrong when I try to choose the company from a combobox instead.