Query By Form using two forms

[MartyBoy]

Hi again
I have two forms that display different data fields from the same table. One form displays the data that users most commonly want to view and query. The secondary form contains more detailed data that is of occasional interest to the users. The second form is opened via a command button on the main form.
The users now want to be able to include fields from the secondary form in their queries of the main form.
I am having considerable difficulty linking the two forms to create a single query of the Recordset. I have tried passing the form filter created by the Query by Form to the second form using code as per below:

Sub CommandOpenFormTwoButt_Click()
Dim MainFilter As String
MainFilter = Me.Filter
DoCmd.OpenForm "SecondayForm"
SecondaryForm.Filter = MainFilter

etc etc but keep getting errors. I have also put the MainFilter string into the DoCmd open argument, which produces the same problems. Although the selected field criteria/values are parsed to the secondary form filter and looks fine when viewed under Records/Advanced Filter, The QBF process seems to create double brackets around the filter data which is not recognised as valid field names by the second form. And as soon as the QBF function is activated on the second form it loses the filter that existed previously.
Both forms are heavily populated with data fields (over 80 in total) and the option of trying to stitch SQL around every field seems to be a cumbersome way of creating a singular query function for the users. Any of the data fields may be required to construct a query, although commonly only 5 - 10 fields are selected in any one query.
Can anyone point me to a solution that will allow the users to continue to use the QBF function - or not if anything else will work?

 

[Frink]

Hallo,

Do both of your forms have the table as the recordset?
ie. they are not based on a query whch only selects the required fields.

- Frink

 

[MartyBoy]

Hi Frink
Yes, both forms get their Recordset's directly from the same table, i.e., no queries are used to generate the Recordsets.
Regards.

 

[Frink]

Hallo,

Could you post a few examples of Me.Filter strings?

- Frink

 

[MartyBoy]

Hi Frink
Below are two extracts from the filters created by using the Query By Form Method (activated by a Command Button on each form). The strings have been extracted from string variables generated on the On Apply Filter Event on each form.
I have no problem parsing the parameters from the first filter to the filter of the secondary form. The problem arises when I want to retain the filter of the first form and add it to the filter created by the Query By Form method on the secondary form.

Example Main form filter after Query By Form:
((Lookup_City.CityName="Wellington") AND (Lookup_TypeCode.TypeDescription="Office/Retail") AND (Property.Suburb="CBD") AND (Property.TypicalFloorArea>500))

Example Secondary form filter after Query By Form
((Property.OfficeArea>500) AND (Property.RetailArea>100 And Property.RetailArea<5000) AND (Property.StudHeight>4.5) AND (Property.Storage>500))

Using the Query By Form method on the secondary form clears the filter copied from the first form. All attempts at concatenating the filter from the first form with the filter created on the second form have bought up error messages such as "Invalid use of null" and "You cannot assign a value to this object".
BTW the references to "Lookup" in the form filters are created by the fact that those fields select the values from a combo box linked to a query that eliminates the duplications and sorts the values in ascending order. The Record Source for these combo boxes is still the underlying table.

Any ideas?

 

[Frink]

Hallo,

Not totally sure I understand, but can't you just set the filter of the second form to Firstform.Filter & " AND " & Secondform.Filter?

- Frink

 

[MartyBoy]

Hi again Frink
Sorry for delay in responding to your suggestion. In the end I resolved the problem by setting up two hidden text boxes which are updated when the filters on the two forms are updated. A bit of code along the lines you suggested concatenates the two textboxes and creates a new filter, which can be passed from one form to the other. Just updating the filter with:

Form1.Filter & "And" & Form2.Filter

produced inconsistent results. I think this is because the two forms are often refiltered a number of times until the users get the recordset they want, and this seemed to be creating the problem.
Thanks for your help, I am now trudging through more stupidities of my own making.