Return nothing to criteria, not balnk

[steallan]

Hi

I have a combo box displaying a list of values from a field in one of my tables. The criteria of a query is linked to this combo box. So when the user selects one of the values in the combo box, then hits the button to run the query, only the records with that value appear.

Works fine

however i want the user to still be able to pull up all the records. However if you leave the value blank in the combo box and run the query it shows no records.

How do you get round this?

Thanks

 

[ShaneMan]

Hey steallan,

If I'm following correctly, it sounds like you may need to do a couple of things.

First: To prevent a "blank" subform from showing. You need to add a little to your code on your cmdButton to prevent users from clicking the cmdButton without choosing a value from the cboBox.
Something like:
If Trim(Nz(cboBoxName,"")) = "" Then
Msgbox "You haven't made a choice" Fire a message here asking whatever you need for them to do.
Else
' Subform filtering code here
End If

Second: To show all records again.
Need to all another cmdButton and turn the filter off. So something like:

Me.FilterOn = False

should work.

HTH,
Shane

 

[steallan]

Sorry i didnt explain to well.

I want the user to be able to specify criteria but if they leave one, two, all etc blank, then it just displays the records for the criteria that has been inputted. If none of the option critieria have been set then all the records are shown.

I have found this:

Like (IIf(IsNull([Forms]![Main]![CBM&E]),"*",[Forms]![Main]![CBM&E]))

if i put that in the query as the call to the form then it works. However it only seams to work for Text boxes, not Combo boxes. So im stuck again.

Found it here:

http://www.access-programmers.co.uk/forums/showthread.php?p=435386#post435386

 

[steallan]

Seam to have solved my problem, dont understand why.

At first i had as the "raw source" of the Combo box

SELECT [CWT_Room].[HospitalZone] AS [Hospital Type] FROM CWT_Room GROUP BY [CWT_Room].[HospitalZone];

I removed the AS and now it works!

SELECT [CWT_Room].[HospitalZone] FROM CWT_Room GROUP BY [CWT_Room].[HospitalZone];

YAY!