472,787 Members | 1,475 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,787 software developers and data experts.

Combo box based on another combo box

I've got two combo boxes on a form:
1. Division Name - "_" (Blank), "Division1", "Division2" etc
2. Members of a selected Division

What I want to do is select a division and then i want the 2nd combo box to show only the members of the selected Division. However, if i select the blank division "_", i want it to show me all members of all divisions.

At the moment, if i select a Division, it comes up with only the members in the selected Division. I don't know how to get it so that if i choose the blank division, all names come up.

i tried putting in the criteria for the second combo box as follows:
IIf([Forms]![FormName]![DivisionName]=" ", <>[Forms]![FormName]![DivisionName], [Forms]![FormName]![DivisionName])

But the result is the same as if i just limited the criteria just to be [Forms]![FormName]![DivisionName], - the result only achieves the first part of what i want getting done.

Sorry if i sounded too confusing for you to follow.
Dec 19 '06 #1
6 9990
143 100+
You need to look at cascading combo boxes. Try this site. Look under cascading combo box.

Dec 19 '06 #2
You need to look at cascading combo boxes. Try this site. Look under cascading combo box.

Thanks Tanis but i find that the cascading combo boxes was too complicated for me to follow, any more ideas?
Dec 19 '06 #3
Thanks, i'm just going to look up cascading combo boxes to work this one out. i thought it would be a simple solution, without much usage of vba, but it doesnt appear to be so.

Thanks anyway.
Dec 19 '06 #4
106 100+
Thanks, i'm just going to look up cascading combo boxes to work this one out. i thought it would be a simple solution, without much usage of vba, but it doesnt appear to be so.

Thanks anyway.
When you find the solution, let me know, becasue I have the same problem
Dec 19 '06 #5
14,534 Expert Mod 8TB
OK guys this isn't that complicated but you will need some VBA code.

In the After Update Event of Combo1 put ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Division_Name_AfterUpdate()
  3.    SELECT CASE Division_Name
  5.    CASE " " ' I'm assuming this is the value in the blank row
  6.       Me.Members.RowSource = "SELECT Member FROM TableName;"
  8.    CASE ELSE
  9.       Me.Members.RowSource = "SELECT Member FROM TableName WHERE DivisionName = [Froms]![FormName]![DivisionName];"
  11.    END SELECT
  13.    Me.Members.Requery
  15. End Sub

Dec 20 '06 #6
134 100+
Let assume your 1st combo box called cboDivision, in the After event, you add this statement, I just show an example, you need to fill up the other column.

I am just coping my coding over here.

Dim sManagerSource As String

sManagerSource = "SELECT [tablename].[DivisonID] as divsionID,......." & _
"WHERE tablename.divsionID = " & Me.cboDivision.Column(0)

2ndCombobox.RowSource = sManagerSource

Try this, just 2 line of code,I have this problem before, now for me it solved.
Also try to put those statement in the Form Current, so the next time when you edit the form, just click the 2nd combo box it will remember the selection from the first combo box, this way you don't have to select 1st combo box and then select 2nd combo box, unless you want to make another selection on your 1st combo box.

Happy coding
Dec 20 '06 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Rob | last post by:
Hello, I am developing an A2K application.In the subform ,I have around 4 combo boxes for Group,Division,Name and Application.The selection criteria of each combo is narrowed based on the value of...
by: Jeff Barry | last post by:
Hi, I wonder if any one can help, I'm pretty new to Access and I can't figure out how to change the contents of a combo box based on a selection I make in another. Let me explain I have a...
by: Megan | last post by:
Hi everybody- I inherited a database that somehow uses a bound combo box as a record selector. Let me give you some background. The form is based on data from 2 tables. The first table, Person,...
by: Lyn | last post by:
Hi, Can anyone tell me how the initial value displayed in Combo Box is determined when a form is opened? I am loading the dropdown from one field ("CategoryName") of a table, with "ORDER BY ". ...
by: meganrobertson22 | last post by:
Hi Everyone- I am trying to use a simple macro to set the value of a combo box on a form, and I can't get it to work. I have a macro with 2 actions: OpenForm and SetValue. I can open my form,...
by: sara | last post by:
I am learning how to use simple functions to make my apps more powerful and efficient. On one screen, I want to populate field B: ItemDescription by looking up the ItemDescription in the Items...
by: robtyketto | last post by:
I have generated combo boxes based on contents of other combos and am newbie who slowly learning vba code. Heres an example of vba code use to update one combo box from another ...
by: banderson | last post by:
Hello amazing vba writers, I am trying to make a combo box return a value based on a combo box selection. I have tried a number of the codes posted here and am still having problems. I think it is...
by: Chezza | last post by:
Hi All, Thanks to those that have helped me before. Itís been a couple of weeks since I posted (I can only play with this during my spare time at work) so hereís the set up again- we receive...
by: WyvsEyeView | last post by:
I am doing the very standard thing of filtering the contents of one combo box based on another combo box. I've done it many times, but always on a main form. Now I'm trying to do it on a datasheet...
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.