By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,912 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Filter subform based on a query .

P: 25
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.
Oct 4 '07 #1
Share this Question
Share on Google+
13 Replies


P: 25
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.
Oct 5 '07 #2

NeoPa
Expert Mod 15k+
P: 31,273
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.
Oct 5 '07 #3

P: 25
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.
Oct 7 '07 #4

puppydogbuddy
Expert 100+
P: 1,923
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).

Expand|Select|Wrap|Line Numbers
  1. Private Sub YourComboName_AfterUpdate()
  2. Me!YourSubformControlName.Form!YourComboName.Requery
  3. End Sub
See this link:
http://www.mvps.org/access/forms/frm0031.htm
Oct 7 '07 #5

NeoPa
Expert Mod 15k+
P: 31,273
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.
Oct 7 '07 #6

P: 25
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo33_AfterUpdate()
  2.  
  3. Me![DeliverySchedule subform].Form!Combo33.Requery
  4. 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 .
Oct 8 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
Hmm , this is what i ve typed in the after update event of the combo box.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo33_AfterUpdate()
  2.  
  3. Me![DeliverySchedule subform].Form!Combo33.Requery
  4. 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
Oct 8 '07 #8

P: 25
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'. :-(.
Oct 8 '07 #9

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 8 '07 #10

P: 25
That should have worked, Please post the sql you have for the row source for combo33.
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [DeliverySchedule].[InvoiceNo]
  2. FROM DeliverySchedule
  3. ORDER BY [InvoiceNo];
Oct 9 '07 #11

puppydogbuddy
Expert 100+
P: 1,923
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [DeliverySchedule].[InvoiceNo]
  2. FROM DeliverySchedule
  3. ORDER BY [InvoiceNo];

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

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [DeliverySchedule].[InvoiceNo]
  2. FROM DeliverySchedule
  3. WHERE [InvoiceNo] = Me!Combo33
  4. 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.
Oct 10 '07 #12

NeoPa
Expert Mod 15k+
P: 31,273
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.
Oct 11 '07 #13

puppydogbuddy
Expert 100+
P: 1,923
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
Oct 11 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.