Solved

Runt-ime Error '3077'

Posted on 2013-12-31
3
354 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access on Mouse move 5 35
Reference Controls on subforms 7 28
Dirty form - conditional formatting 5 27
Sum with where criteria on a report 5 17
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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