Software

Ask something
Question Locked

Copy Row in Access

0 helpful
3 replies

by b_evans

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,
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

3 replies

by shellbot

delimiting txtPerson with single quotes?

strSQL = "INSERT INTO tblSerial (Serial, txtPerson, Lensprf, Lens, Dates) " & _
"Values (" & lngSN & ", '" & Me.txtPersoninput & "', " & Me.Lnsprf & ", " & Me.Lensinput & ", #" & Me.Dateinput & "#);"

by devtrends.com

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.

http://stackoverflow.com/questions/1003048/copying-a-record-in-vba-2-the-new-question

This discussion is closed to new replies.