I have 3 combo boxes on a form that allow the user to select criteria to search for stock in my tblStock.
They do not have to use all combo boxes, for example they could search using only supplier, or alternatively they could search for a particular stock type from a specific supplier.
I am using a query that takes its criteria from these combo boxes and pulls data from tblStock. I need to use an IIF statement in the criteria of the query so that if one of the combo boxes is left empty it ignores that particular field. The following isn't working, ANY IDEAS?
IIF([Forms]![frmMainMenu]![frmSearchManufacturer]![StockType] Is Null,"" [Forms]![frmMainMenu]![frmSearchManufacturer]![StockType)
Set your combo boxes' SQL in VBA. For example, you might put in the Supplier's AfterUpdate event as follows:
Me.StockCategory.RowSource = "SELECT * FROM TableName WHERE Me.Supplier = '" & Me.Supplier.Value & "'"
If Not IsNull(Me.StockType.Value) Then
Me.StockCategory.RowSource = Me.StockCategory.RowSource & " And StockType = '" & Me.StockType.Value & "'"
Me.StockCategory.RowSource = Me.StockCategory.RowSource & ";"
You can put similar code in the StockType's AfterUpdate event and/or the StockCategory's AfterUpdate event.
In the form's OnCurrent event, you will want to put the original SQL statement or Query name for each combo box's RowSource so that each user will get a clean combo box to work from.