Solved

Runt-ime Error '3077'

Posted on 2013-12-31
3
351 Views
Last Modified: 2013-12-31
I use Access 2010

If there's no information in the field (ContactID) and I click on this button within my form with the following code in my click event. I get a Run-time error '3077':  Syntax error (missing operator) in expression.


Dim rs As DAO.Recordset
DoCmd.OpenForm "form1"
With Forms("form1")
      Set rs = .RecordsetClone
      rs.FindFirst "ContactID = " & Me.ContactID
      If rs.NoMatch = True Then
             MsgBox "Sorry, Can't Find ContactID"
             Set rs = Nothing
             Exit Sub
      End If
       .Bookmark = rs.Bookmark
End With
Set rs = Nothing

How do I stop this error from appearing by tweaking the above code?

Thanks.
0
Comment
Question by:CptPicard
  • 2
3 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39748448
If this is a new record, or there is no data entered in the ContactID field, you could change:

rs.FindFirst "ContactID = " & Me.ContactID

to

rs.FindFirst "ContactID = " & NZ(Me.ContactID, 0)

Assuming that you have not contactID values of zero, this will result in the message:

"Sorry, Can't Find ContactID"

But I think you would be better served by adding a line at the top of the button Click event:

If me.ContactID & "" = "" then
    msgbox "Please enter or select a contact ID"
    Exit Sub
endif
0
 

Author Comment

by:CptPicard
ID: 39748462
Perfect thanks.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39748482
By the way,

you don't really need to set the reference to the recordset,  you could use:

With Forms("form1").RecordsetClone
      .FindFirst "ContactID = " & Me.ContactID
      If .NoMatch = True Then
             MsgBox "Sorry, Can't Find ContactID"
      else
            Forms("form1").Bookmark = .Bookmark
      end if
End With

And if the form this is running from is "form1", you can replace:

Forms("form1")

with:

me

So it would look like:

With me.RecordsetClone
      .FindFirst "ContactID = " & Me.ContactID
      If .NoMatch = True Then
             MsgBox "Sorry, Can't Find ContactID"
      else
             me.Bookmark = .Bookmark
      endif
End With

I don't know what other code you have following this With/End With construct, but it wouldn't normally be much.  Since you are not instantiating the rs, you don't need to set it to nothing, and probably don't need the "Exit Sub" line if you add an Else clause before the Bookmark line.

I cannot tell from your code whether ContactID as mentioned above in me.ContactID is the name of a control, or a field.  You should probably consider using a naming convention for your controls (txt_ContactID, cbo_ContactID, lst_ContactID) and might want to use brackets around your field names in code me.[ContactID] when referring to a field rather than a control.

This will make it easier to read your code later, as you get more experienced at this.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now