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.
--
Ken Snell
<MS ACCESS MVP>
Robyn said:
Thank you for your help, Ken.
I am using the LName and FName parameters as the Control Source for
textboxes in both the report and subreport. The controls on the form are
named LName and FName, and I can see no typos in the query with either the
form name or control names.
The query's SQL statement that I am using as the report's record source
and
the SQL statement for the subreport's query are as follows:
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
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
FROM [PVPSA EPISODES]
WHERE ((([PVPSA EPISODES].LName)=[Forms]![First and Last Names]![LName])
AND
(([PVPSA EPISODES].FName)=[Forms]![First and Last Names]![FName]));
Ken Snell (MVP) said:
I still don't see specifically where you're reading the values from the
form, but I believe that your query contains those parameters
([Forms]![First and Last Names]![LName] and [Forms]![First and Last
Names]![FName])?
Do you use these parameters anywhere on the report (e.g., as the Control
Source for a textbox) as well?
Are you sure that the controls on the form are named LName and FName? Is
there a typo in the query with regard to either the form name or a
control
name?
Post the query's SQL statement that you're using as the report's Record
Source.
--
Ken Snell
<MS ACCESS MVP>
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].
On Open, the report includes the following code:
Option Compare Database
Private Sub Report_Close()
DoCmd.Close acForm, "First and Last Names"
End Sub
Private Sub Report_Open(Cancel As Integer)
'Set public variable to true to indicate that the report is in the Open
event
bInReportOpenEvent = True
'Open First and Last Names form
DoCmd.OpenForm "First and Last Names", , , , , acDialog
'Set public variable to false to indicate that the Open event is
completed
bInReportOpenEvent = False
End Sub
Could this be where I am running into problems?
:
The code that you posted contains no "actionable" steps other than
refocusing the focus if one of the textboxes are empty. So I must
assume
that some other code runs when the form is made invisible.
Can you give us more details about the entire process that is
happening
here? Where you say "table", I'm guessing that you mean you're running
a
query that is using two tables as its data sources. I'm also guessing
that
the "need to type in the names" over and over is because of an error
in
the
query's parameters... likely there is a misspelling of the form's name
or
the textboxes' names in the query's parameters.
--
Ken Snell
<MS ACCESS MVP>
I have created a form in Access 2003 to look up client records in two
unrelated tables using LName and FName fields. After entering a last
name
and
a first name in two unbound text boxes I click on the OK button and:
a. If a client is in both tables or only in the second table, the
records
pop up after clicking the OK button twice.
b. However, If the client is only in the first 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?
The two tables are linked to the form in LName and FName fields
criteria
using the following expressions: [Forms]![First and Last
Names]![LName]
and
[Forms]![First and Last Names]![FName].
The OK button includes the following code:
Private Sub OK_Click()
If IsNull([LName]) Or IsNull([FName]) Then
MsgBox "You must enter both Last and First Names."
DoCmd.GoToControl "LName"
Else
Me.Visible = False
End If
End Sub
