CODEWITHSUNDEEP
vbams-accesslistbox
Alex
Alex

Reputation: 283

Microsoft Access - Add all items from a listbox into a table without having to be selected?

I'm making a data entry form that allows me to add new businesses and details for that business, before importing them into their respective tables. For the most part, I have completed it all.

However, there are some pieces of information that require more than one input. For example - a business could have multiple telephone numbers/documents/staff members etc.

I have set up a text box and an add/remove button which adds/removes the text in the textbox to a listbox. I want to be able to import all of the items in the listbox, without having to select them into a table. Is this possible? Most of the answers that I've found online require you to have the items selected.

An example piece of code that I have on the import button is show below. This code adds the address details of the business to the 'Business Address' table.

'Set Table to 'Business Address' then add the fields to the table
Set RST = CurrentDb.OpenRecordset("Business Address", dbOpenTable)
RST.AddNew
RST![Business Name] = Me.txtBusinessName
RST![Address] = Me.txtAddress1 & ", " & Me.txtAddress2
RST![Town/City] = Me.txtTownCity
RST![Postal Code] = Me.txtPostalCode
RST.Update
RST.Close

I was thinking some sort of for loop to add all items in the listbox to a table?

Logically speaking (This is not a real example of working code, just something I imagine this might look like if possible?):

Set RST = CurrentDb.OpernRecordset("Business Telephone", dbOpenTable)
For each item in TelephoneListBox
    Rst.AddNew
    Rst![Business Name] = Me.txtBusinessName
    Rst![Telephone] = Me.TelephoneListBox.Column(0)

I'm not sure how to go about it and if it can actually be done without the items being selected? Any ideas?

Upvotes: 0

Views: 3087

Answers (2)

Nathan_Sav
Nathan_Sav

Reputation: 8531

Dim l As Long

For l = 0 To Me.List0.ListCount - 1

    '   Debug.Print Me.List0.ItemData(l) or me.list0.column(0,l)

Next l

This will loop through the items in the list.

Upvotes: 1

Alex
Alex

Reputation: 283

To improve on Nathan_Sav's answer:

Dim i As Long
Set RST = CurrentDb.OpenRecordset("Business Telephone", dbOpenTable)

For i = 0 To Me.TelephoneListBox.ListCount - 1
        RST.AddNew
        Rst![Business Name] = Me.txtBusinessName
        RST![Telephone] = Me.TelephoneListBox.Column(0, i)
        RST.Update

Next i

Adds the value of 'txtBusinessName' to the table 'Business Telephone', under the field specified (Business Name), for each item that is in the listbox.

Also adds all items in the listbox (TelephoneListBox) to the same table under the field specified (Telephone).

Upvotes: 0

Related Questions