Find a Record Based on Combo Box

[Musa via AccessMonster.com]

I have the following code with Survey_ID as the PK. The Combo Box also
displays First Name , Last Name , and DOB. I want to search based on Last
Name and view the Survey_ID in the drop down of the combo box. I am able to
search Last Name if I leave the Column width for Survey_ID = 0". When I
change the column width for the Survey_ID to show in the drop down, I'm
unable to search by Last Name. The search switches to the Survey_ID. How
can I display Survey_ID, Last Name, First Name, and DOB in the drop down
combo box, but search just on last name ?

Private Sub Matchup_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Survey_ID]= " & Str(Nz(Me![Matchup], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Matchup = Null
End Sub


Thanks...

 

[bismuth83]

Try: rs.FindFirst "[Survey_ID]= " & Str(Nz(Me![Matchup].Column(1), 0))

Column() is 0 based, so (1) would refer to your 2nd column. This
should be regardless of column size.

 

[Musa via AccessMonster.com]

Thanks. I replaced the code with your suggestion.. I am now getting Run -
Time Error 13 - Type Mismatch... I need the Combo Box to pull up the record
based on the search of the last name..

Try: rs.FindFirst "[Survey_ID]= " & Str(Nz(Me![Matchup].Column(1), 0))

Column() is 0 based, so (1) would refer to your 2nd column. This
should be regardless of column size.

 I have the following code with Survey_ID as the PK. The Combo Box also
displays  First Name , Last Name , and DOB.  I want to search based on Last

[quoted text clipped - 19 lines]

 

[bismuth83]

Maybe: rs.FindFirst "[Survey_ID]= """ & Str(Nz(Me![Matchup].Column
(1), 0)) & """"

Add quotes around the criteria if it's a string (" doubled up so it's
printed literally)

 

[Musa via AccessMonster.com]

Hello.. Now I'm getting Run-Time Error 2465

Maybe: rs.FindFirst "[Survey_ID]= """ & Str(Nz(Me![Matchup].Column
(1), 0)) & """"

Add quotes around the criteria if it's a string (" doubled up so it's
printed literally)

 

[Musa via AccessMonster.com]

Or Run Time Error 13 - Type Mismatch

Hello.. Now I'm getting Run-Time Error 2465

Maybe: rs.FindFirst "[Survey_ID]= """ & Str(Nz(Me![Matchup].Column
(1), 0)) & """"

Add quotes around the criteria if it's a string (" doubled up so it's
printed literally)

 

[bismuth83]

If you're searching using the last name and not survey_id, should it
not be:
rs.FindFirst "[Last Name]= """ & Str(Nz(Me![Matchup].Column(1), 0)) &
""""

If you click Debug or use ctrl-break when the error comes up, on which
line does the code stop?

 

[Musa via AccessMonster.com]

The Code is stopping on this line ... I substituted Survey_ID with LNAME..but
it doesn't work..

rs.FindFirst "[LNAME]= """ & Str(Nz(Me![Combo90].Column(1), 0)) & """"

If you're searching using the last name and not survey_id, should it
not be:
rs.FindFirst "[Last Name]= """ & Str(Nz(Me![Matchup].Column(1), 0)) &
""""

If you click Debug or use ctrl-break when the error comes up, on which
line does the code stop?

 

[bismuth83]

How is your form organized? Are you entering LastName in a separate
textbox, and you want the combobox to show the corresponding
Survey_ID? Or are you selecting the LastName from the combobox, and
seaching for Survey_ID someplace else?

 

[Musa via AccessMonster.com]

I'm typing in Last Name in a Combo Box that brings up the corresponding
record (Survey_ID).
I want the User to be able to see the following in the Combo box/ drop down...
Survey_ID, Last Name, First Name, DOB .....They should be able to search by
Last Name in the Combo Box. Once the User selects the Last Name the Record
below should match - based on Last Name and Survey_ID.. It seems I can get
one to work but not the other... meaning.. I can remove the Survey_ID from
the Combo Box and search by Last Name OR I can add the Survey_ID to the combo
box and Only search by Survey_ID ( not Last Name).

 

[bismuth83]

Okay, I see. As far as I know, comboboxes will only search by the
first visible field. One way you can try is to change the rowsource
query to include a duplicate LastName field before Survey_ID, but with
a column width of .0007". From the user's point of view the list
starts with Survey_ID, but it lets you search by LastName. You won't
need an AfterUpdate() event for the searching.

As a side note, it sounds like you'd want to be able to see the list
you're searching through. In that case, I'd suggest using a textbox
and listbox, since comboboxes will hide their lists unless you
explicitly click the arrow. You could assign the listbox value by
using a DLookup function in the AfterUpdate() event of the textbox.

 

[Musa via AccessMonster.com]

Thanks..
But if I remove the After Update Event - when the User Searches and selects
the LNAME the record below will not come up.. I have the combo box in the
Header and it has to somehow link with the records in the Detail section.
Using a List Box, would not be an option for this design.

 

[bismuth83]

Okay, that should work if you keep the AfterUpdate() event. Did you
remember to adjust your bound field for the combobox?

 

[Musa via AccessMonster.com]

OK.. I Finally have it working... Thank you SOO much for your time...