Continuous form filtered by combo boxes to show also Null fields

[bakkouz]

Heya,

I have a Continuous form that is filtered by a number of combo boxes, it all works fine and well, But my problem is that when the value in the combo box is Null, the Continuous form resets (shows all records).
Want I want it to do though is show the corresponding fields in the form that are empty.

I'm using the current code:

Private Sub Form_Open(Cancel As Integer)
lngFirstTime = 0
End Sub

Private Sub Combo703_AfterUpdate()
DoFilter
End Sub

Private Sub DoFilter()
Dim fStr As String
fStr = "true "
If Trim(Nz(Combo703, "")) <> "" Then fStr = fStr + "and [Vendor]='" & Combo703 & "' "
Me.Filter = fStr
Me.FilterOn = True
End Sub

help would be much appreciated.
Thanks.

 

[Taruz]

Hi..

Try this for show empty fields..:

Private Sub DoFilter()
Dim fStr As String
fStr = "true "
If Trim(Nz(Combo703, "")) <> "" Then
fStr = fStr + "and [Vendor]='" & Combo703 & "' "

else
fStr= "[Vendor] is null"
end if

Me.Filter = fStr
Me.FilterOn = True
End Sub

 

[bakkouz]

Taruz:
Works great. Thanks.

 

[bakkouz]

Taruz:
I found an error.
The code works fine now for a single combo box, As I said I have several combo boxes, When I add the code to the other combo boxes, I get an error.
such as:

run time error '3075':
sysntax error (missing operator) in query expression '[Received_By] is nulland [Item]='cpu'

Here is the full code for the filter:

Private Sub DoFilter()
Dim fStr As String
fStr = "true "
If Trim(Nz(Combo703, "")) <> "" Then fStr = fStr + "and [Vendor]='" & Combo703 & "' " Else fStr = "[Vendor] is null"
If Trim(Nz(Combo704, "")) <> "" Then fStr = fStr + "and [Received_By]='" & Combo704 & "' " Else fStr = "[Received_By] is null"
If Trim(Nz(Combo705, "")) <> "" Then fStr = fStr + "and [Item]='" & Combo705 & "' " Else fStr = "[Item] is null"
If Trim(Nz(Combo706, "")) <> "" Then fStr = fStr + "and [OYear]='" & Combo706 & "' " Else fStr = "[OYear] is null"
If Trim(Nz(Combo707, "")) <> "" Then fStr = fStr + "and [Prepared_By]='" & Combo707 & "' " Else fStr = "[Prepared_By] is null"
Me.Filter = fStr
Me.FilterOn = True
End Sub

 

[Taruz]

hi again..

Can you try adding a space

Private Sub DoFilter()
Dim fStr As String
fStr = "true "
If Trim(Nz(Combo703, "")) <> "" Then fStr = fStr + " and [Vendor]='" & Combo703 & "' " Else fStr = "[Vendor] is null"
If Trim(Nz(Combo704, "")) <> "" Then fStr = fStr + " and [Received_By]='" & Combo704 & "' " Else fStr = "[Received_By] is null"
If Trim(Nz(Combo705, "")) <> "" Then fStr = fStr + " and [Item]='" & Combo705 & "' " Else fStr = "[Item] is null"
If Trim(Nz(Combo706, "")) <> "" Then fStr = fStr + " and [OYear]='" & Combo706 & "' " Else fStr = "[OYear] is null"
If Trim(Nz(Combo707, "")) <> "" Then fStr = fStr + " and [Prepared_By]='" & Combo707 & "' " Else fStr = "[Prepared_By] is null"
Me.Filter = fStr
Me.FilterOn = True
End Sub

 

[bakkouz]

Taruz:
Thanks, That did the trick.
But now i have another problem.
when I filter the form by the combo705 which filters by [item]
If there is no blank fields corresponding to an item, the form comes up blank and doesnt fetch any results.
I'm not sure how to explain the problem.

Edit: I've attached the file, its an accdb (2007)
to see the problem, open the form (Purchase_Orders_Items_Received) and try clicking on the (Item) combo and selecting CPU or HDD.

 

[Taruz]

I did filter in your query ..Would you please check

 

[bakkouz]

Taruz:
But where did the blank records go?
I mean there were some entries with some Null values in [Vendor] and [Received_By]
and others, They don't show up anymore. but i need to see the blank values also.

 

[Taruz]

I am sorry. I forgot it

criteria, in the query's..

 

[bakkouz]

Taruz:
You are great, Thanks,
But, still something is missing..
in Vendor combo or Received_By combo, when I click on the blank, it doesn't retreive the blank fields.

 

[Taruz]

Taruz:
You are great, Thanks,
But, still something is missing..
in Vendor combo or Received_By combo, when I click on the blank, it doesn't retreive the blank fields.

You are wellcome

Change the relevant parts of it..:

Private Sub Combo703_AfterUpdate()
If IsNull(Combo703) Then Combo703 = ""
DoFilter

End Sub

Private Sub Combo704_AfterUpdate()
If IsNull(Combo704) Then Combo704 = ""
DoFilter
End Sub

 

[bakkouz]

Taruz:
You're an Access Guru! All is working fine now, Thank you very much