Solved

Runt-ime Error '3077'

Posted on 2013-12-31
3
357 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 (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 48

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

710 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