By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,541 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

Comboboxes with dynamic dropdown menu

P: 68
I have a form with a few combo boxes that I would like to have their menu entered dynamically.

The first box is department and it holds all the departments in the organization. When I select a department in this box I would like the second combo box, which is "user" to be populated with only the users in this department. As it is right now users are being drawn from one table that is populated with all the users in the organization.

After selecting the user i would like the the hardware combobox and the software combo box to be populated with only the assets that are assigned to them. These values are pulled from their respective tables which contain all the assets in the organization.
Jun 9 '09 #1
Share this Question
Share on Google+
11 Replies


100+
P: 167
This should not be that hard. The combo box menus should be based on queries that have the appropriate combo boxes in their criteria section.
Like this:
1. Department box is based on query which is including all the departments.
2. User combo box must be based on a query that includes all the users WHERE Department ID = DepartmentComboBox
3. the same goes for others...
Let us know how it's going with this.
Jun 9 '09 #2

Expert 100+
P: 1,287
This Bytes insight article should help: Cascading Combo/List Boxes
Jun 9 '09 #3

NeoPa
Expert Mod 15k+
P: 31,711
You should find the whole issue covered in Example Filtering on a Form.

Let us know how you get on.
Jun 9 '09 #4

P: 68
Thanks a lot for the ideas shared. I tried the Cascading combo/List Box and am not getting through. Here are the details.

Expand|Select|Wrap|Line Numbers
  1. Departments
  2. DeptID - AutoNumber, PK
  3. DeptName - Text - Name of department
  4.  
  5. Location Officer
  6. LocationOfficerID - Autonumber, PK
  7. LocationOfficer - Text, Name of location or officer asset assigned to
  8. Department - Number
The form contains a combo box named LocationOfficer and one named Department. When i select the department the location officer should be populated but nothing happens, not even an error.

I used the following codes on the Department Combobox Afterupdate event

Expand|Select|Wrap|Line Numbers
  1. Private Sub Department_AfterUpdate()
  2. With Me.LocationOfficer
  3.     If IsNull(Me!Department) Then
  4.        .RowSource = ""
  5.     Else
  6.        .RowSource = "SELECT [LocationOfficer]" & _
  7.                     "FROM [Location officer] " & _
  8.                     "WHERE [Department]=" & Me!Department
  9.     End If
  10.     Call .Requery
  11. End With
  12. End Sub
Jun 10 '09 #5

P: 68
I tried avoiding codes and using a query. this works on the first attemp but does not requery when the value in the department combobox changes. here is the SQl codes:
Expand|Select|Wrap|Line Numbers
  1. SELECT [location officers].LocationOfficerID, [location officers].LocationOfficer
  2. FROM [location officers]
  3. WHERE ((([location officers].Department)=[Forms]![SupportLogsForm]![Department]))
  4. ORDER BY [location officers].LocationOfficer;
  5.  
Jun 10 '09 #6

FishVal
Expert 2.5K+
P: 2,653
Fields set is different in post #5 and post #6.

P.S. You couldn't avoid code at all. Virtually, you could, but this won't make your application more robust.
Jun 10 '09 #7

NeoPa
Expert Mod 15k+
P: 31,711
@KingKen
I never realised that article suggested changing the Row Source. I would not recommend this approach.

Please see the other article and let us know if that works for you.

Fish makes another good point :
Your different posts reflect different and incompatible names. This is a very bad idea. If you want people to consider your problem you should really be very careful to represent it accurately.
Jun 10 '09 #8

P: 68
Thanks for the observations and suggestions. I spotted the inconsistency in the table name [Location Officers] this was also an error in my codes and i corrected it. This however did not caused the program to function. I read the second article and cannot seem to matchup the concepts and worst so the codes with what i want to achieve. some how to me the first article seems like a simple solution that could work and for my ignorant sake i hope it ca. Can it?

You probably could tell by now that I am not so good at what I am trying to do so I am always looking for the easiest way out. The second article posed a bit of a challenge even reading, much more understanding.

So here are some suggestions I have:

What if I move these codes to the on gotfocus event of the LocationOfficer combo box? Would this work

Notice that I am focusing on the Department and LocationOfficer combo boxes right now. I believe that if I get this right then I can replicate it for the rest.
Jun 11 '09 #9

NeoPa
Expert Mod 15k+
P: 31,711
Well, that's me told anyway (author of 2nd article) :D I thought with the examples provided and all it was fool-proof.

The first article can work I'm sure, but changing the SQL itself, instead of passing a simple parameter to the code that opens the form is not a good plan. Workable but untidy (Sorry Rabbit).

If that can work for you though, go for it :)
Jun 11 '09 #10

FishVal
Expert 2.5K+
P: 2,653
I use another approach described in User-defined class for SQL Select expressions. It appeared to be very suitable and proved itself in a long run.
Jun 11 '09 #11

NeoPa
Expert Mod 15k+
P: 31,711
That seems quite sensible for you Fish (doesn't surprise me at all in fact), but do you think such an approach is going to be comprehended by some of the less experienced Access users here? I know I struggled when I first got into working on class code.
Jun 11 '09 #12

Post your reply

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