Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    California
    Posts
    5

    Red face Unanswered: populating a database from a list box

    Hi Access Masters,

    I am a beginner so please bare with me. I built a DB in Access with three tables. One table is coprofile (list of company profiles), the second is industry (list of industries), and the third is IndCo (the junction table) since one company can belong to many industries and vise versa. I made a form with a list box that uses (SELECT DISTINCT IndCo.industryID, industry.industryname FROM IndCo, Industry) so that the listbox is populated with distinct industry names. However, I want the user to be able to select multiple industries for a company on the form and after closing the form, the tables should be populated. My list box allows me to select multiple selections but after closing the form....there is NO CHANGE in the tables regarding the selections I made. Please help!!!! Im dying here!!!!!

    Robert

  2. #2
    Join Date
    Oct 2003
    Posts
    66
    Though i am not by any means an Access Master i would like to try and help.

    I am having a hard time understanding what you are doing and trying to do. Okay so you have a listbox on a form. This list box is populated by a SQL statement that brings in the types of industries. You want to have the user select multiple industries for the company being entered and then have thoses seleciton entered into a table?

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: populating a database from a list box

    Originally posted by lost_in_access
    Hi Access Masters,

    I am a beginner so please bare with me. I built a DB in Access with three tables. One table is coprofile (list of company profiles), the second is industry (list of industries), and the third is IndCo (the junction table) since one company can belong to many industries and vise versa. I made a form with a list box that uses (SELECT DISTINCT IndCo.industryID, industry.industryname FROM IndCo, Industry) so that the listbox is populated with distinct industry names. However, I want the user to be able to select multiple industries for a company on the form and after closing the form, the tables should be populated. My list box allows me to select multiple selections but after closing the form....there is NO CHANGE in the tables regarding the selections I made. Please help!!!! Im dying here!!!!!

    Robert
    Multiple list box selections will be most easily added to the table with VBA code and a recordset based on the IndCo table.

    Gregg

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: populating a database from a list box

    Originally posted by lost_in_access
    Hi Access Masters,

    I am a beginner so please bare with me. I built a DB in Access with three tables. One table is coprofile (list of company profiles), the second is industry (list of industries), and the third is IndCo (the junction table) since one company can belong to many industries and vise versa. I made a form with a list box that uses (SELECT DISTINCT IndCo.industryID, industry.industryname FROM IndCo, Industry) so that the listbox is populated with distinct industry names. However, I want the user to be able to select multiple industries for a company on the form and after closing the form, the tables should be populated. My list box allows me to select multiple selections but after closing the form....there is NO CHANGE in the tables regarding the selections I made. Please help!!!! Im dying here!!!!!

    Robert
    The quickest and easiest way is to use a subform based on the IndCo table that has a combo box for the industry. The parent form will have the company details on it. The user can then enter as many industries as they like for each company. No VBA is required provided you have set the relationships correctly

  5. #5
    Join Date
    Sep 2003
    Location
    California
    Posts
    5
    adrkoehler,

    thank you for your interest in helping me. The way you described my problem is precisely correct. I look forward to hearing you response again.

  6. #6
    Join Date
    Sep 2003
    Location
    California
    Posts
    5

    Re: populating a database from a list box

    Originally posted by justin_tighe
    The quickest and easiest way is to use a subform based on the IndCo table that has a combo box for the industry. The parent form will have the company details on it. The user can then enter as many industries as they like for each company. No VBA is required provided you have set the relationships correctly
    Thank you all for your help thus far. I have thought about using the subform but i already have one subform that links to the table which lists company contacts. If there some other way to go around this. I was actually hoping to have a list box on the form to have multiple selections. I think the problem is that it is a many-to-many relationship and i cant seem to find a way to connect the listbox and the table. SORRY IF THIS MAKES NO SENSE AS I AM LOST AND FEEL AS THOUGH I AM MAKING NO SENSE!!

  7. #7
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: populating a database from a list box

    Originally posted by lost_in_access
    Thank you all for your help thus far. I have thought about using the subform but i already have one subform that links to the table which lists company contacts. If there some other way to go around this. I was actually hoping to have a list box on the form to have multiple selections. I think the problem is that it is a many-to-many relationship and i cant seem to find a way to connect the listbox and the table. SORRY IF THIS MAKES NO SENSE AS I AM LOST AND FEEL AS THOUGH I AM MAKING NO SENSE!!
    Using a listbox called lst1:


    Dim rs as recordset, db as database, varItem as variant
    Dim iComID as integer

    Set db = CurrentDb
    Set rs = db.OpenRecordset("YourTable", dbOpenDynaset)
    If you are going to add new records to the recordset then
    iComID = Me.CompanyID (Whatever your Company is identified by)
    If lst1.ItemsSelected.Count = 0 Then
    MsgBox "There are no items selected"
    Else
    For Each varItem In lst1.ItemsSelected
    rs.AddNew
    rs.Fields("CompanyID") = iComID
    rs.Fields("Industry") = lst1.Column(0, varItem)
    rs.Update
    Next
    End If

    You can try something like this. Maybe after some modifications it can work for your purposes.

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •