Need Help with If Statement

  • Thread starter Thread starter magmike
  • Start date Start date
M

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
 
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
Click to expand...

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
 
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
 
Back
Top