HI All. I posted this on the Forms page but I thought maybe it is better suited over here. Anyway... I hope I'm not being a pest. Scenario: The code below allows me to enter a date range in an unbound form which then opens another form based on the date range. If I enter only one of the dates (the beginning or the end) and not the other. I have a message box display asking for the other date to be entered and the secondary form does not open.
Problem: The "Exit Sub" is keeping the secondary form from opening when only one date has been entered and that is good. BUT it is also keeping the secondary form from opening when I type criteria into a different search field. I have been working on this for a while today and I keep getting closer and closer with everyone's suggestions but I still don't quite have the results I am looking for. Please HELP!
Desired Results: After the message box appears and I click OK on the message. I want the secondary form not to open at all.
***********CODE************
Private Sub btnOK_Click()
Dim ctl As Access.Control
Dim fGotOne As Boolean
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox _
Or ctl.ControlType = acCheckBox _
Or ctl.ControlType = acComboBox _
Or ctl.ControlType = acListBox _
Then
If Not IsNull(ctl.Value) Then
fGotOne = True
Exit For
End If
End If
Next ctl
If fGotOne Then
Dim strSQL As String
Dim strWhere As String
'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If
If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'*" & txtSSN & "*'"
End If
If Not IsNull(txtFName) Then
strWhere = strWhere & " AND TxPrFName Like " & "'*" & txtFName & "*'"
End If
If Not IsNull(txtMidInit) Then
strWhere = strWhere & " AND TxPrMI Like " & "'" & txtMidInit & "'"
End If
If Not IsNull(txtLName) Then
strWhere = strWhere & " AND TxPrLName Like " & "'*" & txtLName & "*'"
End If
If Not IsNull(txtAddress) Then
strWhere = strWhere & " AND Address Like " & "'*" & txtAddress & "*'"
End If
If Not IsNull(txtCity) Then
strWhere = strWhere & " AND City Like " & "'*" & txtCity & "*'"
End If
If Not IsNull(txtState) Then
strWhere = strWhere & " AND State Like " & "'" & txtState & "'"
End If
If Not IsNull(txtZip) Then
strWhere = strWhere & " AND Zip Like " & "'*" & txtZip & "*'"
End If
If Not IsNull(txtEmployerID) Then
strWhere = strWhere & " AND EmployerID Like " & "'*" & txtEmployerID & "*'"
End If
If Not IsNull(txtRoutingNo) Then
strWhere = strWhere & " AND BankRoutNo Like " & "'*" & txtRoutingNo & "*'"
End If
If Not IsNull(txtAccountNo) Then
strWhere = strWhere & " AND BankAcctNo Like " & "'*" & txtAccountNo & "*'"
End If
If Not IsNull(chkProtester) Then
strWhere = strWhere & " AND TaxProtester = " & chkProtester
End If
If Not IsNull(chkDeceased) Then
strWhere = strWhere & " AND Deceased = " & chkDeceased
End If
If Not IsNull(txtComments) Then
strWhere = strWhere & " AND TxPrComments Like " & "'*" & txtComments & "*'"
End If
If Not IsNull(txtAddUser) Then
strWhere = strWhere & " AND AddUser Like " & "'" & txtAddUser & "'"
End If
If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "# and #" & txtAddDateEnd & "#"
Else
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search Criteria"
Exit Sub
End If
If Not IsNull(txtUpdateUser) Then
strWhere = strWhere & " AND UpUser Like " & "'" & txtUpdateUser & "'"
End If
'Trim the leading "AND" strWhere and store the new string in strSQL.
If Not IsNull(strWhere) Then
strSQL = Mid$(strWhere, 6)
End If
'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL
'Set the text boxes back to null.
Clear_Controls:
txtTxPrID = Null
txtSSN = Null
txtFName = Null
txtMidInit = Null
txtLName = Null
txtAddress = Null
txtCity = Null
txtState = Null
txtZip = Null
txtEmployerID = Null
txtRoutingNo = Null
txtAccountNo = Null
chkProtester = Null
chkDeceased = Null
txtComments = Null
txtAddUser = Null
txtAddDateBegin = Null
txtAddDateEnd = Null
txtUpdateUser = Null
txtUpdateDateBegin = Null
txtUpdateDateEnd = Null
Else
DoCmd.Beep
MsgBox "Please enter search criteria or click Cancel. ", vbInformation, "No Search Criteria"
DoCmd.CancelEvent
End If
End Sub
***********END CODE************
Thanks BUNCHES in advance
Shel
