cmdClear

[icemonster]

ok here's the deal, i have this code as shown below, the problem is, whenever i click on command clear, to clear the fields, it should without any hitch reload or requery the listbox with the sql statement, but for some reason, it does not, it remains with filtered criteria. help?


here's the code.

Function setVisitDueList()
    'set starting sql statement
    strStartSqlVisit2 = "SELECT qryHomemaker.ID, tblHomemakerSupervisoryVisit.SupervisoryVisitID, " _
                      & "qryHomemaker.[Homemaker Name], qryHomemaker.HireDate, " _
                      & "IIf([TypeofHomemaker]=1,DateAdd(""m"",3,[HireDate]),IIf([TypeofHomemaker]=2," _
                      & "DateAdd(""m"",6,[HireDate]))) AS InitialVisit, tblHomemakerSupervisoryVisit.SupervisoryVisitDate, " _
                      & "tblHomemakerSupervisoryVisit.ClientID, qryHomemaker.TypeofHomemaker, " _
                      & "IIf([TypeofHomemaker]=1,DateAdd(""m"",3,[SupervisoryVisitDate]),IIf([TypeofHomemaker]=2," _
                      & "DateAdd(""m"",6,[SupervisoryVisitDate]))) AS NextVisitOn, IIf([NextVisitOn] " _
                      & "Is Null,[InitialVisit],[NextVisitOn]) AS VisitDate, tblHomemakerSupervisoryVisit.Supervisor " _
                      & "FROM qryHomemaker LEFT JOIN tblHomemakerSupervisoryVisit " _
                      & "ON qryHomemaker.[ID] = tblHomemakerSupervisoryVisit.HomemakerID "

    'check for values entered for start and ending date parameters
    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 HireDate Between #" & dtStartDate2 & "# " _
                        & "And #" & dtEndDate2 & "# "
        Else
            strWhereSql2 = strWhereSql2 & "AND HireDate Between #" & dtStartDate2 & "# " _
                        & "And #" & dtEndDate2 & "# "
        End If
    End If

    strSortOrderSql2 = " ORDER BY HireDate;"

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

End Function

Private Sub cmdClear2_Click()
Me.txtStartDate2 = Null
Me.txtEndDate2 = Null
setVisitDueList
End Sub

Private Sub txtEndDate2_AfterUpdate()
setVisitDueList
End Sub

 

[pbaldy]

You don't declare the strWhereSql2 variable here. If it's declared at a higher level, it's probably still carrying the value. You might set the variable to "" at the beginning of the function.

 

[icemonster]

what do you mean? sorry am confused already looking at my code and cross referencing it to someone who made a similar one a months back.

 

[pbaldy]

Somewhere should be this line:

Dim strWhereSql2 As String

and I'm curious where. If not, that's a separate problem, but add this line to the beginning of the function:

strWhereSql2 = ""

and see if that fixes the problem.

 

[MarkK]

I agree with Paul, that I expect to see variable declarations where variables are used. It is recommended in programming theory to always minimize the scope of variables, which enhances readability, reliability, scalability, among other things.
But in this case you may also need to explicitly requery here...

    With Me.lstSupervisoryVisit
        .RowSource = strSQL2
[COLOR="DarkRed"]        .Requery[/COLOR]
        .Value = Null
    End With

...and upon requery you may not need to set the value of the control to null. I'd experiment with a few configurations here and see what you get.
HTH

 

[icemonster]

it actually is declared. i just didnt add it at the top because the code is too long, i only took a snap shot at it. but what i did was i just made another function and when clear is used that's what it follow. works though, but bugs me why it wont work on the normal plane when on another form it does.

 

[icemonster]

argh. now the search date wont work.

strStartSql2 = "SELECT qryVisitListVBA.ID, qryVisitListVBA.SupervisoryVisitID, " _
                    & "qryVisitListVBA.[Homemaker Name], qryVisitListVBA.HireDate, qryVisitListVBA.InitialVisit, " _
                    & "qryVisitListVBA.SupervisoryVisitDate, qryVisitListVBA.ClientID, qryVisitListVBA.TypeofHomemaker, " _
                    & "qryVisitListVBA.NextVisitOn, qryVisitListVBA.VisitDate, qryVisitListVBA.Supervisor FROM qryVisitListVBA "

i used a secondary query instead and as the code above for searching between dates but its giving me none!

 

[icemonster]

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


                          
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 qryVisitListVBA.VisitDate Between #" & dtStartDate2 & "# " _
                    & "And #" & dtEndDate2 & "# "
    Else
        strWhereSql2 = strWhereSql2 & "AND qryVisitListVBA.VisitDate Between #" & dtStartDate2 & "# " _
                    & "And #" & dtEndDate2 & "# "
    End If
End If

strSortOrderSql2 = " ORDER BY qryVisitListVBA.VisitDate;"

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

End Function

here's what the code looks like now and i still cant search.

 

[MarkK]

Troubleshooting steps I'd take ...
1) Simplify the SQL you're using for the list. Get it working in the query design grid and eliminate the possibility that the failure is there. Since we don't have your data, this is the area we can't test for you.
Also, I'd be surprised if you NEED all the information you're selecting there to make the system function. How many fields do you need to make an accurate selection in that list? Keep it simple.
2) If you post about a failure post details. To say your date selection is 'giving [you] none,' or that '[you] still can't search,' is not specific enough info about a failure for someone to speculate about the cause. Do you get compile or runtime errors? What are the error descriptions? Do you get a wrong result? What was the expected result that you deem the actual result to be wrong? And describe all the steps, list clicks, keystrokes and so on the occur prior to the problem.
3) Post ALL the code, including variable declarations and error handlers. I've spent hours helping someone who didn't think their error handler was important, but it was why the routine was failing without indication.
Just a few thoughts, hope this helps,

 

[icemonster]

i fixed it, yeah the error thing was really helpful, i looked at it better after having lunch and boom, got it. thanks everyone for your help.