Cascading combo box with conditional msgbox

[alwainwright]

I am using the below sub to create a cascading combo box, which works fine.

However, not every subject in tblsubjects has a subcategory, so I want a msgbox to appear only when there are subcategories to choose from. At the moment I am using
If Me![Subject2] = "Dispute Resolution" Or [Subject2] = "Employment" Then Msgbox "text"
but this will mean changing the code everytime new subjects with subcategories are added. Can anyone help please?

Thanks!
Alison

Private Sub Subject1_AfterUpdate()
On Error Resume Next
Subcategory1.RowSource = "Select tblsubjects.Subcategory " & _
"FROM tblsubjects " & _
"WHERE tblsubjects.Subject = '" & Subject1.Value & "' " & _
"ORDER BY tblsubjects.Subcategory;"
End Sub

 

[RuralGuy]

Why not use a DCount() on tblsubjects to see if there are any records that meet the criteria?

 

[alwainwright]

I've tried having a fiddle with this, but as I'm a newbie, I can't see how I should actually incorporate it into my code. Further pointers would be much appreciated!

Thanks, Alison

 

[RuralGuy]

Hi Alison,
Try something like:

Private Sub Subject1_AfterUpdate()
[B][COLOR="Red"]'On Error Resume Next <<- Never a good idea to disable error handling![/COLOR][/B]
On Error GoTo Err_Subject1_AfterUpdate
If IsNull(DCount([Subcategory], "tblsubjects", "[Subject] = '" & Me.Subject1 & "'")) Then
   MsgBox "Your message"
Else
   Subcategory1.RowSource = _
   "SELECT tblsubjects.Subcategory " & _
   "FROM tblsubjects " & _
   "WHERE tblsubjects.Subject = '" & Subject1.value & "' " & _
   "ORDER BY tblsubjects.Subcategory;"
End If
Exit_Subject1_AfterUpdate:
   Exit Sub
Err_Subject1_AfterUpdate:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.Description
   Resume Exit_Subject1_AfterUpdate
End Sub

 

[alwainwright]

I've tried this and I can't get it to work - it comes up with error 2645 - doesn't recognize field. I've checked all the field names and tried various permutations with quotes, brackets, dots and I can't figure out what's going wrong. Any further help would be much appreciated.

Thanks a lot.

 

[RuralGuy]

Hi Alison,
Do you know which line is causing the error?

 

[alwainwright]

It seems to be the If Is Null line. If I remove this line, the msgbox line, the Else and the End If, the cascading combo box works okay.

 

[RuralGuy]

Change the DCount to DLookup and see what happens.
If IsNull(DLookup([Subcategory], "tblsubjects", "[Subject] = '" & Me.Subject1 & "'")) Then

 

[alwainwright]

It's still saying it can't find the field. Does it know the [Subject] is in tblsubjects?

 

[RuralGuy]

Some DoDo forgot his quotation marks!
Go back to DCount:
If IsNull(DCount("[Subcategory]", "tblsubjects", "[Subject] = '" & Me.Subject1 & "'")) Then

 

[alwainwright]

Still no joy. I'm not getting any errors now, and the cascade works all right, but there's no msgbox appearing. Is there a last gasp I can try?!

 

[RuralGuy]

Hang in there Alison, we've almost got it. How about putting a breakpoint on the DCount line and hover and see what is being returned?

 

[alwainwright]

The only hover that appears is over '" & Me.Subject1 & "'" at the end of the dcount line, which correctly returns what has been entered in Subject1. I'm a bit of a novice at debugging, so hope I've done it right!

Someone has suggested it has to do with looking for null values as a data-less entry isn't the same as being null, so we should be looking for " " fields instead of IsNull. However, I can't see how to code for that.

 

[RuralGuy]

Hi Alison,
Let's see if we can develop a method that will trigger on either a Null or a blank string.

Try this:
If Len(Trim(Nz(DLookup([Subcategory], "tblsubjects", "[Subject] = '" & Me.Subject1 & "')," "))) = 0 Then

 

[alwainwright]

There's a compile error, it's asking for a list separator or ) and highlighting the section
")))= 0 Then

I can't see what's missing. Sorry to be so useless!

Your help is much appreciated, and will hopefully benefit others too, as I haven't been able to find anything elsewhere on how to do this.

 

[RuralGuy]

Not to worry Alison. I just missed a few quotes! This *will* compile:
If Len(Trim(Nz(DLookup("[Subcategory]", "tblsubjects", "[Subject] = '" & Me.Subject1 & "'")))) = 0 Then

 

[alwainwright]

You'll be glad to know it's working. Only problem is, we've been going down the wrong road slightly, as I ideally need the message box to appear when it's not null, and I'm not sure how to turn this code around to do that. Any suggestions?

You're help has been very much appreciated!
Alison.

 

[RuralGuy]

Sorry Alison, I've been out all day on errands. Try:
If Len(Trim(Nz(DLookup("[Subcategory]", "tblsubjects", "[Subject] = '" & Me.Subject1 & "'")))) > 0 Then

 

[alwainwright]

Of course. That's got it. Thank you very much!

Final code looks like this:

Private Sub Subject1_AfterUpdate()
On Error GoTo Err_Subject1_AfterUpdate
If Len(Trim(Nz(DLookup("[Subcategory]", "tblsubjects", "[Subject] = '" & Me.Subject1 & "'")))) > 0 Then
MsgBox "Please Choose a Subcategory in Subcategory1"
Subcategory1.RowSource _
= "SELECT tblsubjects.Subcategory " _
& "FROM tblSubjects " _
& "WHERE tblsubjects.Subject = '" & Subject1.Value & "' " _
& "ORDER BY tblsubjects.Subcategory;"
End If
Exit_Subject1_AfterUpdate:
Exit Sub
Err_Subject1_AfterUpdate:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume Exit_Subject1_AfterUpdate
End Sub

 

[RuralGuy]

Did you forget the ELSE statement or is that a typo?