Refresh subform using ComboBox Onchange (or After Update)?

[weeblesue]

Sorry for the simple question, but I just can't get a handle on this situation. It seems easy but it is turning out to be quite difficult! For me, anyway.

I am using Access 2010. I am writing a database to keep track of my work expenses. I have a table Category, containing entries like A, B, C, D, E. Then I have a Subcategory table with Category/Subcategory like A/A1, A/A2, A/A3, B/B1, B/B2, B/B3 etc. There is a one-to-many relationship based on Category.

I have a subform sbfSubCategory that lists only the subcategory field. I have tried adding and removing the additional Category field as well, but it doesn't change anything. This subform is built from a query rather than a table, so i can sort the fields properly after they are edited.

This subform resides in a form Edit Subcategory.
The Category field is a ComboBox (Combo3) on this Main form.
On the subreport, i have the Link Master Fields and Link Child Fields both equal to Category.

All i want is for the subreport to refresh when i change the value in the Category Combobox.

I have tried entering code to the OnChange event of the combobox to Filter the subreport, but this changes ONLY the Category column and does not show the Subcategory field! I got this from another forum.
If Combo3.ListIndex > -1 and Combo3.value & "" <> "" Then
Me![sbfSubcategory].Form.Filter = "[Category] = '" & Combo3.value & "'"
Me![sbfSubcategory].Form.FilterOn = True
End If
I have tried using the query as the subreport instead of the subform, but that didn't work either.

Access tells me:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit dupliate entries and try again."
I'm not trying to duplicate any data! I just want to refresh the query that's in the subreport!

I am lost! It shouldn't be this difficult!



I have uploaded my database with only these 2 tables in it.


Any advice?

Thanks....
Susan

[June7]

So purpose of form(s) is to create records in Subcategory table? Not yet talking about documenting actual expenses?

Don't need Category textbox in the subform or at least should be locked to prevent editing.

The combobox to select Category is BOUND. Selecting item changes value of current record. If purpose of combobox is to select search criteria from dropdown list then the combobox must be UNBOUND and need code to find record. If you use right click shortcut menu to specify filter criteria then the control can be bound and should be a textbox.

If you want to add a new category then need to navigate to new record on main form. The navigation bar is disabled. However, cannot use the combobox as is to create new Category record.

[azhar2006]

Hi weeblesue
About (DB) to your Access 2007
Until we see what the problem is

[weeblesue]

This is ONE of many forms that are in the overall database. Yes, the ultimate purpose of the complete db is to track expenses. But the users need to be able to edit the list of subcategories that the expenses are tracked against. This is the only part I'm having trouble with.

I do not want a text box because some of the Categories are very long, and users have a bad habit of mistyping things. Even something like "Fees" can give users trouble. So I need it to be a dropdown list.

I changed the ComboBox to "unbound" by deleting the contents of the field when the form was open in DesignMode.

The subtables still do not change one bit when i change the entry in the ComboBox.

Let's forget everything else that I might want to do or not do.... I would like to simply repopulate the subform based on the selection of the combobox. It is not working, and i do not know why.

Azhar, I have NO CLUE what you are saying.

[June7]

Nothing happens when selecting item from combobox dropdown because there is no code behind the combobox. Need code in AfterUpdate event. The code can either filter or go to a record. Do you want to use macro or VBA?

The subform synchronizes with the main form because of the Master/Child Links properties. Find the desired Category record and the subform will display related records.