I have a form with one combo box which allows the user to select a project
number. When they click on okay it opens up a detail screen in single form
mode that lets them scroll through records related to the project they chose.
It works great. Then I have a project summary Continuous Form that displays
the status of different parts of the project. I created a form with 3 combo
boxes to select different viewing options. However, this form doesn't work
at all. I need it to open the summary form displaying only the records
related to the options they chose. I am sure the problem is with my criteria
in the WHERE portion of the DoCmd.OpenForm method. Also, if the user does
not select one of the values, then I would like the form to ignore that
criteria when opening the form so as to NOT limit the records relating to the
other criteria. Here is my code. Thanks:

Private Sub cmdOk_Click()
Dim strProject As String
Dim strStatus As String
Dim strPass As String

' to avoid error on assigning null values
If IsNull(Me.cmbProject) Then
strProject = ""
Else
strProject = Me.cmbProject
End If

If IsNull(Me.cmbStatus) Then
strStatus = ""
Else
strStatus = Me.cmbStatus
End If

If IsNull(Me.cmbPass) Then
strPass = ""
Else
strPass = Me.cmbPass
End If

DoCmd.OpenForm "frmVolumeSummary", , , "Project = '" & strProject & "'" & _
"VolumeStatus = '" & strStatus & "'" & "PassStatus = '" & strPass & "'"

DoCmd.Close acForm, Me.Name
End Sub

Thanks for any that were looking into this for me. I was able to come up
with a solution using as guidance Allen Browne's date range tips. I did not
realize that I would need to set up a variable for the where statement and
then run through multiple branching statements for the different criteria.
Here is the finished code for any interested. It works great.

Private Sub cmdOk_Click()
Dim strWhere As String

If IsNull(Me.cmbProject) Then
If IsNull(Me.cmbStatus) Then
If IsNull(Me.cmbPass) Then 'all three are null
strWhere = ""
Else 'Pass is only one chosen
strWhere = "PassStatus = '" & Me.cmbPass & "'"
End If
Else
If IsNull(Me.cmbPass) Then 'Status is only one chosen
strWhere = "VolumeStatus = '" & Me.cmbStatus & "'"
Else 'Status and Pass chosen
strWhere = "VolumeStatus = '" & Me.cmbStatus & "'" & " AND " & _
"PassStatus = '" & Me.cmbPass & "'"
End If
End If
Else
If IsNull(Me.cmbStatus) Then
If IsNull(Me.cmbPass) Then 'Project only one chosen
strWhere = "Project = '" & Me.cmbProject & "'"
Else 'Project and Pass chosen
strWhere = "Project = '" & Me.cmbProject & "'" & _
" AND " & "PassStatus = '" & Me.cmbPass & "'"
End If
Else
If IsNull(Me.cmbPass) Then 'Project and Status chosen
strWhere = "Project = '" & Me.cmbProject & "'" & _
" AND " & "VolumeStatus = '" & Me.cmbStatus & "'"
Else 'All are chosen
strWhere = "Project = '" & Me.cmbProject & "'" & _
" AND " & "VolumeStatus = '" & Me.cmbStatus & "'" & _
" AND " & "PassStatus = '" & Me.cmbPass & "'"
End If
End If
End If

DoCmd.OpenForm "frmVolumeSummary", , , strWhere

DoCmd.Close acForm, Me.Name
End Sub

"Jason" <(E-Mail Removed)> wrote in message
news:740E3AEC-2909-4A29-BB3B-(E-Mail Removed)
> I have a form with one combo box which allows the user to select a
> project number. When they click on okay it opens up a detail screen
> in single form mode that lets them scroll through records related to
> the project they chose. It works great. Then I have a project
> summary Continuous Form that displays the status of different parts
> of the project. I created a form with 3 combo boxes to select
> different viewing options. However, this form doesn't work at all.
> I need it to open the summary form displaying only the records
> related to the options they chose. I am sure the problem is with my
> criteria in the WHERE portion of the DoCmd.OpenForm method. Also, if
> the user does not select one of the values, then I would like the
> form to ignore that criteria when opening the form so as to NOT limit
> the records relating to the other criteria. Here is my code. Thanks:
>
> Private Sub cmdOk_Click()
> Dim strProject As String
> Dim strStatus As String
> Dim strPass As String
>
> ' to avoid error on assigning null values
> If IsNull(Me.cmbProject) Then
> strProject = ""
> Else
> strProject = Me.cmbProject
> End If
>
> If IsNull(Me.cmbStatus) Then
> strStatus = ""
> Else
> strStatus = Me.cmbStatus
> End If
>
> If IsNull(Me.cmbPass) Then
> strPass = ""
> Else
> strPass = Me.cmbPass
> End If
>
> DoCmd.OpenForm "frmVolumeSummary", , , "Project = '" & strProject &
> "'" & _ "VolumeStatus = '" & strStatus & "'" & "PassStatus = '" &
> strPass & "'"
>
> DoCmd.Close acForm, Me.Name
> End Sub

You need to include conjunctions in the WhereCondition argument you
build. Try it like this:

'----- start of revised code -----
Private Sub cmdOk_Click()

Dim strWhere As String

' to avoid error on assigning null values
If Not IsNull(Me.cmbProject) Then
strWhere = strWhere & _
" AND Project = '" & Me.cmbProject & "'"
End If

If Not IsNull(Me.cmbStatus) Then
strWhere = strWhere & _
" AND VolumeStatus = '" & Me.cmbStatus & "'"
End If

If Not IsNull(Me.cmbPass) Then
strWhere = strWhere & _
" AND PassStatus = '" & Me.cmbPass & "'"
End If

If Len(strWhere) > 0 Then
strWhere = Mid$(strWhere, 6)
End If

DoCmd.OpenForm "frmVolumeSummary", _
WhereCondition:=strWhere

DoCmd.Close acForm, Me.Name

End Sub
'----- end of revised code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)