• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

Navigate to a record using a combo box

I have a form which contains a combo box.  When the user selects a record from the combo box I want that record to be displayed on the form.  But I'm getting a type mismatch error.  Here's the code.

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[PartN] = " & Str(Nz(Me![Combo34], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

PartN is a text field by the way.  What is wrong with the code?
0
SteveL13
Asked:
SteveL13
  • 3
  • 3
1 Solution
 
mbizupCommented:
You need to use quotes to delimit text:

  Set rs = Me.Recordset.Clone
    rs.FindFirst "[PartN] = " & chr(34) &  & Str(Nz(Me![Combo34], 0)) & chr(34)
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

0
 
mbizupCommented:
Chr(34) are double quotes.

Single quotes also work:

Set rs = Me.Recordset.Clone
    rs.FindFirst "[PartN] = '"  & Str(Nz(Me![Combo34], 0)) & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark



But they will cause errors if your data contains single quotes ('), such as in names like O'Brien.
0
 
Dale FyeCommented:
And I would recommend using the rs.nomatch method to determine whether there was a match.

Set rs = me.recordsetclone
Rs.findfirst "[PartN] = '" & nz(me.combo34,0) & """"
If rs.nomatch = false then me.bookmark = rs.bookmark
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
SteveL13Author Commented:
When I copy/paste this code I get red copy on the 2nd line...


  Set rs = Me.Recordset.Clone
    rs.FindFirst "[PartN] = " & chr(34) &  & Str(Nz(Me![Combo34], 0)) & chr(34)
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

0
 
SteveL13Author Commented:
Sorry.. I meant type mismatch
0
 
mbizupCommented:
Sorry...

Correct it like this:

rs.FindFirst "[PartN] = " & chr(34)  & Str(Nz(Me![Combo34], 0)) & chr(34)

Also, I believe the str() is unneeded:

rs.FindFirst "[PartN] = " & chr(34) &  Nz(Me![Combo34], 0) & chr(34)

Finally, is PartN text or numeric?  The above assume it is text.  If it is numeric, the syntax would be quite different:

rs.FindFirst "[PartN] = " &  Nz(Me![Combo34], 0)
0
 
SteveL13Author Commented:
That did it.  Thanks.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now