Combo box recordsource based on IF statement?

[kgoneill]

Hi,

I have a form with three combo boxes: Division, Department and Sublocation.
I've managed to get the second two to update based on the previous choices (i.e. Location will only show the choices from the selected Division, Sublocation will only show the choices from the selected Division and Location).

My problem is I've got a fourth combo box below called MachineNumber.
As of right now I have to make a selection in each of the three above combo boxes in order for the MachNumber combo box to show any values. I have three queries set up that will show:
1. The machine numbers filtered for Division selection
2. The machine numbers filtered for Division and Department selections
3. The machine numbers filtered for Division, Department and Sublocation selections

How do I make the recordsource for my MachNumber combo box be dependent on which of the three above combo boxes have been chosen?
I've tried the following If statement, but it doesn't work at all:

If Division.Value <> "" And Department.Value <> "" And Sublocation.Value <> "" Then
MachineNumber.RowSource = "MachFilterSubLoc"
ElseIf Division.Value <> "" And Department.Value <> "" Then
MachineNumber.RowSource = "MachFilterLoc"
ElseIf Division.Value <> "" Then
MachineNumber.RowSource = "MachFilterDiv"
End If


Any help would be appreciated!

 

[nharrison]

I'm not sure if it's a typo, but off the top of my head I can tell you "Elseif" is supposed to be "Else If" (There is a space).

That would certainly mess up your code...

 

[kgoneill]

I tried changing that, but it seems to think that "If" should start a sentence - I don't have it right in front of me to give you the exact error message, sorry!

Any other suggestions?

 

[pbaldy]

ElseIf (no space) is correct. I suspect you need to test for both Null and the ZLS, and you're not testing for Null. Try this type of test:

If Len(Me.Division & vbNullString) = 0 Then

 

[kgoneill]

Thanks, I'll try that and let you know how it goes...