Criteria in DoCmd.OpenForm

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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
Click to expand...

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 -----
 
Back
Top