list box search and results display on list box

[issactang]

I would like to make a query from
transactions to see the patron history by enter a barcode.
I have made a recordset to get the patron barcode
in the same time to get the Id field from patrons
as this value is connected to the [checked out to] field in the transaction.


I have tried

         str = "SELECT [ID], [BookID], [Checked in date], [Checked Out Date], [DueDate] FROM Transactions ORDER BY [duedate] Where [checked out to] = " & _
            rspatrons![ID]
            
        
            Me.result_list.AddItem "ID; Title; Checked in date; checked out date ; due date "
            Me.result_list.RowSource = str

my result_list is a value list type
and it displays
ID book id checked in date checked outdate due date

in the first line without my search results.

did i miss anything ?
thanks.

[issactang]

i tried to do this

 str = "select  [books].[id],[books].[title],[transactions].[checked in date],[transactions].[checked out date],[transactions].[due date]" & _
"from transactions " & _
"where [transactions].[checked out to] = " & _
rspatrons![ID] & _
"inner join books " & _
" ON [transaction].[bookid] = [books].[id] " & _
"order by [transactions].[due date]"
             
            Me.result_list.RowSource = str

but still no record is displayed

[Rabbit]

That's because if you want to use a query as a row source, you can't use value list as the type.

[issactang]

i used table/ query
and used a wizard to help me configure the query instead with cross table.

and add the following code.

  str = "SELECT ID,[title],[checked in date],[checked out date],[duedate] " & _
             "FROM patronlendinghistory " & _
        "Where [checked out to] = " & _
             rspatrons!ID
             

            Me.result_list.RowSource = str