Date Range search problem

[sparlaman]

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.

Problem: The message box displays correctly but the secondary form still opens because my open form code is separate from the verify dates code. I can see the problem but I can't think how to fix it.

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 SNIPPIT************

'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(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "# and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search Criteria"
End If

'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

***********END CODE SNIPPIT************

Thanks in advance
Shel

 

[pbaldy]

Add

Exit Sub

immediately after the message box.

 

[sparlaman]

Fix created new problem

OK, That worked, but created an new problem. Now when I enter criteria into any other field on my form the message box appears and the search does not run. I have been postin pieces of my code, this time i'll give all of it.

Thanks Bunches

****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 ***

 

[pbaldy]

I inferred from your first post that they had to enter dates. I'm assuming now that they don't have to, but if they do they have to enter both? Try this:

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then 'both filled out
  strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "# and #" & txtAddDateEnd & "#"
ElseIf Not IsNull(txtAddDateBegin) OR Not IsNull(txtAddDateEnd) Then 'one or the other is null
  Beep
  MsgBox "Please enter both dates", vbInformation, "Incomplete Search Criteria"
  Exit Sub
End If

If they leave both empty, then neither condition will be met, and it will move on.

 

[sparlaman]

Yay!

That worked fabulously. I must have been working on this problem for too long yesterday. Thanks VERY MUCH!