# Access 2003: Basing one combo box on another



## dyfswillz (Apr 5, 2007)

Hi,

Ive created a failry simple Access database to record incoming calls and referrals. Everything is working fine appart from one section in one of the forms. What I want to do is let the user select a "Reason" and then select a "Sub Reason". The contents of the "Sub Reason" combo box should change dependent on what is selected in the "Reason" combo box.

I have created tables for both Reason and Sub Reason which have a field linked through a one to many relationship (this field is named Reason). 

Could someone please give me a step to step tutorial of how to set up this system of one combo box based on another. This problem is really annoying me at the moment and I have tried following the Microsoft websites tutorial but its hard to follow.


Please people help me!


----------



## OBP (Mar 8, 2005)

Hello dyfswillz, this is fairly simple to achieve.
Create a Query for the Sub Reason Table. (The Combo for Reason should also be based on a Query so that the Reasons can be listed in Alphabetical order).
In the ""Reason" field of of the Sub Reason Query enter the following in the "Criteria Row" -

[forms]![Name of your form]![Name of your Reason Combo]

Where Name of your form is the actual name of your form with the combo boxes on and Name of your Reason Combo is the actual name of your Reason Combo.
Now you can test this Query to make sure it works.
Open the Form and select a Reason in the Reason Combo.
Run the query and it should only display the Sub Reasons for that reason.
If that is working Ok then the only thing to do is to get the Sub Reason Combo to display the new results when you make a Reason selection.
Open the Form in Design Mode, click on the Reason Query and in it's "Properties" click on the Event Tab.
The second item on the Events list is After Update. Click on the row and from the drop down select Event Procedure.
When you click it 3 small dots should appear on the right hand side of that row, Click on the 3 dots.
You should now be in the Visual Basic Editor. You should see something like this

Private Sub Combo0_AfterUpdate()

End Sub

Copy this in between the those lines

me.comboname.Requery

so you get this

Private Sub Combo0_AfterUpdate()
me.comboname.Requery
End Sub

Change the comboname to the actual name of your combo, i.e. 
me.combo1.requery etc.

Now when you make a selection in your Reason Combo it should update your Sub Reason Combo.

Welcome to the world of Visual Basic.

If you can't get it to work please post a Zipped copy of your database on here and I will create it for you.


----------



## dyfswillz (Apr 5, 2007)

Thanks a lot for the quick reply! Unfortunately I haven't been able to get it to work. Its likely that something in my database is wrong and stopping this process working.

Id be really grateful if you could take a look at the database and try and get this small matter working. Also it would be great if you could post any tips e.t.c that would improve my database building skills it.

Enclosed is my database.

I am off work now until next Tuesday so there is no rush in getting this done. If you have time to fiddle around with it it would be great as someone is waiting on it here at work.

I will pop back here next Tuesday.

Thanks again for your help.


----------



## dyfswillz (Apr 5, 2007)

Hi OBP,

Sorry i cant PM you back for some reason so ill type my reply here.

Thanks a lot for having a look at my database, your ideas sound good. I was thinking of creating another table like you suggested but as this is a work project I got dragged into other things and did not want to start the process again from the start. It would be great if you could perform all you have stated for my database. I then could have a look into how it really should be done and do that next time I need to create a database.

This forum os really great, since starting my job just over a month ago ive used Access a lot and encountered a lot of time searching for solutions to my problems. This forum will be very useful for me in the future, especially with members like you who is prepared to use their knowledge to help others.

Thanks again


----------



## Rockn (Jul 29, 2001)

You didn't have the field defined in your subReason dropdown. You might also want to NOT name controls with spaces in the names, always try to use an underscore instead of a space.


----------



## OBP (Mar 8, 2005)

dyfswillz, please have a look at the attached database, it has the changes that we discussed plus a few more "ideas" for you to look at.
First of all I split the Contacts (names, addresses etc) from the Call Table, but kept it's name as Contacts and renamed the Call Table Call Log. I have modified the Relationships to suit.
I split the new Table's Name in to First name and Surname and added a "gender" field. So the names can now be sorted by Surname and First name.
I created a new form for the Contacts and modified the old Contacts form to Call Log with a Combo to select the Contact details. If a Contact is new and not in the Combo List just Double Click the Combo and it opens the Contact Form so that you can add a new contact. The new contact will be saved in the Contacts table and in the Call Log as well, it will also be in the Call Log Contact Combo.
I have added a second Menu called "Admin Menu" where you can maintain your Call Reasons, Sub Reasons and Workers.
It also has buttons for a new "Search Form" and various simple "stats" queries that might be useful.
The Search Form allows searching by Reason, Sub Reason, Client Name (exact match) and wild card Client Name (matches partial names), between 2 dates, or just one date, by gender, by worker.
The Stats buttons, run queries that supply the number of calls, by Reason, Sub Reason, by Client, by Client's Age, by the Hour, by the Day and by the Worker. There is also a Cross Tab query that shows Calls by reason for each month as an example of how they work.

I have experienced an Error on closing the Call Log Form where Access asks for the "parameter" for the Contacts Combo's Query, if this continous to happen for you I will have to rebuild the Call Log Form, so please let me know.

This database should give you some good ideas of what Access can do for you.


----------



## dyfswillz (Apr 5, 2007)

Thanks a lot OBP for all your help! The database looks good at the moment and I only need to change small details before sending it to the end user. At the moment there is no "real" data available to enter or transfer into the database. What is going to happen is that the user will receive a telephone call and enter the clints details into the database.

I have created a new tab in the main switchboard named all/edit client details. This will make it easier for the user to quickly enter new clients details before logging the telephone referral. One thing I hope to do is create a link to a report printing out a certain months telephone logs. This report will hold Clients details, DOB, Address, Tel number, Reason and Sub Reason. At the moment im trying to find out how to get the Reason and Sub Reason to come up as text and not a reference to their ID Number. However im sure ill figure this out fairly soon.

As for your question asking if this is a "secure" database. To tell you the truth im not entirely sure what this means, but assume its something to do with user access. The end user has not requested specific user access limitations so ill leave it like it is.

Thanks again for helping me with this project, you have been very helpful. Im sure ill learn more from these forums in the future if all members are as helpful as you. Also thanks you to the other member who replied to my post. :up:


----------



## OBP (Mar 8, 2005)

dyfswillz, to get the Reason and Sub Reason Text you combine the 3 tables in a Query, see the Reason Stats Query for 2 of them. You just create a query for the Contacts and add the Reason and Sub Reason Tables to it, you can then add the required fields. 
You create your Report from the new query using the Report Wizard.


----------



## dyfswillz (Apr 5, 2007)

Ive managed to create the report and after testing it for a while it is working fine. Also as for the Error on closing the Call Log Form where Access asks for the "parameter" for the Contacts Combo's Query problem you mentioned does not appear when im adding/updating record directly from the switchboard. Therefore there is not need to re-build the form. Thanks again for all your help. Will report back if any other problems that I cannot fix appear.


----------



## amdainul (May 9, 2007)

Hi, Im a beginner of MS Access and I had take a look of dyfswillz's database. I also have a simple database to create and I wanna ask that is it possible to make (dyfswillz's database as example):
first combo box is : SubReason
2nd combo box is : Reason that change according to first combo box

If it is possible, what is the differences I must done to the database?
Thank you.


----------



## OBP (Mar 8, 2005)

Yes it is possible, you just need to change the Criteria Row of the Query.
If you post a zipped copy of your database and an exact explanation of which combos to work with and I can show you how.


----------



## amdainul (May 9, 2007)

My file is quite big to be uploaded. 2,845 KB after zipped..can it be smaller? 
Is it connected with the way I create the database?


----------



## OBP (Mar 8, 2005)

amdainul, did you Compact & Repair it prior to zipping it?
Main Menu>Tools>Database Utilities>Compact & Repair


----------



## amdainul (May 9, 2007)

Thanks..It's smaller now.

I already can make my 2nd combo read based on selection on 1st combo but I got probs with textbox after that. Can you check it for me? 

If you don't understand please let me know because I use Malay language in that database and maybe will trouble you with my mess database..


----------



## OBP (Mar 8, 2005)

amdainul, which Textbox on which Form (Maklumat Peserta?) are you having a problem with and what is the actual problem?


----------



## amdainul (May 9, 2007)

Form Pendaftaran Masuk

-texbox Jenis and texbox Harga

need to display Jenis from tblJenis and Harga from tblHarga automatically depend on what have been chosen on cboNoBilik (but cboNoBilik is referenced from cboNamaBlok)


----------



## amdainul (May 9, 2007)

Hi OBP, i got it..my combo is working now..thanks for your time. i also make the sample database as my reference at : http://www.microsoft.com/downloads/...a1-3c4e-4371-81e4-f9347b7a1dd7&displaylang=en
but now i cant save the ID from the combo,did i miss anything?


----------



## OBP (Mar 8, 2005)

amdainul, I am sorry not to have got back to you before this.
I am also glad that you are making progress on your own.
If the ID is not being saved check that you have set the Combo is "Bound" (has a Control Source) and that you have selected the correct Bound Column.
If the combo can't be bound then you can enter the ID in to the field using the VBA.
In the combo box's "After Update" event procedure place something like

me.IDField = me.combo1.Column(0)

where IDField is the actual name of the field on the form where you want to store the ID and Combo1 has the actual number of your combo and Column(0) is the column number that holds the ID in the Combo.
Note the column numbers start at zero in VBA.


----------



## amdainul (May 9, 2007)

It's ok OBP, im still need help from you and everyone in this forum.
Thanks, my combo can be saved now.
Can I ask another question.
How can I change the status of the room to the Yes when I click save button. My save button will save all the data to the table Registration while the status need to be changed at table Room.If I need to use VBA, please give some explanation.


----------



## OBP (Mar 8, 2005)

Amdainul, have you got a "Yes/No" (tick box) field in the Table Room?
If so it should automatically register a tick when you click it.
If you want to do it without clicking on the tick box on the form you can use VBA to set it to -1 which means "ticked".
Or does the field actually need to be the word "Yes"?


----------



## amdainul (May 9, 2007)

Yeah, I've "Yes/No" field at table Room which is Register. In the form I need a check box right? I put a save button then all of the data will be saved to table Registration. Only check box need to change Register to Yes at table Room. Now the check box does not functioning at all.This is my tables:

Registration(RegistrationID,CustomerID,RoomID,StartDate,EndDate)
Room(RoomID,RoomNo,BlockID,TypeID,PriceID,Register,Notes)


----------



## OBP (Mar 8, 2005)

Is your check box on the form the actual text box in the table, or just from the toolbox?


----------



## amdainul (May 9, 2007)

actually, I really dont know how to use check box. Im sorry but I attached my dbase again.
At frmregistration, please take a look. 
This form need to retrieve CustomerID from Customers_Information(but not functioning yet) and save all to table Registration. At the same time, need to change Register status to ticked or Yes at table Room.

Thanks again.


----------



## amdainul (May 9, 2007)

Hi, Im sorry for not telling earlier but the checkbox at the form does not mean to be visible. As long as when clicked the button, it 'checked' the table Room, it is ok also. Does it related to the record source of the form?


----------



## psycho_chicken (Aug 24, 2007)

Hello there, I am new to this forum, but I have a similar problem.

I also have 2 comboboxes, with the second combobox that needs to be linked to the selection made in the first one. I've already tried like everything with the knowledge I have or the things I learned the past few days to make this work, but it still isn't working.

I have some basic knowledge of Access, and I just found out a few days ago how to link one box to another. Now, when I select the first option in combobox one, the next combobox gives me the options I want for the one selected in the first box. However, when selecting the second option in box one, my next box still gives me the options for option nr. 1 in my first box.

I already entered the
Me.combobox1 Reqeury code in VBA in the property field UpdateAfter, but it doesn't perform it...

Does anybody have an idea of what I might be doing wrong?

If needed, I can try to zip it and attach it on a post, but the DB is in dutch. Unless this is no problem?

Any help would be very much appreciated  

Cheers,
P_C


----------



## psycho_chicken (Aug 24, 2007)

Found it with lots of common sense  

So you guys can disregard the last post, but I think I'll be dropping in maybe later another time because the DB is not finished yet...


----------



## Normal (Apr 14, 2008)

I used the "Basing one combo box on another" scenario on a subform. It worked very well. But when I went to the form the subform was attached to, then it started to treat the lower combo's query's [forms]![etc]![etc] as a parameter and was asking me to enter information for it. Is there anyway to correct this?


----------



## OBP (Mar 8, 2005)

Have you changed any Form or Field names?
It sounds as if you have lost the link to the form that you originally established.
Do you mean that it worked on the Sub Form as an ordinary Form but not when it is on the Main Form as a subform?
It can be difficult to get the correct syntax when referring to a Subform from a query, you cna use a work around by having a Dummy Field on the mainform and put the Selection of the First Combo in that field and the use that in the Query.


----------



## Normal (Apr 14, 2008)

I haven't changed any form names and my subform (where the combos are) hasn't lost the link to the main form where the subform is located. The combos work fine when running the subform, but when you try to run the main form it treats the minor combo with the [forms]![Name of your form]![Name of your Reason Combo] as a parameter query requesting entry. I can cancel each time it requests a parameter fill and get most of the job done on the main form. I get one parameter fill request when I open the main form, one when I click on the subform in the mainform, one when I click on "reason" (1st cbo) and one when I click on "subreason" (2nd cbo). Also, the subreason combo gives me _blank options_, nada, no subreasons at all - but only when I'm doing this in the main form.

It seems like I need to do something with the mainform in the event procedures-- either in the Load event or Current event, or both. I just don't know that much about writing code.


----------



## OBP (Mar 8, 2005)

It is the Criteria Row reference to the Field in the Query that is the problem, once the Subform is placed and used on the main Form, it no longer has the "[forms]![Name of your form]![Name of your Reason Combo] " as it's reference you may be able to get it to work using 
the [forms]![MainForm Name]![Name of your form]![Name of your Reason Combo]

If not you will have to use the method that I mentioned where you use the After Update event of the first Combo to put the selection in to an Unbound Field on the Main Form.


----------



## Normal (Apr 14, 2008)

You've given me a lot of ammunition. I'll give it a try. Thanks.


----------



## Normal (Apr 14, 2008)

Thanks, OBP. That worked!! But now... sorry I didn't mention this before...I was kind of focusing on one problem at a time. But the subform feeds into a subform which then feeds into a main form (last place). The 2nd subform now works with your advice, but now when I try the same solution with the final main form: [forms]![main Main Form Name]![Name of your form]![Name of your Reason Combo] I get the same response I got before with the sub subform. I tried several times to retype it. Going with the source name of the MAIN form and then the caption name of the MAIN form seeing which one would work... but didn't work. I'm still thinking your After Update Event might work, but I'm not quite sure what you think I should do there. Any help...Thanks.


----------



## OBP (Mar 8, 2005)

Can you post a zipped copy for me to look at with the names of the problem forms?


----------

