Results 1 to 12 of 12
  1. #1
    Join Date
    May 2010
    Posts
    67

    Unanswered: Can the combo list on my form be populated by another table

    I have two combo list on my form that need to be populated by other tables other than the table that the Access form is connected to. Is this possible?

    If it is possible, I want to use the values selected from the combo list to update fields in the table that my access form is connected to.

    What are the correct property values?

    Thank you in advance for your help!

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by eva.thaeh View Post
    I have two combo list on my form that need to be populated by other tables other than the table that the Access form is connected to. Is this possible?

    If it is possible, I want to use the values selected from the combo list to update fields in the table that my access form is connected to.

    What are the correct property values?

    Thank you in advance for your help!
    If I understand you correctly, that is a great use of a combo box.

    I would recommend using the combo box control wizard to create the combo box. It will walk you through the steps. Simple delete the current combo box. make sure the wizard is turned on. Add a combo box and follow the wizard's steps.

    Be sure to make a back up first.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    May 2010
    Posts
    67
    Thank you HiTechCoach. I will try the combo wizard approach.

    Just to let you know. I got the "Developers Handbook" you suggested. It is an excellent reference. Also when I revert back to my old school COBOL coding mindset (which I often do, due to 15 years mainframe coding) I think of your quote "sprinkle code in events". Just that quote alone has helped.

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by eva.thaeh View Post
    Thank you HiTechCoach. I will try the combo wizard approach.

    Just to let you know. I got the "Developers Handbook" you suggested. It is an excellent reference. Also when I revert back to my old school COBOL coding mindset (which I often do, due to 15 years mainframe coding) I think of your quote "sprinkle code in events". Just that quote alone has helped.
    Glad the book and quote are helping.

    Tossing out something new to think about ...

    I think of each control as a separate mine app or process. When a user sets the "focus" to control (using mouse, keyboard, etc), you do not know where the user came from. It could be any other control on the form, on another form or database object. It could even be from a different Windows App. When the user takes the focus from the same control, you do not know where there are going. Again, It could be any other control on the form, on another form or database object. It could even be from a different Windows App.

    Once I changed my thing to: "I should not care where the came from or where they are going," I sure helped me make the leap to thing more Object Oriented.

    Thinking Oject Oriented truly is a huge paradigm shift from the structured Cobol world.

    Once I got shifted to thinking Oject Oriented, it really even help with my Cobol coding!
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    May 2010
    Posts
    67
    Good Day HiTechCoach

    Question:

    The table (client_table) I wish to use to populate my combo list, has a primary key field (ClientId) and another field named ClientName (the clients literal name). I want to populate my combo list with the ClientName field, but store the clientID field in the table connected to the form. Can I achieve this using the wizard. I have tried, but maybe I am missing something.

    Thanking you in advance

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Yes you can. When you are selecting fields from the Client table, select both fields and make sure the Hide Key Column check box is selected. Then select the field you want to store the value in. Once it has been created look at the following fields (so you can tweak it if needed)

    Column Count shows the number of fields you combo box is collecting.
    Column Widths is responsible for "hiding" the index field (0";1")
    Rowsource stores the SQL that will be used to populate the combobox. You can edit it so that you can show the Client Names in alphabetic order.

  7. #7
    Join Date
    May 2010
    Posts
    601
    @DCKunkle, thanks for jumping in and explaining how to use the wizard.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  8. #8
    Join Date
    May 2010
    Posts
    67
    Thank you both, DCKunkle and HiTechCouch for coming to my aid. Much appreciated!

    But, I am obviously doing something wrong.

    I followed DCKuncle's directions. Using the wizard, I created the combo list, and it is using the correct table. I selected both fields (clientname, clientid). Made sure the hide key column is selected. Selected the field I want to store the clientid value in.

    Column Count is 2
    Column Width is (0";1")
    I modified Rowsource to SELECT clientname in ascending order (to be displayed in combo list)

    The problem is: in design view the combo list it is displaying the ClientID fieldname and not the ClientName fieldname

    What am I missing? Any thoughts?

    Thanking you both in advance!

  9. #9
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by eva.thaeh View Post
    Thank you both, DCKunkle and HiTechCouch for coming to my aid. Much appreciated!

    But, I am obviously doing something wrong.

    I followed DCKuncle's directions. Using the wizard, I created the combo list, and it is using the correct table. I selected both fields (clientname, clientid). Made sure the hide key column is selected. Selected the field I want to store the clientid value in.

    Column Count is 2
    Column Width is (0";1")
    I modified Rowsource to SELECT clientname in ascending order (to be displayed in combo list)

    The problem is: in design view the combo list it is displaying the ClientID fieldname and not the ClientName fieldname

    What am I missing? Any thoughts?

    Thanking you both in advance!
    The order of the fields in the row source are important.

    Column Count is 2
    Column Width is (0";1")

    Select ClientID, ClientName

    Bound Column is 1

    The key is that ClientID is the first column in the query, the first column's width is set to zero, and the bound column is 1.

    When in design view the combo box will show the bound column for the form's record source not the row source of the combo box.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  10. #10
    Join Date
    May 2010
    Posts
    67
    Got it!

    One last question!

    Can I re-use the combo list to display an existing records CLIENTID value?

    RS1.Open "select * from download_log where download_log.jobnumber = " & Chr(34) & Forms!download_log_form!JobNumber & Chr(34) & ";"
    ReportDate.Value = RS1![ReportDate]
    FromDate.Value = RS1![FromDate]
    ToDate.Value = RS1![ToDate]
    Purposefld.Value = RS1![Purposefld]
    Commentsfld.Value = RS1![Commentsfld]

    RS2.Open "select * from client where clientid = " & RS1![Clientid]
    Clientid.Value = RS2![Clientid]

  11. #11
    Join Date
    May 2010
    Posts
    67
    I figured it out.

    clientid.Value = RS1![clientid]

    Very much appreciative for this site and the help that I get!

  12. #12
    Join Date
    May 2010
    Posts
    601
    You're welcome.

    Great job figuring it out on your own. You are learning!

    Glad we can assist.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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