Adding records using form drop-down

  • Thread starter Thread starter Cheswyck
  • Start date Start date
C

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?
 
Rick Brandt said:
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
Click to expand...

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 & " ' , " & _
 
Back
Top