OK I have a form that has a combo box of names and a combo box of options related specifically to those names and both are empty by default.
This form has a button that launches a report based query that pulls data based on the name in the form. What I want the report to do (that it isn't doing) is show all the options for that name if the 2nd combo box is empty or if the 2nd combo box has something it, show only the records for that one.
I was thinking that somehow I could change the Record source to a different query that includes the 2nd combo as a criteria, but that isn't working. And when I run the query with 2nd combo as a criteria and that combo is left blank, I get an error...
Anybody have a different / better way of doing this? (I am sure my sucks because it doesn't work
You might put the following code in your command button's OnClick event.
Dim strSQL As String, rstQry As DAO.Recordset
strSQL = "SELECT * FROM YourTableName WHERE YourNameField = '" & _
Forms!YourFormName!NameComboBox.Value & "'"
If Nz(Forms!YourFormName!OptionComboBox.Value," ")>" " Then _
strSQL=strSQL & " And YourOptionField = '" & Forms!YourFormName!OptionComboBox.Value & "';"
Set rstQry = CurrentDb.OpenRecordset(strSQL,dbOpenDynaset)