insert into statement

[rkmaitra]

Hello, I am very new to VBA, so please excuse the incompetence.
I am using the on-click property of a form object to store a field value of the current record and then insert it into a new record on my table. The syntax I have typed is:

Private Sub Insert_Record_Before_Click()
Dim lngmRecno As Long
lngmRecno = Me.Recno
DoCmd.RunSQL "insert into tblMain_Table (Recno) Values (lngmRecno)"
End Sub

Recno is the name of the field in the form and also on the table.
When I run this, it asks me to input the value of lngmRecno - I have already specified it with the lngmRecno = Me.Recno. Where is my syntax error?
Also, in the VBA edittor, I am unable to run this stepwise using the F8 function. Is this because the procedure is called by the form?

Thank you,
Rudra

 

[pbaldy]

You need to concatenate the value of the variable into the string. Presuming the data type is numeric:

DoCmd.RunSQL "insert into tblMain_Table (Recno) Values (" & lngmRecno & ")"

FYI, CurrentDb.Execute is slightly more efficient and doesn't throw the warnings that RunSQL will.

 

[rkmaitra]

Thanks for the reply. However, when I use the " the compiler says 'Expected end of statement'.
Private Sub Insert_Record_Before_Click()
Dim lngmRecno As Long
lngmRecno = Me.Recno
CurrentDb.Execute "insert into tblMain_Table (Recno) Values("&lngmRecno&")"
End Sub
Any ideas?
Rudra

 

[rkmaitra]

Sorry, ignore last message - all I had to do was add a space before and after the & and the error disappeared. However, when I run the procedure, I assume that there will be a new record at the end of my table - no such record has appeared. Can you say why?
Many thanks,
Rudra

 

[pbaldy]

Perhaps a key violation? Try

CurrentDb.Execute "insert into tblMain_Table (Recno) Values(" & lngmRecno & ")", dbFailOnError

and see if you get a meaningful error.

 

[rkmaitra]

Thanks, I needed to set one of the other fields from required to not required. Sorted.

 

[pbaldy]

Glad you got it sorted out.