using listboxes

[nazir.atif]

I need to pick up search results from a table and display them in a
listbox. I've made a simple 2-column listbox and the following code
returns "Object Required" on the Me.ListBox.Column(0,0) lines.

What's the solution?

Here's the code:

-- snip --

Private Sub SearchButton_Click()
Dim query As String, myDate As String, rs As Recordset

DateBox.SetFocus

query = "SELECT Through,Amount FROM ExpDaily WHERE Date=" &
DateBox.Text & ";"

MsgBox (query)
Set rs = CurrentDb.OpenRecordset(query)

If rs.RecordCount <> 1 Then
Me.ListBox.Column(0, 0) = "None"

Else
Me.ListBox.Column(0, 0) = rs!Through

End If
...
-- snip --

[salad]

na********@gmail.com wrote:

I need to pick up search results from a table and display them in a
listbox. I've made a simple 2-column listbox and the following code
returns "Object Required" on the Me.ListBox.Column(0,0) lines.

What's the solution?

Here's the code:

-- snip --

Private Sub SearchButton_Click()
Dim query As String, myDate As String, rs As Recordset

DateBox.SetFocus

query = "SELECT Through,Amount FROM ExpDaily WHERE Date=" &
DateBox.Text & ";"

MsgBox (query)
Set rs = CurrentDb.OpenRecordset(query)

If rs.RecordCount <> 1 Then
Me.ListBox.Column(0, 0) = "None"

Else
Me.ListBox.Column(0, 0) = rs!Through

End If
...
-- snip --

Back in A97 we'd create a UserDefined function to display items in
listboxes if the list were large (see RowSourceType and user-defined
function in that topic in help).

If the list is small, we could also type the values.
Me.ListBox.RowSource = "test;Test1;Test2"

In more recent versions I believe you can use AddItem/DeleteItem. But
I've never seen assigning a column value like the way you are doing it.

Most often people create a rowsource and then assign it.
strSQL = "Select EmpID, EmployeeName From Employee"
Me.ListBox0.Rowsource = strSQL

[bruce]

Unfortunately you can't set the value of a column of a listbox like
you're describing. If what you are trying to do is simply indicate
that no records were returned by your query you could do this with a
message box, e.g.

If rs.RecordCount = 0 Then
MsgBox "No records found."
Else
Set Me.ListBox.Recordset = rs!Through
End If

You could also bypass using a recordset altogether by setting the
rowsource property of your listbox and using the listcount property to
tell you how many rows were returned:

Me.ListBox.Rowsource = "SELECT Through,Amount FROM ExpDaily WHERE
Date=" &
DateBox.Text

If Me.ListBox.ListCount = 0 Then MsgBox "No records found."

Finally, you could simulate the behavior you're describing by placing a
hidden label with caption = "None" control on top of your listbox,
positioning it at the upper left corner of the listbox, and toggling
whether or not it is visible based on the number of records returned:

Me.ListBox.Rowsource = "SELECT Through,Amount FROM ExpDaily WHERE
Date=" &
DateBox.Text

If Me.ListBox.ListCount = 0 Then
lblMyHiddenLabel.Visible = True
Else
lblMyHiddenLabel.Visible = False
End If

Hope this helps,
Bruce