IIf on two levels of cascading combos

[Steindawg]

I have three levels of fairly simple cascading combos. All contained in the same table- Division>Admin>Dept. All three boxes work fine, but now I am attempting to have them do two additional steps.
1. I want the lower levels to default to a null value upon entry into the upper levels. I tried the onclick field cboname=" " but that didn't work.

2. I want the query to default to all if no selection is made. I am using an IIF statement which works fine for the Admin level, but will not work for the Department level. Here is the syntax I am using for both...

IIf(IsNull([Forms]![Main]![CboDiv]),[Admin],[Forms]![Main]![CboDiv])

works fine

IIf(IsNull([Forms]![Main]![CboAdmin]),[Deptid],[Forms]![Main]![CboAdmin])

Doesn't work, and actually isn't pulling a value at all. I am new to Access so forgive me if I am overlooking the obvious. Thanks in advance....

 

[antomack]

I'm assuming that you are trying to blank Admin and Dept if someone selects a Division, and Dept if someone selects Admin.

I have a form which is used to filter reports which contain 5 drop downs involved in the filtering of the reports: Year, Week, Region Branch, Agent. I've called each of the combos Filter#, i.e. Filter1, Filter2, etc..

Behind each combo then I have the following code which requeries each of the lower level combos and sets the combos to null.

Private Sub Filter1_AfterUpdate()

Dim intCntr As Integer

For intCntr = 2 To 5
Me("Filter" & intCntr).Requery
Me("Filter" & intCntr) = ""
Next

End Sub

Then for each of the subsequent combos (except the last one which doesn't require the code at all) you just change the 2 in the For Loop to a 3, 4, or 5.

 

[Steindawg]

Ok so I would have

For IntCntr = 2 to 3
Me.("CboAdmin" & IntrCntr).Requery
Me.("CboAdmin" & IntrCntr)=""


Sorry for my ignorance but what is the IntCntr in your expression?

thanks, Chad

 

[antomack]

The intCntr is just an integer that I declared to use in the For Next, I could just as easily have used 'i' which one would normally see

For i = 1 to 10

It just keeps track of the number that should go at the end of the name.

By the way don't forget to put the 'Next' at the end like so
For IntCntr = 2 to 3
Me.("CboAdmin" & IntrCntr).Requery
Me.("CboAdmin" & IntrCntr)=""
Next

 

[Steindawg]

Ok now I have the boxes clearing and requerying afterwards, I actually just added the me.cboadmin="" before my requery line and that works how I want. Does anyone have any insight to the IIf problem, and if it should work in the Dept box the same way it works for the Admin box...