Simple cascading combo boxes

[Graham]

I have a MainTable with an associated Form on which i want to create at least
two, possibly three, or even four cascading combo boxes, to display a single
item on the form. I've created a separate Table2 for the three entries on the
first combo box and created an association between the two tables.(MainTable
& Table2)
Both MainTable & Form display the unique ID (Column 1) rather than the
actual data I want (Column 2) from Table2 ?
I've rechecked the association, and in Form/Control/Properties 'Control
Source', 'Row Source Type' & 'Row Source'.
Should my values from Table2 not now show in the combox on Form ?
I've created aseparate Table3 for around 15 values, only approx five of
which should show up in my second Combobox2, dependent upon the value
selected in Combobox1.
Is this the best way to set this up, and can I get Combobox1 working first
before trying to sort out Combobox2, or should the two be set up at the same
time?
I've found the various advice on this forum very confusing (different ways
of doing the same thing ?), particulary where to place the various "code"
suggested, if necessary ?
Can anyone advise if this can be done without the use of code, for Combobox1
& Combobox2 ?, and the simplest way of getting this to work ?
Many Thanks

 

[Graham]

By deleting the relationships, and deleting the Unique ID column in Table2,
my Combobox1 now works properly in both MainTable & Form. Now onto Combobbox2!

 

[Graham]

I entered the following in the 'After Update' Property of CB1.

Private Sub Resource_AfterUpdate()
On Error Resume Next
Select Case cboResource.Value
Case "System_Engineering_Services"
cboCapability1.RowSource = "tblSES-Primary_Capability"
Case "Management_Services"
cboCapability1.RowSource = "tblMS-Primary_Capability"
Case "Specialist_Services"
cboCapability1.RowSource = "tblSS-Primary_Capability"
End Select
End Sub

It did populate CB2 with the correct options, but didn't alter if I changed
the selection in CB1 ?
After checking various other Properties, I've now lost it again in CB2,
though CB1 is still working Ok ?
Any suggestions ?

 

[John W. Vinson]

It did populate CB2 with the correct options, but didn't alter if I changed
the selection in CB1 ?

You will also need to Requery CB2 in the code.

Having one combo using three different tables as rowsource suggests that your
table structures aren't ideal!

 

[Ken Sheridan]

If you are going to keep the tables normalized you can't really avoid using
code. You'll find an example using three combo boxes at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


Its easy enough to set up in single form view, but trickier with continuous
forms. The demo file covers both, however.

Ken Sheridan
Stafford, England

 

[Graham]

Thanks for your reply John, I'll look into this "requery" business.
You highlighted one of the problems of the forums to a novice, one posting
specifically recommends splitting the information into separate tables,
avoiding multiplication of entries in a single one; another posting will then
state categorically that the opposite is the best way to proceed ? NO doubt
both could be true in particular circumstances, but tricky fro a novice to
know which to follow!

 

[Graham]

Thank you for your reply Ken. I've found a number of postings referring to
your zip file, but the security settings here don't allow the download. If I
have no joy today I'll have to download it at home and view it!
I'll now have to look up 'normalised' & 'continuous forms'!!

 

[Graham]

I've added a line to Requery Cbo2, but now get the error message:
Compile error: method or data member not found
Can anyone spot what I've done wwrong ?
I've also altered the names by putting square brackets around names with
spaces rather than an underscore where the spaces are, Is this Ok ?

Private Sub Resource_AfterUpdate()
On Error Resume Next
Select Case cboResource.Value
Case "[System Engineering Services]"
cboCapability1.RowSource = "tblSESPrimaryCapability"
Case "[Management Services]"
cboCapability1.RowSource = "tblMSPrimaryCapability"
Case "[Specialist Services]"
cboCapability1.RowSource = "tblSSPrimaryCapability"
End Select
Me.cboCapability1.Requery
End Sub


Regards

 

[Graham]

I've changed the name of the cbo2 to make it simpler in caase it didn't like
the previous one! I've moved it above the' End Select ' line, and baack
again, changed the . for a !, still get the same error message. Also deleted
the old cbo2 and recreated it in case of errors - Still no joy, teh latest
attempt is as follows :
Private Sub Resource_AfterUpdate()
On Error Resume Next
Select Case cboResource.Value
Case "[System Engineering Services]"
cboCapability.RowSource = "tblSESPrimaryCapability"
Case "[Management Services]"
cboCapability.RowSource = "tblMSPrimaryCapability"
Case "[Specialist Services]"
cboCapability.RowSource = "tblSSPrimaryCapability"
End Select
Me.[cboCapability.] Requery
End Sub
Anyone see what's wrong ?

I've added a line to Requery Cbo2, but now get the error message:
Compile error: method or data member not found
Can anyone spot what I've done wwrong ?
I've also altered the names by putting square brackets around names with
spaces rather than an underscore where the spaces are, Is this Ok ?

Private Sub Resource_AfterUpdate()
On Error Resume Next
Select Case cboResource.Value
Case "[System Engineering Services]"
cboCapability1.RowSource = "tblSESPrimaryCapability"
Case "[Management Services]"
cboCapability1.RowSource = "tblMSPrimaryCapability"
Case "[Specialist Services]"
cboCapability1.RowSource = "tblSSPrimaryCapability"
End Select
Me.cboCapability1.Requery
End Sub


Regards

Thank you for your reply Ken. I've found a number of postings referring to
your zip file, but the security settings here don't allow the download. If I
have no joy today I'll have to download it at home and view it!
I'll now have to look up 'normalised' & 'continuous forms'!!

 

[John W. Vinson]

Thanks for your reply John, I'll look into this "requery" business.
You highlighted one of the problems of the forums to a novice, one posting
specifically recommends splitting the information into separate tables,
avoiding multiplication of entries in a single one; another posting will then
state categorically that the opposite is the best way to proceed ? NO doubt
both could be true in particular circumstances, but tricky fro a novice to
know which to follow!

Absolutely. I've been working with relational databases (in Oracle,
SQL/Server, MySQL and Access) since the early 1980's and I'm still learning.
It's as much an art as a science, it's controversial with different people
having contradictory strongly held opinions, and it's just plain difficult!

As you learn the principles and get more experience with what works well and
what just makes your life harder, you'll find it getting easier... but it's
still a very complex field.

 

[John W. Vinson]

I've changed the name of the cbo2 to make it simpler in caase it didn't like
the previous one! I've moved it above the' End Select ' line, and baack
again, changed the . for a !, still get the same error message. Also deleted
the old cbo2 and recreated it in case of errors - Still no joy, teh latest
attempt is as follows :
Private Sub Resource_AfterUpdate()
On Error Resume Next
Select Case cboResource.Value
Case "[System Engineering Services]"
cboCapability.RowSource = "tblSESPrimaryCapability"
Case "[Management Services]"
cboCapability.RowSource = "tblMSPrimaryCapability"
Case "[Specialist Services]"
cboCapability.RowSource = "tblSSPrimaryCapability"
End Select
Me.[cboCapability.] Requery
End Sub
Anyone see what's wrong ?

Several things here. Your Private Sub Resource_AfterUpdate suggests that you
have a control (a combo box??) named Resource; but in your code you are
referring to cboResource. What is the name of the control - Resource or
cboResource?

Secondly, does the value in cboResource - or Resource if that's what it's
named - actually consist of a text string containing the square brackets?
Don't confuse putting the *NAMES OF CONTROLS* into square brackets - always
permitted, usually helpful to clarity of your code, often required - with
putting *text strings* in square brackets! If the combo is displaying the text
"System Engineering Services" to the user, then that is exactly the text that
should be in your Case statement.

Finally, I'd suggest using Me!cboCapability.RowSource - that may be the source
of your error message, since you're not explicitly referring to 'an object on
this form' by just referring to cboCapability without a qualifier.

One question: do you have Option Explicit as the first line in your module,
above any and all other code? If not... add it. This will detect cases where
you have some variable which makes sense to you but which Access consideres to
be an undimensioned, undefined variable name.

 

[Graham]

Much thanks for your help again, I've been taking examples too literally.
I'll let you know how I get on.

I've changed the name of the cbo2 to make it simpler in caase it didn't like
the previous one! I've moved it above the' End Select ' line, and baack
again, changed the . for a !, still get the same error message. Also deleted
the old cbo2 and recreated it in case of errors - Still no joy, teh latest
attempt is as follows :
Private Sub Resource_AfterUpdate()
On Error Resume Next
Select Case cboResource.Value
Case "[System Engineering Services]"
cboCapability.RowSource = "tblSESPrimaryCapability"
Case "[Management Services]"
cboCapability.RowSource = "tblMSPrimaryCapability"
Case "[Specialist Services]"
cboCapability.RowSource = "tblSSPrimaryCapability"
End Select
Me.[cboCapability.] Requery
End Sub
Anyone see what's wrong ?

Several things here. Your Private Sub Resource_AfterUpdate suggests that you
have a control (a combo box??) named Resource; but in your code you are
referring to cboResource. What is the name of the control - Resource or
cboResource?

Secondly, does the value in cboResource - or Resource if that's what it's
named - actually consist of a text string containing the square brackets?
Don't confuse putting the *NAMES OF CONTROLS* into square brackets - always
permitted, usually helpful to clarity of your code, often required - with
putting *text strings* in square brackets! If the combo is displaying the text
"System Engineering Services" to the user, then that is exactly the text that
should be in your Case statement.

Finally, I'd suggest using Me!cboCapability.RowSource - that may be the source
of your error message, since you're not explicitly referring to 'an object on
this form' by just referring to cboCapability without a qualifier.

One question: do you have Option Explicit as the first line in your module,
above any and all other code? If not... add it. This will detect cases where
you have some variable which makes sense to you but which Access consideres to
be an undimensioned, undefined variable name.

 

[Graham]

Hi, Still can't get this to work. The first combo box is called 'Resource'
and has only three options, stored on a separate table. This combo box works
OK. These options are purely text strings, with spaces ?
When I select from the first 'Resource' combo box I want the second combo
box 'Capability' to display the contents of their respective tables, which
should be clear from the code below?
I have removed the square brackets [] from the text strings as previously
shown.
I have also removed all instances of 'cbo' from the code except the
'Requery' option where you have specifically included it, but it makes no
difference. Currently they are baack in ?

The very first line becomes highlighted in yellow ?
The 'Requery' line also throws up various error messages. If I change the
exclamation mark for a full stop in this line it stops it highlightng in red
?
I have tried iinserting 'Option explicit' at the very start, but this throws
up into the last line of the previous code and/or displays an error message
if placed further in ?

Private Sub Resource_AfterUpdate()
On Error Resume Next
Select Case Resource.Value
Case "System Engineering Services"
[cboCapability.RowSource] = "tblSESPrimaryCapability"
Case "Management Services"
[cboCapability.RowSource] = "tblMSPrimaryCapability"
Case "Specialist Services"
[cboCapability.RowSource] = "tblSSPrimaryCapability"
End Select
Me!cboCapability.RowSource.Requery
End Sub

Much thanks for your help again, I've been taking examples too literally.
I'll let you know how I get on.

I've changed the name of the cbo2 to make it simpler in caase it didn't like
the previous one! I've moved it above the' End Select ' line, and baack
again, changed the . for a !, still get the same error message. Also deleted
the old cbo2 and recreated it in case of errors - Still no joy, teh latest
attempt is as follows :
Private Sub Resource_AfterUpdate()
On Error Resume Next
Select Case cboResource.Value
Case "[System Engineering Services]"
cboCapability.RowSource = "tblSESPrimaryCapability"
Case "[Management Services]"
cboCapability.RowSource = "tblMSPrimaryCapability"
Case "[Specialist Services]"
cboCapability.RowSource = "tblSSPrimaryCapability"
End Select
Me.[cboCapability.] Requery
End Sub
Anyone see what's wrong ?

Several things here. Your Private Sub Resource_AfterUpdate suggests that you
have a control (a combo box??) named Resource; but in your code you are
referring to cboResource. What is the name of the control - Resource or
cboResource?

Secondly, does the value in cboResource - or Resource if that's what it's
named - actually consist of a text string containing the square brackets?
Don't confuse putting the *NAMES OF CONTROLS* into square brackets - always
permitted, usually helpful to clarity of your code, often required - with
putting *text strings* in square brackets! If the combo is displaying the text
"System Engineering Services" to the user, then that is exactly the text that
should be in your Case statement.

Finally, I'd suggest using Me!cboCapability.RowSource - that may be the source
of your error message, since you're not explicitly referring to 'an object on
this form' by just referring to cboCapability without a qualifier.

One question: do you have Option Explicit as the first line in your module,
above any and all other code? If not... add it. This will detect cases where
you have some variable which makes sense to you but which Access consideres to
be an undimensioned, undefined variable name.

 

[John W. Vinson]

Hi, Still can't get this to work. The first combo box is called 'Resource'
and has only three options, stored on a separate table. This combo box works
OK. These options are purely text strings, with spaces ?

What's the actual content of the table and properties of the combo box -
particularly the ColumnCount and ColumnWidths properties? If it's a wizard
generated Lookup Field combo, then it probably has TWO columns: a number value
that's stored and a Text field that's displayed. You'll need to do your Case
on whichever field is the combo's bound column.]

When I select from the first 'Resource' combo box I want the second combo
box 'Capability' to display the contents of their respective tables, which
should be clear from the code below?
I have removed the square brackets [] from the text strings as previously
shown.
I have also removed all instances of 'cbo' from the code except the
'Requery' option where you have specifically included it, but it makes no
difference. Currently they are baack in ?

The cbo is NOT something Access provides. If the name of the combo box is
cboCapability then you need to requery cboCapability. If the name of the combo
box is Capability then you need to requery Capability! I (like many others)
use a naming convention; when I bind a combo box to a field named Capability
I'll *change the name of the combo box* to cboCapability so that the control
can be distinguished from the field.

The very first line becomes highlighted in yellow ?

The Private Sub? You've lost me here completely.

The 'Requery' line also throws up various error messages. If I change the
exclamation mark for a full stop in this line it stops it highlightng in red
?
I have tried iinserting 'Option explicit' at the very start, but this throws
up into the last line of the previous code and/or displays an error message
if placed further in ?

Option Explicit must be at the ABSOLUTE VERY TOP of your module (or form
module) code, prior to *any other code* - any other routines, any Private Sub
lines, any constant declarations. Clear at the very top.

Private Sub Resource_AfterUpdate()
On Error Resume Next
Select Case Resource.Value
Case "System Engineering Services"
[cboCapability.RowSource] = "tblSESPrimaryCapability"
Case "Management Services"
[cboCapability.RowSource] = "tblMSPrimaryCapability"
Case "Specialist Services"
[cboCapability.RowSource] = "tblSSPrimaryCapability"
End Select
Me!cboCapability.RowSource.Requery
End Sub

:

Delete the Sub altogether. Put in Option Explicit (again, above any and all
other code). Select Debug... Compile <database name>. Fix any errors; the
Option Explicit may make previously invisible errors apparent! Close the form,
and use Tools... Database Utilities... Compact and Repair to compact the
database.

Then open the form in design view and select the combo box. Click the
AfterUpdate event and the ... icon by it, and choose Code Builder to let the
wizard create the Sub form you . Reedit the code. I would STRONGLY suggest
doing two things: bracket *the name of the control*, not the name and property
as one string; and use the Me! qualifier to be sure that it's referencing the
name of the control object on this form. Right now it's looking for a control
named

cboCapability.RowSource

rather than looking for the RowSource property of a combo box. E.g.

Private Sub Resource_AfterUpdate()
On Error GoTo Proc_Error
Select Case Resource ' the Value property is the default, not needed
Case "System Engineering Services"
Me![cboCapability].RowSource = "tblSESPrimaryCapability"
Case "Management Services"
Me![cboCapability].RowSource = "tblMSPrimaryCapability"
Case "Specialist Services"
Me![cboCapability].RowSource = "tblSSPrimaryCapability"
End Select
Me!cboCapability.Requery
End Sub

If the Name property of the Capability combo box is in fact Capability (rather
than cboCapability), either change it; or replace all of the cboCapability
references in the code with the actual name of the combo box.

Do note that using a table as the RowSource of the combo will give possibly
unwanted sort order; normally you would use a sorted Query, such as

= "SELECT Capability FROM tblSESPrimaryCapability ORDER BY Capability"

rather than a table name, to ensure correct sorting.

 

[Graham]

Thanks for your help again on this John.
Each of the new Tables has a single field(column), holding the Values that I
want displayed in the Combo Boxes.
The tblResource holds just the three Resource areas that I want to select
from, the properties box shows a single column, being a single Bound column.

The row source property of the Capability combobox shows the following:

SELECT [SSPrimaryCapability].[Capability] FROM SSPrimaryCapability;

Clearly this property is not being uupdated properly by the code in the
Resource combo ?

After deleting the original Sub, I created a new one with Option Explicit at
the very head. This still forced a 'page break' type line immediately under
it, and appeared to create a Module rather than a Sub ?
After attempting a debug i copied nad pasted it back to the Sub page, but
without the Option Explicit line; this kept popping back up to the end of the
previous Sub ?
I eventually got the debug to accept Select Case "[Resource]".
It didn't like your On Error line so I've reverted to previous.
It didn't like your Me! in front of the [Capability].Rowsource.
It still wont accept [Capability].Rowsource it doesn't recognise it as a
valid name, though it's definately the same as the Control name ?
I can't get it as far as the Requery line as it chokes on the former three

Any further suggestions much appreciated ?

Private Sub Resource_AfterUpdate()
On Error Resume Next
Select Case "[Resource]"
Case "System Engineering Services"
[Capability].RowSource = "tblSESPrimaryCapability"
Case "Management Services"
[Capability].RowSource = "tblMSPrimaryCapability"
Case "Specialist Services"
[Capability].RowSource = "tblSSPrimaryCapability"
End Select
Me!Capability.RowSource.Requery
End Sub

Hi, Still can't get this to work. The first combo box is called 'Resource'
and has only three options, stored on a separate table. This combo box works
OK. These options are purely text strings, with spaces ?

What's the actual content of the table and properties of the combo box -
particularly the ColumnCount and ColumnWidths properties? If it's a wizard
generated Lookup Field combo, then it probably has TWO columns: a number value
that's stored and a Text field that's displayed. You'll need to do your Case
on whichever field is the combo's bound column.]

When I select from the first 'Resource' combo box I want the second combo
box 'Capability' to display the contents of their respective tables, which
should be clear from the code below?
I have removed the square brackets [] from the text strings as previously
shown.
I have also removed all instances of 'cbo' from the code except the
'Requery' option where you have specifically included it, but it makes no
difference. Currently they are baack in ?

The cbo is NOT something Access provides. If the name of the combo box is
cboCapability then you need to requery cboCapability. If the name of the combo
box is Capability then you need to requery Capability! I (like many others)
use a naming convention; when I bind a combo box to a field named Capability
I'll *change the name of the combo box* to cboCapability so that the control
can be distinguished from the field.

The very first line becomes highlighted in yellow ?

The Private Sub? You've lost me here completely.

The 'Requery' line also throws up various error messages. If I change the
exclamation mark for a full stop in this line it stops it highlightng in red
?
I have tried iinserting 'Option explicit' at the very start, but this throws
up into the last line of the previous code and/or displays an error message
if placed further in ?

Option Explicit must be at the ABSOLUTE VERY TOP of your module (or form
module) code, prior to *any other code* - any other routines, any Private Sub
lines, any constant declarations. Clear at the very top.

Private Sub Resource_AfterUpdate()
On Error Resume Next
Select Case Resource.Value
Case "System Engineering Services"
[cboCapability.RowSource] = "tblSESPrimaryCapability"
Case "Management Services"
[cboCapability.RowSource] = "tblMSPrimaryCapability"
Case "Specialist Services"
[cboCapability.RowSource] = "tblSSPrimaryCapability"
End Select
Me!cboCapability.RowSource.Requery
End Sub

:

Delete the Sub altogether. Put in Option Explicit (again, above any and all
other code). Select Debug... Compile <database name>. Fix any errors; the
Option Explicit may make previously invisible errors apparent! Close the form,
and use Tools... Database Utilities... Compact and Repair to compact the
database.

Then open the form in design view and select the combo box. Click the
AfterUpdate event and the ... icon by it, and choose Code Builder to let the
wizard create the Sub form you . Reedit the code. I would STRONGLY suggest
doing two things: bracket *the name of the control*, not the name and property
as one string; and use the Me! qualifier to be sure that it's referencing the
name of the control object on this form. Right now it's looking for a control
named

cboCapability.RowSource

rather than looking for the RowSource property of a combo box. E.g.

Private Sub Resource_AfterUpdate()
On Error GoTo Proc_Error
Select Case Resource ' the Value property is the default, not needed
Case "System Engineering Services"
Me![cboCapability].RowSource = "tblSESPrimaryCapability"
Case "Management Services"
Me![cboCapability].RowSource = "tblMSPrimaryCapability"
Case "Specialist Services"
Me![cboCapability].RowSource = "tblSSPrimaryCapability"
End Select
Me!cboCapability.Requery
End Sub

If the Name property of the Capability combo box is in fact Capability (rather
than cboCapability), either change it; or replace all of the cboCapability
references in the code with the actual name of the combo box.

Do note that using a table as the RowSource of the combo will give possibly
unwanted sort order; normally you would use a sorted Query, such as

= "SELECT Capability FROM tblSESPrimaryCapability ORDER BY Capability"

rather than a table name, to ensure correct sorting.