Access 2007 vba How to link a query to a button that will fill a field in a form?

Since everyone has been so helpful to me as a Access/SQL/VBA newbie, I thought I'd try another question. I'm building a system tracking form for some of our information assurance processes and I've got a button on a form that I want to take a CSV formatted field called Affected_Machine_Name from Table B and insert it into the Affected_Machine_Name field in Table A, which is what the form is based off of.

The query (All_Machine_Names) I wrote to try to do this looks like:

INSERT INTO TableA ( Affected_Machine_Name.[Value] )

SELECT TableB.Affected_Machine_Name

FROM TableA, TableB;

After doing some research in one of the other posts, I have it linked into the button using the VBA onClick as follows:

Public Sub All_Button()

Screen.ActiveDatasheet!Affected_Machine_Name = DoCmd.OpenQuery "All_Machine_Names"

End Sub

My current problem is I get a syntax error for the sub function and I have no clue why because the error box provides no useful details. For that matter, I'm not even sure if my query is right, as I was trying to copy some of the other posts for how to do inserts.

I think I'm not referencing the individual records as when I just run the query by itself, it wants to mod all 180 records instead of just the one I have highlighted.

Any help that can be provided would be greatly appreciated! Thanks!

UPDATE: Since I've been looking into the options for how to complete this and think I need to close this question and redefine my issue. Thank you to all who provided some hints and d开发者_开发问答irection.

I'm going to assume you have a unique machine ID (the PK?) on your form and you'd like the Affected_Machine_Name from table B where the Machine_ID (or whatever the ID/PK is) equals the current machine record from table A. Let's assume your form is BOUND (not just based off of) to TableA and the ID field in both tables is named Machine_ID.

If that were the case then the following should work in the code for a button on the form:

me!Affected_Machine_Name = dlookup("Affected_Machine_Name", "TableB", "Machine_ID = " & me!Machine_ID

In that code "me!" is a way to reference the fields of the bound table for the currently selected record. Dlookup returns data from a table with the arguments dlookup(field,table,condition). So that will return the machine name from TableB that has the same ID as the current record in the form.

(Side question: Why are you storing CSV data in a database instead of breaking it out into a separate table?)