Copy Row in Access

By b_evans ·
I have a database that tracks our companies serial numbers. Many times our assemblers take out a bunch of serial numbers at a time, but were kinda lacking in the tracking of those numbers. I currently have a database that copies a row and automatically fills in data. The fields are Serial, LensPrf, txtPerson, Lens, Dates. The serial is auto filed with an unbound text field and all the others the user inputs. My issues is my SQL works with all inputs except txtPerson and I can't figure out why. All fields are filled from a form. My code is below. Any help would be appreciated!

Private Sub Command8_Click()
Dim i As Integer
Dim lngSN As Long
Dim strSQL As String

lngSN = Me.LastSN

For i = 1 To Me.Copies
lngSN = Me.LastSN + i
strSQL = "INSERT INTO tblSerial (Serial, txtPerson, Lensprf, Lens, Dates) " & _
"Values (" & lngSN & ", " & Me.txtPersoninput & ", " & Me.Lnsprf & ", " & Me.Lensinput & ", #" & Me.Dateinput & "#);"

CurrentDb.Execute strSQL
Next i
End Sub

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -


by Shellbot In reply to Copy Row in Access

delimiting txtPerson with single quotes?

strSQL = "INSERT INTO tblSerial (Serial, txtPerson, Lensprf, Lens, Dates) " & _
"Values (" & lngSN & ", <font color=red><B>'</B></font>" & Me.txtPersoninput & "<font color=red><B>'</B></font>, " & Me.Lnsprf & ", " & Me.Lensinput & ", #" & Me.Dateinput & "#);"

Collapse -

Access VBA

by In reply to Copy Row in Access

While developing Access VBA projects, I have run into the same type of issues. Sometimes I don't type variables properly, other times it is a weird quirk - likely because I am taking an invalid approach.

Check all of your object names. Change the type of SQL insert, instead of using a concatenation type text INSERT INTO statement, approach it from a object based method. Using .AddNew, .Fields("name").Value and .Update.

Related Discussions

Related Forums