Parameter Values?

[Samt]

So i have a page in my for where the new employee selects all of the database programs they know (MS SQL, Oracle, ECT)

I have two tables in which the command draws information and inserts information

one table has the columns: ID and Program name (this table assigns each program a different ID)

the other: Staff DB ID, Staff ID, and DBID (this table matches the employee with the program he/she uses)

I am trying to take the ID from the first table, and insert it into the second table based on the selection chosen

Here is what my query looks like
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim PerminantStaffID As Integer
Dim DBID As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [staffid] FROM [Staff] WHERE [First]= '" & txtFN.Value & "' AND [Last] = '" & txtLN.Value & "'")
Set rs1 = db.OpenRecordset("SELECT [ID] FROM [Database Programs] WHERE [Program] = 'MS SQL'")
rs.MoveFirst
PerminantStaffID = rs!staffid
DBID = rs1!ID

If chkmssql.Value = True Then
InsertSQL = "INSERT INTO [Staff: Database connection] ( [Staff ID], [DBID] ) VALUES ( ['" & PerminantStaffID & "'], ['" & DBID & "'] )"
DoCmd.RunSQL (InsertSQL)
End If

when i run the query, it asks for parameters for the staff id and the DBID, i think it asks for the column names in which to put the staff id and the DBID. how can have it so it automatically knows which columns to put the information in

 

[Access Hero]

Change this:

If chkmssql.Value = True Then
InsertSQL = "INSERT INTO [Staff: Database connection] ( [Staff ID], [DBID] ) VALUES ( ['" & PerminantStaffID & "'], ['" & DBID & "'] )"
DoCmd.RunSQL (InsertSQL)
End If

to this:

If chkmssql.Value = True Then
InsertSQL = "INSERT INTO [Staff: Database connection] ( [Staff ID], [DBID] ) VALUES ( ['" & PerminantStaffID & "'], ['" & DBID & "'] )"
Debug.Print InsertSQL
'DoCmd.RunSQL (InsertSQL)
End If

run it, copy the value in the immediate window to a query and run it.

If you can't figure it out from there, post the SQL along with the error you are getting.

 

[Samt]

When i made your changes, nothing was inserted into the table that i wanted. The doCmd.RunSQL works, but it needs parameter values. I am pretty sure i defined all of my values in my statement, but i am not sure

 

[Access Hero]

Yes, if you'll re-read my previous post, you'll see that's what I expected (I commented out the update statement).

Did you check the SQL in the immediate window? Did you put that SQL into a query window and run it? Did you do further troubleshooting based on the results of that? Did you post back here with the SQL from the immediate window along with the error you received when running it from the query window, having done all the above?