multiselect listbox to add data to table

Question

I am sorry that the forum did not have selection choice for visual studio 2017 and access db.

I have a form above.  the short of my question is:  I want to select a project # from dropdown, then select multiple companies from list box.  and have them added to the table.  ie… project#, company#.  I have a table for projects and 1 for companies, I have set up a table for linked projects/companies to keep records in.  you can have multiple companies involved in 1 project.

could someone please help with this?

my code is:

Private Sub UpdateSelected()

        Dim xPR As String      'variable for project id to use same # for each data row insert

        Dim xCR As String      'variable for company id to use selected # for each data row insert

        Dim xselected As Boolean

        xPR = PtblProjectIDComboBox.SelectedValue

        xPR = Convert.ToInt32(xPR)

        For i = 0 To ListBox1.Items.Count - 1     'count to stroll thru company db and check if selected

            If ListBox1.GetSelected(i) = True Then      'if selected then add record project # and selected company#

                xCR = Convert.ToInt32(ListBox1.ValueMember)

                xselected = True

                TableGroupProjectTableAdapter.Insert(xPR, xCR, xselected)     'add the record if it is valid

                'update db

                Validate()

                TableAdapterManager.UpdateAll(CMDB1copy2DataSet)

            End If

        Next i

    End Sub

Answer 1

Hi James, I'm an independent adviser and will try to help.

You have the right idea here, but the wrong methodology. You also are not using Access VBA. If you are using an Access front end, then your code won't work. If you are using a VB.Net front end, then you need to ask in a VB.Net forum.

For Access, I would use Code like:

Dim intPRID as Integer

Dim intCRID as Integer

Dim strSQL as String

Dim varItm As Variant

Dim ctl As Control

intPRID = CInt(Me.PtblProjectIDComboBox)

Set ctl = Me.lstMultiSelect



For Each varItm In ctl.ItemsSelected

    strSQL = "INSERT INTO tablename (ProjectID, CompanyID) " & \_

                    "VALUES(" & intPRID & ", " & ctl.Selected(varItm) & ");"

    CurrentDB.Execute strSQL

Next varItm

So this will loop through the select items in the list box and add a record for each Selected company.

Answer 2

Hi JamesB1260

I am an independent advisor and have a few questions for you with regards to the ListBox…

When somebody selects a Project ID do you want the ListBox to populate with:

• All Company IDs?
• Company IDs that have not been selected already?

If you want the ListBox to populate with all Company IDs, then do you want, existing Company IDs already assigned to the Project selected or just a full list of Company IDs.

If you want all Company IDs returned, do you want the ListBox to select these by default. If selected by defaultwhat happens if somebody deselects a Company ID, does if get removed from the Project ID Company ID Association?

A few links for you on inserting records:

https://www.codeproject.com/Questions/725824/In...

https://www.homeandlearn.co.uk/NET/nets12p4.html

https://docs.microsoft.com/en-us/visualstudio/d...

A few links on deleting records:

https://www.homeandlearn.co.uk/NET/nets12p11.html

https://www.codeproject.com/Questions/1250775/H...

This forum is more intended for direct VBA, not Visual Studio.

Is there any reason you are using Visual Studio and not using an Access Form?

Please let me know if you have any queries or questions.

Kind Regards

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

Answer 3

I have the project combobox populated from the projects db and the company list box populated from the company db.

what I want to do simply be able to use my small form and select a project then select company(s) from the list box.  click the add button and have only the selected items added to a 3rd db which I have created.  it has projectID and companyID in it.  I just store the ID's for later use lookup

in construction, we have a job and on the job there are several to many subs.....

so my logic is to be able to select 1 project and multiselect subs.  so to say...….

to link them to the job.

does this make sense??