Make-Table from Multi-Select Listbox?

R

Ray

Hello -

I need to create a form with a multi-select listbox (items are SELECT
DISTINCT from another table) which passes the user-selected items to a
make-table. How would this be done?

thanks in advance...
ray
 
A

Allen Browne

Loop through the ItemsSelected collection of the list box, and excute an
INSERT query statement for each one.

It might be more efficient to OpenRecordset and AddNew/Update for each item.
 
R

Ray

Hi Allen -

Thanks for the reply....but could you please be more specific? I'm
relatively new to VBA/Access and while I have a basic idea what you're
suggesting, I have no idea where to start with the coding...

rgds, ray
 
A

Allen Browne

This kind of thing:

Dim rs As DAO.Recordset
Dim varItem As Variant

Set rs = dbEngine(0)(0).OpenRecordset("Table2", dbOpenDynaset,
dbAppendOnly)

With Me.[NameOfYourListBoxHere]
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
rs.AddNew
rs![Field1] = .ItemData(varItem)
'rs![DateAdded] = Date
'etc for any other fields you wish to assign.
rs.Update
End If
Next
End With

rs.Close
Set rs = Nothing
 
Top