Hi Group,
WinXP, Access 2007.

I have a set of 3 cascading combo boxes that all work the way I want. So
far.

cboSelectCategory
cboSelectSubCategory
cboSelectPart

I have run into a situation where some categories do not have sub
categories.
All of the queries return the expected result when run individually.
This is what I have now.

Private Sub cboSelectCategory_AfterUpdate()

Me.cboSelectSubCategory.Visible = True
Forms!frmMainMenu!cboSelectSubCategory.RowSourceType = "Table/Query"
Forms!frmMainMenu!cboSelectSubCategory.RowSource = "qryGetSubCategory"

End Sub


For what I need I think I have the concept but I can't find the words.

Private Sub cboSelectCategory_AfterUpdate()


If **qryGetSubCategory returns no records** then

Me.cboSelectPart.Visible = True
Forms!frmMainMenu!cboSelectPart.RowSourceType = "Table/Query"
Forms!frmMainMenu!cboSelectPart.RowSource = "qryGetPartWithoutSubCategory"

Else
Me.cboSelectSubCategory.Visible = True
Forms!frmMainMenu!cboSelectSubCategory.RowSourceType = "Table/Query"
Forms!frmMainMenu!cboSelectSubCategory.RowSource = "qryGetSubCategory"

End If

End Sub

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike

Mike Revis wrote:
>WinXP, Access 2007.
>
>I have a set of 3 cascading combo boxes that all work the way I want. So
>far.
>
>cboSelectCategory
>cboSelectSubCategory
>cboSelectPart
>
>I have run into a situation where some categories do not have sub
>categories.
>All of the queries return the expected result when run individually.
>This is what I have now.
>
>Private Sub cboSelectCategory_AfterUpdate()
>
>Me.cboSelectSubCategory.Visible = True
>Forms!frmMainMenu!cboSelectSubCategory.RowSourceType = "Table/Query"
>Forms!frmMainMenu!cboSelectSubCategory.RowSource = "qryGetSubCategory"
>
>End Sub
>
>For what I need I think I have the concept but I can't find the words.
>
>Private Sub cboSelectCategory_AfterUpdate()
>
>If **qryGetSubCategory returns no records** then
>
>Me.cboSelectPart.Visible = True
>Forms!frmMainMenu!cboSelectPart.RowSourceType = "Table/Query"
>Forms!frmMainMenu!cboSelectPart.RowSource = "qryGetPartWithoutSubCategory"
>
>Else
>Me.cboSelectSubCategory.Visible = True
>Forms!frmMainMenu!cboSelectSubCategory.RowSourceType = "Table/Query"
>Forms!frmMainMenu!cboSelectSubCategory.RowSource = "qryGetSubCategory"
>End If
>
>End Sub


Try using:

If DLookup("*", "qryGetSubCategory") > 0 Then

--
Marsh
MVP [MS Access]

Mike Revis wrote:
>I have a set of 3 cascading combo boxes that all work the way I want. So
>far.
>
>cboSelectCategory
>cboSelectSubCategory
>cboSelectPart
>
>I have run into a situation where some categories do not have sub
>categories.
>All of the queries return the expected result when run individually.
>This is what I have now.
>
>Private Sub cboSelectCategory_AfterUpdate()
>
>Me.cboSelectSubCategory.Visible = True
>Forms!frmMainMenu!cboSelectSubCategory.RowSourceType = "Table/Query"
>Forms!frmMainMenu!cboSelectSubCategory.RowSource = "qryGetSubCategory"
>
>End Sub
>
>
>For what I need I think I have the concept but I can't find the words.
>
>Private Sub cboSelectCategory_AfterUpdate()
>
>
>If **qryGetSubCategory returns no records** then
>
>Me.cboSelectPart.Visible = True
>Forms!frmMainMenu!cboSelectPart.RowSourceType = "Table/Query"
>Forms!frmMainMenu!cboSelectPart.RowSource = "qryGetPartWithoutSubCategory"
>
>Else
>Me.cboSelectSubCategory.Visible = True
>Forms!frmMainMenu!cboSelectSubCategory.RowSourceType = "Table/Query"
>Forms!frmMainMenu!cboSelectSubCategory.RowSource = "qryGetSubCategory"
>
>End If
>
>End Sub


Another way:

Me.cboSelectSubCategory.RowSourceType = "Table/Query"
Me.cboSelectSubCategory.RowSource = "qryGetSubCategory"
Me.cboSelectSubCategory.Visible = _
Me.cboSelectSubCategory.ListCount > 0

If Me.cboSelectSubCategory.ListCount = 0 Then
Me.cboSelectPart.Visible = True
Me.cboSelectPart.RowSourceType = "Table/Query"
Me.cboSelectPart.RowSource = _
"qryGetPartWithoutSubCategory"
End If

--
Marsh
MVP [MS Access]

Thank you Marsh,

When I use the DLookup I keep getting an error of "missing operator". I
can't see it.

So after looking at help for DLookup I decided to replace "*" part with
"[Category]". That works.

Thanks for getting me headed down the right path.

I haven't tried your other suggestion. Maybe later.

Regards,
Mike

"Marshall Barton" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> Mike Revis wrote:
>>WinXP, Access 2007.
>>
>>I have a set of 3 cascading combo boxes that all work the way I want. So
>>far.
>>
>>cboSelectCategory
>>cboSelectSubCategory
>>cboSelectPart
>>
>>I have run into a situation where some categories do not have sub
>>categories.
>>All of the queries return the expected result when run individually.
>>This is what I have now.
>>
>>Private Sub cboSelectCategory_AfterUpdate()
>>
>>Me.cboSelectSubCategory.Visible = True
>>Forms!frmMainMenu!cboSelectSubCategory.RowSourceType = "Table/Query"
>>Forms!frmMainMenu!cboSelectSubCategory.RowSource = "qryGetSubCategory"
>>
>>End Sub
>>
>>For what I need I think I have the concept but I can't find the words.
>>
>>Private Sub cboSelectCategory_AfterUpdate()
>>
>>If **qryGetSubCategory returns no records** then
>>
>>Me.cboSelectPart.Visible = True
>>Forms!frmMainMenu!cboSelectPart.RowSourceType = "Table/Query"
>>Forms!frmMainMenu!cboSelectPart.RowSource = "qryGetPartWithoutSubCategory"
>>
>>Else
>>Me.cboSelectSubCategory.Visible = True
>>Forms!frmMainMenu!cboSelectSubCategory.RowSourceType = "Table/Query"
>>Forms!frmMainMenu!cboSelectSubCategory.RowSource = "qryGetSubCategory"
>>End If
>>
>>End Sub
>
>
> Try using:
>
> If DLookup("*", "qryGetSubCategory") > 0 Then
>
> --
> Marsh
> MVP [MS Access]

My mistake. That should be "[SubCategory]".


"Mike Revis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thank you Marsh,
>
> When I use the DLookup I keep getting an error of "missing operator". I
> can't see it.
>
> So after looking at help for DLookup I decided to replace "*" part with
> "[Category]". That works.
>
> Thanks for getting me headed down the right path.
>
> I haven't tried your other suggestion. Maybe later.
>
> Regards,
> Mike
>
> "Marshall Barton" <(E-Mail Removed)> wrote in message
> news(E-Mail Removed)...
>> Mike Revis wrote:
>>>WinXP, Access 2007.
>>>
>>>I have a set of 3 cascading combo boxes that all work the way I want. So
>>>far.
>>>
>>>cboSelectCategory
>>>cboSelectSubCategory
>>>cboSelectPart
>>>
>>>I have run into a situation where some categories do not have sub
>>>categories.
>>>All of the queries return the expected result when run individually.
>>>This is what I have now.
>>>
>>>Private Sub cboSelectCategory_AfterUpdate()
>>>
>>>Me.cboSelectSubCategory.Visible = True
>>>Forms!frmMainMenu!cboSelectSubCategory.RowSourceType = "Table/Query"
>>>Forms!frmMainMenu!cboSelectSubCategory.RowSource = "qryGetSubCategory"
>>>
>>>End Sub
>>>
>>>For what I need I think I have the concept but I can't find the words.
>>>
>>>Private Sub cboSelectCategory_AfterUpdate()
>>>
>>>If **qryGetSubCategory returns no records** then
>>>
>>>Me.cboSelectPart.Visible = True
>>>Forms!frmMainMenu!cboSelectPart.RowSourceType = "Table/Query"
>>>Forms!frmMainMenu!cboSelectPart.RowSource =
>>>"qryGetPartWithoutSubCategory"
>>>
>>>Else
>>>Me.cboSelectSubCategory.Visible = True
>>>Forms!frmMainMenu!cboSelectSubCategory.RowSourceType = "Table/Query"
>>>Forms!frmMainMenu!cboSelectSubCategory.RowSource = "qryGetSubCategory"
>>>End If
>>>
>>>End Sub
>>
>>
>> Try using:
>>
>> If DLookup("*", "qryGetSubCategory") > 0 Then
>>
>> --
>> Marsh
>> MVP [MS Access]
>
>

The missing operator message almost always means you are
missing a spece or quote or you have a quote in the wrong
place.

"*" should work fine and it can be much faster than using a
field name. The essential difference is that "*" counts all
records and "fieldname" counts the number of non-Null values
in the field.


Mike Revis wrote:
>When I use the DLookup I keep getting an error of "missing operator". I
>can't see it.
>
>So after looking at help for DLookup I decided to replace "*" part with
>"[Category]". That works.
>
>Thanks for getting me headed down the right path.
>
>I haven't tried your other suggestion. Maybe later.
>
>
>"Marshall Barton" wrote
>> If DLookup("*", "qryGetSubCategory") > 0 Then

--
Marsh
MVP [MS Access]

Marsh,
Thanks again for your help.
I went back and tried it again and still get "missing operator" etc.

Here is what I have that produces the desired result followed by what
doesn't. Cut & paste.

Private Sub cboSelectCategory_AfterUpdate()


If DLookup("[subcategory]", "qryGetSubCategory") > 0 Then
Me.cboSelectSubCategory.Visible = True
Me.cboSelectSubCategory.RowSourceType = "table/query"
Me.cboSelectSubCategory.RowSource = "qrygetsubcategory"

Else

Me.cboSelectPart.Visible = True
Forms!frmmainmenu!cboSelectPart.RowSourceType = "Table/Query"
Forms!frmmainmenu!cboSelectPart.RowSource = "qryGetPartWithoutSubCategory"

End If

End Sub


++This gets the error.

Private Sub cboSelectCategory_AfterUpdate()


If DLookup("*", "qryGetSubCategory") > 0 Then
Me.cboSelectSubCategory.Visible = True
Me.cboSelectSubCategory.RowSourceType = "table/query"
Me.cboSelectSubCategory.RowSource = "qrygetsubcategory"

Else

Me.cboSelectPart.Visible = True
Forms!frmmainmenu!cboSelectPart.RowSourceType = "Table/Query"
Forms!frmmainmenu!cboSelectPart.RowSource = "qryGetPartWithoutSubCategory"

End If

End Sub

I've looked and looked and I can't see what I'm doing wrong.

Best regards,
Mike



"Marshall Barton" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> The missing operator message almost always means you are
> missing a spece or quote or you have a quote in the wrong
> place.
>
> "*" should work fine and it can be much faster than using a
> field name. The essential difference is that "*" counts all
> records and "fieldname" counts the number of non-Null values
> in the field.
>
>
> Mike Revis wrote:
>>When I use the DLookup I keep getting an error of "missing operator". I
>>can't see it.
>>
>>So after looking at help for DLookup I decided to replace "*" part with
>>"[Category]". That works.
>>
>>Thanks for getting me headed down the right path.
>>
>>I haven't tried your other suggestion. Maybe later.
>>
>>
>>"Marshall Barton" wrote
>>> If DLookup("*", "qryGetSubCategory") > 0 Then
>
> --
> Marsh
> MVP [MS Access]

Mike Revis wrote:
>I went back and tried it again and still get "missing operator" etc.
>
>Here is what I have that produces the desired result followed by what
>doesn't. Cut & paste.
>
>Private Sub cboSelectCategory_AfterUpdate()
>
>If DLookup("[subcategory]", "qryGetSubCategory") > 0 Then
>Me.cboSelectSubCategory.Visible = True
>Me.cboSelectSubCategory.RowSourceType = "table/query"
>Me.cboSelectSubCategory.RowSource = "qrygetsubcategory"
>
>Else
>
>Me.cboSelectPart.Visible = True
>Forms!frmmainmenu!cboSelectPart.RowSourceType = "Table/Query"
>Forms!frmmainmenu!cboSelectPart.RowSource = "qryGetPartWithoutSubCategory"
>
>End If
>
>End Sub
>
>
>++This gets the error.
>
>Private Sub cboSelectCategory_AfterUpdate()
>
>If DLookup("*", "qryGetSubCategory") > 0 Then
>Me.cboSelectSubCategory.Visible = True
>Me.cboSelectSubCategory.RowSourceType = "table/query"
>Me.cboSelectSubCategory.RowSource = "qrygetsubcategory"
>
>Else
>
>Me.cboSelectPart.Visible = True
>Forms!frmmainmenu!cboSelectPart.RowSourceType = "Table/Query"
>Forms!frmmainmenu!cboSelectPart.RowSource = "qryGetPartWithoutSubCategory"
>
>End If
>
>End Sub


You did nothing wrong beyond taking my word for what the
code should be. I was really thinking DCount, but my idiot
fingers typed DLookup and my eyes read DCount. I can't
believe I did that. I even proof read it twice. What kind
of brain fault am I suffering from? (Note to self: Run the
brain path diagnostics - again)

Let me try one more time:

If DCount("*", "qryGetSubCategory") > 0 Then

Actually, you did well to make the DLookup work. The reason
it worked for you is that DLookup returns Null if it doesn't
find anything. And comparing Null to zero is also Null (not
True or False) so the If fails. That's an obscure way to do
it and if you prefer using DLookup for this, it should be
written:

If Not IsNull(DLookup("subcategory", "qryGetSubCategory"))
Then

Sorry for all the time you wasted on this.

--
Marsh
MVP [MS Access]

Marsh,

I substituted
If DCount("*", "qryGetSubCategory") > 0 Then

It didn't throw an error but it also didn't work as desired. The
cboSelectSubCategory went visible (it shouldn't) but with no records.

The If Not IsNull(DLookup....... one works as desired so I think I'll go
with that for now.

Time spent learning is not wasted time.

Thanks for your help.

Regards,
Mike




"Marshall Barton" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> Mike Revis wrote:
>>I went back and tried it again and still get "missing operator" etc.
>>
>>Here is what I have that produces the desired result followed by what
>>doesn't. Cut & paste.
>>
>>Private Sub cboSelectCategory_AfterUpdate()
>>
>>If DLookup("[subcategory]", "qryGetSubCategory") > 0 Then
>>Me.cboSelectSubCategory.Visible = True
>>Me.cboSelectSubCategory.RowSourceType = "table/query"
>>Me.cboSelectSubCategory.RowSource = "qrygetsubcategory"
>>
>>Else
>>
>>Me.cboSelectPart.Visible = True
>>Forms!frmmainmenu!cboSelectPart.RowSourceType = "Table/Query"
>>Forms!frmmainmenu!cboSelectPart.RowSource = "qryGetPartWithoutSubCategory"
>>
>>End If
>>
>>End Sub
>>
>>
>>++This gets the error.
>>
>>Private Sub cboSelectCategory_AfterUpdate()
>>
>>If DLookup("*", "qryGetSubCategory") > 0 Then
>>Me.cboSelectSubCategory.Visible = True
>>Me.cboSelectSubCategory.RowSourceType = "table/query"
>>Me.cboSelectSubCategory.RowSource = "qrygetsubcategory"
>>
>>Else
>>
>>Me.cboSelectPart.Visible = True
>>Forms!frmmainmenu!cboSelectPart.RowSourceType = "Table/Query"
>>Forms!frmmainmenu!cboSelectPart.RowSource = "qryGetPartWithoutSubCategory"
>>
>>End If
>>
>>End Sub
>
>
> You did nothing wrong beyond taking my word for what the
> code should be. I was really thinking DCount, but my idiot
> fingers typed DLookup and my eyes read DCount. I can't
> believe I did that. I even proof read it twice. What kind
> of brain fault am I suffering from? (Note to self: Run the
> brain path diagnostics - again)
>
> Let me try one more time:
>
> If DCount("*", "qryGetSubCategory") > 0 Then
>
> Actually, you did well to make the DLookup work. The reason
> it worked for you is that DLookup returns Null if it doesn't
> find anything. And comparing Null to zero is also Null (not
> True or False) so the If fails. That's an obscure way to do
> it and if you prefer using DLookup for this, it should be
> written:
>
> If Not IsNull(DLookup("subcategory", "qryGetSubCategory"))
> Then
>
> Sorry for all the time you wasted on this.
>
> --
> Marsh
> MVP [MS Access]

Mike Revis wrote:
>I substituted
> If DCount("*", "qryGetSubCategory") > 0 Then
>
>It didn't throw an error but it also didn't work as desired. The
>cboSelectSubCategory went visible (it shouldn't) but with no records.
>
>The If Not IsNull(DLookup....... one works as desired so I think I'll go
>with that for now.


The difference between the results of those two is that
DCount("*"... counts all the records whereas the
DLookup(""... can find a record with Null in the field and
still take the Else path. Is there a chance that the query
returned such a record?

--
Marsh
MVP [MS Access]