Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    I have a form with two combo boxes that are populated from separate tables. The form uses those combo boxes to create a junction table. I would like an entry in one combo box when updated to repopulate the combo box in the second with an appropriate subset of choices from its corrsponding table.

    For example, suppose I have first names in tblFirst, last names in tblLast, and full names (the junction) in tblFull. I use frmFull to populate tblFull. frmFull has two combo boxes that are populated from tblFirst and tblLast when it opens. I would like to be able to enter a first name in its combo box and when I shift focus have the combo box for last name populate only with last names that correspond to that first name in tblFull.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)


  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    This is a great help, but I'm still not that good with VBA.

    I understand what is going on in the post from MS. But what I want to do is populate the combo box with names from a table other than my junction table (the junction table just contains ID numbers from different tables, while the table with the names has an ID number and the name in text). The MS example populates the second combo box with names that are in the junction table.

    So, I'm missing a bit of VBA to make a compound statement.

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    I provided a quick example database for you. I don't understand why you need the junction table as one table with First Name and Last Name is all you need since a query can combine them. In the example, I created one table with first name and last name. There are two entries for Gary and one for Alice. The form, formName has two combo boxes. The first selects distinct first names. The second selects all last names for the first name selected. If you select Gary as the first name you get the two last names in the second combo box. Selecting Alice only gives you one last name in the second combo box. Perhaps this will help.

    HTH
    Attached Files Attached Files
    Regards,

    Gary
    (It's been a while!)

  5. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    I'm not sure if this last reply is covering me or not.

    The intention of my form is to add items to my junction table, and to the underlying tables if need be.

    Would it really be appropriate to do this through a query?

    Or is the suggestion to use a query just a way to populate my combo boxes that are already updating the table?

  6. #6
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    I'm wondering if my last reply has been waiting too long, and whether I should repost.

    Anybody out there following this thread?

    Also, what should be my protocol on this site - should I contact the two people who commented with answers directly, or should I wait here for another response?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    I have been following this thread, but just like Gary, I don't really understand your setup. Could you post a small database so that we can look at it?
    <UL><LI>Make a copy of your database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
    <LI>Attach the zip file to a reply.[/list]Perhaps we'll get a better idea then.

  8. #8
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    A stripped down database is attached. I hope this helps.

    I've been designing (bad) databases on and off for about 8 years, and I am trying to unlearn all my spreadsheet-like habits. I know some VBA (and more VB) but am having trouble applying it to Access. I am open to all suggestions about how to improve my database design, or to links that can help me with VBA.

    I'm just using names as a straightforward example to figure out some design issues involving junction tables (which I more or less used to keep in a spreadsheet form). So, I am not married to this set of names in any way.

    The DB contains 3 tables: 1) tblFirstNames (containing an autonumber primary key called idFirstName and the text field firstName), 2) tblLastNames (containing an autonumber primary key called idLastName and the text field lastName), and 3) a junction table called tblFullName (containing an autonumber primary key called idFullName and foreign keys idFirstName and idLastName). There is a query showing the text equivalent of the junction table. There are three forms: 1) one to get and set in tblFirstNames, 2) one to get and set in tblLastNames, and 3) one to add entries to tblFullNames.

    The DB has one tweak: a suggestion from HansV from about two weeks ago about how to allow a form that is updating a junction table to add those new records to the underlying table too (i.e., frmFullNames will update tblFullNames and/or tblFirstNames and tblLastNames if a new name is entered in frmFullNames.

    What I would like to figure out how to do is if I enter the name Smith in my cboLastNames in frmFullNames have cboFirstNames populated only with the names from tblFirstNames that match up with the last name Smith in tblFullNames.
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    It still seems a strange setup to me, but apparently, it is just meant as an example.
    <UL><LI>Change the row source of cboSetFirstName to

    SELECT tblFirstNames.idFirstName, tblFirstNames.firstName FROM tblFirstNames INNER JOIN tblFullNames ON tblFirstNames.idFirstName=tblFullNames.idFirstName WHERE (((tblFullNames.idLastName)=Forms!frmFullNames!cbo SetLastName));

    This limits the first names to those occurring for the last name.

    <LI>Requery cboSetFirstName in the On Current event of the form and in the After Update event of cboSetLastName.[/list]But now, you'll run into the oddness of the setup. Since cboSetFirstName only displays the first names corresponding to the selected last name, any other first name you enter will be considered to be "Not In List" even if it already occurs in tblFirstNames! The name will be added to the table (causing duplicates), but still be refused.

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    Sorry,

    I have been on and off line and was not able to get back to you in a timely manner. Hans picked up the thread (Thanks Hans). Has the solution Hans provided been able to solve the issue you are having?
    Regards,

    Gary
    (It's been a while!)

  11. #11
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    That's OK. I was just getting nervous as my thread dropped further down onto the second page.

    Hans' solution is raising new issues. I will respond to his post in a few mintues.

  12. #12
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    I'm learning a lot of stuff from this thread, but I'm not sure if I am getting clearer on (what I thought) the core issue was.

    Could you explain the oddness of the setup? Is it the division into first and last names? Is it the desire to limit first names to those that match up with the last names? This is apparently one of the things I am having trouble picking up from texts.

    I also had trouble getting the requery to work in the onCurrent event. What should the syntax of that be?

  13. #13
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    Can I ask a question about your database design.

    I am lost with the purpose of the junction table and what you are trying to do with it.

    Tables one and two contain data. If table one has first names and table two has last names, then creating a junction table of first and last names combined only wastes table space and can make it harder to program and use.

    What is the relationship between the tables. One to One? One to Many? How are they linked? Are you trying to make a many to many link?

    I would suggest, for this example, making a one to many relationship between the tables if people with multiple last names can have the same first name. I.e., Items in table1 can be related to many items in table2.

    I think you will find this much simpler if you simplify your design.
    Regards,

    Gary
    (It's been a while!)

  14. #14
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    One other item,

    In the example you provided, what happens if you have more then one person with the same name or last name. You have the last name indexed in the last name table such that if you have more then one person with the same last name (i.e., Smith), your design will fail.

    I would suggest that you simplify this and only use two tables, or perhaps one.

    Table one stores First name and is linked to table two which stores last names (for this example) as a one to many relationship. However, this will also create problems on people with the same name as how will you know which one they are? Is this what you are using the junction table for, identifying unique id's for common first and last names (which will also cause problems).

    Typically, in a database that is storing key data such as a persons name, an ID is created that identifies each person as a unique entity. So what happens if they have the same first and last name? How do you tell them apart as the data is the same. You could use some other identifier such as address, phone number, etc (none of which is error proof).

    In either event if you use one or two tables to store the data in this example of names, it would be easier to drive the combo boxes from these tables then the junction table.

    HTH
    Regards,

    Gary
    (It's been a while!)

  15. #15
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Populate Combo Boxes from Other Combo Boxes (Access 2002 SP-2)

    Gary:

    The purpose of the junction table is that I am really trying to figure out all I can about how junction tables work. So, I set up (what I thought) was a simple example where I would understand what I wanted the data to do before I started working on it in Access.

    Let me say first that the issue of first names in one table and last names in the other is a lark. I did that because I have an outstanding (low priority) database problem that I need to sort through involving teams of different individuals, grouped in different ways, where the order of the names matters. Rather than working with pairs of individuals's full names, I did it with first and last names - which was less cumbersome to write out. The set of names in the tables might suggest that there is a one-to-one relationship, but that is only because I shortened that list considerably before posting it.

    So, I have a table of first names with a one-to-many relationship with a junction table of full names (each first name may belong to more than one person), and a table of last names with a one-to-many relationship with a junction table of full names (each last name may belong to more than one person).

    So, I'm not sure if your suggestion helps me here (to repeat "I would suggest, for this example, making a one to many relationship between the tables if people with multiple last names can have the same first name. I.e., Items in table1 can be related to many items in table2"). I have set up my data intentionally so that there are some last names that are repeated with different first names, and some first names that are repeated with different last names.

Page 1 of 2 12 LastLast

Posting Permissions

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