1 Form, 2 Queries???

[Angello Pimental]

Is it possible to have one form based on two queries??

The reason for this, is that I have given the user the choice of searching for renewal dates by month and year, but I also want to be able to have a query which brings back all renewaldates from the present date.

I have both queries working, the question now is how do you intergrat them, so that the user can select either one??

Thnx

 

[Fornatian]

As you know...
A form can only be based on one recordset.
That recordset can be either a query or a table. A query can contain other queries.

if the queries you are referencing don't share any fields then you will be best using two subforms.

if they both query the same data then integrate both queries into one query(with multiple criteria and use that as the source for the form.(this seems like the option you are after)

you may have to do some jiggery pokery to get the form to requery after update of the criteria fields but that should work.

sorry the answer is a bit vague.

Ian

 

[Laocon]

There was a post here today about choosing a record source based on a tick box - the user ticks which option they want to search by and presses a button and voila...

It sounds like that you need to use this to choose your query

 

[Angello Pimental]

Ian:

I tried combining the queries, as they are both based on the same table and fields...
But it then brings back a random melody of records that don't relate to what I asked for.
Is there anyway that you know of that you can use the combined query method, but tell the query it ignore one criteria or another??

Laocon: Thanks for your suggestion, I will have a look at what you have suggested.

DOes anyone else have 2 cents??
THnx

 

[Fornatian]

Try using the filter property of the form to set the required filter, then requery your form. You can build the filter as a string in code and then set it as follows:

Dim strFilter as string

'build the string
strFilter = "THE FILTER GOES HERE"

Forms("THEFORM").SetFocus
Forms("THEFORM").Filter = strFilter
Forms("THEFORM").FilterOn = True
Forms("THEFORM").Requery

'something along those lines should do it without being too specific.

Ian



[This message has been edited by Fornatian (edited 08-30-2001).]

 

[Chris RR]

Following Fornation's idea, then add a button called "Show All Records", with the code:
' blank out the dates on the form
Me.Month = Null
Me.Year = Null
' show everything
DoCmd.ShowAllRecords