need a query that will work in any form

[piggitypow]

I have a search form that includes the following expression for the criteria component of query design:

Like "*" & [Forms]![frmDocumentsUpdate]![Search2] & "*"

In the form frmDocumentsUpdate this works fine, but I want to apply this single query to several other forms without designing a different version for each form. How do I alter the [frmDocumentsUpdate] portion of the query criteria so that it will work in any form? I assume this involves the "me" command but I don't know the proper syntax.

 

[piggitypow]

Hello?

I get the impression I'm being ignored because people feel this topic has already been addressed in the forum. However, I have searched all over and have come up with only part of the solution.

There should be a simple substitution in query design criteria to address my problem, right? I've got two forms that use the same query, and a third form that uses those two forms as subforms. In part I'm trying to solve the issue of being forced to enter parameter data upon opening the form that contains subforms, but trust me when I say I don't want to use the "one form" strategy cause there are other issues here.

If there is no easy fix for this (which I doubt it), then I would greatly appreciate a response saying this.

 

[Pat Hartman]

There is no way to do this with a querydef since you can't change its structure on-the-fly. There are two solutions that come to mind.
1. If you are using a version of Access that supports the OpenArgs argument of the OpenReport method, you can pass the name of the current form to the report. In the report's Open event, you can use code that substitutes the proper form field name reference and place the SQL string in the report's RecordSource
2. You can create a public function. This method has been discussed a number of times. Each form that captures the parameters needs to set the value in a public variable. Then in the query, the public function can retrieve that value.

Like "*" & YourFunc() & "*"

BTW, are you sure you need the Like operator?

 

[piggitypow]

I'll keep looking into the public function stuff a little more, but not sure that will work. Here's what's going on.

My big form is for data entry. It pulls data from two tables (tblDocuments and tblStreams) via two separate searches embedded in the form, allowing the user to both select a document AND select a stream for which they want to enter data, which then goes into a junction table (tblDocumentsAndStreams). The problem is that, when the user selects a document and a stream, I want the form to display their document and stream selections in textboxes, which as far as I know requires having bound tables (so that I can link the textbox control sources to fields). This runs me into the problem of not being able to bind two tables to a single form.

To get around the two-table binding issue, I decided to try subforms. So I made a new big data entry form with each of the two "search and select" sections being separate subforms. But now I have the query problem. The big form prompts me for search parameters because the queries are associated with the subforms, not the big form.

Right now the two search and select forms work fine as discrete forms, but not as subforms due to the query issue. That's why I was looking for a way for a query to work in any form. Am I looking at this right? If stick with the subform method, what I would like is one query that will work in each subform when they are housed in the larger data entry form.

 

[Pat Hartman]

Both suggestions will work regardless of whether you use subforms or not.

It is better practice to base forms/reports on queries rather than tables. Queries allow you to join multiple tables, specify data order, and include selection criterial.

 

[piggitypow]

Still stuck

This public function method is new to me, and I only have a little VBA experience (Excel, at that), so I need some babying if you can be patient enough.

If I can get one form to work as a subform without asking for query parameters in the parent form, then we have success and the rest will be a walk in the park. So I'll just focus on trying to get one "search and select" form to work as a subform in a parent form. Take a look at my form if you have the time (attached zip).

In my efforts to begin making the query public (as opposed to form-specific) I tried altering the query so that it said: Like "*" & MyFunc & "*" (repeated in several columns as an "or" query).
I also put the following code in a new module:

Option Compare Database
Option Explicit
MyGlobalVariable = Me.Search2

Public Function MyFunc() As Variant
MyFunc = MyGlobalVarable
End Function

Is that right so far? Search2 is in a private sub- what goes there to make the public function work?

I have attached two access files: 1) one with the working search form so you can see what should be happening (no public function in it though, so it doesn't work as a subform within frmParent); 2) one with the new module and my attempt to add a public function, but it's not quite right yet (the search doesn't work).

 

[piggitypow]

Got the subform to work seamlessly by altering the SQL code of the corresponding query a bit. Thanks for the insights though.