Adding 'All' to the last of 3 cascading combo boxes

[tonycl69]

HI, I have question in two parts. I have 3 combo boxes on a form all work fine with the last producing a list in a list box for the user to select a category then run a report. What I would like to do is have an 'All' in the final combo box to print all categories at a location or still choose individual categories. My next question slightly related is, can I run the report from any of the combo box selections rather than only after the final combo box has been chosen?

[ranman256]

You'd need to check the combos to build the where clause...

select case true
  case isnull(cbobox1) and isnull(cboBox2) and isnull(cbobox3)
     SWhere =""
  case not isnull(cbobox1) and isnull(cbobox2) and....
     sWhere = "[field1]='" & cboBox1 & "'"
  case not....
end select 

docmd.openreport "rMyReport",,sWhere

[Homegrownandy]

Like "*" & [Forms]![Form Name]![combo name] & "*" Or Is Null

Then when its null it will work with "all".

Your report can run from any yes, but we need more detail. describe what you want.

[tonycl69]

Thanks for the replies.
I have 3 combo boxes, combo1 is based on a depot table, combo2 is based on what locations that depot has so location table and combo 3 is based on categories at that location from the depot, I would either like to add 'all' to combo3 or be able to print from combo 2 and combo 3.

[Homegrownandy]

You just build a query that references these boxes and produce the report from that. The criteria code I posted earlier is what I use to filter "jobs".

[tonycl69]

The final box is a list box that shows the results of the comboboxes then the report runs from that, however for some reason I am getting duplicate results in the last combobox, and the results are not always what I choose, the 3 boxes were running fine, I am just wondering if there is another way around this, maybe start from scratch.

[Homegrownandy]

Your combo box is showing a list of query results. So the problem lies in your query.

an example of why this is happening:
yout query shows two tables..where you only need 1. If they are linked by a primary key you dont need to show both keys on the database. The database is already aware of this relationship.

Im just guessing here but ive done this ALOT too

so table 1 depot... table 2 locations.

a depot can have many locations. So when you are showing a lit of locations and depot is also in your query, the relevent depot will apear for every time it has a linked record.

[tonycl69]

Hi and thanks for the prompt response, I have tried several methods this is the final combo box query, where would I put the 'union 'all' into this I have tried several places but fail to accomplish what I need.

SELECT tblCategory.CategoryID, tblCategory.Category, tbl_location.ContractNo, tbl_location.Location, tbl_location.LocationID from tbl_location INNER JOIN ((tblCategory INNER JOIN tblmain ON tblCategory.CategoryID = tblmain.CategoryID) INNER JOIN qry_AssetOnHire ON tblmain.Unitid = qry_AssetOnHire.UnitID) ON tbl_location.LocationID = qry_AssetOnHire.tbl_location.LocationID WHERE (((tbl_location.LocationID)=[Forms]![frm_Combos]![cboLocation]));

[Homegrownandy]

can you provide a sample DB with test data?

[tonycl69]

Unfortunately not it's am access frontend and SQL backend and in order for it to work correctly as a standalone it would be too large to post sorry, I think I am going to have to muddle through this myself. Thanks again for your input, I am looking around the net to see if anyone else has a solution, I have found a couple that may work

[Homegrownandy]

Cant you just create a new DB and drop in whats needed for this?

[orange]

Here is a sample db showing the use of ALL in a combo.

[tonycl69]

Hi thanks for your help I really appreciate it, I have managed to isolate the tables, queries and form, but even stripping it right back data wise it is still to big, can you work with it with no data at all?