a good idea

[icemonster]

hello.

i have this code here that perhaps i can combine into one big VBA instead, deal is, the searchDB is based on a query statement where my listbox is filled using an SQL statement with VBA. here is my code:

Option Compare Database
Option Explicit

Dim rs As DAO.Recordset

Dim strSQL2 As String
Dim strSQL3 As String
Dim strStartSql2 As String
Dim strWhereSql2 As String
Dim strSortOrderSql2 As String
Dim strSortOrderSql3 As String
Dim varSearch3 As Variant
Dim strSearch2 As String
Dim dtStartDate1 As Date
Dim dtEndDate1 As Date
Dim dtStartDate2 As Date
Dim dtEndDate2 As Date

Function setVisitDueList()
'set starting sql statement
        strStartSql2 = "SELECT qryVisitListVBA2.ID, qryVisitListVBA2.SupervisoryVisitID, qryVisitListVBA2.[Homemaker Name], " _
                       & "qryVisitListVBA2.HireDate, qryVisitListVBA2.InitialVisit, qryVisitListVBA2.SupervisoryVisitDate, " _
                       & "qryVisitListVBA2.ClientID, qryVisitListVBA2.TypeofHomemaker, qryVisitListVBA2.NextVisitOn, " _
                       & "qryVisitListVBA2.VisitDate, qryVisitListVBA2.supervisor FROM qryVisitListVBA2 "

    strWhereSql2 = ""
    
If Not IsNull(Me.txtStartDate2) And Not IsNull(Me.txtEndDate2) Then
    'read the dates selected in the variables
    dtStartDate2 = Me.txtStartDate2
    dtEndDate2 = Me.txtEndDate2
    If strWhereSql2 = "" Then
        strWhereSql2 = " WHERE VisitDate Between #" & dtStartDate2 & "# " _
                    & "And #" & dtEndDate2 & "# "
    Else
        strWhereSql2 = strWhereSql2 & "AND VisitDate Between #" & dtStartDate2 & "# " _
                    & "And #" & dtEndDate2 & "# "
    End If
End If

strSortOrderSql2 = " ORDER BY qryVisitListVBA2.VisitDate;"

strSQL2 = strStartSql2 & strWhereSql2 & strSortOrderSql2
With Me.lstSupervisoryVisit
    .RowSource = strSQL2
    .Value = Null
End With

End Function

Private Sub cmdClear2_Click()
Me.txtStartDate2 = Null
Me.txtEndDate2 = Null
Me.Search3 = ""
setVisitDueList
Me.lstSupervisoryVisit.SetFocus
End Sub

Private Sub lstHomemakerReview_DblClick(Cancel As Integer)

Dim strf As String

Me.Refresh
strf = "frmHomemakerReview"
DoCmd.OpenForm strf
Forms(strf).Recordset.FindFirst "ID = " & Me!lstHomemakerReview

End Sub

Private Sub lstSupervisoryVisit_DblClick(Cancel As Integer)

Dim strf As String

Me.Refresh
strf = "frmHomemakerSupervisoryVisit"
DoCmd.OpenForm strf
Forms(strf).Recordset.FindFirst "ID = " & Me!lstSupervisoryVisit

End Sub

Private Sub Search3_AfterUpdate()
setVisitDueList
End Sub

Private Sub txtEndDate2_AfterUpdate()
ManageClearFilterButton
setVisitDueList
End Sub
Function ManageClearFilterButton()
If Not IsNull(Me.txtStartDate2) Or Not IsNull(Me.txtEndDate2) Then
    Me.cmdClear2.enabled = True
Else
    Me.cmdClear2.enabled = False
End If
End Function

[COLOR="Red"]Private Sub Search3_Change()
Dim vSearchString2 As String

    vSearchString2 = Search3.Text
    Search4.Value = vSearchString2
    Me.lstSupervisoryVisit.Requery
End Sub[/COLOR]

Private Sub Search3_GotFocus()
ManageClearFilterButton
End Sub

as you may see at the bottom, where it is highlighted some of you might be familiar with this, i got it from SearchDB in the repository section. now here's the SQL statement from a query that should accompany the code above:

SELECT qryVisitListVBA2.ID, qryVisitListVBA2.SupervisoryVisitID, qryVisitListVBA2.[Homemaker Name], qryVisitListVBA2.HireDate, qryVisitListVBA2.InitialVisit, qryVisitListVBA2.SupervisoryVisitDate, qryVisitListVBA2.ClientID, qryVisitListVBA2.TypeofHomemaker, qryVisitListVBA2.NextVisitOn, qryVisitListVBA2.VisitDate, qryVisitListVBA2.supervisor
FROM qryVisitListVBA2
WHERE (((qryVisitListVBA2.[Homemaker Name]) Like "*" & [Forms]![frmSupervisor]![Search3] & "*"));

now my prob is, can anyone help me combine the two? where i can still utilize the function of the searchDB without compromising mine? thanks.