Cascading combo boxes

  • Thread starter Thread starter Mike Revis
  • Start date Start date
M

Mike Revis

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 said:
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
Click to expand...


Try using:

If DLookup("*", "qryGetSubCategory") > 0 Then
 
Mike said:
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
Click to expand...


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
 
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
 
My mistake. That should be "[SubCategory]".


Mike Revis said:
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
Click to expand...
 
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Mike said:
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
Click to expand...


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,

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 said:
Mike said:
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
Click to expand...


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.
Click to expand...
 
Mike said:
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.
Click to expand...


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,
We're getting in a little over my head now.

When I close everything and am looking at what used to be the database
window I double click qryGetSubcategory. I get prompted for
[forms]![frmmainmenu]![cboselectcategory]. I type in Breaker. I know that
the category breaker doesn't have a subcategory. There is only one record
that has breaker as a category.

The, excuse my terminology, query results table opens with two columns. 1st
column is subcategory which is blank (empty) and has the focus. Blinking
curser. 2nd column is category which shows Breaker.

The subcategory will not let me enter anything. I think that's called not
updateable?

With my limited understanding I think the query requested the category. I
typed in breaker and the query found 1 record that has breaker as a
category.

The subcategory field is blank because breaker has no subcategory.

Query pasted below.


SELECT DISTINCT tblParts.SubCategory, tblParts.Category
FROM tblParts
WHERE (((tblParts.Category)=[forms]![frmmainmenu]![cboselectcategory]));


Microsoft Access. The most fun you can have with your clothes on.

Regards,
Mike
 
That's what I thought. So you have a choice of how to deal
with it. I think the cleanest approach would be to modify
the query so it does not find records whith a Null
subcategory:

SELECT DISTINCT tblParts.SubCategory
FROM tblParts
WHERE tblParts.Category=Forms!frmmainmenu!cboselectcategory
And tblParts.SubCategory Is Not Null

Note that I removed the Category field from the Select
clause only because **I** don't see why you need it. If you
don't need it and leave it out, don't forget to change the
combo box's ColumnCount to 1

If you have some reason that I can't see to have the Null
SubCategory value returned in the query, then you can
probably get away with changing the DCount to:

If DCount("SubCategory", "qryGetSubCategory") > 0 Then
--
Marsh
MVP [MS Access]


Mike said:
We're getting in a little over my head now.

When I close everything and am looking at what used to be the database
window I double click qryGetSubcategory. I get prompted for
[forms]![frmmainmenu]![cboselectcategory]. I type in Breaker. I know that
the category breaker doesn't have a subcategory. There is only one record
that has breaker as a category.

The, excuse my terminology, query results table opens with two columns. 1st
column is subcategory which is blank (empty) and has the focus. Blinking
curser. 2nd column is category which shows Breaker.

The subcategory will not let me enter anything. I think that's called not
updateable?

With my limited understanding I think the query requested the category. I
typed in breaker and the query found 1 record that has breaker as a
category.

The subcategory field is blank because breaker has no subcategory.

SELECT DISTINCT tblParts.SubCategory, tblParts.Category
FROM tblParts
WHERE (((tblParts.Category)=[forms]![frmmainmenu]![cboselectcategory]));


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?
Click to expand...
Click to expand...
 
Back
Top