Comboboxes with dynamic dropdown menu

[KingKen]

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.

[hjozinovic]

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 = DepartmentCombo Box
3. the same goes for others...
Let us know how it's going with this.

[ChipR]

This Bytes insight article should help: Cascading Combo/List Boxes

[NeoPa]

You should find the whole issue covered in Example Filtering on a Form.

Let us know how you get on.

[KingKen]

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

Departments
DeptID - AutoNumber, PK
DeptName - Text - Name of department

Location Officer
LocationOfficerID - Autonumber, PK
LocationOfficer - Text, Name of location or officer asset assigned to
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

Private Sub Department_AfterUpdate()
With Me.LocationOfficer
    If IsNull(Me!Department) Then
       .RowSource = ""
    Else
       .RowSource = "SELECT [LocationOfficer]" & _
                    "FROM [Location officer] " & _
                    "WHERE [Department]=" & Me!Department
    End If
    Call .Requery
End With
End Sub

[KingKen]

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:

SELECT [location officers].LocationOfficerID, [location officers].LocationOfficer
FROM [location officers]
WHERE ((([location officers].Department)=[Forms]![SupportLogsForm]![Department]))
ORDER BY [location officers].LocationOfficer;

[FishVal]

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.

[NeoPa]

Private Sub Department_AfterUpdate()
With Me.LocationOfficer
    If IsNull(Me!Department) Then
       .RowSource = ""
    Else
       .RowSource = "SELECT [LocationOfficer]" & _
                    "FROM [Location officer] " & _
                    "WHERE [Department]=" & Me!Department
    End If
    Call .Requery
End With
End Sub

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.

[KingKen]

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.

[NeoPa]

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 :)

[FishVal]

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.

[NeoPa]

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.