Results 1 to 13 of 13
  1. #1
    astrelow is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    6

    Populate Text Boxes by ComboBox Choice within Form

    Hello,


    My goal here is to create a contact sheet when I choose different companies. I currently have 2 tables set up. One table for a master list of all the companies. And then a contacts table that has a company field which pulls from the master list. The contact table also has ContactName, WorkFunction, PhoneNumber, and Email. What I would like to have is the Company combo box shown below show the companies. Then populate the text boxes below with the contact(s) information. The current issue I have is the combo box is showing every entry within the contact table instead of 1 entry for each business. For example, if Company A has 2 contacts, the combo box is showing 2 Company A's instead of just one.

    Click image for larger version. 

Name:	CurrentForm.png 
Views:	11 
Size:	8.1 KB 
ID:	45164


  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    to quick view contact info, make a form (like yours shown) with an UNBOUND combo box to pick the company. The combo has all the CoID & CoNames in it. (bound to CoID)
    the form shows ALL records,
    when user picks the name from the combo box, filter the record:
    Code:
    sub cboBox_Afterupdate()
    If IsNull(cboBox) Then
      Me.FilterOn = False
    Else
        'if numeric
      Me.Filter = "[CompanyID]=" & cboBox 
      Me.FilterOn = True
    End If
    end sub
    then only that data shows.




    for a SHIPPING form :
    make a subform bound to CONTACTS table
    put all the company info in the form but NOT that combo box. That goes on the master shipping form and the combo box is bound to SHIPTO field.

    In the master form the subform is bound to the combo box.
    use picks company ,and all the info will show.

  3. #3
    astrelow is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    6
    Thank you for your help on this. How would I then populate the text boxes with each contact associated to that company? I believe I can do it if there is only one contact but when there are multiple, I don't know how to do it.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a copy of the db with just a few dummy records
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    astrelow is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    6
    Hey Bob, Here is a sample of what my two tables currently look like.

    tblCompanies:
    Company ID Company
    1 Arizona
    2 California

    tblContacts:
    ContactID Company ContactName WorkFunction PhoneNumber Email
    1 Arizona Joe Thomas Janitor 123-456-7891 j.thomas@az.com
    2 California Jessica Thorns Construction 198-765-4321 j.thorns@ca.com
    3 Arizona Hank Butter Engineering 753-951-7812 h.butter@az.com

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Try this. The combobox must be unbound. Code will create a value list for the combobox.
    I assume you have code for the after_update event to update the company details.
    The combobox format is one column with a single column width specified.

    Code:
    '---------------------------------------------------------------------------------------
    ' Method : Form_Load
    ' Author : davegri
    ' Purpose: Define the value lists for combo box cboCompany
    '   based on the current contents of tblCompany
    '---------------------------------------------------------------------------------------
    Private Sub Form_Load()
        strList = "ALL;"
        With Me.cboCompany
            With CurrentDb.OpenRecordset("SELECT DISTINCT Company FROM tblCompany;")
                Do Until .EOF
                     strList = strList & !Company & ";"
                    .MoveNext
                Loop
            End With
            .RowSourceType = "Value List"
            .RowSource = strList
        End With
    End Sub

  7. #7
    astrelow is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    6
    Davegri, when I attempt that it seems to just show all the companies together with some of them being on the same row.

    Is there a way to save the Company choice from the combo box, create a temporary table (maybe using query?) that only has contacts related to that company, and then fill out the text boxes based on the rows? So like Row 1 will go to the first contact info, row 2 will go to the second info, etc.

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by astrelow View Post
    Hey Bob, Here is a sample of what my two tables currently look like.

    tblCompanies:
    Company ID Company
    1 Arizona
    2 California

    tblContacts:
    ContactID Company ContactName WorkFunction PhoneNumber Email
    1 Arizona Joe Thomas Janitor 123-456-7891 j.thomas@az.com
    2 California Jessica Thorns Construction 198-765-4321 j.thorns@ca.com
    3 Arizona Hank Butter Engineering 753-951-7812 h.butter@az.com
    I don't think your tables are not properly normalized.
    The "tblContacts" table should have a Foreign Key field that is related to the Primary Key of "tblCompanies".
    I would also advise against having spaces in the name of any object (e.g. "Company ID")
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by astrelow View Post
    Davegri, when I attempt that it seems to just show all the companies together with some of them being on the same row.

    Is there a way to save the Company choice from the combo box, create a temporary table (maybe using query?) that only has contacts related to that company, and then fill out the text boxes based on the rows? So like Row 1 will go to the first contact info, row 2 will go to the second info, etc.
    I can't envision some of what you are describing. Can you post some images of the problem? What you seem to be describing is a form/subform arrangement. Bob Fitz has requested a copy of the DB, in which he can set up that arrangement in no time.

  10. #10
    astrelow is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    6
    Bob, the Company field in tblContacts is a lookup field to the tblCompanies if that's what you mean. If it is not, can you explain further? Also, thank you for pointing out the space!

  11. #11
    astrelow is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    6
    Quote Originally Posted by Bob Fitz View Post
    Can you post a copy of the db with just a few dummy records
    Bob, I am currently getting a sample database together.

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You should NOT be using Lookup Fields in your tables.

    The Company field should be renamed to CompanyID and make the data type a Number.
    Change the Lookup Properties for the Display Control to Text Box vice ComboBox
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by astrelow View Post
    Bob, I am currently getting a sample database together.
    Take a look at the attached.
    As mentioned by others, use Lookups in forms, rather than in tables.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 04-26-2021, 05:20 PM
  2. Populate field from combobox choice
    By mick3911 in forum Forms
    Replies: 16
    Last Post: 02-16-2020, 02:12 AM
  3. Replies: 4
    Last Post: 03-22-2017, 08:00 AM
  4. Visible text boxes when combo box choice selected
    By nichmeg in forum Programming
    Replies: 3
    Last Post: 10-16-2011, 02:53 PM
  5. populate boxes based on combo box choice
    By Mattm1958 in forum Forms
    Replies: 13
    Last Post: 08-30-2010, 02:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums