Change a query that a form is linked to based on some controls

[Guest]

Hi Guys,

I need a little help here. What I am doing is setting up a form where users
can chose a bunch of values and from about 5 different controls and hit
"Find".
I then want a subform to refresh which has queried the DB based on those
values. The controls are three drop-down boxes and one Option Group
consisting of two radio buttons.

The SQL I am fine with, it's the Dynamic query bulding in access that I'm
not sure about. I know how to build a select statement in VB - what I'm not
sure about is how to then save that string as the underlying query for the
subform.

If anyone could please help that would be great. Alternatively if there's a
better way then I would love to hear it.

Many thanks in advance

 

[Allen Browne]

If you can build just a WHERE clause, you can filter the form to assigning
it to the Filter property of the form:
Me.Filter = strWhere
Me.FilterOn = True
For an example of do that, see:
http://allenbrowne.com/unlinked/Search2000.zip

If you prefer, you can build the entire SQL string, and assign it to the SQL
property of the QueryDef, e.g.:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql

 

[Guest]

Excellent!

Thank you very much for the help.

If you can build just a WHERE clause, you can filter the form to assigning
it to the Filter property of the form:
Me.Filter = strWhere
Me.FilterOn = True
For an example of do that, see:
http://allenbrowne.com/unlinked/Search2000.zip

If you prefer, you can build the entire SQL string, and assign it to the SQL
property of the QueryDef, e.g.:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql

 

[Guest]

Hi Allen,

I'm trying to build just a where clause like you mentioned.

This is for a subform. When I try to update the filter I get Run-time error
'438' - Object doesn't support this property or method.

Revelant code is:

Forms!Add_Prom_Part!Add_Cont_List.Filter = True

Forms!Add_Prom_Part!Add_Cont_List.Filter.Refresh

I removed all the actual where caulses to just try to "get at" the filter
value but still no luck. Do you have any suggestions?

 

[Allen Browne]

Presumably Add_Cont_List is the name of a subform control.

To refer to the form in the subform control:
Forms!Add_Prom_Part!Add_Cont_List.Form.Filter = strWhere

Further explanation:
http://allenbrowne.com/casu-04.html

 

[Guest]

Thanks Allen. Now the filter isn't working, lol.

Please allow me to elaborate what I'm trying to do.

Assume one table Contacts, one table Promo (for promotions) and Promo_Part
which puts a Contact into a promotion.

I have one main form - with two subforms. One subform shows who is currently
listed for a promotion. The other is a list of potentials, and the user can
chose who to sign up or not. The one I'm trying to build is the list of
potentials. The idea being that you can scroll down the list of potentions -
after filtering - and click on who you want to add to the promotion (clicking
willl do the insert and re-run the query etc).

I have the SQL for this (from the query window) -
SELECT [Contacts].[Comp_Type_ID], [Contacts].[Contact_Type_ID],
[Promo_Part].[Promo_Id], *
FROM Contacts INNER JOIN Promo_Part ON
[Contacts].[Contact_Id]=[Promo_Part].[Contact_Id]
WHERE (((Contacts.Comp_Type_ID)=[Forms]![Add_Prom_Part]![Sel_Comp_Type]) AND
((Contacts.Contact_Type_ID)=[Forms]![Add_Prom_Part]![Sel_Cont_Type]) AND
((Promo_Part.Promo_Id) Not In (Select Promo_ID from Promo_Part WHERE
Contact_ID=Contacts.Contact_ID and
Promo_ID=[Forms]![Add_Prom_Part].[Promo_ID] )) AND
((Contacts.Contact_Id) In (Select Contact_ID from Promo_Part WHERE (Promo_ID
= [Forms]![Add_Prom_Part]![Sel_Pst_Prom]))));

this works fine but it doesn't work when I try to take the where clauses out
and make a filter with them. I'm wondering if building the filter seperately
loses the connection with the tablenames in the select statement?

Thanks!

 

[Guest]

Actually - I went back and started using your other initial suggestion -
updating the QueryDef field. It seems to be working ok - so far. Struggling
to get the .requery to work but I'll figure that out.

Thanks for all your help.

Thanks Allen. Now the filter isn't working, lol.

Please allow me to elaborate what I'm trying to do.

Assume one table Contacts, one table Promo (for promotions) and Promo_Part
which puts a Contact into a promotion.

I have one main form - with two subforms. One subform shows who is currently
listed for a promotion. The other is a list of potentials, and the user can
chose who to sign up or not. The one I'm trying to build is the list of
potentials. The idea being that you can scroll down the list of potentions -
after filtering - and click on who you want to add to the promotion (clicking
willl do the insert and re-run the query etc).

I have the SQL for this (from the query window) -
SELECT [Contacts].[Comp_Type_ID], [Contacts].[Contact_Type_ID],
[Promo_Part].[Promo_Id], *
FROM Contacts INNER JOIN Promo_Part ON
[Contacts].[Contact_Id]=[Promo_Part].[Contact_Id]
WHERE (((Contacts.Comp_Type_ID)=[Forms]![Add_Prom_Part]![Sel_Comp_Type]) AND
((Contacts.Contact_Type_ID)=[Forms]![Add_Prom_Part]![Sel_Cont_Type]) AND
((Promo_Part.Promo_Id) Not In (Select Promo_ID from Promo_Part WHERE
Contact_ID=Contacts.Contact_ID and
Promo_ID=[Forms]![Add_Prom_Part].[Promo_ID] )) AND
((Contacts.Contact_Id) In (Select Contact_ID from Promo_Part WHERE (Promo_ID
= [Forms]![Add_Prom_Part]![Sel_Pst_Prom]))));

this works fine but it doesn't work when I try to take the where clauses out
and make a filter with them. I'm wondering if building the filter seperately
loses the connection with the tablenames in the select statement?

Thanks!

Presumably Add_Cont_List is the name of a subform control.

To refer to the form in the subform control:
Forms!Add_Prom_Part!Add_Cont_List.Form.Filter = strWhere

Further explanation:
http://allenbrowne.com/casu-04.html