Filtering a subform from a combo box

[hunterfan48]

Starting from scratch...here's what I did. It is a form for picking a customer from a list in my combo box and in the subform below it, it fill filter the subform to only show those records with that first name.

1)Create a blank form in design view
2)Inserted a combo box
a) It is unbound, however the row source comes from a query where I have it show the First name of the person. (All I see from the combo box is a list of first names)
3) Then I insert a subform that shows each customer for every item they've bought.
4) In going through the wizard for the subform, I created the relationship that the ID field from my query relates to the ID field from the combo box.
However, whenever I change the name of the person in the combo box, nothing changes.

I have a feeling there's something super simple I need to do, but I can't figure it out. Please give me some direction on this...thank you so much!

Brady

 

[John Big Booty]

To filter your subform based on a Combo on your main form try;

Dim strFilter As String

strFilter = "stateID = " & Me.ComboComboName

    Forms!MainFormName!SubFormName.Form.Filter = strFilter
     Forms!MainFormName!SubFormName.Form.FilterOn = True

Check the sample for a practical example.

 

[hunterfan48]

thank you sir...but I really don't understand this coding stuff at all! lol What exactly does that do?

Where would I input that code at? Ok, I know to go to properties but where??

Is there any other way that this could be done without coding it in??? I'll check your sample...thanks for the post tho!

 

[hunterfan48]

btw...I've been seeing more and more forms open up in a window view as compared to the tab. Is this better? do only have the forms open up this way or do you do it for everything in the database?

Tabs or windows....which one and why?

Thanks!

 

[John Big Booty]

thank you sir...but I really don't understand this coding stuff at all! lol What exactly does that do?

Where would I input that code at? Ok, I know to go to properties but where??

Is there any other way that this could be done without coding it in??? I'll check your sample...thanks for the post tho!

In the sample DB the Code is behind the On Change event of the combo.

 

[John Big Booty]

btw...I've been seeing more and more forms open up in a window view as compared to the tab. Is this better? do only have the forms open up this way or do you do it for everything in the database?

Tabs or windows....which one and why?

Thanks!

It's all personal preference, and depends on the look and feel you are after in your DB environment.

 

[hunterfan48]

Awesome!

Just learned how to have my combo box show both fields and not just the 1st one. Only problem there is no space between the fields. Is there an easy way to do that?

Fields are

FirstName
LastName

I entered this in the query builder

. In the next column, in the Field row, enter:
FullName: Trim([Title] & " " & [FirstName] & " " & [LastName])

Ok, so atleast now the combo box when I select a person, it gives me both their first name and last name, but there's no space between. Any way to add a space in between to have it read Brad Smith instead of BradSmith???

thanks!

 

[hunterfan48]

Got an error...

Option Compare Database
Private Sub Combo2_Change()
Dim strFilter As String
If Me.Combo2 = 9 Then
Forms!Form3!CustomerCardSalePricesubform1.Form.FilterOn = False
Exit Sub
End If

strFilter = "FirstName = " & Me.Combo2
Forms!Form3!CustomerCardSalePricesubform1.Form.Filter = strFilter
Forms!Form3!CustomerCardSalePricesubform1.Form.FilterOn = True


End Sub
Private Sub Form_Current()
Dim strFilter As String
If Me.Combo2 = 9 Then
Forms!Form3!CustomerCardSalePricesubform1.Form.FilterOn = False
Exit Sub
End If
strFilter = "FirstName = " & Me.Combo2
Forms!Form3!CustomerCardSalePricesubform1.Form.Filter = strFilter
Forms!Form3!CustomerCardSalePricesubform1.Form.FilterOn = True

End Sub


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

Where'd I go wrong?

Thanks again...

 

[JANR]

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

Where'd I go wrong?

You are trying to compare a textcontrol with a numbercontrol.

For text, use quotes as delimiters.

ex.
strFilter = "FirstName = '" & Me.Combo2 & "'

However since your combo2 has its bound column to a number Id field then you probably have the firstname in the second column in this combobox, then you must change the code to:

strFilter = "FirstName = '" & Me.Combo2.Column(1) & "'

Remember that comboboxes are zerobased starting on 0.

JR

 

[hunterfan48]

Ok thanks...I will try that. Again I ask though, is there any way to do this without putting in this code??? It is so confusing! lol

 

[John Big Booty]

Ok thanks...I will try that. Again I ask though, is there any way to do this without putting in this code??? It is so confusing! lol

Fraid not

 

[hunterfan48]

Hmmmm...I'm no longer to post my Access database anymore. When it's compressed it's still 17.6 MB. Any other ideas for zipping this...maybe even shrinking the size of my database?? It'd be nice if I can post it up to have u guys see what I'm seeing...

 

[John Big Booty]

Have you done a compact and repair on your DB prior to zipping it?

 

[hunterfan48]

Yup I do that several times...

 

[John Big Booty]

Try stripping out the majority of the records you have in the DB.

 

[John Big Booty]

...
. In the next column, in the Field row, enter:
FullName: Trim([Title] & " " & [FirstName] & " " & [LastName])

Ok, so atleast now the combo box when I select a person, it gives me both their first name and last name, but there's no space between. Any way to add a space in between to have it read Brad Smith instead of BradSmith???

thanks!

Try;

FullName: Trim([Title]) & " " & Trim([FirstName]) & " " & Trim([LastName])

 

[vbaInet]

Ok thanks...I will try that. Again I ask though, is there any way to do this without putting in this code??? It is so confusing! lol

There might just be a way without code but what John has advised runs quicker.

1. In the Link Master Fields property of the subform type in the name of the combobox control, e.g. Combo2. Don't click the button in the property because it won't work this way
2. In the Link Child Fields property of the subform type in the field name you want it to link it via. That is the name of the field that is in the subform's record source, e.g. [Firstname].

This will work if the link field in the combo box is the first column.

 

[hunterfan48]

Try stripping out the majority of the records you have in the DB.

If you mean to delete records, not going to happen. lol

Please explain further...

 

[hunterfan48]

There might just be a way without code but what John has advised runs quicker.

1. In the Link Master Fields property of the subform type in the name of the combobox control, e.g. Combo2. Don't click the button in the property because it won't work this way
2. In the Link Child Fields property of the subform type in the field name you want it to link it via. That is the name of the field that is in the subform's record source, e.g. [Firstname].

This will work if the link field in the combo box is the first column.

Didn't get it on 1st try but I agree that John's code works better...as long as I can figure it out lol

 

[John Big Booty]

If you mean to delete records, not going to happen. lol

Please explain further...

Yes I do mean deleting records, however I am not suggesting that you do this on your live data, rather in a copy of your DB. In any case you should be doing your development in a copy of your DB rather than on the live DB. You will find this a whole lot easier if you have split your DB into a Front End (FE) and Back End (BE). That way once you are satisfied with the development of you FE you can simply re-link it to the live BE tables. This will also allow you to continue using your currently working DB whilst you make changes on your copy.