INSERT INTO

[swk003]

Trying to get the below INSERT INTO sql statement to run. Any ideas where I am going wrong?


Private Sub CmdInsertAbbotBoxNumber_Click()

Dim rs As DAO.Recordset ' requires DAO reference
'assumes number data type for PID
Set rs = CurrentDb.OpenRecordset( _
"INSERT INTO tbl_Validation_Data " &_
"(Abbot Box Number) VALUES (" & Me.txt_AbbotBoxNumber & ");"
"Where PID =" & Me.[txt_RGC_BoxNumber_unbound])
"(Abbot Box Number) VALUES (" & Me.txt_AbbotBoxNumber & ");"
rs.Close
Set rs = Nothing
End Sub

thanks

SWK003

 

[bboffin]

You can't execute an INSERT command using OpenRecordSet.

Use the CurrentDB.Execute method instead.

Also you cannot have a WHERE clause in the SQL statement you are using. It should look like this:

CurrentDb.Execute "INSERT INTO tbl_Validation_Data  " &_
           "(Abbot Box Number) VALUES (" & Me.txt_AbbotBoxNumber & ");"

Bob Boffin

 

[PHV]

CurrentDb.Execute "INSERT INTO tbl_Validation_Data " &_
"([highlight][[/highlight]Abbot Box Number[highlight]][/highlight]) VALUES (" & Me.txt_AbbotBoxNumber & ");"
But I guess you want an UPDATE instead of an INSERT

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swk003]

Thanks Bob

Firstly, i get a compile error:

expected: line number or label or statement or end of statement here. Any ideas?

Secondly, I only want to update records that are:

Where PID =" & Me.[txt_RGC_BoxNumber_unbound])

Can this be done from an insert statement??

swk003

 

[PHV]

CurrentDb.Execute "UPDATE tbl_Validation_Data" & _
" SET [Abbot Box Number]=" & Me.txt_AbbotBoxNumber & _
" WHERE PID=" & Me.txt_RGC_BoxNumber_unbound

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swk003]

Hi PHV

Am still getting a compile error: syntax error, here's the code. Note I've changed the column names

Private Sub CmdInsertAbbotBoxNumber_Click()

CurrentDb.Execute "UPDATE tbl_Validation_data" &
" SET [AbbotBoxNumber]=" & Me.txt_AbbotBoxNumber & _
" WHERE RGCBoxNumber=" & Me.txt_RGC_BoxNumber_unbound

End Sub

thanks

swk003

 

[swk003]

Hi PH

think I've got it sussed now:

CurrentDb.Execute "UPDATE tbl_Validation_data SET [AbbotBoxNumber]=(" & Me.txt_AbbotBoxNumber & ") WHERE RGCBoxNumber=(" & Me.txt_RGC_BoxNumber_unbound & ")"


thanks for your help