Cascading combo box confusion

[meznak]

I have three combo boxes on a form displaying Type, Make, and Model of
machine in the selected record. I only have Model stored in the
record (in tblServers) and the form should pull Type and Make from
another table (tblModel). I am able to get that working by setting
"Control Source = Model" for cbxType and cbxMake. That works for
displaying existing records, but not well for entering new records.

When creating a new record, I want to be able to select Type,
filtering cboMake; select Make, filtering cboModel. I am able to get
this working with the following code:

Private Sub cbxType_AfterUpdate()
Dim strSQL As String
'This function sets the RowSource of cbxMake, based on the
'value selected in cbxType.
sSQL = "SELECT DISTINCT Make " _
& " FROM tblModel WHERE Type = '" & Me.cbxType _
& "' ORDER BY Make"
Me!cbxMake.RowSourceType = "Table/Query"
Me!cbxMake.RowSource = sSQL

(repeated/tweaked for cbxMake)

That function only works when I unbind the combos. I would very much
like to have both functions work on the same form. Does anyone have
suggestions on how to do this?

 

[Arvin Meyer [MVP]]

Record lookups must always be based on unbound list or combo boxes. By
unbound, I mean to the form's recordsource. They must still be bound to a
sql statement, table, or query as a row source.

You can bind a succession of combo or list boxes to the recordsource like
you have appeared to do for creating records. You may need to requery the
rowsource if it doesn't change with your code. Try adding:

Me.cbxMake.Requery

to the end of your code as shown.

 

[Graham Mandeno]

You should leave cbxType and cbxMake as unbound, as their primary purpose is
to narrow down the choice for cbxModel, not to change data.

I suggest you add the tblModel and tblMake tables (joined appropriately) to
the recordsource query of your form and include the Make FK from tblModel
and the Type FK from tblMake in the recordsource.

Then, in your form's Current event code, set your unbound combo box values
and call their AfterUpdate event procedures to update the cascading
RowSources:

cbxType = Me.TypeFKField
cbxType_AfterUpdate
cbxMake = Me.MakeFKField
cbxMake_AfterUpdate

A couple of other points (well, three actually <g>):

1. You do not need to set RowSourceType in your AfterUpdate proc, as it
remains the same.

2. You do not need to explicitly requery the combo box when you change the
RowSource because it will happen automatically.

3. You may find it is faster and reduces flicker if you set the RowSource
only if it needs changing:

If Me!cbxMake.RowSource <> sSQL Then
Me!cbxMake.RowSource = sSQL
End If

 

[meznak]

Graham -

I've taken your tips into account and implemented them. Thanks for
the advice

As for the solution you proposed, I'm a bit confused as to exactly
what you're telling me. I apologize for my naivety, but I am still an
Access newb.

You should leave cbxType and cbxMake as unbound, as their primary purpose is
to narrow down the choice for cbxModel, not to change data.

I suggest you add the tblModel and tblMake tables (joined appropriately) to
the recordsource query of your form and include the Make FK from tblModel
and the Type FK from tblMake in the recordsource.

Then, in your form's Current event code, set your unbound combo box values
and call their AfterUpdate event procedures to update the cascading
RowSources:

cbxType = Me.TypeFKField
cbxType_AfterUpdate
cbxMake = Me.MakeFKField
cbxMake_AfterUpdate

A couple of other points (well, three actually <g>):

1. You do not need to set RowSourceType in your AfterUpdate proc, as it
remains the same.

2. You do not need to explicitly requery the combo box when you change the
RowSource because it will happen automatically.

3. You may find it is faster and reduces flicker if you set the RowSource
only if it needs changing:

If Me!cbxMake.RowSource <> sSQL Then
Me!cbxMake.RowSource = sSQL
End If

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have three combo boxes on a form displaying Type, Make, and Model of
machine in the selected record. I only have Model stored in the
record (in tblServers) and the form should pull Type and Make from
another table (tblModel). I am able to get that working by setting
"Control Source = Model" for cbxType and cbxMake. That works for
displaying existing records, but not well for entering new records.

When creating a new record, I want to be able to select Type,
filtering cboMake; select Make, filtering cboModel. I am able to get
this working with the following code:

Private Sub cbxType_AfterUpdate()
Dim strSQL As String
'This function sets the RowSource of cbxMake, based on the
'value selected in cbxType.
sSQL = "SELECT DISTINCT Make " _
& " FROM tblModel WHERE Type = '" & Me.cbxType _
& "' ORDER BY Make"
Me!cbxMake.RowSourceType = "Table/Query"
Me!cbxMake.RowSource = sSQL

(repeated/tweaked for cbxMake)

That function only works when I unbind the combos. I would very much
like to have both functions work on the same form. Does anyone have
suggestions on how to do this?

 

[Graham Mandeno]

Sorry, it's often hard to gauge your level of experience from your question


I understand that you have four tables (I am assuming some of their names):
- a table of machine types (tblType)
- a table of machine makes (tblMake)
- a table of machine models (tblModel)
- your main table (tblServers)

I initially assumed that there was a one-to-many chain of relationships:
Type to Make, Make to Model, Model to Machine, but looking more closely it
appears you have one-to-many relationships between tblMake and tblModel AND
between tblType and tblModel. This makes better logical sense because a
manufacturer can make more than one type of machine.

So, in your form you want to select the machine type in cbxType and filter
cbxMake to show only the manufacturers who have records in tblModel of that
type.

Then, having selected a Make, you want to filter cbxModel to show only
models of the selected Make AND Type.

Is my understanding all correct?

OK, so it seems you have the AfterUpdate filtering working fine. That's
great! It's the hardest part

What you need to do when displaying an existing record is to simulate the
selections in your two unbound combo boxes (cbxType and cbxMake). To do
this you need to know the type and make of the model corresponding to the
current record.

Now, the Type and Make are not stored in tblServers, so you need to add
those fields to the RecordSource query on which your form is based. Just
add tblModel to the query (with the appropriate join - it should happen
automatically) and add the ModelType and ModelMake fields to the query grid.
(If the form is based on the table, tblServers, not a query, then create a
query using the two tables.)

[Note that it is a bad idea to name a field "Type" as this is a reserved
word in the Access object model, so I changed "Type" to "ModelType" and used
"ModelMake" to match]

Then add the code gave you yesterday to the Form_Current event procedure:
cbxType = Me.ModelType
cbxType_AfterUpdate
cbxMake = Me.ModelMake
cbxMake_AfterUpdate

This simulates the user selecting the the Type and Model by setting their
values and calling the AfterUpdate event procedure.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham -

I've taken your tips into account and implemented them. Thanks for
the advice

As for the solution you proposed, I'm a bit confused as to exactly
what you're telling me. I apologize for my naivety, but I am still an
Access newb.

You should leave cbxType and cbxMake as unbound, as their primary purpose
is
to narrow down the choice for cbxModel, not to change data.

I suggest you add the tblModel and tblMake tables (joined appropriately)
to
the recordsource query of your form and include the Make FK from tblModel
and the Type FK from tblMake in the recordsource.

Then, in your form's Current event code, set your unbound combo box
values
and call their AfterUpdate event procedures to update the cascading
RowSources:

cbxType = Me.TypeFKField
cbxType_AfterUpdate
cbxMake = Me.MakeFKField
cbxMake_AfterUpdate

A couple of other points (well, three actually <g>):

1. You do not need to set RowSourceType in your AfterUpdate proc, as it
remains the same.

2. You do not need to explicitly requery the combo box when you change
the
RowSource because it will happen automatically.

3. You may find it is faster and reduces flicker if you set the RowSource
only if it needs changing:

If Me!cbxMake.RowSource <> sSQL Then
Me!cbxMake.RowSource = sSQL
End If

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have three combo boxes on a form displaying Type, Make, and Model of
machine in the selected record. I only have Model stored in the
record (in tblServers) and the form should pull Type and Make from
another table (tblModel). I am able to get that working by setting
"Control Source = Model" for cbxType and cbxMake. That works for
displaying existing records, but not well for entering new records.

When creating a new record, I want to be able to select Type,
filtering cboMake; select Make, filtering cboModel. I am able to get
this working with the following code:

Private Sub cbxType_AfterUpdate()
Dim strSQL As String
'This function sets the RowSource of cbxMake, based on the
'value selected in cbxType.
sSQL = "SELECT DISTINCT Make " _
& " FROM tblModel WHERE Type = '" & Me.cbxType _
& "' ORDER BY Make"
Me!cbxMake.RowSourceType = "Table/Query"
Me!cbxMake.RowSource = sSQL

(repeated/tweaked for cbxMake)

That function only works when I unbind the combos. I would very much
like to have both functions work on the same form. Does anyone have
suggestions on how to do this?

 

[meznak]

Sorry, it's often hard to gauge your level of experience from your question

I understand completely.

Is my understanding all correct? Perfectly!

What you need to do when displaying an existing record is to simulate the
selections in your two unbound combo boxes (cbxType and cbxMake). To do
this you need to know the type and make of the model corresponding to the
current record.

Now, the Type and Make are not stored in tblServers, so you need to add
those fields to the RecordSource query on which your form is based. Just
add tblModel to the query (with the appropriate join - it should happen
automatically) and add the ModelType and ModelMake fields to the query grid.
(If the form is based on the table, tblServers, not a query, then create a
query using the two tables.)

My form is based on tblServers, not a query. I pasted your
Form_Current code, but got the error "Method or data member not found"
when trying to load the form. I created a RecordSource query for the
form, but now I'm only getting one record; also, the fields in
question are not automatically updated.

[Note that it is a bad idea to name a field "Type" as this is a reserved
word in the Access object model, so I changed "Type" to "ModelType" and used
"ModelMake" to match]

Great advice! All "Type" instances have been changed to "EquipType".

This simulates the user selecting the the Type and Model by setting their
values and calling the AfterUpdate event procedure.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

"meznak" <[email protected]> wrote in message

<snip>

 

[Graham Mandeno]

[snip]

My form is based on tblServers, not a query. I pasted your
Form_Current code, but got the error "Method or data member not found"
when trying to load the form. I created a RecordSource query for the
form, but now I'm only getting one record; also, the fields in
question are not automatically updated.

As I said just above, you need to create a query based on the two tables
(tblServers and tblModel). They should be joined on the one-to-many
relationship fields (i.e. tblServers.Model = tblModel.ModelID, or whatever
your field names are. This should happen automatically when you add the two
tables to the query design screen)

Add to the query grid ALL the fields from tblServers (drag the "*" to the
grid) and the Make and EquipType fields from tblModel.

View the query results and you should see exactly what you see when you open
tblServers with an additional two columns.

Save the query and use the name of the saved query as your form's
RecordSource.

Note that the "fields in question" (I assume you mean the Type and Make
combo boxes) are unbound and need to be updated in code. That is what your
Form_Current event procedure is supposed to do.

If you're still having difficulty, copy and paste the following into your
reply:

a) the SQL code from your RecordSource query (click View->SQL)
b) the Form_Current code
c) the code for both your combo box AfterUpdate events

 

[meznak]

I thought that made perfect sense, but it doesn't seem to be working.
When I type "Me.", EquipType and EquipMake do not show up in the list,
although all the other fields in the query do. If I ignore that, and
type it anyway, I get "Compile error: Method or data member not found"
when trying to view a different record, and the VB debugger points to
Me.EquipType.

RecordSource: SELECT tblServers.*, tblEquipModel.EquipType,
tblEquipModel.EquipMake
FROM tblEquipModel INNER JOIN tblServers ON tblEquipModel.Index =
tblServers.EquipModel;

Private Sub Form_Current()
cbxType = Me.EquipType
cbxType_AfterUpdate
cbxMake = Me.EquipMake
cbxMake_AfterUpdate
End Sub

Private Sub cbxType_AfterUpdate()
Dim strSQL As String

sSQL = "SELECT DISTINCT tblEquipMake.Index, tblEquipMake.EquipMake
" _
& "FROM tblEquipMake " _
& "INNER JOIN tblEquipModel " _
& "ON tblEquipMake.Index = tblEquipModel.EquipMake " _
& "WHERE tblEquipModel.EquipType = " & Me!cbxType & " " _
& "ORDER BY tblEquipMake.EquipMake"

If Me!cbxMake.RowSource <> sSQL Then
Me!cbxMake.RowSource = sSQL
End If

End Sub

Private Sub cbxMake_AfterUpdate()
Dim sSQL As String

sSQL = "SELECT DISTINCT tblEquipModel.Index,
tblEquipModel.EquipModel, " _
& "tblEquipType.Index, tblEquipModel.EquipMake " _
& "FROM tblEquipType " _
& "INNER JOIN (tblEquipMake " _
& "INNER JOIN tblEquipModel " _
& "ON tblEquipMake.Index = tblEquipModel.EquipMake) " _
& "ON tblEquipType.Index = tblEquipModel.EquipType " _
& "WHERE tblEquipModel.EquipType = " & Me!cbxType & " " _
& "AND tblEquipModel.EquipMake = " & Me!cbxMake & " " _
& "ORDER BY tblEquipModel.EquipModel"

If Me!cbxModel.RowSource <> sSQL Then
Me!cbxModel.RowSource = sSQL
End If

End Sub

[snip]

My form is based on tblServers, not a query. I pasted your
Form_Current code, but got the error "Method or data member not found"
when trying to load the form. I created a RecordSource query for the
form, but now I'm only getting one record; also, the fields in
question are not automatically updated.

As I said just above, you need to create a query based on the two tables
(tblServers and tblModel). They should be joined on the one-to-many
relationship fields (i.e. tblServers.Model = tblModel.ModelID, or whatever
your field names are. This should happen automatically when you add the two
tables to the query design screen)

Add to the query grid ALL the fields from tblServers (drag the "*" to the
grid) and the Make and EquipType fields from tblModel.

View the query results and you should see exactly what you see when you open
tblServers with an additional two columns.

Save the query and use the name of the saved query as your form's
RecordSource.

Note that the "fields in question" (I assume you mean the Type and Make
combo boxes) are unbound and need to be updated in code. That is what your
Form_Current event procedure is supposed to do.

If you're still having difficulty, copy and paste the following into your
reply:

a) the SQL code from your RecordSource query (click View->SQL)
b) the Form_Current code
c) the code for both your combo box AfterUpdate events
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Try using a bang (!) rather than a dot.

The field names in the recordsource will not always automatically appear as
form properties if there is no control on the form bound to that field.
They will, on the other hand, appear as members of the form's default
collection. [In case you didn't know the difference, ! references a member
of a collection, while . references a property or method]

Just check, also, that you can open the recordsource query and view all the
records, including the extra two fields.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I thought that made perfect sense, but it doesn't seem to be working.
When I type "Me.", EquipType and EquipMake do not show up in the list,
although all the other fields in the query do. If I ignore that, and
type it anyway, I get "Compile error: Method or data member not found"
when trying to view a different record, and the VB debugger points to
Me.EquipType.

RecordSource: SELECT tblServers.*, tblEquipModel.EquipType,
tblEquipModel.EquipMake
FROM tblEquipModel INNER JOIN tblServers ON tblEquipModel.Index =
tblServers.EquipModel;

Private Sub Form_Current()
cbxType = Me.EquipType
cbxType_AfterUpdate
cbxMake = Me.EquipMake
cbxMake_AfterUpdate
End Sub

Private Sub cbxType_AfterUpdate()
Dim strSQL As String

sSQL = "SELECT DISTINCT tblEquipMake.Index, tblEquipMake.EquipMake
" _
& "FROM tblEquipMake " _
& "INNER JOIN tblEquipModel " _
& "ON tblEquipMake.Index = tblEquipModel.EquipMake " _
& "WHERE tblEquipModel.EquipType = " & Me!cbxType & " " _
& "ORDER BY tblEquipMake.EquipMake"

If Me!cbxMake.RowSource <> sSQL Then
Me!cbxMake.RowSource = sSQL
End If

End Sub

Private Sub cbxMake_AfterUpdate()
Dim sSQL As String

sSQL = "SELECT DISTINCT tblEquipModel.Index,
tblEquipModel.EquipModel, " _
& "tblEquipType.Index, tblEquipModel.EquipMake " _
& "FROM tblEquipType " _
& "INNER JOIN (tblEquipMake " _
& "INNER JOIN tblEquipModel " _
& "ON tblEquipMake.Index = tblEquipModel.EquipMake) " _
& "ON tblEquipType.Index = tblEquipModel.EquipType " _
& "WHERE tblEquipModel.EquipType = " & Me!cbxType & " " _
& "AND tblEquipModel.EquipMake = " & Me!cbxMake & " " _
& "ORDER BY tblEquipModel.EquipModel"

If Me!cbxModel.RowSource <> sSQL Then
Me!cbxModel.RowSource = sSQL
End If

End Sub

[snip]

(If the form is based on the table, tblServers, not a query, then
create
a
query using the two tables.)

My form is based on tblServers, not a query. I pasted your
Form_Current code, but got the error "Method or data member not found"
when trying to load the form. I created a RecordSource query for the
form, but now I'm only getting one record; also, the fields in
question are not automatically updated.

As I said just above, you need to create a query based on the two tables
(tblServers and tblModel). They should be joined on the one-to-many
relationship fields (i.e. tblServers.Model = tblModel.ModelID, or
whatever
your field names are. This should happen automatically when you add the
two
tables to the query design screen)

Add to the query grid ALL the fields from tblServers (drag the "*" to the
grid) and the Make and EquipType fields from tblModel.

View the query results and you should see exactly what you see when you
open
tblServers with an additional two columns.

Save the query and use the name of the saved query as your form's
RecordSource.

Note that the "fields in question" (I assume you mean the Type and Make
combo boxes) are unbound and need to be updated in code. That is what
your
Form_Current event procedure is supposed to do.

If you're still having difficulty, copy and paste the following into your
reply:

a) the SQL code from your RecordSource query (click View->SQL)
b) the Form_Current code
c) the code for both your combo box AfterUpdate events
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[meznak]

That was exactly it! I wasn't aware of the bang/dot difference.

Thanks for so patiently walking me through this; I've learned a lot
and my form is working properly

-- Nate

Try using a bang (!) rather than a dot.

The field names in the recordsource will not always automatically appear as
form properties if there is no control on the form bound to that field.
They will, on the other hand, appear as members of the form's default
collection. [In case you didn't know the difference, ! references a member
of a collection, while . references a property or method]

Just check, also, that you can open the recordsource query and view all the
records, including the extra two fields.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I thought that made perfect sense, but it doesn't seem to be working.
When I type "Me.", EquipType and EquipMake do not show up in the list,
although all the other fields in the query do. If I ignore that, and
type it anyway, I get "Compile error: Method or data member not found"
when trying to view a different record, and the VB debugger points to
Me.EquipType.

RecordSource: SELECT tblServers.*, tblEquipModel.EquipType,
tblEquipModel.EquipMake
FROM tblEquipModel INNER JOIN tblServers ON tblEquipModel.Index =
tblServers.EquipModel;

Private Sub Form_Current()
cbxType = Me.EquipType
cbxType_AfterUpdate
cbxMake = Me.EquipMake
cbxMake_AfterUpdate
End Sub

Private Sub cbxType_AfterUpdate()
Dim strSQL As String

sSQL = "SELECT DISTINCT tblEquipMake.Index, tblEquipMake.EquipMake
" _
& "FROM tblEquipMake " _
& "INNER JOIN tblEquipModel " _
& "ON tblEquipMake.Index = tblEquipModel.EquipMake " _
& "WHERE tblEquipModel.EquipType = " & Me!cbxType & " " _
& "ORDER BY tblEquipMake.EquipMake"

If Me!cbxMake.RowSource <> sSQL Then
Me!cbxMake.RowSource = sSQL
End If

End Sub

Private Sub cbxMake_AfterUpdate()
Dim sSQL As String

sSQL = "SELECT DISTINCT tblEquipModel.Index,
tblEquipModel.EquipModel, " _
& "tblEquipType.Index, tblEquipModel.EquipMake " _
& "FROM tblEquipType " _
& "INNER JOIN (tblEquipMake " _
& "INNER JOIN tblEquipModel " _
& "ON tblEquipMake.Index = tblEquipModel.EquipMake) " _
& "ON tblEquipType.Index = tblEquipModel.EquipType " _
& "WHERE tblEquipModel.EquipType = " & Me!cbxType & " " _
& "AND tblEquipModel.EquipMake = " & Me!cbxMake & " " _
& "ORDER BY tblEquipModel.EquipModel"

If Me!cbxModel.RowSource <> sSQL Then
Me!cbxModel.RowSource = sSQL
End If

End Sub

[snip]
(If the form is based on the table, tblServers, not a query, then
create
a
query using the two tables.)
My form is based on tblServers, not a query. I pasted your
Form_Current code, but got the error "Method or data member not found"
when trying to load the form. I created a RecordSource query for the
form, but now I'm only getting one record; also, the fields in
question are not automatically updated.
As I said just above, you need to create a query based on the two tables
(tblServers and tblModel). They should be joined on the one-to-many
relationship fields (i.e. tblServers.Model = tblModel.ModelID, or
whatever
your field names are. This should happen automatically when you add the
two
tables to the query design screen)
Add to the query grid ALL the fields from tblServers (drag the "*" to the
grid) and the Make and EquipType fields from tblModel.
View the query results and you should see exactly what you see when you
open
tblServers with an additional two columns.
Save the query and use the name of the saved query as your form's
RecordSource.
Note that the "fields in question" (I assume you mean the Type and Make
combo boxes) are unbound and need to be updated in code. That is what
your
Form_Current event procedure is supposed to do.
If you're still having difficulty, copy and paste the following into your
reply:
a) the SQL code from your RecordSource query (click View->SQL)
b) the Form_Current code
c) the code for both your combo box AfterUpdate events

 

[Graham Mandeno]

Hi Nate

That's awesome! I'm glad it's all working now