Thank you so much...it was making me crazy!
Here's the code:
Option Compare Database
Option Explicit
Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.
'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
'If Not IsNull(Me.txtVesselName) Then
'strWhere = strWhere & "([Vessel_Name] = """ & Me.txtVesselName &
""") AND "
'End If
'Another text field example. Use Like to find anywhere in the field.
'If Not IsNull(Me.txtYear) Then
'strWhere = strWhere & "([Year] Like ""*" & Me.txtYear & "*"") AND "
'End If
' Another text field by DA
'If Not IsNull(Me.txtInclude) Then
'strWhere = strWhere & "([IncludeComps] Like """ & Me.txtInclude &
""") AND "
'End If
' Text field for State Filter
'If Not IsNull(Me.txtState) Then
'strWhere = strWhere & "([State] Like """ & Me.txtState & """) AND "
'End If
'Yes/No field and combo example. If combo is blank or contains "ALL", we
do nothing.
'If Me.cboFilterState = -1 Then
'strWhere = strWhere & "([State] = True) AND "
'ElseIf Me.cboFilterState = 0 Then
'strWhere = strWhere & "([State] = False) AND "
'End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
'Private Sub cmdFilteredReport_Click()
'DoCmd.OpenReport "rptMegaSearch", View:=acPreview
'End Sub
'End Sub
Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control
'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
'Remove the form's filter.
Me.FilterOn = False
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its
AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event
instead.
'The problems are explained at
http://allenbrowne.com/bug-06.html
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation,
"Permission denied."
End Sub
Private Sub cmdCloseForm_Click()
On Error GoTo Err_cmdCloseForm_Click
DoCmd.Close
Exit_cmdCloseForm_Click:
Exit Sub
Err_cmdCloseForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseForm_Click
End Sub
Private Sub PreviewRpt_Click()
On Error GoTo Err_PreviewRpt_Click
Dim stDocName As String
stDocName = "rptMegaSearch"
DoCmd.OpenReport stDocName, acPreview
Exit_PreviewRpt_Click:
Exit Sub
Err_PreviewRpt_Click:
MsgBox Err.Description
Resume Exit_PreviewRpt_Click
End Sub
Private Sub PrintRpt_Click()
On Error GoTo Err_PrintRpt_Click
Dim stDocName As String
stDocName = "rptMegaSearch"
DoCmd.OpenReport stDocName, acNormal
Exit_PrintRpt_Click:
Exit Sub
Err_PrintRpt_Click:
MsgBox Err.Description
Resume Exit_PrintRpt_Click
End Sub
Private Sub FileRpt_Click()
On Error GoTo Err_FileRpt_Click
Dim stDocName As String
stDocName = "rptMegaSearch"
DoCmd.OutputTo acReport, stDocName
Exit_FileRpt_Click:
Exit Sub
Err_FileRpt_Click:
MsgBox Err.Description
Resume Exit_FileRpt_Click
End Sub
Private Sub cmdPrintRec_Click()
On Error GoTo Err_cmdPrintRec_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection
Exit_cmdPrintRec_Click:
Exit Sub
Err_cmdPrintRec_Click:
MsgBox Err.Description
Resume Exit_cmdPrintRec_Click
End Sub
Private Sub cmdStateRegion_Click()
On Error GoTo Err_cmdStateRegion_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "rptMegaSearch"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdStateRegion_Click:
Exit Sub
Err_cmdStateRegion_Click:
MsgBox Err.Description
Resume Exit_cmdStateRegion_Click
End Sub
Private Sub open_mega()
DoCmd.OpenForm "frmSuperYachtRegister", acNormal, , "Num*" & Me.Num
End Sub
Private Sub Vessel_Name_DblClick(Cancel As Integer)
open_mega
End Sub
