Need help getting values to change in a list box.

[Richard Penfold]

I am developing an order tracking database, which to keep this explanation simple, consists of 'Orders' table, 'Order Details' table, 'Deliveries' table & 'Inventory' table.
There are one-to-many relationships from Orders to 'Order Details, Orders to Deliveries and Orders to Inventory.
I have forms to enter and track customer orders and inventory transactions; these work fine but I am having difficulty getting a list-box control to work the way I want on the deliveries form.
I have created a bound split form using the forms wizard. The main form is bound to Orders and the subform bound to Inventory. The intention is to pick from Inventory, items for delivery.
I have added a list-box to the main form to display item order code and quantity from the Order details table. The query behind the list-box has a criterion to select from Order Details table where order number from Orders table equals the order number displayed in a text-box on the form. The difficulty I have is that this list-box does not update when I move between orders - It shows only the first Order Details data. Yet I have other text-boxes on the main form bound to the same Order Details table that do work as expected. Can someone help me get this part of the form working please.

[puppydogbuddy]

You did not provide any of your code, so I am guessing on this and using illustrative object names. What I am telling you to do is requery the listbox (located on the main (parent) form) when you move between orders.

Private Sub OrderNumber_Change()
Me.Parent.YourListbox.Requery
End Sub

[Richard Penfold]

I did not post my code in case it lead people astray - I was hoping for an answer to the problem, not a fix for my clumsy code. I tried the code you provided - It looks like this:

Private Sub OrderNumber_Change()
Me.Deliveries.List27.Requery
End Sub

My code was similar

Private Sub OrderNumber_Change()
Me.List27.Requery
End Sub

The code is attached as an event to the OrderNumber text box. Neither of them work. There are no errors, no change in the list box, it just seems to ignore the code. Where do we go from here?

[puppydogbuddy]

At this point, I think the most likely reason the requery did not work is that the syntax is incorrect. You stated that the listbox is on the main form, which I assume is named deliveries. If the order form is a subform of the main form and the code is placed behind the Ordersubform, the syntax would be as follows:
Private Sub OrderNumber_Cha nge() 'place code behind the OrdersSubform
Me.Parent.List2 7.Requery
End Sub

If this does not work, please list your forms and subforms by name, and state which one has the listbox and which one has the requery code.
example
MainForm Deliveries (has listbox)
Subform1 Orders (has requery code)
Subform2
Something else ....you need to place a control break in yourOrderNumber _Change proc to determine if it is firing.

[Richard Penfold]

Thanks for staying with this,,, In response to your last posting:
Main Form is named Deliveries
Main Form is bound to Transactions table
Main Form has Order Number text box named 'TransactionRef erence'
Main Form has Unbound list box named 'List27'


Subform is named 'Deliveries Subform'
Subform is bound to Inventory table

[puppydogbuddy]

Richard,
I believe the code below should work, provided that the TransactionRefe rence_Exit() event is firing. I assume that this textbox is unbound, and therefore you can not use the change or afterUpdate events to fire the requery code. It should fire in the textbox's exit event if you are entering the OrderNumber into the TransactionRefe rence textbox. If you do not enter the OrderNumber in that textbox, please tell me where it is entered.

Private Sub TransactionReference_Exit()
Me.List27.Requery
End Sub

[Richard Penfold]

Thanks again... I have tried the new code but still no joy.
To recap and expand on a couple of things...
The 'Deliveries' main form is bound to and reads data from the 'Transactions' table.
As I move from record to record, the order number (entered previously) in the 'TransactionRef erence' (bound) text box, changes.
The 'TransactionRef erence' text box is bound to the 'Transactions' table, 'TransactionRef erence' field.
'List27' (unbound) reads 'OrderCode' from the 'Order Codes' table and 'QtyOrdered' from the 'Transaction Details' table.
The 'Transactions' table has a one-to-many relationship with 'Transaction Details'
'Transaction Details has a one-to-many relationship with 'Order Codes'.

The criteria placed on 'List27 query is to select based on the value in the 'TransactionRef erence' text box on my form
The query code is listed here:

"
SELECT Transactions.TransactionReference, [Transaction Details].OrderCodeID, [Order Codes].OrderCode, [Transaction Details].QtyOrdered
FROM Transactions INNER JOIN ([Order Codes] INNER JOIN [Transaction Details] ON [Order Codes].ID = [Transaction Details].OrderCodeID) ON Transactions.ID = [Transaction Details].TransactionsID
WHERE (((Transactions.TransactionReference)=[Forms]![Deliveries]![TransactionReference]));
"

I have created a separate query using the code above and tested it by running the query after I move to the next record and the test query works just fine.

I think your code is good and I suspect that the event procedures are not firing (same on different computers). How can I test if they are? Your previous post said to put a control break into the code - I do not know how to do this. If we establish that the event procedures are not firing, how do we fix it?

[Richard Penfold]

Success!... of sorts.

I have taken a further look at event procedures in MSDN and I find that I have to type something into a text field before the 'On Change" event (or many other) works. After stumbling around with different event types I arrived at this:
Set the 'On Got Focus' event...

Private Sub TransactionReference_GotFocus()
Me.List27.Requery
End Sub

This will work but only if you click in the TransactionRefe rence text box after moving between records. So I added the following:
Set the main form 'On Current' event...
Private Sub Form_Current()
Me.TransactionR eference.SetFoc us()
End Sub

This forces the focus to return to the TransactionRefe rence text box after moving between records, which in turn re-queries the list box

Seems to work but is it the right solution? - Is there something more elegant that I should be using?

[puppydogbuddy]

Richard,

The fact that you are not able to fire the change and AfterUpdate events is why I was trying to tell you in my previous post to try the Exit event. Quote from my previous post:"It should fire in the textbox's exit event if you are entering the OrderNumber into the TransactionRefe rence textbox. If you do not enter the OrderNumber in that textbox, please tell me where it is entered."

My question remains>>>>>> where/how do you orignate the change in the OrderNumber...v ia the listbox or via the textbox or how ??? The answer to this question will help determine if there is a more elegant solution. Even if there is a more elegant solution, congrats to you for finding a work around.

[Richard Penfold]

Seems like I didn't do a good enough job explaining how this database works. Just shows we need to test understanding at every stage - And thats with us using a common language.
The database has 3 functions
1 - Order Processing
2 - Inventory management
3 - Deliveries register
A customer order number is entered during order processing. This number is entered to the 'TransactionRef erence' field of the 'Transactions' table via an 'Orders' form.
When we come to deliveries, the 'TransactionRef erence' field is displayed on the 'Deliveries' form. The number in the text box on the 'Deliveries' form changes as I move from record to record but it is not entered or overtyped here.

[puppydogbuddy]

Richard,
I believe this is the elegant solution you want. Place this code in the current event of the [Deliveries Subform], not the Main Form. It should force a requery as you move from record to record on the [Deliveries Subform]. I am assuming that [Deliveries Subform] is the name of the source object <a form> that is embedded in the subform control. The subform container is on the main form and the subform source object is the form that is usually referred to as the subform. Often the subform control on the main form and the subform source object have the same name, but not always....

Private Sub Form_Current()                    'current event of [Deliveries Subform]
Me.Parent.List27.Requery 
End Sub

[Richard Penfold]

The list box is now working well. Thanks for your time and patience.mate.
Please keep your eyes open for future postings from me. Never having developed a database before, I am sure to hit more submerged logs as I try to sail these (for me) uncharted waters.

[puppydogbuddy]

Richard,
You are welcome. I am glad your list box problem is resolved. I am sure I'll see you around. Take care.

pDog