I have a form (MAIN) with two combo boxes (SELECTPROD and SELECTPRI) and a command button (GO). I want to use the combo boxes to select a PROJECT and PRIORITY (since we have numerous projects with different priority levels), and then click the button to open the form (PROJECTS) but only show records matching the combo box selections. The fields referenced on the PROJECTS form are PRIORITY and PRODUCT. The code I used for this is:
Code:
Private Sub GO_Click()
Dim strWhere As String
strWhere = "1 = 1"
If Not IsNull(Me.SELECTPRI) Then
strWhere = strWhere & " AND [PRIORITY] = " & Me.SELECTPRI
End If
If Not IsNull(Me.SELECTPROD) Then
strWhere = strWhere & " AND [PRODUCT] = """ & Me.SELECTPRODe & """ "
End If
DoCmd.OpenForm "PROJECTS", , , strWhere
End Sub
This works just fine. But if there are no records that match both fields, then it opens a blank form which I do not want to do. I used a function on earlier version of this database that accomplished this where I only used one criterion (PRIORITY) with this code:
Code:
Private Sub Go_Click()
On Error GoTo Err_GO_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim intHolder As Integer
intHolder = DCount("PRIORITY", "PROJECTS")
If intHolder > 0 Then
stDocName = "PROJECTS"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else: MsgBox "You Do Not Have Any Projects With This Priority Level. Please Verify Selection Or Enter A New Project."
End If
Exit_GO_Click:
Exit Sub
Err_GO_Click:
Resume Exit_GO_Click
End Sub
This worked fine, too but now I want to combine these in my new database so that only the selected criteria from the combo boxes are used and not open a blank form. I apologize if this has been asked before, but I am a newbie to this forum and know enough VBA to break everything so, please help. Thanks for taking the time to look this over.
Grumpy