Help... On change textbox, populate listbox with values from table?

[Scaniafan]

Hello all,

I've got a form with an unbound textbox where I can enter a 7 digit shipment ID, for example 1175080

On the same form, I've got a listbox, which I want to display the values from a table, but filtered on "Like textbox"

What I have created is an on change event for my textbox

Private Sub ShipmentNumberFRM_AfterUpdate()

Me.ReasonCodeTBL.RowSource = "SELECT TBL_Master_Reason_Code_Per_Shipment.*
FROM TBL_Master_Reason_Code_Per_Shipment" _
            & "WHERE (((TBL_Master_Reason_Code_Per_Shipment.[Shipment Number]) Like " & Me.ShipmentNumberFRM.Text & "));"

End Sub

However, the listbox stays empty. I think that it has to do something with the Row Souce and Row Souce Type properties from the listbox, but I don't know what.

Can someone help me? I can't figure out what I'm doing wrong here.

 

[arnelgp]

Me.ReasonCodeTBL.RowSource = "SELECT TBL_Master_Reason_Code_Per_Shipment.*
FROM TBL_Master_Reason_Code_Per_Shipment" _
& "WHERE (((TBL_Master_Reason_Code_Per_Shipment.[Shipment Number]) Like '" & Me.ShipmentNumberFRM & "'));"

 

[Scaniafan]

Thank you for the quick reply, however, I still do not get result. My listbox remains empty.

There is data for the value I enter in the TBL_Master_Reason_Code_Per_Shipment, of that I'm sure.

-- Edit --

What should I put in the Record Source field for my form? If I put TBL_Master_Reason_Code_Per_Shipment in it, my form "locks". I am not able to put any shipment number in the unbound textbox.

If I build a query with below code and put this query as record source, I am able to enter data in the textbox, but I still don't get any result in the listbox...:banghead:

 

[Scaniafan]

It turned out I was over-engineering something that could be build easily with the table in a subform... More or less solved it