Changing Rowsource in a combo box depending on the text in another

[ebdm]

Hi all,
I am trying to have the rowsource change on a "subcategory" combo box depending on what the "category" combo box says.

I added an event procedure to the "Category" combo box which changes the Row Source in the subcategory combo box to something else. I'm not sure that I did this right as if I close the database and reopen it, what I selected in the subcategory combobox is gone and it is blank. Any ideas? Here is my simple, likely incorrect event procedure:

Private Sub Category_Change()

If Category.Text = "Rare Books" Then
SubCat.RowSource = "scRareBooks"

End If

If Category.Text = "Legal Documents" Then
SubCat.RowSource = "scLegalDocuments"

End If


End Sub

Thanks!

 

[WayneRyan]

ebdm,

The Change event fires as the user types EACH character in a control.
It's probably not the event you want to use for a combo box; try the
Before/After Update event instead.

Similarly, .Text is only available in the OnChange event, remove the
.Text as it defaults to .Value

Also, you should probably do a Me.SubCategory.Requery after assigning
the new RowSource.

Also, is the combobox bound to a column in your form's RecordSource?

hth,
Wayne

 

[ebdm]

Hi Wayne,
Thank you for the information. I'm not very good at Access Programming. What exactly is a Me.SubCategory.Requery and how do you add it?

I'm also not quite sure how to tell if it is bound to anything. I'm making all of this up between my QBASIC programming knowledge and the help files.
Thanks

 

[WayneRyan]

ebdm,

Where you have:

SubCat.RowSource = "scRareBooks"

add this line after:

SubCat.Requery

If you put "Me." in front of it:

Me.SubCat.RowSource = "scRareBooks"
Me.SubCat.Requery

Then you'll see the words supplied as you type them (IntelliSense).
"Me" is your form and the "Me." is the introduction to all things
that are a part of your form.

Your combo box probably is not bound to your form's recordsource.
On its properties, the "ControlSource" should match one of the columns
in your table.

Wayne

 

[ebdm]

Hi Wayne,
Thanks for your advice so far. I tried adding the Me. and it worked. However I think I've found out why the data disappears every time I exit the program. I previously had it linked to a query. Now I have adjusted it to link directly to a table, and that works at keeping the data. The only problem is - the Me. lines bring up error messages now. It gets a "Run-time error 2115" which says "The macro or function set to BeforeUpdate or ValidationRule property for this field is preventing Collections Management Database from saving the data in this field"

Here is what I now have in my event procedures:

Private Sub Category_AfterUpdate()
If Category.Text = "Rare Books" Then
Me.SubCat.RowSource = "SELECT [subcatRare Books].[SubcatID], [subcatRare Books].[SubcatName] FROM [subcatRare Books]"
Me.Requery
End If

If Category.Text = "Legal Documents" Then
Me.SubCat.RowSource = "SELECT [subcatLegal Documents].[SubcatID], [subcatLegal Documents].[Subcat2Name] FROM [subcatLegal Documents]"
Me.Requery
End If

End Sub

Also you mentioned in your first response "Similarly, .Text is only available in the OnChange event, remove the .Text as it defaults to .Value"
If I remove the .text it doesn't seem to work. Is there another value I should change it to?


Thank you very much!

 

[WayneRyan]

ebdm,

Instead of: Me.Requery <-- implies to Requery your whole form

use: Me.SubCat.Requery <-- Requery the child combobox

Also, from the RowSource: SELECT [subcatRare Books].[SubcatID], [subcatRare Books].[SubcatName]

Your SubCategory combobox has two columns:

Me.SubCat.Column(0) = [subcatRare Books].[SubcatID]
Me.SubCat.Column(1) = [subcatLegal Documents].[Subcat2Name]

You probably don't "See" the [SubCatID]
Your widths for the combobox are probably something like 0";1",
making the first column invisible.

What is the bound column?

Incidently, use the Search Facility here and look for "Cascading".
It is a pretty common topic here.

Also look in the Sample Database forum here, there should also
be some samples.

Wayne