Issue with a Listbox query.

[Duff-Man]

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:

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):

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.:

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!

 

[cheekybuddha]

Try setting the RowSource of TestListbox to:

SELECT [GoodsReceived].[GRN] FROM GoodsReceived WHERE [GoodsReceived].[AssociatedPO] = [ContractNumberTextbox];

 

[Duff-Man]

Hi all,

I solved the issue, at least partially - it wasn't actually to do with the query, it was the listbox itself not updating upon switching records.

For now I've added a "TestListbox.Requery" on every command button that can change a record.

... is there a more direct way to add commands when a form moves from one record to the next? Which of the Form events are reliably performed when the form moves to a new record? I tried a few that seemed promising, but no update for the textbox.

Alternatively, is there a way to bind the listbox in such a way that it automatically changes focus when the records in the form are updated?


Thanks to everyone who read this, and I hope this can save someone else some frustration in the future!

 

[Gasman]

Form Current event runs when you change record?
If you set the rowsource to a new value, no need to requery, see what I posted in another thread here in this site.