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

0
votes

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 direction.

1
sqlvbams-access-2007
Yes, Affected_Machine_name is a multi-value field from a check box form field that references my master system hosts table. It needs to store the names of all affected machines for a given system vulnerability. I was going through the access tutorials and that seemed to be the way to do it. Was I wrong about this? – user971584
Multi-value fields appear convenient to a user. From a developer standpoint, multi-value fields are more challenging for use in VBA code and SQL statements. Personally I will only use them when connecting Access with SharePoint data sources. In all other cases, I use the traditional approach where the "multiple values" are stored in separate rows of a related table which includes a foreign key pointing back to the key of a row in a "parent" table. – HansUp
Unfortunately, I find myself more in the former than the latter role, even though I'm "developing" this form and the tables that go with it. Basically, our in house DBAs, who could probably write this in their sleep, are unavailable to me as the IA compliance guy. I'm basically having to homegrow what I need to do my job. There's no beauty in my code, I'm just trying to get work done. So I'm open to whatever works to get me to the ends. I appreciate the helpful comments so far and will try to figure out what you're saying. Thanks! – user971584

1 Answers

0
votes

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?)