Combo boxes Access 2007

[Scotlands Lion]

I am trying to use 3 combo boxes on a form to filter my data in the table on the subform.



I have my first combo box working and filtering the data, but i need two more combo boxes to filter the data even further.

Can i do this? I am a complete novice and don't know where to start on the second combo box.

[NoellaG]

Hi there, you can build the needed SQl string as follows in the VBA module of the form:

dim strSQL as string

strSQL = "1 = 1"

if Nz(me.cboCombo1, 0) = 0 then
[or substitute this with following expression for text valiues:
If len(Nz(me.cboCombo1,"")) > 0 then ]
strSQL = strSQL & " and myValue1 = " & me.cboCombo1
[or for textvalues:
strSQL = strSQL & " and myValue1 = """ & me.cboCombo1 & """"]
end if

You can repeat this building expression for each new combobox.

you end with

me.filter = strSQL
me.filteron = true

[Scotlands Lion]

Could you break it down a bit and explain what each line is doing? I'm trying to learn and build at the same time and would like to know how it works. If you cant its ok, it would be nice to know how it works though.

Thanks

[Scotlands Lion]

Private Sub Combo110_AfterUpdate()
Dim strSQL As String
strSQL = "1 = 1"
If Nz(Me.cboCombo110, 0) = 0 Then

strSQL = strSQL & " and myValue1 = " & Me.cboCombo110
End If

Me.Filter = strSQL
Me.FilterOn = True

End Sub


The bold/underlined is where i got an error. I tried to put it in the second combo box to see if it worked. I got an error though.

I have the first box filtering and doing what i want. Its the second and third combo boxes that are the problem. I can get them to filter one by one, but not together like i would like.

[NoellaG]

Hi there,

the purpose of the test is to look if the combobox contains a value. I use the Nz function to trap the empty values and replace them with a 0 for numeric values or the empty string for text values. So if you're combobox contains a numeric value (for instance the bound column returns an autonumber ID) you use the expression

If Nz(Me.cboNameCombobox,0) > 0

'sorry for the error in the original example where I typed = 0

So the SQL string will only be changed if a value > 0 has been chosen in the second combo

If the second combo returns a text value, you test with the len function if the lenght of the text is greater then 0 with

If len(Nz(Me.cboNameCombobox,"")) > 0

hope this helps

NG

[Scotlands Lion]

I appreciate your help, im a complete novice with VB though. Could you tell me what the piece of code: and myValue1 is for?

[Scotlands Lion]

I got this:

Run-time error ‘3075’:

Syntax error (missing operator) in query expression ‘1 = 1 and myValue1 = Radial Pad’.

[NoellaG]

Hi,

the "and" is the logical operator in the SQL "Where" clause; the myValue stands for the fieldname you want to filter. For instance, if the second filter is on the CustomerID of a Customers table: it would read:

strSQL = strSQL & " and CustomerID = " & Me.cboNameCombobox2

if no other selection criterium was used (combo1 = empty) and the user had chosen the customer with the ID number 2, the generated filter expression would be:

"1=1 and CustomerID = 2"

filtering the report/form on the data for customer number 2

greetings
NG

[NoellaG]

Hi Lion,

Reply to: "Syntax error (missing operator) in query expression ‘1 = 1 and myValue1 = Radial Pad’.

you're missing the quotes around Radial Pad. As it is a string, and in VBA a double quote "" is used to pass through the character ": the expression for text values is:
strSQL = strSQL & " and myValue = """ & Me.cboNameCombobox & """"

Make sure you replace myValue1 with the (existing) field name you want to test.

If you use the expression to build a complete SQL expression instead of a filter, don't forget to put the "Where" statement in front of the "1=1"

grNG

[Scotlands Lion]

I changed the code again but i think im doing it wrong:

Private Sub Combo110_AfterUpdate()
Dim strSQL As String
strSQL = "1 = 1"
If Len(Nz(Me.Combo104, "")) > 0 Then

strSQL = strSQL & " and Part Type = " & Me.Combo104
End If

Me.Filter = strSQL
Me.FilterOn = True

End Sub

The bolded and underlined is were i am getting a new error: Run-time error '3075'


I only have one table "Info"


Combobox1 is called Combo104, it gets its data from the column "Part Type"

Combobox2 is called Combo110, it gets its data from the column "Identifier"

Combobox3 is called Combo113, it gets its data from the column "Shaft"

Combobox1 works perfectly and i can select a part type and it will filter all the part types to the selection i made.

[NoellaG]

Hi,

I see you have spaces in your field names, in this case you have to put square brackets aroud them like [my field]. best is not to use spaces in object names.
I include a working example here of the code I described.

succes
Noëlla

[Scotlands Lion]

Dude, you are a legend... Thank you, i have spent so many hours on this its unbelievable lol.... You deserve a medal for this lol.

[kellyk87]

Hi noella I am trying to use this code but having a problem getting anything to happen, I keep getting an error. My subform looks to a query though as apposed to a table does that make a difference? this is exactly the tyoe of thing I am looking for though.

THanks

Kelly

[NoellaG]

Hi noella I am trying to use this code but having a problem getting anything to happen, I keep getting an error. My subform looks to a query though as apposed to a table does that make a difference? this is exactly the tyoe of thing I am looking for though.

THanks

Kelly

Hi Kelly,

It doesn't make any difference if you use a query or table as recordsource. But my example is a simple form with the filter in the header and the data in the detail section. If you use a subform you need to filter the subform, not the form. The expression would then be something like: me![name subform].Form.filter = strSQL and me![name subform].Form.filteron = true.