Solved

Runt-ime Error '3077'

Posted on 2013-12-31
3
358 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye 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 48

Expert Comment

by:Dale Fye
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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

636 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