Create a dependent combo box

Question

I create a database in access 2013. The Operative System is Window 7. In this data base I have Table Accounts, Table Account Categories and Table Transactions. Each of this tables has a single form. In the Form Transaction I create a CboAcctCateg that show the Account Category Name and store the Account Category ID (AcctCategID) in the transaction table. I also create a CboAccount that show the Account Name and store the Account ID (AcctID)in the transaction table.

I will like to get some help in how to make the CboAccount dependent of the CboAcctCateg, or whether I select one Account Category in the CboAcctCateg only the related Account show up in the CboAccount.

In my attempts I make one to many relationship between Table Account Categories , Table Transactions and Table Accounts with the fields AcctID and AcctCAtegID in the Transaction Table. I also try a one to many relationship between Table Account Categories and Table Account, creating the respective query in each scenario but it has not been able to make it work .

I do not know what the control source of the CboAcctCateg and what the control source of the CboAccount should be. I 'm lost and I will appreciated any help to make this work.

Thanks so much for any help.

Answer 1

Thanks again Mr Sctott...thanks a lot for your attention and help...however I must be doing something wrong. Please let me repeat what I have in my tables and form. I want to make sure I am giving the right information.

Table Name: tAccounts   Table Name: tAccount Categories  Table Name: tTransactions

                      AcctID                              AcctCategID                                    TransactID

                      AcctNo                             AcctCategNo                                   TransactNo

                      Acct                                  AcctCateg                                        TransactDate

                      AcctCategID                                                                              TripID

                                                                                                                        Amount

                                                                                                                        TransactDescrip

                                                                                                                        AcctID

As I mentioned the relation are between table Account Categories and table Accounts in a one two many because a category could have multiple accounts. The other relationship is between table Account and table Transactions because an account could have many transactions. I delete the AcctCategID from table Transactions to avoid redundancy according you told me.

The Record Source of the Transaction form is tTransaction. Date type in each table foreign key are number (Long Log) to make possible the relationship.

The query I mention in the beginning are the internal one that took place when in the cboAcctCateg row source line I click the dot button. Doing that a query window open. I select table Account Categories and add AcctCategID, AcctCategNo, AcctCateg. I the query and the data come out. I did the same with the cboAccount and I select AcctID, AcctNo, Acct, AcctCategID. I run the query and  the data come out. I save and close both query and doing so, in the row source of each combo box appear the select line. Excuse me if I am repeating things that are so elementary for you, but my purpose is to be clear on what I have.

Thanks again for your time. I am grateful

Nelson Ruiz

Answer 2

A control doesn't have a Recordsource it has a ControlSource. The cboAcctCateg should not have a Controlsource. But it does need a RowSource like:

SELECT AcctCategID, AcctCateg FROM AccountCategories ORDER BY AcctCateg;

In the RowSource for cboAccount formname should be the name of your YOUR form, not just formname.

Answer 3

Mr. Scott

Thanks so much for your help.

I check the table Transactions as you suggested. I delete the field AcctCategID to avoid redundancy. 

In the row source for the cboAccount  in the Transactions form I wrote: SELECT AcctID, AccountName FROM Accounts WHERE AcctCAtegID = Forms!formname!CboAcctCateg ;

The record source in the Transactions form is tTransactions.

After doing the above when I click the cboAcctCategory in the form anything show up and the same happen when I click the cboAccount.

The relationship is :

* One to many tAccount Categories to tAccounts

* One to many tAccounts to tTransactions

The cboAcctCateg in the form does not have any record source and any row source either. Please, should I write some record source or row source for the cboAcctCategory.  I also going to look the information send by Mr. Sheridan.

Thanks so much for your help

Nelson

Answer 4

As Scott has pointed out the relationships with a set of correctly normalized tables would be like this:

Transactions>----Accounts>----AccountCategories

You'll find an example of the use of correlated combo boxes in this sort of context as ComboDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file I would suggest that you take a look at the form for a simple 2-level hierarchy, in which my Locations table is analogous to your Transactions table, my Parishes table to your Accounts table, and my Districts table to your AccountTransactions table.

The relevant form in my demo uses 'hybrid' controls in which a text boxes is superimposed on a combo box to give the appearance of a single combo box control.  This is necessary because of the use of hidden numeric key columns as the BoundColumn of each combo box.  If combo boxes alone were used, data in some rows other than the current one would disappear (not lost, just invisible) because when the combo box is requeried there would be no value in the visible column in the control's list to which the numeric column can map.  This value is shown in the text box superimposed on the combo box.

Note how the form is based on a query which joins the three tables, and In the form's module take a look particularly at the AfterUpdate event procedure of the cboDistict combo box, and at the form's Current event procedure, in both of which the cboParish control is requeried to show only those parishes within the selected district, analogous to showing the accounts within the selected account category in your case.

Answer 5

This is a standard technique called Cascading or Synchronized comboboxes. There is a lot of documentation for this technique if you search on those terms.

The first thing you need is to have a field in your Accounts table for AcctCAtegID. This means that an Account is associated with only one Category. It also means that you should NOT have a field in the Transactions table for AcctCAtegID, since it would be redundant. All you need is the AcctID to find out the category.

I'm not sure what these queries are that you refer to, but they are unnecessary for this task. The CboAcctCateg should be unbound since its value is not stored. The RowSource of CboAccount should look like this:

SELECT AcctID, AccountName FROM Accounts WHERE AcctCAtegID = Forms!formname!CboAcctCateg ;

In the After Update even of CboAcctCateg you want to requery CboAccount. CboAccount will then show only those accounts that match the selected category.