DoCmd.RunSQL, trying to update two fields based on form values

[vapid2323]

I am trying to use the below code to add in two values into a table but I cant seem to get the VBA right.

DoCmd.RunSQL "INSERT INTO tblObservation ([fk_QualificationID], [fk_SiteID]) VALUES " & Me.QualificationID.Value, Me.SiteID.Value & ";"

I get the following error:

An expression you entered is the wrong data type for one of the arguments. 2498

The code I used before is below, I just wanted to add in another value (the SiteID)

DoCmd.RunSQL "INSERT INTO [tblObservation] (fk_QualificationID) SELECT " & Me.QualificationID.Value & " AS EXPR1;"

 

[boblarson]

Is SiteID text or numeric? If text you need quotes.

But you are also missing some parens, quotes and ampersands (&)

DoCmd.RunSQL "INSERT INTO tblObservation ([fk_QualificationID], [fk_SiteID]) VALUES (" & Me.QualificationID.Value & "," & Me.SiteID.Value & ");"

 

[vapid2323]

Thanks! they are both numbers so your solution worked!

 

[vapid2323]

Ok so that works every time but I found another problem with this setup.

I am trying to use the following to show the last record in the form, or to say it better, I want it to show the newly created record from the code we used above.

   Private Sub Form_Load()
       If Not Me.NewRecord Then
           RunCommand acCmdRecordsGoToLast
       End If
   End Sub

Any Ideas?

 

[boblarson]

What's the primary key of the table? If you want this, you can modify your code to do this (Untested Air Code):

Dim db As DAO.Database
Dim strSQL As String
Dim lngID As Long
Dim rst As DAO.Recordset
 
Set db = CurrentDb
 
strSQL = [COLOR=black]"INSERT INTO tblObservation ([fk_QualificationID], [fk_SiteID]) VALUES (" & Me.QualificationID.Value & "," & Me.SiteID.Value & ");" [/COLOR]
 
db.Execute strSQL, dbFailOnError
lngID = @@IDENTITY
 
Set rst = Me.RecordsetClone
 
rst.FindFirst "[PrimaryKeyFieldNameHere]=" & lngID
 
If rst.NoMatch Then
   MsgBox "No Match Found"
Else
   Me.Bookmark = rst.Bookmark
End If
 
rst.Close
Set rst = Nothing

 

[vapid2323]

Ok so I found a second solution but I am not sure its the best or if I should be doing it your way...

Mine is very simple, I added "[tblObservation].[ObservationID]" To the order by area on the form I want to load.

I have added about 15 records so far without issue.

 

[boblarson]

Is ObservationID an autonumber? If so, just be aware that autonumbers only guarantee you a UNIQUE number. They don't guarantee (even if they are set to increment) to increment. They can go into negative numbers and also very, very large numbers with no notice. So, ordering should probably be best used with a date/time stamp field.

 

[vapid2323]

Thanks for that, I had no idea that they did not always go up in value