Using combo box to filter form

[BH]

Hi

I have a combo box that filters a contacts form depending on what is selected from the list. The list works on surname. Everything works fine until there are more than one surname the same. For example:

Bloggs J
Bloggs B

When I select Bloggs J or Bloggs B it only ever filters to Bloggs B

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Surname] = '" & Me![Combo99] & "'"
Me.Bookmark = rs.Bookmark
DoCmd.GoToControl "Title"

Can anyone help please?

 

[dhookom]

Why are you searching on Surname when apparently you want a unique value? Is there a primary key field you can use? Your users don't have to see the primary key value.

Duane
Hook'D on Access
MS Access MVP

 

[BH]

Hi dhookom

Thank you for getting back.

I have a primary key field but the users wish to search by surname as the unique contact_id number would not mean anything to them.

My combo row source reads:

SELECT tblContacts.Surname, tblContacts.[First Name], tblContacts.Contact_ID FROM tblContacts ORDER BY tblContacts.Surname;

Your help is much appreciated

 

[MajP]

Make sure the properties of the combo are as follows
Number of columns:3
bound column:3

Depending on your column widths this will show your name but the value is the contact id.

    Dim rs As dao.recordset
    Set rs = Me.Recordset
    rs.FindFirst "[Contact_ID] = '" & Me![Combo99].value & "'"
    DoCmd.GoToControl "Title"

Even better would be

SELECT tblContacts.Surname & "," & tblContacts.[First Name] AS FullName, tblContacts.Contact_ID FROM tblContacts ORDER BY tblContacts.Surname;

then make the properties
column count:2
bound column:2
widths: x";0

 

[MajP]

if the contact id is numeric remove the single quotes from the find first

 

[dhookom]

I typically select the primary key as the first column of the combo box and set the Column Widths to 0",... so the first column is hidden. I also concatenate the surname and first name as suggested by MajP.

SELECT Contact_ID, Surname & "," & [First Name] AS FullName
FROM tblContacts 
ORDER BY Surname & "," & [First Name];

[tt]
column count:2
bound column:1
Column widths: 0";1.5"
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[BH]

Brilliant....thank you very much it works fine now!

I had tried the contact_id in the code but could not get it to do anything......looks like the bound column was where i was going wrong!

Thanks again

 

[MajP]

value is the default property of a control
so
Me![combo99]
is the same as
Me![Combo99].value

In a listbox or combobox the bound column provides the value

However you can refer to other columns as well as the bound column.
me.column99.column(2)
provides the value in the 3 column (the first column is column(0))

 

[BH]

Hi MajP

Thank you again for the extra information. You have explained it extremely well and it is much appreciated.