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

Topic

New Reply

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.

Reply | Quote

Replies

See MSKB article How to Synchronize Two Combo Boxes on a Form.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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?

• Make a copy of your database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
• Attach the zip file to a reply.
  [/list]Perhaps we’ll get a better idea then.

Reply | Quote

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.

Reply | Quote

It still seems a strange setup to me, but apparently, it is just meant as an example.

• 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!cboSetLastName));

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

• 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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Reply to Gary’s “One Other Item” post: there are a lot of separate questions here so I cut and pasted where appropriate.

1) “what happens if you have more then one person with the same name” – the current version of the database will fail when that happens, but I am aware of that problem and was putting it off until I understood more about junction tables.
2) what happens if you have more then one person with the same … last name” – I haven’t been able to get the current set up to fail in that circumstance. If you can, I’d like to know about it.
3) “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).” – Yes, that is what the junction table is for. I don’t think there is a problem until I get to a common first and last name, which as I remarked above, is a problem I will address down the road (there are additional fields I can use).
4) “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.” – yes, I do get into the non-unique entity issue here, but as I said in the previous post, that is a lark. I could’ve used full and distinct names for entities, and then had a junction table to put them into pairs, but it would have been more cumbersome.
5) “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.” – this is something that I am already comfortable with.

I think the bottom line for all the posts, and particularly for these last two, is that I’m really undertaking an exercise to learn more about junction tables. First and last names was just what I thought might be a natural (and shorthand) way to do that. If it would help, I can redo the sample DB I posted with full names of individuals/entities in each of the two tables, and then a junction table to form pairs of them.

Reply | Quote

Reply to Gary’s “One Other Item” post: there are a lot of separate questions here so I cut and pasted where appropriate.

1) “what happens if you have more then one person with the same name” – the current version of the database will fail when that happens, but I am aware of that problem and was putting it off until I understood more about junction tables.
2) what happens if you have more then one person with the same … last name” – I haven’t been able to get the current set up to fail in that circumstance. If you can, I’d like to know about it.
3) “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).” – Yes, that is what the junction table is for. I don’t think there is a problem until I get to a common first and last name, which as I remarked above, is a problem I will address down the road (there are additional fields I can use).
4) “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.” – yes, I do get into the non-unique entity issue here, but as I said in the previous post, that is a lark. I could’ve used full and distinct names for entities, and then had a junction table to put them into pairs, but it would have been more cumbersome.
5) “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.” – this is something that I am already comfortable with.

I think the bottom line for all the posts, and particularly for these last two, is that I’m really undertaking an exercise to learn more about junction tables. First and last names was just what I thought might be a natural (and shorthand) way to do that. If it would help, I can redo the sample DB I posted with full names of individuals/entities in each of the two tables, and then a junction table to form pairs of them.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

It still seems a strange setup to me, but apparently, it is just meant as an example.

• 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!cboSetLastName));

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

• 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.

Reply | Quote

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.

Reply | Quote

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?

• Make a copy of your database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
• Attach the zip file to a reply.
  [/list]Perhaps we’ll get a better idea then.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

See MSKB article How to Synchronize Two Combo Boxes on a Form.

Reply | Quote