Linking combo boxes in access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
 
Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos
 
Hi Nikos

Thanx for getting back to me.....I can't find any information that explains
this clearly to me and I am now quite confused so I will appreciate any help.

I have a table called CATEGORY. Within this table is a field called
Category. Within this field are a number of rows Vehicle, Animal etc.....

In my form I have a combo box (ComboCat) that draws its list up from this
Table (tblCATEGORY)

So far so good.....

I then have a second Combo box (ComboClass) and a second table called
CATEGORYCLASS.....within this table I have several fields corresponding to
the records in tblCATEGORY (Vehicle, Animal etc.)

What I am trying to do is:

When I select an entry in my fist Combo box (ComboCat) for eg. Animal.....I
want the corresponding values to be brought into the list of second Combo box
(ComboClass)...in this case the values under the field Animal within the
table CATEGORYCLASS......

What would you suggest would be the best/easiest way to do this? I'd
appreciate any help.....

Mike

Nikos Yannacopoulos said:
Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos
Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
Click to expand...
Click to expand...
 
Mike,

I understand what you are trying to do, what I'm not clear on is your
table and field names (for instance, is it CATEGORY or tblCATEGORY? you
mention both) so I can understand how the two tables are related and
provide exact syntax. Can you please post *the exact names* of both
tables and fields in them, and some sample data?

Nikos
Hi Nikos

Thanx for getting back to me.....I can't find any information that explains
this clearly to me and I am now quite confused so I will appreciate any help.

I have a table called CATEGORY. Within this table is a field called
Category. Within this field are a number of rows Vehicle, Animal etc.....

In my form I have a combo box (ComboCat) that draws its list up from this
Table (tblCATEGORY)

So far so good.....

I then have a second Combo box (ComboClass) and a second table called
CATEGORYCLASS.....within this table I have several fields corresponding to
the records in tblCATEGORY (Vehicle, Animal etc.)

What I am trying to do is:

When I select an entry in my fist Combo box (ComboCat) for eg. Animal.....I
want the corresponding values to be brought into the list of second Combo box
(ComboClass)...in this case the values under the field Animal within the
table CATEGORYCLASS......

What would you suggest would be the best/easiest way to do this? I'd
appreciate any help.....

Mike

:

Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos
Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
Click to expand...
Click to expand...
Click to expand...
 
Sorry Nikos....yes

Tables:

Name: Category
Field name: Category
Data within field: Accommodation, Cultural, Entertainment etc...

Name: CategoryClass
Field Names: Accommodation, Cultural, Entertainment etc....
Data within fields: Relevant to field name....

I hope this makes it a bit more clear to you. I appreciate you time and
help...

Mike

Nikos Yannacopoulos said:
Mike,

I understand what you are trying to do, what I'm not clear on is your
table and field names (for instance, is it CATEGORY or tblCATEGORY? you
mention both) so I can understand how the two tables are related and
provide exact syntax. Can you please post *the exact names* of both
tables and fields in them, and some sample data?

Nikos
Hi Nikos

Thanx for getting back to me.....I can't find any information that explains
this clearly to me and I am now quite confused so I will appreciate any help.

I have a table called CATEGORY. Within this table is a field called
Category. Within this field are a number of rows Vehicle, Animal etc.....

In my form I have a combo box (ComboCat) that draws its list up from this
Table (tblCATEGORY)

So far so good.....

I then have a second Combo box (ComboClass) and a second table called
CATEGORYCLASS.....within this table I have several fields corresponding to
the records in tblCATEGORY (Vehicle, Animal etc.)

What I am trying to do is:

When I select an entry in my fist Combo box (ComboCat) for eg. Animal.....I
want the corresponding values to be brought into the list of second Combo box
(ComboClass)...in this case the values under the field Animal within the
table CATEGORYCLASS......

What would you suggest would be the best/easiest way to do this? I'd
appreciate any help.....

Mike

:

Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos

MikeT wrote:

Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
Click to expand...
Click to expand...
Click to expand...
 
Mike,

I'm afraid the only thing this makes clear is your original approach to
the SQL statement for the second combo's recordsource... it makes me
seriously doubt your data structure design though... one table's field's
data being another table's fieldnames is highly unusual, to say the
least, so even if you get this particular one working (I mean the
dependent combo), you are more than likely to abng into more walls
further down your development.
Would you care to explain in plain english what you are trying to
achieve with this data structure, so you might be offered a more robust
design suggestion (be it by me or anybody else)?

Nikos
Sorry Nikos....yes

Tables:

Name: Category
Field name: Category
Data within field: Accommodation, Cultural, Entertainment etc...

Name: CategoryClass
Field Names: Accommodation, Cultural, Entertainment etc....
Data within fields: Relevant to field name....

I hope this makes it a bit more clear to you. I appreciate you time and
help...

Mike

:

Mike,

I understand what you are trying to do, what I'm not clear on is your
table and field names (for instance, is it CATEGORY or tblCATEGORY? you
mention both) so I can understand how the two tables are related and
provide exact syntax. Can you please post *the exact names* of both
tables and fields in them, and some sample data?

Nikos
Hi Nikos

Thanx for getting back to me.....I can't find any information that explains
this clearly to me and I am now quite confused so I will appreciate any help.

I have a table called CATEGORY. Within this table is a field called
Category. Within this field are a number of rows Vehicle, Animal etc.....

In my form I have a combo box (ComboCat) that draws its list up from this
Table (tblCATEGORY)

So far so good.....

I then have a second Combo box (ComboClass) and a second table called
CATEGORYCLASS.....within this table I have several fields corresponding to
the records in tblCATEGORY (Vehicle, Animal etc.)

What I am trying to do is:

When I select an entry in my fist Combo box (ComboCat) for eg. Animal.....I
want the corresponding values to be brought into the list of second Combo box
(ComboClass)...in this case the values under the field Animal within the
table CATEGORYCLASS......

What would you suggest would be the best/easiest way to do this? I'd
appreciate any help.....

Mike

:



Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos

MikeT wrote:


Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
Click to expand...
Click to expand...
Click to expand...
 
I can only agree with you Nikos. Before I carry on I think I need to have
another good look at relational data base design.

I do not want to waste your time anymore than I have. Thankyou for trying to
help me out but I'm afraid that I still have a bit more homework to do......

I appreciate it.....

Mike

Nikos Yannacopoulos said:
Mike,

I'm afraid the only thing this makes clear is your original approach to
the SQL statement for the second combo's recordsource... it makes me
seriously doubt your data structure design though... one table's field's
data being another table's fieldnames is highly unusual, to say the
least, so even if you get this particular one working (I mean the
dependent combo), you are more than likely to abng into more walls
further down your development.
Would you care to explain in plain english what you are trying to
achieve with this data structure, so you might be offered a more robust
design suggestion (be it by me or anybody else)?

Nikos
Sorry Nikos....yes

Tables:

Name: Category
Field name: Category
Data within field: Accommodation, Cultural, Entertainment etc...

Name: CategoryClass
Field Names: Accommodation, Cultural, Entertainment etc....
Data within fields: Relevant to field name....

I hope this makes it a bit more clear to you. I appreciate you time and
help...

Mike

:

Mike,

I understand what you are trying to do, what I'm not clear on is your
table and field names (for instance, is it CATEGORY or tblCATEGORY? you
mention both) so I can understand how the two tables are related and
provide exact syntax. Can you please post *the exact names* of both
tables and fields in them, and some sample data?

Nikos

MikeT wrote:

Hi Nikos

Thanx for getting back to me.....I can't find any information that explains
this clearly to me and I am now quite confused so I will appreciate any help.

I have a table called CATEGORY. Within this table is a field called
Category. Within this field are a number of rows Vehicle, Animal etc.....

In my form I have a combo box (ComboCat) that draws its list up from this
Table (tblCATEGORY)

So far so good.....

I then have a second Combo box (ComboClass) and a second table called
CATEGORYCLASS.....within this table I have several fields corresponding to
the records in tblCATEGORY (Vehicle, Animal etc.)

What I am trying to do is:

When I select an entry in my fist Combo box (ComboCat) for eg. Animal.....I
want the corresponding values to be brought into the list of second Combo box
(ComboClass)...in this case the values under the field Animal within the
table CATEGORYCLASS......

What would you suggest would be the best/easiest way to do this? I'd
appreciate any help.....

Mike

:



Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos

MikeT wrote:


Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
Click to expand...
Click to expand...
Click to expand...
 
You're still welcome to post back if you need help with your design!
I can only agree with you Nikos. Before I carry on I think I need to have
another good look at relational data base design.

I do not want to waste your time anymore than I have. Thankyou for trying to
help me out but I'm afraid that I still have a bit more homework to do......

I appreciate it.....

Mike

:

Mike,

I'm afraid the only thing this makes clear is your original approach to
the SQL statement for the second combo's recordsource... it makes me
seriously doubt your data structure design though... one table's field's
data being another table's fieldnames is highly unusual, to say the
least, so even if you get this particular one working (I mean the
dependent combo), you are more than likely to abng into more walls
further down your development.
Would you care to explain in plain english what you are trying to
achieve with this data structure, so you might be offered a more robust
design suggestion (be it by me or anybody else)?

Nikos
Sorry Nikos....yes

Tables:

Name: Category
Field name: Category
Data within field: Accommodation, Cultural, Entertainment etc...

Name: CategoryClass
Field Names: Accommodation, Cultural, Entertainment etc....
Data within fields: Relevant to field name....

I hope this makes it a bit more clear to you. I appreciate you time and
help...

Mike

:



Mike,

I understand what you are trying to do, what I'm not clear on is your
table and field names (for instance, is it CATEGORY or tblCATEGORY? you
mention both) so I can understand how the two tables are related and
provide exact syntax. Can you please post *the exact names* of both
tables and fields in them, and some sample data?

Nikos

MikeT wrote:


Hi Nikos

Thanx for getting back to me.....I can't find any information that explains
this clearly to me and I am now quite confused so I will appreciate any help.

I have a table called CATEGORY. Within this table is a field called
Category. Within this field are a number of rows Vehicle, Animal etc.....

In my form I have a combo box (ComboCat) that draws its list up from this
Table (tblCATEGORY)

So far so good.....

I then have a second Combo box (ComboClass) and a second table called
CATEGORYCLASS.....within this table I have several fields corresponding to
the records in tblCATEGORY (Vehicle, Animal etc.)

What I am trying to do is:

When I select an entry in my fist Combo box (ComboCat) for eg. Animal.....I
want the corresponding values to be brought into the list of second Combo box
(ComboClass)...in this case the values under the field Animal within the
table CATEGORYCLASS......

What would you suggest would be the best/easiest way to do this? I'd
appreciate any help.....

Mike

:




Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos

MikeT wrote:



Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
Click to expand...
Click to expand...
Click to expand...
 
Not sure if this helps, however this link will take you to a good tutorial on
how to do exactly what you are asking...

http://www.microsoft-accesssolutions.co.uk/filter_combo_boxes.htm

Nikos Yannacopoulos said:
You're still welcome to post back if you need help with your design!
I can only agree with you Nikos. Before I carry on I think I need to have
another good look at relational data base design.

I do not want to waste your time anymore than I have. Thankyou for trying to
help me out but I'm afraid that I still have a bit more homework to do......

I appreciate it.....

Mike

:

Mike,

I'm afraid the only thing this makes clear is your original approach to
the SQL statement for the second combo's recordsource... it makes me
seriously doubt your data structure design though... one table's field's
data being another table's fieldnames is highly unusual, to say the
least, so even if you get this particular one working (I mean the
dependent combo), you are more than likely to abng into more walls
further down your development.
Would you care to explain in plain english what you are trying to
achieve with this data structure, so you might be offered a more robust
design suggestion (be it by me or anybody else)?

Nikos

MikeT wrote:

Sorry Nikos....yes

Tables:

Name: Category
Field name: Category
Data within field: Accommodation, Cultural, Entertainment etc...

Name: CategoryClass
Field Names: Accommodation, Cultural, Entertainment etc....
Data within fields: Relevant to field name....

I hope this makes it a bit more clear to you. I appreciate you time and
help...

Mike

:



Mike,

I understand what you are trying to do, what I'm not clear on is your
table and field names (for instance, is it CATEGORY or tblCATEGORY? you
mention both) so I can understand how the two tables are related and
provide exact syntax. Can you please post *the exact names* of both
tables and fields in them, and some sample data?

Nikos

MikeT wrote:


Hi Nikos

Thanx for getting back to me.....I can't find any information that explains
this clearly to me and I am now quite confused so I will appreciate any help.

I have a table called CATEGORY. Within this table is a field called
Category. Within this field are a number of rows Vehicle, Animal etc.....

In my form I have a combo box (ComboCat) that draws its list up from this
Table (tblCATEGORY)

So far so good.....

I then have a second Combo box (ComboClass) and a second table called
CATEGORYCLASS.....within this table I have several fields corresponding to
the records in tblCATEGORY (Vehicle, Animal etc.)

What I am trying to do is:

When I select an entry in my fist Combo box (ComboCat) for eg. Animal.....I
want the corresponding values to be brought into the list of second Combo box
(ComboClass)...in this case the values under the field Animal within the
table CATEGORYCLASS......

What would you suggest would be the best/easiest way to do this? I'd
appreciate any help.....

Mike

:




Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos

MikeT wrote:



Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
Click to expand...
Click to expand...
Click to expand...
 
Disregard my last post, as you seem to have that already. My apologies.

Nikos Yannacopoulos said:
You're still welcome to post back if you need help with your design!
I can only agree with you Nikos. Before I carry on I think I need to have
another good look at relational data base design.

I do not want to waste your time anymore than I have. Thankyou for trying to
help me out but I'm afraid that I still have a bit more homework to do......

I appreciate it.....

Mike

:

Mike,

I'm afraid the only thing this makes clear is your original approach to
the SQL statement for the second combo's recordsource... it makes me
seriously doubt your data structure design though... one table's field's
data being another table's fieldnames is highly unusual, to say the
least, so even if you get this particular one working (I mean the
dependent combo), you are more than likely to abng into more walls
further down your development.
Would you care to explain in plain english what you are trying to
achieve with this data structure, so you might be offered a more robust
design suggestion (be it by me or anybody else)?

Nikos

MikeT wrote:

Sorry Nikos....yes

Tables:

Name: Category
Field name: Category
Data within field: Accommodation, Cultural, Entertainment etc...

Name: CategoryClass
Field Names: Accommodation, Cultural, Entertainment etc....
Data within fields: Relevant to field name....

I hope this makes it a bit more clear to you. I appreciate you time and
help...

Mike

:



Mike,

I understand what you are trying to do, what I'm not clear on is your
table and field names (for instance, is it CATEGORY or tblCATEGORY? you
mention both) so I can understand how the two tables are related and
provide exact syntax. Can you please post *the exact names* of both
tables and fields in them, and some sample data?

Nikos

MikeT wrote:


Hi Nikos

Thanx for getting back to me.....I can't find any information that explains
this clearly to me and I am now quite confused so I will appreciate any help.

I have a table called CATEGORY. Within this table is a field called
Category. Within this field are a number of rows Vehicle, Animal etc.....

In my form I have a combo box (ComboCat) that draws its list up from this
Table (tblCATEGORY)

So far so good.....

I then have a second Combo box (ComboClass) and a second table called
CATEGORYCLASS.....within this table I have several fields corresponding to
the records in tblCATEGORY (Vehicle, Animal etc.)

What I am trying to do is:

When I select an entry in my fist Combo box (ComboCat) for eg. Animal.....I
want the corresponding values to be brought into the list of second Combo box
(ComboClass)...in this case the values under the field Animal within the
table CATEGORYCLASS......

What would you suggest would be the best/easiest way to do this? I'd
appreciate any help.....

Mike

:




Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos

MikeT wrote:



Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
Click to expand...
Click to expand...
Click to expand...
 
Hi Nikos...

Just to let you know....The problem at the end of the day was very stupid of
me. The code was correct but I put it in the second combo box instead of the
first!! Being new to Access I then went on chasing my tail to try to do it
another way which confused me even more....;-)

With regard to the table design. I should have mentioned that this is purly
for data capturing so the combo boxes bring up specific information - hence
the confusing table structure.
I could have created more tables and relationships but in this case the
easies solution was to simply create an unorthodox table to store the data.

You did help me out quite a bit though by telling me that I am dealing with
'dependant combos' which after searching brought me straight to the info that
I had been looking for with the bonus of a couple over very good links....

So thanx a lot...I'm sure you'll come accross me again though...
Cheers
Mike

Nikos Yannacopoulos said:
You're still welcome to post back if you need help with your design!
I can only agree with you Nikos. Before I carry on I think I need to have
another good look at relational data base design.

I do not want to waste your time anymore than I have. Thankyou for trying to
help me out but I'm afraid that I still have a bit more homework to do......

I appreciate it.....

Mike

:

Mike,

I'm afraid the only thing this makes clear is your original approach to
the SQL statement for the second combo's recordsource... it makes me
seriously doubt your data structure design though... one table's field's
data being another table's fieldnames is highly unusual, to say the
least, so even if you get this particular one working (I mean the
dependent combo), you are more than likely to abng into more walls
further down your development.
Would you care to explain in plain english what you are trying to
achieve with this data structure, so you might be offered a more robust
design suggestion (be it by me or anybody else)?

Nikos

MikeT wrote:

Sorry Nikos....yes

Tables:

Name: Category
Field name: Category
Data within field: Accommodation, Cultural, Entertainment etc...

Name: CategoryClass
Field Names: Accommodation, Cultural, Entertainment etc....
Data within fields: Relevant to field name....

I hope this makes it a bit more clear to you. I appreciate you time and
help...

Mike

:



Mike,

I understand what you are trying to do, what I'm not clear on is your
table and field names (for instance, is it CATEGORY or tblCATEGORY? you
mention both) so I can understand how the two tables are related and
provide exact syntax. Can you please post *the exact names* of both
tables and fields in them, and some sample data?

Nikos

MikeT wrote:


Hi Nikos

Thanx for getting back to me.....I can't find any information that explains
this clearly to me and I am now quite confused so I will appreciate any help.

I have a table called CATEGORY. Within this table is a field called
Category. Within this field are a number of rows Vehicle, Animal etc.....

In my form I have a combo box (ComboCat) that draws its list up from this
Table (tblCATEGORY)

So far so good.....

I then have a second Combo box (ComboClass) and a second table called
CATEGORYCLASS.....within this table I have several fields corresponding to
the records in tblCATEGORY (Vehicle, Animal etc.)

What I am trying to do is:

When I select an entry in my fist Combo box (ComboCat) for eg. Animal.....I
want the corresponding values to be brought into the list of second Combo box
(ComboClass)...in this case the values under the field Animal within the
table CATEGORYCLASS......

What would you suggest would be the best/easiest way to do this? I'd
appreciate any help.....

Mike

:




Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos

MikeT wrote:



Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
Click to expand...
Click to expand...
Click to expand...
 
Hopefully my credibility isn't shattered at this point...

Let's assume the following is true:

_________________________
|Table Name= tblCategory|
-------------------------
Column1: "Category" [DataType= String]

----------------------------
|Table Name= tblCategoryClass|
-----------------------------
Column1: "ClassValue" [DataType = whatever, doesn't matter)
Column2: "Category" [Datatype = String] <---- Foreign key to the tblCategory



So if you have two combo boxes named "cmbCategory, and cmbClassValue,
holding the tblCategory.Category and tblCategoryClass.ClassValue information
respectively. If you want the ClassValue list to be "filtered" by the
Category selection, use the following...

************************************
Private Sub cmbCategory_AfterUpdate()

Dim strSql As String

strSql = "select tblCategoryClass.ClassValue from tblCategoryClass where
tblCategoryClass.Category = """ &
strSql = strSQL & me.cmbCategory.value & """
Me.ClassValue.RowSource = strSql
Me.ClassValue.Requery

End Sub
*************************************


Try to apply this to your specific situation. Understand the trick here is
this sub-procedure is assigned to the first combo-box, and affects the
row-source of the second combo-box.

Additionality Nikos was on the right track earlier on, however I think he
was missing the necessary "value" property of the me.ComboCate object.

tfossum said:
Disregard my last post, as you seem to have that already. My apologies.

Nikos Yannacopoulos said:
You're still welcome to post back if you need help with your design!
I can only agree with you Nikos. Before I carry on I think I need to have
another good look at relational data base design.

I do not want to waste your time anymore than I have. Thankyou for trying to
help me out but I'm afraid that I still have a bit more homework to do......

I appreciate it.....

Mike

:


Mike,

I'm afraid the only thing this makes clear is your original approach to
the SQL statement for the second combo's recordsource... it makes me
seriously doubt your data structure design though... one table's field's
data being another table's fieldnames is highly unusual, to say the
least, so even if you get this particular one working (I mean the
dependent combo), you are more than likely to abng into more walls
further down your development.
Would you care to explain in plain english what you are trying to
achieve with this data structure, so you might be offered a more robust
design suggestion (be it by me or anybody else)?

Nikos

MikeT wrote:

Sorry Nikos....yes

Tables:

Name: Category
Field name: Category
Data within field: Accommodation, Cultural, Entertainment etc...

Name: CategoryClass
Field Names: Accommodation, Cultural, Entertainment etc....
Data within fields: Relevant to field name....

I hope this makes it a bit more clear to you. I appreciate you time and
help...

Mike

:



Mike,

I understand what you are trying to do, what I'm not clear on is your
table and field names (for instance, is it CATEGORY or tblCATEGORY? you
mention both) so I can understand how the two tables are related and
provide exact syntax. Can you please post *the exact names* of both
tables and fields in them, and some sample data?

Nikos

MikeT wrote:


Hi Nikos

Thanx for getting back to me.....I can't find any information that explains
this clearly to me and I am now quite confused so I will appreciate any help.

I have a table called CATEGORY. Within this table is a field called
Category. Within this field are a number of rows Vehicle, Animal etc.....

In my form I have a combo box (ComboCat) that draws its list up from this
Table (tblCATEGORY)

So far so good.....

I then have a second Combo box (ComboClass) and a second table called
CATEGORYCLASS.....within this table I have several fields corresponding to
the records in tblCATEGORY (Vehicle, Animal etc.)

What I am trying to do is:

When I select an entry in my fist Combo box (ComboCat) for eg. Animal.....I
want the corresponding values to be brought into the list of second Combo box
(ComboClass)...in this case the values under the field Animal within the
table CATEGORYCLASS......

What would you suggest would be the best/easiest way to do this? I'd
appreciate any help.....

Mike

:




Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos

MikeT wrote:



Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
Click to expand...
Click to expand...
Click to expand...
 
Hey tfossum

Thanx.....the only good thing about having to fight to get things right is
that you will never forget the solution.....

Credibility....well lets see.....I have my form all done now. I would now
like to enter data into 1 field (TelephoneNo)(primary key)....then click on a
button control which should then check my table (AllRecords) to see if there
is an existing entry.

If there is then I would like to be able to view the record and edit it if
need be.....other wise I would like to simply carry on entering a new
record....?

I have played around a bit but the closest I have come is a button control
that brings up a find/replace msgbox which I don't particulary like

Any ideas...?

Mike

tfossum said:
Hopefully my credibility isn't shattered at this point...

Let's assume the following is true:

_________________________
|Table Name= tblCategory|
-------------------------
Column1: "Category" [DataType= String]

----------------------------
|Table Name= tblCategoryClass|
-----------------------------
Column1: "ClassValue" [DataType = whatever, doesn't matter)
Column2: "Category" [Datatype = String] <---- Foreign key to the tblCategory



So if you have two combo boxes named "cmbCategory, and cmbClassValue,
holding the tblCategory.Category and tblCategoryClass.ClassValue information
respectively. If you want the ClassValue list to be "filtered" by the
Category selection, use the following...

************************************
Private Sub cmbCategory_AfterUpdate()

Dim strSql As String

strSql = "select tblCategoryClass.ClassValue from tblCategoryClass where
tblCategoryClass.Category = """ &
strSql = strSQL & me.cmbCategory.value & """
Me.ClassValue.RowSource = strSql
Me.ClassValue.Requery

End Sub
*************************************


Try to apply this to your specific situation. Understand the trick here is
this sub-procedure is assigned to the first combo-box, and affects the
row-source of the second combo-box.

Additionality Nikos was on the right track earlier on, however I think he
was missing the necessary "value" property of the me.ComboCate object.

tfossum said:
Disregard my last post, as you seem to have that already. My apologies.

Nikos Yannacopoulos said:
You're still welcome to post back if you need help with your design!

MikeT wrote:
I can only agree with you Nikos. Before I carry on I think I need to have
another good look at relational data base design.

I do not want to waste your time anymore than I have. Thankyou for trying to
help me out but I'm afraid that I still have a bit more homework to do......

I appreciate it.....

Mike

:


Mike,

I'm afraid the only thing this makes clear is your original approach to
the SQL statement for the second combo's recordsource... it makes me
seriously doubt your data structure design though... one table's field's
data being another table's fieldnames is highly unusual, to say the
least, so even if you get this particular one working (I mean the
dependent combo), you are more than likely to abng into more walls
further down your development.
Would you care to explain in plain english what you are trying to
achieve with this data structure, so you might be offered a more robust
design suggestion (be it by me or anybody else)?

Nikos

MikeT wrote:

Sorry Nikos....yes

Tables:

Name: Category
Field name: Category
Data within field: Accommodation, Cultural, Entertainment etc...

Name: CategoryClass
Field Names: Accommodation, Cultural, Entertainment etc....
Data within fields: Relevant to field name....

I hope this makes it a bit more clear to you. I appreciate you time and
help...

Mike

:



Mike,

I understand what you are trying to do, what I'm not clear on is your
table and field names (for instance, is it CATEGORY or tblCATEGORY? you
mention both) so I can understand how the two tables are related and
provide exact syntax. Can you please post *the exact names* of both
tables and fields in them, and some sample data?

Nikos

MikeT wrote:


Hi Nikos

Thanx for getting back to me.....I can't find any information that explains
this clearly to me and I am now quite confused so I will appreciate any help.

I have a table called CATEGORY. Within this table is a field called
Category. Within this field are a number of rows Vehicle, Animal etc.....

In my form I have a combo box (ComboCat) that draws its list up from this
Table (tblCATEGORY)

So far so good.....

I then have a second Combo box (ComboClass) and a second table called
CATEGORYCLASS.....within this table I have several fields corresponding to
the records in tblCATEGORY (Vehicle, Animal etc.)

What I am trying to do is:

When I select an entry in my fist Combo box (ComboCat) for eg. Animal.....I
want the corresponding values to be brought into the list of second Combo box
(ComboClass)...in this case the values under the field Animal within the
table CATEGORYCLASS......

What would you suggest would be the best/easiest way to do this? I'd
appreciate any help.....

Mike

:




Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos

MikeT wrote:



Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
Click to expand...
Click to expand...
Click to expand...
 
Well, this was the reasonable thing to assume - my assumption too, if
you read further up the thread - only Mike's table design is different,
and we have already established that!

Furthermore, a reference to a control without any specific property
reference returns its value by default, that's why I usually omit it.
Not necessary at all, try it!

Nikos
Hopefully my credibility isn't shattered at this point...

Let's assume the following is true:

_________________________
|Table Name= tblCategory|
-------------------------
Column1: "Category" [DataType= String]

----------------------------
|Table Name= tblCategoryClass|
-----------------------------
Column1: "ClassValue" [DataType = whatever, doesn't matter)
Column2: "Category" [Datatype = String] <---- Foreign key to the tblCategory



So if you have two combo boxes named "cmbCategory, and cmbClassValue,
holding the tblCategory.Category and tblCategoryClass.ClassValue information
respectively. If you want the ClassValue list to be "filtered" by the
Category selection, use the following...

************************************
Private Sub cmbCategory_AfterUpdate()

Dim strSql As String

strSql = "select tblCategoryClass.ClassValue from tblCategoryClass where
tblCategoryClass.Category = """ &
strSql = strSQL & me.cmbCategory.value & """
Me.ClassValue.RowSource = strSql
Me.ClassValue.Requery

End Sub
*************************************


Try to apply this to your specific situation. Understand the trick here is
this sub-procedure is assigned to the first combo-box, and affects the
row-source of the second combo-box.

Additionality Nikos was on the right track earlier on, however I think he
was missing the necessary "value" property of the me.ComboCate object.

:

Disregard my last post, as you seem to have that already. My apologies.

:

You're still welcome to post back if you need help with your design!

MikeT wrote:

I can only agree with you Nikos. Before I carry on I think I need to have
another good look at relational data base design.

I do not want to waste your time anymore than I have. Thankyou for trying to
help me out but I'm afraid that I still have a bit more homework to do......

I appreciate it.....

Mike

:



Mike,

I'm afraid the only thing this makes clear is your original approach to
the SQL statement for the second combo's recordsource... it makes me
seriously doubt your data structure design though... one table's field's
data being another table's fieldnames is highly unusual, to say the
least, so even if you get this particular one working (I mean the
dependent combo), you are more than likely to abng into more walls
further down your development.
Would you care to explain in plain english what you are trying to
achieve with this data structure, so you might be offered a more robust
design suggestion (be it by me or anybody else)?

Nikos

MikeT wrote:


Sorry Nikos....yes

Tables:

Name: Category
Field name: Category
Data within field: Accommodation, Cultural, Entertainment etc...

Name: CategoryClass
Field Names: Accommodation, Cultural, Entertainment etc....
Data within fields: Relevant to field name....

I hope this makes it a bit more clear to you. I appreciate you time and
help...

Mike

:




Mike,

I understand what you are trying to do, what I'm not clear on is your
table and field names (for instance, is it CATEGORY or tblCATEGORY? you
mention both) so I can understand how the two tables are related and
provide exact syntax. Can you please post *the exact names* of both
tables and fields in them, and some sample data?

Nikos

MikeT wrote:



Hi Nikos

Thanx for getting back to me.....I can't find any information that explains
this clearly to me and I am now quite confused so I will appreciate any help.

I have a table called CATEGORY. Within this table is a field called
Category. Within this field are a number of rows Vehicle, Animal etc.....

In my form I have a combo box (ComboCat) that draws its list up from this
Table (tblCATEGORY)

So far so good.....

I then have a second Combo box (ComboClass) and a second table called
CATEGORYCLASS.....within this table I have several fields corresponding to
the records in tblCATEGORY (Vehicle, Animal etc.)

What I am trying to do is:

When I select an entry in my fist Combo box (ComboCat) for eg. Animal.....I
want the corresponding values to be brought into the list of second Combo box
(ComboClass)...in this case the values under the field Animal within the
table CATEGORYCLASS......

What would you suggest would be the best/easiest way to do this? I'd
appreciate any help.....

Mike

:





Mike,

Your slq expression doesn't look right, at least if your data is the way
I assume; that is, I assume the category ID field (primary key) from
table tblCategory is a foreign key in table tblCategoryClass. If my
assumption is correct, then your sql string assignment should be
something like:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = '"
strSQL = strSQL & Me.ComboCat & "'"

if the CatID field is text, or:

strSQL = "SELECT ClassID FROM tblCategoryClass WHERE CatID = "
strSQL = strSQL & Me.ComboCat

if the CatID field is numeric.

In the above I have assumed the names CatID and ClassID for the ID
fields in the tables; change to the actual names. The rest of your code
remains unchanged.

The alternative is to use a query (in the form's design view) as
ComboClass's recordsource, filtering by referencing the first combo
directly like:

Forms![YourFormName].ComboCat

in which case you then only need the requery in your ComboClass AfterUpdate.

HTH,
Nikos

MikeT wrote:




Hi
I have 2 bombo boxes on a form. ComboCat is the first box that gives the
user a list to select from. The data for this combo box is drawn from
tblCategory.

ComboClass is the 2nd combo box. Depending on the selection made by the user
in ComboCat I would like the data shown in ComboClass to reflect the
selection.

This data is held in tblCategoryClass.

I have tried the following....

Private Sub ComboClass_AfterUpdate()

Dim strSql As String
strSql = "select " & ComboCat & " from tblCategoryClass"
Me.ComboClass.RowSource = strSql
Me.ComboClass.Requery

End Sub

Am I going in the right direction or is there another way of doing this that
works?
Any help will be appreciated...thanx

Mike
Click to expand...
Click to expand...
Click to expand...
 
Back
Top