Accessing all records! 1

[rickyoswaldiow]

I've got a VB form with a combo box which populates with a list of "Warehouses", when I select one it in turn populates another combo box with a list of "Items". I can then go ahead and create a report based on these selections - What is required now is a check box next to the "Warehouses" combo box labled "All", as in you can select all of the warehouses. When the "All" checkbox is clicks, the "Warehouses" combo is cleared so it has no text string displayed.
I am not very good with the SQL side of things and the code I am using was generated by someone else in the office, at the moment it states

tblParts_ReOrder.WarehouseIDFor = " & lngWarehouseID

where lngWarehouseID is a value passed into the function from the combo box.

I thought that possibly, instead of setting the "Warehouses" combo box string to "", I could set it to "*", which in other places denotes "All". But this does not work. Any suggestions?

 

[Remou]

You cannot have =*, it must be Like *, or simply leave out the line when all is checked.

 

[rickyoswaldiow]

/me needs an SQL book.

I've got the lead programmer helping me out on this now, thanks for the help though

 

[rickyoswaldiow]

I know this is hardly the place but, do you know where I can find some good online SQL tutorials relating to Access2002/VBA?

 

[Remou]

As search on-line for Jet SQL could be useful.

http://www.tek-tips.com/threadminder.cfm?pid=701

http://msdn2.microsoft.com/en-us/library/Aa140015(office.10).aspx

http://msdn2.microsoft.com/en-us/library/Aa139977(office.10).aspx

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

Most SQL can be translated to Jet with a little thought, so it is also worth reading SQL Server and any other SQL books/websites you may find.

http://sqlservercode.blogspot.com/

http://r937.com

 

[rickyoswaldiow]

Good links, very much appreciated.

 

[PWise]

what i would do is put this a s the SQL for the Warehouses combo

Select 0,"--All Warehouses--"
Select WarehousesID,WarehousesName
from  tblWarehouses

and this the Sql for the items combo

Select .......
from  tblParts_ReOrder
where tblParts_ReOrder.WarehouseIDFor=iif(lngWarehouseID=0,tblParts_ReOrder.WarehouseIDFor,lngWarehouseID)