Filter with Combo Boxes

[Guest]

Hi,
I am using the following code to filter a datasheet form. It returns the
records expected if customer is selected or customer + contact. If I select
pump type, it gives all that pump type regardless of customer or contact
selected. Will someone please tell me what I'm doing wrong?

Thanks, Pam

Private Sub CmdViewJobList_Click()
Dim stWhere As String
Dim stDoc As String
stDoc = "fJobList"

If Not IsNull(Me.CboCustomer) Then
stWhere = "CustomerName= """ & Me.CboCustomer & """"
End If

If Not IsNull(Me.CboContact) Then
If Len(stWhere) > 0 Then
stWhere = stWhere & " And "
End If
stWhere = "ContactName= """ & Me.CboContact & """"
End If

If Not IsNull(Me.CboPumpType) Then
If Len(stWhere) > 0 Then
stWhere = stWhere & " And "
End If
stWhere = "PumpType= """ & Me.CboPumpType & """"
End If


DoCmd.OpenForm stDoc, acFormDS, , stWhere

End Sub

 

[Guest]

This one's gonna make you go Doh!

stWhere = "PumpType= """ & Me.CboPumpType & """"
Should be:
stWhere = strWhere & "PumpType= """ & Me.CboPumpType & """"

 

[Guest]

Thanks for replying, but it makes me go Huh???

Why is it different from the contact code?

 

[Guest]

This original code will replace whatever is in strWhere, not concatenate it:
stWhere = "PumpType= """ & Me.CboPumpType & """"
Should be:
This does it like the contact code where it concatenates it:
stWhere = strWhere & "PumpType= """ & Me.CboPumpType & """"

Now can we hear a Doh! ?

 

[Douglas J. Steele]

Your code rewrites what's in stWhere.

Klatuu's code appends to what's there.

 

[Guest]

All three boxes work now - but shouldn't I put the extra stWhere in the
contact code also since it follows customer code.

stWhere = stWhere &"ContactName= """ & Me.CboContact & """"

 

[Guest]

No, it is correct.

All three boxes work now - but shouldn't I put the extra stWhere in the
contact code also since it follows customer code.

stWhere = stWhere &"ContactName= """ & Me.CboContact & """"

 

[Guest]

Okay, I'll give you a "Doh!", but while I do like all the additional
"features" code allows, I don't think I will ever completely understand it.
As always, thanks so much for your help.

P.S. - Would you mind taking a look at a post I entered on 7/10/06 titled
"Option Buttons" that no one replied to? There may be a better way to do
what I'm shooting for.

Thanks again,
Pam

 

[Guest]

I would be happy to take a look at the question, but I could not find it. If
you would be kind enough to post back with the question, I will see what I
can do.

 

[Guest]

Thank you - I reposted with same name - Option Buttons.