Populating multiple fields from single listbox

springa

Registered User.
Local time
Tomorrow, 00:48
Joined
Dec 1, 2008
Messages
27
Afternoon all,

I've been battling with this, and can't seem to find a solution on the net. I have a form called "WHDispatch", on this form I have a list box ("NewOrders") based on a query listing outstanding orders, with the following fields: "Order Number", "Item ID", "Quantity", "Order Date" & "Date Required".

On selecting a record in this listbox, I would like this to do following:
Store "Order Number" and "Item ID" in their respective fields in "Inventory Movement" table on closing the form. I have been trying to get the listbox to update other textboxes on the form with this information as a possible workaround but have had no luck there...

Any help would be greatly appreciated! Thanks so much!
 
One way I have found to get a list box to update the other records on a from is to use the 'requery method' in VBA. The VBA is triggered by the 'After Update' event.

You would insert the code in the 'after update' properties of your listbox.
 
Hi PaulJR,

Thanks for your feedback, how exactly would I go about doing that, I'm clueless when it comes to VBA... Thanks again.
 
After you have made a selection from your list box you can refer to individual columns in the selected row

Me.Txt1 = Me.ListBox.Columns(nIndex,1)

Where nIndex is the selected index of the listbox and 1 represents which column you want the data. Remember that columns start at 0 not 1 in VBA

Some times tyou can get this to work without using the nIndex approach so try both.
 
I was making the assumption here that you are using a query (from a table unspecified in your post) to assist in populating some fields on the form, that will save data into your "Inventory Movements" table. This is fine for having fields in the form coming from lookup tables/queries to restrict what can be selected.

However I do wonder if you are copying some of these same fields from one table to another. If so, it might be worthwhile looking at your tables again to ensure fields are not duplicated across tables. Can you provide your table names and what fields they contain? This will help me see where we're going.
 
Hi PaulJR,

Tables are as follows:
Inventory Movement Table
Fields: ID(Autonumber); Date; Entered By; ItemID; Transaction Detail; Order Number; CR Number; Warehouse Location; UnitsOpening; UnitsReceived; UnitsDispatched; UnitsDisposedOf; Remarks

Second Table Is For Purchase Order Detail, "Orders":
Fields: Order Number(Autonumber); Item ID; Quantity; Ordered By; Order Date; Order Time; Date Required

My listbox is based on a query "New Orders":
Fields: Order Number; Item ID; Quantity; Order Date; Date Required

This query is based on another query which detects PO numbers that do not appear in both tables (Inventory Movement & Orders). The SQL looks like this:

SELECT Orders.[Order Number], Orders.[Item ID], Orders.Quantity, Orders.[Order Date], Orders.[Date Required]
FROM Orders LEFT JOIN [Inventory Movement] ON Orders.[Order Number]=[Inventory Movement].[Order Number]
WHERE [Inventory Movement].[Order Number] IS NULL;

Thanks
 
Thanks all for your suggestions, DCrake's suggestion worked after some fiddling around.
 

Users who are viewing this thread

Back
Top Bottom