Listbox Trouble (1 Viewer)

[jrjr]

Hi !

On my form I am opening a listbox onclick of a command button. Form recordsource is from a select query. In the listbox rowsource I have this:

SELECT tblWO.Status, tblWO.Property, tblWO.Asset FROM tblWO WHERE ((( tblWO.Status) Like [Forms]![FrmFilter]![FrmFilterCombo] & "*"));

This only returns the Status column and has all of the records instead of being filtered by FrmFilterCombo on the FrmFilter form. Where is my error?? I appreciate any help you can offer!!!

 

[Mile-O]

Set the ColumnCount property of the listbox to 3.

 

[jrjr]

Now that was WAY too easy! Thank you. Couldn't see the forest for the trees I guess. It still is not filtering the records on the value of the combo. Any more ideas?

 

[jrjr]

Ok, I just tried this on the onclick of the command button and got no records returned:

Private Sub Command71_Click()
Listbox1.Visible = True

Me.Listbox1.RowSource = "tblWO.Status, tblWO.property" & _
"From tblWO " & _
"Where [tblWO.status] = '" & Forms![FrmFilter]![FStatusFilterCombo] & "' And " & _
" [tblWO.Property] = '" & Forms![FrmFilter]![PropertyFilterCombo] & "'"
Me.Listbox1.Requery
End Sub

 

[Mile-O]

Ok, I just tried this on the onclick of the command button and got no records returned:

Private Sub Command71_Click()
Listbox1.Visible = True

Me.Listbox1.RowSource = "tblWO.Status, tblWO.property" & _
"From tblWO " & _
"Where [tblWO.status] = '" & Forms![FrmFilter]![FStatusFilterCombo] & "' And " & _
" [tblWO.Property] = '" & Forms![FrmFilter]![PropertyFilterCombo] & "'"
Me.Listbox1.Requery
End Sub

You are not putting spaces in the run-on lines to break the SQL up. You also have no SELECT statement in it. Brackets, also, surround each object

.[Field].

Private Sub Command71_Click()
    Me.Listbox1.RowSource = "SELECT [tblWO].[Status], [tblWO].[property] " & _
    "From tblWO " & _
    "Where [tblWO].[status] = """ & [Forms]![FrmFilter]![FStatusFilterCombo] & """ And " & _
                       "[tblWO].[Property] = """ & [Forms]![FrmFilter]![PropertyFilterCombo] & """;"
    Listbox1.Visible = True
End Sub

Put the Visible statement at the end of the procedure so that the listbox has queries before it becomes visible. And remove the Requery line - there's no need.

HOWEVER

There's nothing dynamic about this query. Open up a new query, switch to SQL view and copy the following into it:

SELECT [tblWO].[Status], [tblWO].[property]
FROM tblWO
WHERE [tblWO].[status] = "" & [Forms]![FrmFilter]![FStatusFilterCombo] & "" AND [tblWO].[Property] = "" & [Forms]![FrmFilter]![PropertyFilterCombo] & "";

Save this query and set this as your listbox's RowSource.

Now, change the procedure simply to:

Private Sub Command71_Click()
    With Me.Listbox1
        .Requery
        .Visible = True
    End With
End Sub

All shall be fine.

 

[jrjr]

This is fun and I am learning something as well. Thanks a lot for your help, it worked out for me. Now I am going into populating the open form by clicking a record in the open listbox. Heading to do a search now. Thanks again!