Hi all,
So i'm having an unexpected issue getting a listbox to query data from another table. It seems like it should be straightforward? Hopefully it's just a syntax issue?
So, in my main form ( PO_Creation_Form ), I have a listbox that I would like to show items from another table that are associated with the record displayed in the main form.
- In the main form the field of interest is "ContractNumber", and is stored in a control "ContractNumberTextbox".
- The secondary table is "GoodsReceived", and the field to match is called "AssociatedPO". The field to show, from GoodsReceived, is "GRN", which is also the primary key.
If I create an unbound textbox on the form, and manually enter a value for the "ContractNumber" then it works as expected:
... but I can't seem to get it to work with the bound textbox control. I've tried (among many others):
... among many others. I also tried updating the rowsource for the listbox in the form's "before update" event, e.g.:
Any help would be greatly appreciated. This seems like it should be fairly straightforward?
Thanks in advance!
So i'm having an unexpected issue getting a listbox to query data from another table. It seems like it should be straightforward? Hopefully it's just a syntax issue?
So, in my main form ( PO_Creation_Form ), I have a listbox that I would like to show items from another table that are associated with the record displayed in the main form.
- In the main form the field of interest is "ContractNumber", and is stored in a control "ContractNumberTextbox".
- The secondary table is "GoodsReceived", and the field to match is called "AssociatedPO". The field to show, from GoodsReceived, is "GRN", which is also the primary key.
If I create an unbound textbox on the form, and manually enter a value for the "ContractNumber" then it works as expected:
Code:
SELECT [GoodsReceived].[GRN] FROM GoodsReceived WHERE [GoodsReceived].[AssociatedPO] = TmpTextbox;
... but I can't seem to get it to work with the bound textbox control. I've tried (among many others):
Code:
SELECT [GoodsReceived].[GRN] FROM GoodsReceived WHERE [GoodsReceived].[AssociatedPO] = ContractNumber;
SELECT [GoodsReceived].[GRN] FROM GoodsReceived WHERE [GoodsReceived].[AssociatedPO] = Me.ContractNumberTextbox;
SELECT [GoodsReceived].[GRN] FROM GoodsReceived WHERE [GoodsReceived].[AssociatedPO] = Me.ContractNumberTextbox.Text;
SELECT [GoodsReceived].[GRN] FROM GoodsReceived WHERE [GoodsReceived].[AssociatedPO] = Forms![PO_Creation_Form]!ContractNumberTextbox;
SELECT [GoodsReceived].[GRN] FROM GoodsReceived WHERE [GoodsReceived].[AssociatedPO] = Forms![PO_Creation_Form]!ContractNumberTextbox.Text;
SELECT [GoodsReceived].[GRN] FROM GoodsReceived WHERE [GoodsReceived].[AssociatedPO] = '"Forms![PO_Creation_Form]!ContractNumberTextbox"'
SELECT [GoodsReceived].[GRN] FROM GoodsReceived WHERE [GoodsReceived].[AssociatedPO] = ' " & Forms![PO_Creation_Form]!ContractNumberTextbox & " ';
SELECT [GoodsReceived].[GRN] FROM GoodsReceived WHERE [GoodsReceived].[AssociatedPO] Like '" & Forms![PO_Creation_Form]!ContractNumberTextbox & "*';
... among many others. I also tried updating the rowsource for the listbox in the form's "before update" event, e.g.:
Code:
Dim sql As String
sql = "SELECT GRN, AssociatedPO FROM GoodsReceived WHERE AssociatedPO Like '" & Me.ContractNumberTextbox.Text & "*' ORDER BY AssociatedPO"
Me.TestListbox.RowSource = sql
Any help would be greatly appreciated. This seems like it should be fairly straightforward?
Thanks in advance!
Last edited: