Listbox population (1 Viewer)

[jrjr]

Hi, I was wondering if someone could look at this code and see where I am going wrong. I open form 2 from form 1 and Form 2 has date fields populated by a calendar. When I click the search button on Form 2 I am trying to populate the listbox on form1 with records between the selected dates on form2. I think the listbox is requering as if flickers. Hope this makes sense!

Here is the code so far:

Private Sub cmdSearch_Click()
On Error Resume Next

Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "

If Me![StartDate] <> "" And EndDate <> "" Then
sCriteria = sCriteria & " AND tblWO.Created between #" & Format(StartDate, "dd-mmm-yyyy") & "# and #" & Format(EndDate, "dd-mmm-yyyy") & "#"
End If

sSql = "SELECT DISTINCT [work_priority], [UDF1],[WOnumber],[Status],[Property],[Description],[Requested],[RequestedBy],[Service],[Created],[Phone],[Asset] from tblWO " & sCriteria
[Forms]![FrmFilter]![Listbox1].Form.RowSource = sSql
[Forms]![FrmFilter]![Listbox1].Form.Requery

End Sub

 

[Mile-O]

sCriteria = "WHERE 1=1 "

What is that supposed to do? 1 will always be 1. Also, the elft side should be a field or an expression.

 

[jrjr]

What is that supposed to do? 1 will always be 1.

I was working with some code from a sample database from this site. I didnt think it was needed and tried it both with and without. No difference.

Also, the elft side should be a field or an expression.

elft=left Not sure what you mean by this......

 

[Mile-O]

Here's my reworking.

Private Sub cmdSearch_Click()
    On Error Resume Next

    Dim strSql As String
    Dim strCriteria As String
    strCriteria = "WHERE "

    If Not IsNull(Me.[StartDate]) And Not IsNull(Me.[EndDate]) Then
        strCriteria = strCriteria & "tblWO.Created Between #" & _
            Format(Me.[StartDate], "mm/dd/yyyy") & "# And #" & _
            Format(Me.[EndDate], "mm/dd/yyyy") & "#"

    strSql = "SELECT DISTINCT [work_priority], [UDF1],[WOnumber],[Status]," & _
               "[Property],[Description],[Requested],[RequestedBy],[Service],[Created]," & _
               "[Phone],[Asset] FROM tblWO " & strCriteria

        Forms("frmFilter").Listbox1.RowSource = strSql
    Else
        MsgBox "Please enter dates", vbExclamation, "No Dates"
    End If

End Sub

If you get an error then please state which line.

 

[jrjr]

No errors are evident. What is happening though is the listbox winds up empty. This is due I think to the formatting of the date. My table has values like this - 5/24/05 where as the calendar I am working with fills in the field with 11-May-2005

I am trying to correct this now. Thanks for your input so far!!

 

[jrjr]

Ok,
I changed the date format in both the module and in your code to d/m/yyyy. It reflects the correct formatting now in the text box on the calendar form. Still though, there are no entries in the listbox. Initially the listbox is populated with all records. When clicking the button on the calendar form it clears the listbox.

 

[jrjr]

I copied your code before you changed it to Not IsNull
That was the problem. I think now its just a matter of tidying things up. Thanks a bunch!

 

[Mile-O]

Be aware that the format of the date in the table doesn't matter - that's only for show. When querying, always use the "mm/dd/yyyy" method as it solves any possible problems with regional settings.