Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Runt-ime Error '3077'

Posted on 2013-12-31
3
Medium Priority
?
362 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 2000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
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…

722 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