Insert Into SQL string from form

[a.t.brooks]

Hi
How do I insert a string/integer into a table using a value obtained
from a form. I seem to get a type conversion failure for some reason.
I've checked the fields (source on form and target in 2nd table) and
both are long integers.
I basically want a button on one form which takes the PK autonumber and
the populates the 2nd form/table with it. The source and the target are
linked in a relationship.

My code:

Private Sub Command29_Click()
On Error GoTo Err_Command29_Click

Dim EID As Integer
'Also tried the above as String
Dim SQLscript As String

EID = Me.ExpIDfrm
'to get the number from the form (field in form called ExpIDfrm)
'ExpIDfrm is a PK autonumber in a 2nd table

SQL = "INSERT INTO Publication(ExpID) VALUES('EID')"
DoCmd.RunSQL (SQL)
'want to insert 'EID' into the field ExpID in the Publication table

Exit_Command29_Click:
Exit Sub

Err_Command29_Click:
MsgBox Err.Description
Resume Exit_Command29_Click

End Sub

Thanks in advance
Tony

 

[John Nurick]

Hi Tony,

This
"INSERT INTO Publication (ExpID) VALUES ('EID')"
attempts to insert the string
EID
into the integer field ExpID, so of course it fails.

If you want to use the value of the integer variable EID, you have to
interpolate it explicitly, e.g.

SQL = "INSERT INTO Publication (ExpID) VALUES (" _
& EID & ");"
CurrentDB.Execute SQL, dbFailOnError