Form won't remember last value - Again

[Robyn]

The Problem:

I am running a client look-up report that obtains information from two
separate client tables. The main report shows client information from one
table and a subreport shows client information from the second table. The
report draws its data from two queries—one for each table--that contain only
open client information. Both queries are linked to one form using LName and
FName fields criteria with the following expressions: [Forms]![First and Last
Names]![LName] and [Forms]![First and Last Names]![FName].

I created a form to look up client records in the two unrelated tables using
LName and FName fields. After entering a last name and a first name in two
unbound text boxes on the form, I click on the OK button and:

a. If a client is in both tables or only in the second table (the subreport
source table), the records pop up after clicking the OK button twice.

b. However, If the client is only in the first table (the main report source
table), after clicking OK twice, the names disappear from the two text boxes,
and I have to type in the last and first name again, click OK (names
disappear again) type in the names a second time and click OK again in order
to obtain the report.

Is there a way to make this form work with only one click and no extra typing?

Ken Snell <MS ACCESS MVP> proposed the following fix (thank you, Ken):

This may be the source of the problem: "I am using the LName and FName
parameters as the Control Source for textboxes in both the report and
subreport."

Modify your query to have calculated fields that are the values from the
form; for example:

Report SQL:
SELECT [PVOPX EPISODES].CLIENT_NUMBER, [PVOPX EPISODES].LName, [PVOPX
EPISODES].FName, [PVOPX EPISODES].Age, [PVOPX EPISODES].Staff_LName, [PVOPX
EPISODES].Staff_FName, [PVOPX EPISODES].OPENING_DATE, [PVOPX
EPISODES].CLOSING_DATE, [PVOPX EPISODES].EPISODE_STATUS_FLAG, [PVOPX
EPISODES].FINANCIAL_RESPONSIBILITY, [PVOPX EPISODES].LAST_SERVICE_DATE,
[PVOPX EPISODES].REPORTING_UNIT,
[Forms]![First and Last Names]![LName] AS FormLName_R,
[Forms]![First and Last Names]![FName] AS FormFName_R
FROM [PVOPX EPISODES]
GROUP BY [PVOPX EPISODES].CLIENT_NUMBER, [PVOPX EPISODES].LName, [PVOPX
EPISODES].FName, [PVOPX EPISODES].Age, [PVOPX EPISODES].Staff_LName, [PVOPX
EPISODES].Staff_FName, [PVOPX EPISODES].OPENING_DATE, [PVOPX
EPISODES].CLOSING_DATE, [PVOPX EPISODES].EPISODE_STATUS_FLAG, [PVOPX
EPISODES].FINANCIAL_RESPONSIBILITY, [PVOPX EPISODES].LAST_SERVICE_DATE,
[PVOPX EPISODES].REPORTING_UNIT
HAVING ((([PVOPX EPISODES].LName)=[Forms]![First and Last Names]![LName])
AND (([PVOPX EPISODES].FName)=[Forms]![First and Last Names]![FName]))
ORDER BY [PVOPX EPISODES].CLIENT_NUMBER;

Subreport SQL:
SELECT [PVPSA EPISODES].CLIENT_NUMBER, [PVPSA EPISODES].LName, [PVPSA
EPISODES].FName, [PVPSA EPISODES].Age, [PVPSA EPISODES].Staff_LName, [PVPSA
EPISODES].Staff_FName, [PVPSA EPISODES].OPENING_DATE, [PVPSA
EPISODES].CLOSING_DATE, [PVPSA EPISODES].EPISODE_STATUS_FLAG, [PVPSA
EPISODES].FINANCIAL_RESPONSIBILITY, [PVPSA EPISODES].LAST_SERVICE_DATE,
[PVPSA EPISODES].REPORTING_UNIT,
[Forms]![First and Last Names]![LName] AS FormLName_S,
[Forms]![First and Last Names]![FName] AS FormFName_S
FROM [PVPSA EPISODES]
WHERE ((([PVPSA EPISODES].LName)=[Forms]![First and Last Names]![LName]) AND
(([PVPSA EPISODES].FName)=[Forms]![First and Last Names]![FName]));

Then change the Control Sources of your textboxes to be the FormLName_R,
FormFName_R, FormLName_S, and FormFName_S fields as appropriate for the
textboxes on the main report and in the subreport.

I've found that ACCESS does not adequately carry over the parameters into
the report, and the report can then ask for them again because you use the
form references in the Control Source expressions.

Also, check to see if you're using the form references in the Sorting &
Grouping (View | Sorting & Grouping) option. If yes, change them to these
new calculated fields' names.

However, after completing all of the above tasks (including removing the
extraneous commas—thanks Ken), the original problem remains: I still must hit
OK twice and typing the names in twice. Have I overlooked something?