Adding records to a table using a form

[pkan71]

I have a form (Form1) and a table (tblScanned).

The form has a unbound box called Test. The table has a field called ApplicantID.

I want to scan in Applicant numbers using the form and populate the table.

My vba code is

Private Sub Test_Click()
DoCmd.RunSQL "INSERT INTO tblScanned(ApplicantID)" & _
"VALUES (FORMS!Form1!Test)"
End Sub

I want the form to add the record and then clear the value in the unbound box.

Then I want to scan in a new record and add the new record to the table and clear the value in the unbound box.

Any help would be appreciated.

Thanks.

Patrick

 

[Beetle]

The reference to the form control needs to outside the query string (i.e. - outside the quotes) and you'll need to use the proper string delimiters as well (assuming ApplicantID is a text field). Also, it would be a bit more efficient to use CurrentDb.Execute rather than DoCmd.RunSQL.

Example;

Private Sub Test_Click()

Dim strSQL As String

strSQL = "Insert Into tblScanned(ApplicantID) Values(""" & Me!Test & """)"

CurrentDb.Execute strSQL, dbFailOnError

Me!Test = vbNullString

End Sub

 

[pkan71]

Hi Sean,

Thanks for the information.

The ApplicantID is a number.

The process works once and then I get the error message "Type mismatch"

Any suggestions?

Thanks.

Patrick

 

[Beetle]

If ApplicantID is a number then you would need to remove the quote delimiters from my previous example. What does your modified code look like?

 

[pkan71]

Hi Sean,

Here is what I have.

Private Sub Test_Click()
Dim strSQL As Integer
strSQL = "Insert Into tblScanned(ApplicantID) Values(& Me!Test & )"
CurrentDb.Execute strSQL, dbFailOnError
Me!Test = vbNullString
End Sub

When I remove the quotes, the line

strSQL = "Insert Into tblScanned(ApplicantID) Values(& Me!Test & )"

turns yellow.

Thanks for your help.

Patrick

 

[Beetle]

You removed a few too many quotes. Try this;

strSQL = "Insert Into tblScanned(ApplicantID) Values(" & Me!Test & ")"

 

[pkan71]

Hi Sean,

this is the current code.

Private Sub Test_Click()
Dim strSQL As String
strSQL = "Insert Into tblScanned(ApplicantID) Values(" & Me!Test & ")"
CurrentDb.Execute strSQL, dbFailOnError
Me!Test = vbNullString
End Sub

I runs once fine and it gives me a Run-time error '3134':
Syntax error in INSERT INTO Statement.

When I push the debug button, the line

CurrentDb.Execute strSQL, dbFailOnError

is highlighted in yellow.

Thanks.

Patrick