Adding records from a List Box

[Softweb]

Hi. I would appreciate help on how to do this:

I am running MS Access 16 and I have a Table (tblPurchase) and a Form containing the following 3 controls:
ListBox (lstThisPurchase)
ComboBox (cboProductRange
Command Button (cmdUpdateRecords)

Operation:
Items will be added to the List by selecting each one in turn from the Combo Box menu containing about 20 items. Say for example 3 of the items are added to the List, I want each of the 3 items in the List to be added as records to my Table (tblPurchase) when I click the Update Records button (cmdUpdateRecords).

 

[jdraw]

For clarity, you have a table, and a combo. You select something(s) from the combo, move them to the listbox, then do something else, then insert those data into you table.

What is the purpose of the listbox exactly?

 

[sneuberg]

There seems to be at least one table missing from this setup. That being a table for the row source of the combo box or do you intend on putting the combo box items in a fixed value list. Assuming you add a table for the item to select from wouldn't a listbox be better than a combo box. With a list box the user can select multiple items. With that in place your could have buttons that move selected items from or to the tables and display the results of these moves in the listboxes. The would work like the Access wizards that have you selected fields, etc.

If you are interested in this approach I'll see if I can give you some details of what the code behind the buttons would be.

 

[Softweb]

Hi
Thanks for the replies. Each time an item is selected from the combo, it is added to the list. When all the required items have been added, the command button sends the items to the table. The List is just a way of seeing what has been selected before it is written to the table. I have simplified my description, but the combo will be populated from another table using a query.

 

[MarkK]

Why not add items to the table directly when they are selected from the combo? Add them with a pending flag set to true. Then, drive the list using a query that shows pending items, which would be simpler.

SELECT * FROM tOrderDetail WHERE IsPending AND OrderID = 1234

It doesn't make sense to me to use a listbox as temporary storage for an in-progress shopping cart, since adding to and deleting from a table is so much simpler. And populating a listbox from a query is similarly simple.
Finally, if the user approves the list, simply edit all the pending flags to false in the table.

UPDATE tOrderDetail SET IsPending = False WHERE OrderID = 1234

That would be my recommendation for this process, FWIW.

 

[Softweb]

Thanks Mark.
I understand what you are saying about the simplicity of the SQL, but my background is programming and my knowledge of applying SQL is limited. Hence using the list in the way I have suggested, which is the easiest way for me at the moment. I can see what your SQL is doing but I am not sure how it would be driven by the combo and command button. It would be great to be able to do that, so any help in that way would be appreciated. Also, I would need to see each item on the List as it is selected by the combo, so the query would have to run each time the combo is selected. Thanks again

 

[sneuberg]

Hence using the list in the way I have suggested, which is the easiest way for me at the moment.

Once you got into it I don't think you would find adding items from a combo box to a listbox's value list any easier than adding the items from the combo box to a table that form's the listboxes' row source, but what would really make harder for you is extracting the values from the listbox and adding them to a table.

To do it the way MarkK suggested you would insert the values from the combo box into the table with the order details in the combo box afterupdate and then requery the listbox with code something like this:

 Private Sub cboProductRange_AfterUpdate()

CurrentDb.Execute "INSERT INTO tblOrderDetail (OrderNo, ItemNo, IsPending) VALUES ( " & Me.cboOrder & "," & Me.cboProductRange & ", True)", dbFailOnError
Me.lstThisPurchase.Requery

End Sub

The row source of the listbox would be a query of the tblOrderDetail table joined with perhaps the table or the combo box row source and filter by IsPending and the order no. See qrylstThisPurchaseRowSource in the attached database

The command button would update the tblOrderDetail table as MarkK suggested with code something like:

Private Sub cmdUpdateRecords_Click()

CurrentDb.Execute "UPDATE tblOrderDetail SET IsPending = False WHERE OrderNo = " & Me.cboOrder
Me.lstThisPurchase.Requery


End Sub

If you look at the attached database you will notice a few problems it has that you will need to address in your design. First it needs error checking, e.g., order needs to be selected, Also I think you will want some way of removing items from the pending order and perhaps adding code to prevent the same item from being added to the same order twice.