Add new records to table from listbox form

[DaveCrate]

New to forms and VB
Access2000

I have a form with a listbox containing 4 columns and 5 different suppliers from SupplierTable(SELECT SupplierTable.VendorID, SupplierTable.Supplier, SupplierTable.Vendor, SupplierTable.PO FROM SupplierTable;
............................................................

Form Button:
Private Sub Button_New_Request_Click()
On Error GoTo Err_cmdqryAppendSupplierRequest

Dim stDocName As String

stDocName = "qryAppendSupplierRequest"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True

Exit_cmdqryAppendSupplierRequest:
Exit Sub

Err_cmdqryAppendSupplierRequest:
MsgBox Err.Description
Resume Exit_cmdqryAppendSupplierRequest
End Sub
............................................................

I want to select a supplier and INSERT into NewRequestTable
with a append query:
(INSERT INTO NewRequesttable ( SUPPLIER, VENDOR, PO )
SELECT SupplierTable.Supplier AS Expr1, SupplierTable.Vendor AS Expr2, SupplierTable.PO AS Expr3
FROM SupplierTable
............................................................

How do I insert the selection from the listbox to the NewRequestTable table? I want to eventually be able to enter a quantity(i.e. 5) for the supplier and update the NewRequestTable with 5 entries.

Currently NewRequestTable is inserted with the entire listbox

These forums are a great source of knowledge and ideas
Thank you for your help!!

 

[batteam]

Here's some code that will fill a defined variable with a list box selection. You can then use that variable wherever it needs to go to be inserted into your table. This code assumes you want to select only one field of data in only 1 row in your list box (at a a time):

Dim varitems as variant
Dim variablename as string(?)

With lstboxname
For each varitems in .ItemsSelected
variablename = .Column(0, varitems)
Next varitems
End with

Now you have your data field in a variable and you can do with it what you want.

IMPORTANT: this snippet uses the first column (.Column 0) in the list box. You would substitute the column number that applies to where in the list box your field is that you want to add to the table. For example, if the field is the four one from the left, you would use: .Column(3, varitems) in the snippet above.

Hope this helps. Good luck.

 

[DaveCrate]

Thanks for the reply. This what I have now. It updates the table with all of Supplier_List

SUPPLIER PO VENDOR
Rock 92400 308
Cart 40000 599
ABC 92400 408
Star 20194 399
DEF 20393 725
............................................................

Private Sub Button_New_Request_Click()
On Error GoTo Err_cmdqryAppendSupplierRequest

Dim stDocName As String
Dim varSupplierList As Variant
Dim stSupplier As String
Dim stPO As String
Dim stVendor As String


With Supplier_List
For Each varSupplierList In .ItemsSelected
Supplier = .Column(1, varSupplierList)
PO = .Column(2, varSupplierList)
Vendor = .Column(3, varSupplierList)
Next varSupplierList
End With


stDocName = "qryAppendSupplierRequest"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True

Exit_cmdqryAppendSupplierRequest:
Exit Sub

Err_cmdqryAppendSupplierRequest:
MsgBox Err.Description
Resume Exit_cmdqryAppendSupplierRequest
End Sub

Not sure what I should use for variable name.
If I select row: ABC 92400 408 then query to insert to table.
Thanks for the help.

 

[batteam]

Can you explain more what you meant in your first question, last part when you say: "I want to eventually be able to enter a quantity(i.e. 5) for the supplier and update the NewRequestTable with 5 entries."

Where is the 'quantity' in all this and what do you mean by updating the table with "... 5 entries."? Is 5 a supplier code? And what are the 5 entries you want to update in the table (or do you mean add to?). Thanks and let me know.

 

[Lightning]

DaveCrate

Add a WHERE condition to your SQL to select only the currently selected item in your listbox record. That way only that item will be added to the table.

Something like:

INSERT INTO NewRequesttable ( SUPPLIER, VENDOR, PO )
SELECT SupplierTable.Supplier AS Expr1, SupplierTable.Vendor AS Expr2, SupplierTable.PO AS Expr3
FROM SupplierTable
WHERE (((SupplierTable.Supplier)='" & Me.MyListbox.Selected & "'));"

HTH
Lightning

 

[DaveCrate]

If I selected the Supplier named Rock from the form listbox and entered how many records I wanted it to produce in a textbox called "How many orders?". The table (NewRequestTable) would consist of 5 new duplicated records like this:

SUPPLIER PO VENDOR
Rock 92400 308
Rock 92400 308
Rock 92400 308
Rock 92400 308
Rock 92400 308

Does this make more sense? Later in the process another user may update this table with other fileds like "unloaded by", "date unloaded".

Lightning, I'm working on your suggestion.
Thanks for the responses. I appreciate them.

 

[DaveCrate]

Lightning,
Got it! Thanks for your help

INSERT INTO NewRequesttable ( REQUESTDATE, SUPPLIER, VENDOR, PO )
SELECT Now() AS Expr4, SupplierTable.Supplier AS Expr1, SupplierTable.Vendor AS Expr2, SupplierTable.PO AS Expr3
FROM SupplierTable
WHERE (((SupplierTable.VendorID)=Forms![Dave New Reaquest]!Supplier_List));

Hopefully Batteam will have a response about the previous post.

Thanks Again!!!

 

[Lightning]

Glad to help!