filter form based on combo box

[lloyddobler]

I'm trying to use a form with a drop down of names to filter another form with only records from the selected name. I've got a macro in the after update properties of my combo box that does this:



[first name]=[Forms]![frm_ClaimantDropDown].[combo2]

The bound column is for firstname, but I also need to pull across last name to match records properly. I tried an AND statement but that didn't work. How do I make the lastname column a bound column as well?

[dcrake]

What you can do is use the .Columns() event

For example

 
Dim StrFilter as string
 
StrFilter = "[FirstName] ='" & Me.combo.Column(1) & "' And [LastName]='" & Me.Combo.Column(2) & "'"

This will then allow you to combine more than one item in your combo. Remember that when referring to columns they are zero based.

David

[lloyddobler]

so where does this code go? On my form that has my drop down, or on the form I'm trying to use the filters to, and which section, the after update?

[dcrake]

If you declare Strfilter as a Public Variable in a standard module and populate it on the after update event of the combo box. Then on the OnLoad event of the form that you wanting filtering use.

 
Me.Filter = strFilter
Me.FilterOn = True

David

[lloyddobler]

That didn't work. I put the code in the after update event as follows:

Private Sub Combo2_AfterUpdate()
Dim StrFilter As String
StrFilter = "[First Name] ='" & Me.Combo2.Column(1) & "' And [Last Name]='" & Me.Combo2.Column(2) & "'"
End Sub

When I select a name from the drop down, it only selects the first person (alphabetically) for that first name instead of the actual selection, and the form I'm trying to get to doesn't even open. I've actually tried using .column(0) and (1) as well to no avail.

[dcrake]

You can choose to show or hide columns by setting the column widths accordingly.

You also have not issued a open form event

DoCmd.OpenForm "YourForm"

You need to decide where you want to issue this command either from the OnClick Event of a form or directly after setting the strFilter on the AfterUpdate event.

David

[lloyddobler]

Okay, it's opening the form, but not applying the filter. I already am hiding the columns. I have 3 columns; 1= firstname (hidden), 2=lastname (hidden) and 3=concactonated lastname, firstname (visible). The user selects from the 3rd column, but I want first and lastnames to be bound and open the form based on the first and last name.

[dcrake]

First name (hidden) column 0
Last Name (hidden) Column 1
Full Name (visible) Column 2

After Update Event:

StrFilter = "[First Name] ='" & Me.Combo2.Column(0) & "' And [Last Name]='" & Me.Combo2.Column(1) & "'"

Debug.Print StrFilter

Little question though, why are you not using the persons primary key in your combo box to identify the person? Also what happens if you have 2 or more John Smith's ? How do you know you have the right one?

David

[lloyddobler]

Ok, that didn't seem to apply the filter to the new form on load. Great question though. I've inherited this database, which is nowhere near being normalized. There's another field I think I'm going to key off of instead though, which isn't a UID, and appears to be duplicated a few times but it also appears they want it that way (duplicates), but at least it's more unique to the name being selected. Thanks for your help David, I'm sure I'll be able to use this code elsewhere down the road.