Need Help with If Statement

[magmike]

The following code runs on Form Current:
----------------------------------------
Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.Database

Set db = DBEngine(0)(0)
strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
& " VALUES ( " & Me.ID & " ) "
db.Execute strSQL
Set db = Nothing

End Sub
----------------------------------------

However, when creating a new record, I receive an error on the INSERT
INTO statement, because there is no value to insert. How would I
construct an If statement with this code. I've messed with it a bit,
but can't seem to get it to work.

Thanks in advance!

magmike

 

[magmike]

The following code runs on Form Current:
----------------------------------------
Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.Database

    Set db = DBEngine(0)(0)
    strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
                & " VALUES ( " & Me.ID & " ) "
    db.Execute strSQL
    Set db = Nothing

End Sub
----------------------------------------

However, when creating a new record, I receive an error on the INSERT
INTO statement, because there is no value to insert. How would I
construct an If statement with this code. I've messed with it a bit,
but can't seem to get it to work.

Thanks in advance!

magmike

I figured it out. I was using If Me.ID Is Null and was getting an
object needed error. I changed it to If Me.ID < 0 and now it works.
Thanks for thinking about helping me though!

magmike

 

[Baz]

Note the use of the dbFailOnError option with the Execute method (otherwise,
if it fails, it will do so silently):

If Not Me.NewRecord Then
Set db = DBEngine(0)(0)
strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
& " VALUES ( " & Me.ID & " ) "
db.Execute strSQL, dbFailOnError
Set db = Nothing
End If

 

[Baz]

If IsNull(Me.ID) Then...