Using Combo Box in Form to Filter Data Displayed in Subform

[GearGirl]

I am an excel user who is very new to Access. I am trying to change the data displayed in a form's subform, based on the data chosen in the form's combo box.

I have two tables
Table 1 = Member table with a full listing of all my members each record in the table has the following fields: last name, first name, member status)
Table 2 = Table with a list of unique last names from Member table. This table only has the last name field

I create a form - in the detail part of the form, I inserted a subform which displays all data from the Member table. In the header of the form I inserted a combo box. The values available to the combo box are from my table listing only unique last names.



I want to be able to choose a Last name (or type a last name) into the combo box. This will cause the data for only the members in the Member table that have that last name to be displayed in the subform. Essentially filtering what is shown in the subform based on what is selected or typed into the combo box. So if I type or select Smith in the combo box - All of my members with the last name Smith would display in the subform.

I have tried to do this by using After Update in the Event tabs in the Property Sheet for the combo box

Private Sub cbo_Lastname_Search_AfterUpdate()
Dim myMember As String
myMember = "Select * from tblMembers where ([Last_Name] = " & Me.cbo_Lastname_Search & ") "
Me.tblMembers_subform.Form.RecordSource = myMember
Me.tblMembers_subform.Form.Requery


End Sub


When I run this, I get the following error

Run-time error '3464'
Data type mismatch in criteria expression

I don't know how to try to fix this error. Any help is most appreciated.

[ItsMe]

Maybe you can do something like ...

myMember = "Select * from tblMembers where ([Last_Name] = '" & Me.cbo_Lastname_Search & "') "

The syntax will be different when comparing text, dates, and numbers.

[GearGirl]

Thanks!

I tried myMember = "Select * from tblMembers where ([Last_Name] = '" & Me.cbo_Lastname_Search & "') "


I don't get the same runtime error. Now my subform does not return any data when I change the selection in the combobox. I know that the Last names are the same in both tables because I used the names from the Member List as the Source for the Unique names. I feel like I am getting closer!

I am an excel user who is very new to Access. I am trying to change the data displayed in a form's subform, based on the data chosen in the form's combo box.

I have two tables
Table 1 = Member table with a full listing of all my members each record in the table has the following fields: last name, first name, member status)
Table 2 = Table with a list of unique last names from Member table. This table only has the last name field

I create a form - in the detail part of the form, I inserted a subform which displays all data from the Member table. In the header of the form I inserted a combo box. The values available to the combo box are from my table listing only unique last names.

I want to be able to choose a Last name (or type a last name) into the combo box. This will cause the data for only the members in the Member table that have that last name to be displayed in the subform. Essentially filtering what is shown in the subform based on what is selected or typed into the combo box. So if I type or select Smith in the combo box - All of my members with the last name Smith would display in the subform.

I have tried to do this by using After Update in the Event tabs in the Property Sheet for the combo box

Private Sub cbo_Lastname_Search_AfterUpdate()
Dim myMember As String
myMember = "Select * from tblMembers where ([Last_Name] = " & Me.cbo_Lastname_Search & ") "
Me.tblMembers_subform.Form.RecordSource = myMember
Me.tblMembers_subform.Form.Requery


End Sub


When I run this, I get the following error

Run-time error '3464'
Data type mismatch in criteria expression

I don't know how to try to fix this error. Any help is most appreciated.

[GearGirl]

HI,

I also noticed that now when I select a name in my combobox that the subform is blank and the ID field (which was the unique ID field created by access when I imported my data) says new in the field. I cannot get full table listing back unless I go out of form view into design view and then back to form view.

[ItsMe]

What is likely happening is the Value of your combo is not what you suspect it to be. For instance, the Bound Column property of your Combo Control may not be the Last Name. Maybe the Bound Column is looking to the First Name.

Can you post the RowSource of your Combo Control? If the RowSource is a Query Object, post the SQL of the query object. I am curious about the SQL that supports your combo.

[GearGirl]

I think this is what you are asked for me to post. Sorry, I really am a novice!

[GearGirl]

I got it!!!! I changed the Bound Column to Column 2 which is where my Last_name field is located. THANK YOU!

[ItsMe]

Glad to hear you were able to figure it out. In the future, although screenshots can be helpful, posting the actual code by copying and pasting is good. This way, the code can be easily reviewed and edited.