Querying database from form info entered by user

[Islam Elkabani]

I needed to create a form that will let the user select one or more values
from a group of combo boxes and then push a button to run a query that will
retrieve the specified information from the various tables and return them
in report form. I already succeded to do so but now I want to change my
form so that the user can leave the combo box blank, and that will search
all values that come from the table that make up that particular combo box.
Thank you.
Rose

 

[Allen Browne]

Use the WhereCondition of the OpenReport action.

This exmaple shows how to build up the WhereCondition from any number of
combo boxes, and just ignore the combo if the user did not enter anything:

Private sub cmdPreview_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.combo1) Then
strWhere = strWhere & "([Field1] = " & Me.combo1 & ") AND "
End If

If Not IsNull(Me.combo2) Then
strWhere = strWhere & "([Field2] = " & Me.combo2 & ") AND "
End If

'and so on for other combos.

IngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End Sub


Note: If Field1 is a Text type field (not a Number type field), you need
extra quotes:
strWhere = strWhere & "([Field1] = """ & Me.combo1 & """) AND "
Same for the other fields.