Trouble running a query in a form and copying results

[sshepard]

I am a chemist attempting to load our lab’s data into an Access 2007 database and have hit a snag.
I have a table that contains the log numbers of all of my samples (Log#), the original sample they were produced from (RawMaterial), and the ID and concentration of up to three additives (Additive1, Additive2, Additive3 and Conc1, Conc2, and Conc3). On my form, when you select a raw material, I would like my form to auto-fill the fields for the additives and their concentrations using the same values as the raw material.
When I do this however, I would like to be able to edit the additive information. For instance, sample R031 might have 3% additive a. I want to make a new sample, call it R032, which is R031 with an additional 4% of additive b. When entering the information for R032, upon entering R031 into the raw material field, I would like Additive1 to show “additive a” and Conc1 to show “3%.” But then when I enter “additive b” and “5%” into Additive2 and Conc2, respectively, I want R032 to reflect the presence of two additives but R031 to be unchanged.
I figured I might need a query that accepts the raw material as a parameter and then looks up the additives for that raw material. On my form (QueryTest), I have a combo box that enables you to select the raw material (RawM) and a button to run the query. The query accepts [Forms]![ QueryTest].[RawM] as its criteria and shows the additives and their concentrations. However, when I run it, no records are returned.
My questions then are 1) What am I doing wrong with the query? and 2) How do I take the query results and copy them into the additive and concentration fields on my form? If the query route is not one I want to take, I'm open to trying something else, I just don't know what it might be.

I’ve been reading lots of tutorials and trying to google my question in creative ways but I can’t seem to find an answer. I would appreciate any advice you might have. Thanks very much!

[June7]

Tables store data.
Queries manipulate data.
Forms interface with tables/queries. Just running a query will not transfer data to a form. The query must be the form's RecordSource so as to enter/edit data.
Explore the tutorials on this site http://www.datapigtechnologies.com/AccessMain.htm, in particular the ones in the AccessForms: Control Basics section about comboboxes and the ones in Access Forms: Tips section about Custom Filter and Search Form.

Reports also use tables/queries as RecordSource for the purpose of output.

[sshepard]

Okay, so I understand that in order to do this, I need two queries, (AdditiveSelect and AdditiveUpdate). AdditiveSelect is just the select query I discussed above, which will find the additives and concentrations of the record whose ID matches that of the raw material. AdditiveUpdate is an update query that has as its criteria that the record to be updated must have the same ID as the one currently open on the form ([Forms]![QueryTest].[ID]). It updates that record's Additive1, Additive2, Additive3, Conc1, Conc2, and Conc3 fields to [Queries]![AdditiveSelect].[Additive1], [Queries]![AdditiveSelect].[Conc1], etc.

I designed both these queries, created buttons to run them on the form, and then when I clicked them they worked! So now my questions are:
1) Can I somehow make the results of AdditiveSelect not pop up every time I hit the button on my form to run the query?
2) After running both queries, I have to use the arrows to navigate away from the record I am looking at and then back to the original record in order for the form to refresh with the new information in the table. Is there some way to streamline this?
3) Can I somehow combine the buttons so that one click runs the AdditiveSelect, then AdditiveUpdate, and finally refreshes the form?
EDIT: It'd be even better if just selecting the raw material in my form automatically did the above three things, removing the button from the equation entirely.

Thanks all for your help.

[June7]

sshepard, read your post in another thread reminding me about this one. Sorry I lost track of it. Maybe I should stop practice of removing subscriptions with no responses in 5 days. Do you still need these issues addressed?

I presume these queries are Access objects, not VBA coded.

1. Don't actually 'run' a Select query, just use it by reference in other queries.

2. Use Me.Requery or Me.Refresh in the procedure that updates the table.

3. Use AfterUpdate event of control where you select raw material. Might want to include a response MsgBox for users to verify that they really, really want to proceed. Like:
If MsgBox("Do you really, really want to update data?", vbYesNo,"Confirm") = vbYes Then
'update code here
End If

[sshepard]

Thanks very much for the help! That did it.