Do you understand how Filter by Form does it?

[mitchem1]

This is a very simple example, but one that I hope will demonstrate what I am trying to figure out.
I have a table with 4 columns and 100 records. The columns are County, Route, Section, and Engineer. I have created a search form with 4 unbound combo boxes -- one for counties, one for routes, one for sections and one for engineers. The idea is that if the user leaves all 4 combos blank, all 100 records should be retrieved. However, it only retrieves records where there is no null value in any of the columns. I have tried all kinds of variations in my query, but cannot get what I want.
After creating a form that was bound to the table and then using the Filter by Form feature, I found that Access would retrieve the records the way I am attempting to do in my custom form (trying to shield very green users from Filter by Form). Can anyone explain this or point me in the right direction. Thank you.

 

[mission2java_78]

This is a very simple example, but one that I hope will demonstrate what I am trying to figure out.
I have a table with 4 columns and 100 records. The columns are County, Route, Section, and Engineer. I have created a search form with 4 unbound combo boxes -- one for counties, one for routes, one for sections and one for engineers. The idea is that if the user leaves all 4 combos blank, all 100 records should be retrieved. However, it only retrieves records where there is no null value in any of the columns. I have tried all kinds of variations in my query, but cannot get what I want.
After creating a form that was bound to the table and then using the Filter by Form feature, I found that Access would retrieve the records the way I am attempting to do in my custom form (trying to shield very green users from Filter by Form). Can anyone explain this or point me in the right direction. Thank you.

Simple do the following:

if(isnull(cbo1) and isnull(cbo2)...and isnull(cbo4)) then
   'all combo boxes are null
   strSQL = "SELECT * FROM yourtable of Records"
   'open your form
   now set your forms recordsource to the strSQL
   Me.Recordsource=strSQL
else
   'do other checking
end if

Jon

 

[mitchem1]

Thanks Mission, I'll give it a shot.