Filter subform based on a query .

[shreyansghia]

Hello ,

I am using MS Access 2007. Now if i ve a subform based on a table, filtering the subform is extremely easy . I only ve to click the relevant column head on the subform and check/uncheck the required records (clicking on the column head would yeild a drop down list).

However , since my subform is based on a query , i cannot avail the filter option using the above method. I get the error no 3075.

Can somebody please help me here,

Thanks a lot.

Regards,

Shreyans.

[shreyansghia]

Also , as an alternate what i can do is have a combo box on the main form. The combo box will have for example purchase order no. So i should see only those records in subform which bears the purchase order no as i ve selected from the combo box.

Is this possible?

Thanks in advance.

[NeoPa]

Check out Example Filtering on a Form. and see if that helps.
Otherwise your explanation doesn't really give much information.
Let us know if you need more help with this.

[shreyansghia]

Check out Example Filtering on a Form. and see if that helps.
Otherwise your explanation doesn't really give much information.
Let us know if you need more help with this.

Well NeoPa thanks for the above link . I could nt explain my requirement precisely earlier because i think it is really very complicated.

Anyways i would be really greatful if u could provide me with any link as how do i filter a subform when i make any selection from a combo box which would be on the main form.

[puppydogbuddy]

Well NeoPa thanks for the above link . I could nt explain my requirement precisely earlier because i think it is really very complicated.

Anyways i would be really greatful if u could provide me with any link as how do i filter a subform when i make any selection from a combo box which would be on the main form.

this code goes on main form...YourSubformControlName is the name of the subform container, not the subform (i.e. the source object).

Private Sub YourComboName_AfterUpdate()
Me!YourSubformControlName.Form!YourComboName.Requery
End Sub

See this link:
http://www.mvps.org/access/forms/frm0031.htm

[NeoPa]

Well NeoPa thanks for the above link . I could nt explain my requirement precisely earlier because i think it is really very complicated.

Anyways i would be really greatful if u could provide me with any link as how do i filter a subform when i make any selection from a combo box which would be on the main form.

So providing the question is too much trouble but you'd like us to work in the dark and give you an answer anyway.
Priceless.
This without even bothering to check out the link already provided.

[shreyansghia]

So providing the question is too much trouble but you'd like us to work in the dark and give you an answer anyway.
Priceless.
This without even bothering to check out the link already provided.

Hmm , this is what i ve typed in the after update event of the combo box.

Private Sub Combo33_AfterUpdate()
 
Me![DeliverySchedule subform].Form!Combo33.Requery
End Sub

So when i select my desired option from the combo box, what i get is
run time error 2465. It also says that combo33 cannot be located .

[puppydogbuddy]

Hmm , this is what i ve typed in the after update event of the combo box.

Private Sub Combo33_AfterUpdate()
 
Me![DeliverySchedule subform].Form!Combo33.Requery
End Sub

So when i select my desired option from the combo box, what i get is
run time error 2465. It also says that combo33 cannot be located .

My fault.Combo33 is on the main form, not the subform:

Me!Combo33.Requery

[shreyansghia]

My fault.Combo33 is on the main form, not the subform:

Me!Combo33.Requery

Ok i incorporated the change suggested by u. What i get now is error 438 ; 'object does'nt support the property or method'. :-(.

[puppydogbuddy]

Ok i incorporated the change suggested by u. What i get now is error 438 ; 'object does'nt support the property or method'. :-(.

That should have worked, Please post the sql you have for the row source for combo33.

[shreyansghia]

That should have worked, Please post the sql you have for the row source for combo33.

SELECT DISTINCT [DeliverySchedule].[InvoiceNo]
FROM DeliverySchedule
ORDER BY [InvoiceNo];

[puppydogbuddy]

SELECT DISTINCT [DeliverySchedule].[InvoiceNo]
FROM DeliverySchedule
ORDER BY [InvoiceNo];

1.change the row source for your combo to the following:

SELECT DISTINCT [DeliverySchedule].[InvoiceNo]
FROM DeliverySchedule
WHERE [InvoiceNo] = Me!Combo33
ORDER BY [InvoiceNo];

You can use Me!Combo33 if you create the row source using vba behind the form. If you create the row source via the property sheet, then you have to use a fully qualified reference>>>Forms!YourMainForm!Combo33

2. Requery the combo in the combo's afterupdate event as previously discussed.

3. Make sure you have set the master & child links in the subform control to InvoiceNo.
4. If InvoiceNo is unique in the DeliverySchedule table, then you should eliminate the word Distinct from the select statement you used for combo33.

[NeoPa]

PDB,

I don't think you can use the = Me!Combo33 version within SQL (even when embedded in a control's properties). The fully-qualified reference would be needed I'm afraid.

[puppydogbuddy]

PDB,

I don't think you can use the = Me!Combo33 version within SQL (even when embedded in a control's properties). The fully-qualified reference would be needed I'm afraid.

Hi NeoPa,
I was not sure......that is why I provided the long and short references. Thanks for confirming that for me.

PDB