Unanswered: Insert Into Join Table - sql string issue
Could anyone give me a steer (or a good kick) to help with an INSERT INTO problem. I have three tables in play ... A Hardware table, a UserGroups Table and a UserGroupsToServers join table. The join table allows me to have multiple servers assigned to a group and vice versa.
I want to bulk assign a Usergroup to a bunch of servers based on the work stream that the servers are assigned to in the Hardware table.
e.g I want to be able to assign the Migration User group to ALL servers in the Hardware table, whose Stream is Migration.
This means writing into the two fields (Hardware_FK and UserGroup_FK) in the UserGroupsToServers join table.
I build the sql string from a form and it writes thus:
INSERT INTO UserGroupsToServers ( UserGroup_FK, Hardware_FK) SELECT 7 AS UserGroup_FK, Hardware_FK FROM hardware WHERE hardware.Stream = 'Migration';
However I am getting the old 3061 too few parameters error
I have tried this in the SQL Query writer and when I run, it prompts for the Hardware_FK value, which tells me it is not getting that.
Any clue as to why?
I have spent hours playing around with the single and double quotes thing, to no avail but now I need some intelligent help.
In the string I am building, I am proposing to SELECT HARDWARE_FK from the Hardware table and that field does not exist in that table. Howare do I tell it, to use the HARDWARE_ID from the Hardware table, as the HARDWARE_FK in the UserGroupsToServers table?
I have crated a loop to to work on a select * recordset but the issue with that is that Access does not like inserting into multiple records using the VALUES method. It want to insert based on a SELECT which means that the following code wont' work, even though the SQL strings is builds DO work but only when they are run individually ((
UserGroup = Me.cbo_UserGroup
Stream = Me.cbo_StreamList
strSQL = "SELECT * FROM Hardware WHERE Stream = '" & Stream & "';"
Set rstHardwareServers = dbsHardware_Current.OpenRecordset(strSQL, dbOpenDynaset)
'If the recordset is empty, exit.
If rstHardwareServers.EOF Then Exit Sub
intI = 1
Do Until .EOF
HardwareID = rstHardwareServers!HW_ID
'MsgBox "The new hardware_ID is " & HardwareID, vbInformation
strSQL = "INSERT INTO UserGroupsToServers (Hardware_FK, UserGroup_FK) " & _
"Select (" & HardwareID & "," & UserGroup & ");"
intI = intI + 1