Code To make ComboBx work together

[DATADUDE28]

Hi, I have a form in Access 2010 with 3 ComboBox's and I'd like to have one of them ([Maj_CBx]) check the other two ([PCM_CBx] & [Sup_CBx]) for their current value and use it when it does it's filtering on the respective fields.



Here's what I have so far:

Private Sub Maj_CBx_AfterUpdate()

Me.Filter = "[Maj] = '" & Me.Maj_CBx & "'" & "[Name] = '" & Me.PCM_CBx & "'" & "[Supplier] = '" & Me.Sup_CBx & "'"

DoCmd.RunCommand acCmdApplyFilterSort

End Sub

This does give me an Run-Time error 3075 Syntax Error (missing operator) in query expression. Though it seems to have found the correct values for each combobox. I think the problem is that the code doesn't have a way to handle blanks/nulls which in this case [Sup_CBx] happens to be null, however I'm not sure how to do that.

Any help would be much appreciated!

[burrina]

For the Control, set the value using the Nz function; Example:

= Nz([Something],0)

HTH

[DATADUDE28]

Hi burrina, now I'm thinking it's not a blank/null issue as I set Sup_CBx to 0 in the first line of my afterupdate my code:
Sup_CBx.Value = 0.

I get the same error, except now it recognizes that value as 0.

[June7]

Lacking AND operators. Use LIKE and wildcard.

Me.Filter = "[Maj] LIKE '" & Me.Maj_CBx & "*' AND [Name] LIKE '" & Me.PCM_CBx & "*' AND [Supplier] LIKE '" & Me.Sup_CBx & "*'"

Are you sure the combobox values are text? If the combobox RowSource includes ID field as the first column and that is the BoundColumn then the combobox value is not the descriptive test.

[DATADUDE28]

Hi June7,
Apologies for the late reply!
Your post lead me to the right thing....totally blank on AND operators.
Here's the code I wrote on one of the Cmb Boxes (other two are the same just switching references):

Private Sub Maj_CBx_AfterUpdate()
Dim blnTest As Boolean
Dim strPCM, strMAJ, strSUP
blnTest = False

If IsNull(Me.Maj_CBx) Or Me.Maj_CBx = "" Then
strMAJ = ""
Else
strMAJ = "[Maj] = '" & Me.Maj_CBx & "'"
End If
If IsNull(Me.PCM_CBx) Or Me.PCM_CBx = "" Then
strPCM = ""
Else
strPCM = " AND [Name] = '" & Me.PCM_CBx & "'"
End If
If IsNull(Me.Sup_CBx) Or Me.Sup_CBx = "" Then
strSUP = ""
Else
strSUP = " AND [Supplier] = '" & Me.Sup_CBx & "'"
End If

Me.Filter = strMAJ & strPCM & strSUP
Me.FilterOn = True

DoCmd.RunCommand acCmdApplyFilterSort

End Sub


Thanks again for putting me on the right track!

[June7]

Are you sure you want to set a criteria of empty string?

If boxes are empty your filter criteria will return records only where empty string is found.

Do you allow empty string in tables? I don't.

Might find this of interest http://allenbrowne.com/ser-62code.html

Simpler If Then to handle both Null and empty string:

If Me.Maj_CBx & "" = "" Then