Subform filter

[SunTop]

Hello everyone

On my database I have a form and on this form I have a text box, a list box and a subform. On the subform I have two combo boxes and a text box and the subform is set to datasheet view. The list box on the main form is unbound and I use it to filter the underlying query for the row source of the two combo boxes on the subform. The main form has a one to many relation with the subform. I also have an onclick event on my list box to filter the combo boxes on my subform if I needed to enter more than one record on my subform. So the process goes as follows:

Starting with the main form:
1. Enter a value on the text box
2. Pick a value on the unbound list box to filter the two combo boxes on the subform

Moving to the subform:
1. pick a value for the two combo boxes
2. enter a value for the text box

Now here is what I am facing an issue with:
Suppose now I needed to enter another record on the subform, I would have to move back to the main form to select another value from the list box to filter the underlying query of the two combo boxes on the subform. When I do that and the onclick event fires up, the datasheet on my subform looks really awkward. The columns for the two combo boxes are empty now, but the value on the text box is still there. This confuses the user. Now the user does not know where to enter the new record. How can I filter the underlying query of the combo boxes on the subform and keep the previously entered data ?

[June7]

This is a well-known and often discussed issue. Here is one https://www.accessforums.net/showthread.php?t=65938
Dependent comboboxes displaying lookup alias just do not work nice with continuous or datasheet forms. There is no perfect solution.

[CJ_London]

your issue is akin to cascading combos in a continuous form. You need a 'default' rowsource for your combos to select everything e.g.

SELECT ID, Name from myTable

then in the combo enter event put the following code (change names to suit)

combo1.rowsource="SELECT ID, Name from myTable WHERE somevalue=[parent].
[listboxname]"

and in the exit event
combo1.rowsource="SELECT ID, Name from myTable"

do not have any code in your listbox afterupdate event that affect the combo rowsources or requeries the subform

[SunTop]

Thanks for your replay June7,Ajax I have read the thread that June7 linked on his post and I have tried the method Ajax suggested. I have been tackling this issue for two days now, but no luck. I have attached a sample database, I hope somebody takes a look at it and tell me what am I doing wrong ?

[CJ_London]

I think the problem is that the rowsource is referencing a control outside of the existing record which is why it is not working as expected. Also if the user goes from the product control to the listbox by using the mouse the lostfocus/exit events for the product control are not triggered. You have also not referenced the listbox as I said, you are using forms!form. rather than parent.

I don't have time to figure out a solution right now. It may be that you need also need to apply the 'default' code to the listbox enter or gotfocus event.

[SunTop]

Thanks for your replay Ajax,

I hope that you look at it again when you have some free time.

[Bob Fitz]

It might be better to use a continuous subform. See attached db

[CJ_London]

it may be a while - my paying clients have to come first.

The principle is right, but needs some experimenting to find the right events to trigger

[SunTop]

Thanks Bob Fitz for your replay

The attached db on your pervious post is pretty close to what I want (putting a text box on a combo box was very smooth by the way, it made me scratch my head for a while wondering why a text box is behaving like a combo box). However, you have removed the List box on the main form, which I need for two reasons:

• In my actual database I have many regions and I use a text box together with the list box to search for the region (search as you type) and then I use the list box to filter the combo box region
• I also use the value on the list box to be the source of an image control on the main form