Adding records using form drop-down

[Cheswyck]

Access 2000.I am using the following SQL and getting the following error
message:

Compile error, Syntax error:

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

INSERT INTO tblStaff (staOrg, staMember, staActive) & _
VALUES (frmStaff.cmbOrg, frmStaff.cmbMem, "Y") ;

End Sub

frmStaff is a form that uses 2 combos to select the Organizations & Staff.
Do I need to 'DIM' the database and use a 'DO' command?

 

[Douglas J. Steele]

SQL is not VBA code and thus cannot just be typed into a sub-routine like
you have. Your code needs to implement a method that can execute SQL
code. You also have form references that are not correct.

Private Sub cmdAdd_Click()

Dim sql as String
sql = "INSERT INTO tblStaff (staOrg, staMember, staActive) " & _
"VALUES(" & Forms!frmStaff.cmbOrg & ", " & _
Forms!frmStaff.cmbMem & ", 'Y')"

CurrentDB.Execute sql, dbFailOnError

End Sub

Note that Rick's solution assume that staOrg and staMember are both numeric
fields. If, for example, staOrg happened to be a text field, you'd need to
put quotes around the value:

sql = "INSERT INTO tblStaff (staOrg, staMember, staActive) " & _
"VALUES('" & Forms!frmStaff.cmbOrg & "', " & _
Forms!frmStaff.cmbMem & ", 'Y')"

Exagerated for clarity, that second line is

"VALUES( ' " & Forms!frmStaff.cmbOrg & " ' , " & _