Combine Report with Update Query

[Randy]

Ok, this may be a little tricky and I apologize for the lengthy description!
Here’s the scenario; A little background on the db first. This is an
affordable housing waiting list database. Applicants are qualified by income
levels or percentages (i.e.; 35%, 45%, etc...) Reports are generated by the
qualifying percentages. When a user wishes to run a report, the report has a
filter that asks the user to enter the qualifying percent (35%, 45%, etc...)

Now, I have a special report that is generated to send out “update lettersâ€,
if you will, to the selected applicants. This report also has the same filter
asking users to enter the percentage (35%, 45%, etc...) which will pull only
applicants who are qualified in that percentage.

I have also created an “update query†that utilizes the same filtering
method of the above-mentioned reports/letters. When selected, the update
query will ask the user to enter the percentage (35%, 45%, etc...) of the
records to be updated. The update query will then update certain
controls/fields within the respective applicant records.

What I am trying to accomplish is when the report or “update letters†are
selected and the user enters the percentage as required, I would like the
“Update Query†to run using the same filter as the “Update Letters†and
update the records selected without having two separate procedures.

Anyone know how I could combine the report with the update query?

Right now I have this in the Report_Open sub:

Private Sub Report_Open (Cancel As Integer)
On Error GoTo Report_Open_Err
DoCmd.Maximize
DoCmd.ApplyFilter "Enter Percentage Filter Query"
DoCmd.OpenQuery "Public_Letter_Sent_Update_Query", acViewNormal,
acReadOnly
DoCmd.Close
Report_Open_Exit:
Exit Sub
Report_Open_Err:
MsgBox Error$
Resume Report_Open_Exit
End Sub


Both query’s use the same filter where the control [Qual_Pct] = [Enter
Percentage %]. The above method was the only way I could figure out how to
get them to run at the same time, however, is asks to “Enter Percentage %â€
twice. I am trying to get this to only ask for the “Percentage†once.

Any ideas or suggestions will be greeeeaaaaatly appreciated. I thank you in
advance for any time you may commit to this.

 

[Douglas J. Steele]

Create a form with a text box on it, and have them fill in the rate in that
text box.

Change the filter/query to refer to Forms!NameOfForm!NameOfControl rather
than [Enter Percentage %]

 

[Dennis]

You cannot run a report from an Update query.

You should create a small popup form that askes for the value you want.
Then, using Public Functions, you can pass the value to the various
queries/reports/whatever. Personally (and this is actually redundant, but I'm
a control freak), I create Public variables, fill them from the form(s), and
then feed the Public Functions from them. (You can only pass external vaules
into queries via Functions.)

I define the Public Variables and Public Functions in a module. Since
everything's declared PUBLIC, all formes can use/set the data, and all forms
and queries can access the functions.

Ok, this may be a little tricky and I apologize for the lengthy description!
Here’s the scenario; A little background on the db first. This is an
affordable housing waiting list database. Applicants are qualified by income
levels or percentages (i.e.; 35%, 45%, etc...) Reports are generated by the
qualifying percentages. When a user wishes to run a report, the report has a
filter that asks the user to enter the qualifying percent (35%, 45%, etc...)

Now, I have a special report that is generated to send out “update lettersâ€,
if you will, to the selected applicants. This report also has the same filter
asking users to enter the percentage (35%, 45%, etc...) which will pull only
applicants who are qualified in that percentage.

I have also created an “update query†that utilizes the same filtering
method of the above-mentioned reports/letters. When selected, the update
query will ask the user to enter the percentage (35%, 45%, etc...) of the
records to be updated. The update query will then update certain
controls/fields within the respective applicant records.

What I am trying to accomplish is when the report or “update letters†are
selected and the user enters the percentage as required, I would like the
“Update Query†to run using the same filter as the “Update Letters†and
update the records selected without having two separate procedures.

Anyone know how I could combine the report with the update query?

Right now I have this in the Report_Open sub:

Private Sub Report_Open (Cancel As Integer)
On Error GoTo Report_Open_Err
DoCmd.Maximize
DoCmd.ApplyFilter "Enter Percentage Filter Query"
DoCmd.OpenQuery "Public_Letter_Sent_Update_Query", acViewNormal,
acReadOnly
DoCmd.Close
Report_Open_Exit:
Exit Sub
Report_Open_Err:
MsgBox Error$
Resume Report_Open_Exit
End Sub


Both query’s use the same filter where the control [Qual_Pct] = [Enter
Percentage %]. The above method was the only way I could figure out how to
get them to run at the same time, however, is asks to “Enter Percentage %â€
twice. I am trying to get this to only ask for the “Percentage†once.

Any ideas or suggestions will be greeeeaaaaatly appreciated. I thank you in
advance for any time you may commit to this.

 

[Randy]

oooo....good idea...i'll give that a shot....Thanks guys!
--
Randy Street
Rancho Cucamonga, CA

Create a form with a text box on it, and have them fill in the rate in that
text box.

Change the filter/query to refer to Forms!NameOfForm!NameOfControl rather
than [Enter Percentage %]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, this may be a little tricky and I apologize for the lengthy
description!
Here's the scenario; A little background on the db first. This is an
affordable housing waiting list database. Applicants are qualified by
income
levels or percentages (i.e.; 35%, 45%, etc...) Reports are generated by
the
qualifying percentages. When a user wishes to run a report, the report has
a
filter that asks the user to enter the qualifying percent (35%, 45%,
etc...)

Now, I have a special report that is generated to send out "update
letters",
if you will, to the selected applicants. This report also has the same
filter
asking users to enter the percentage (35%, 45%, etc...) which will pull
only
applicants who are qualified in that percentage.

I have also created an "update query" that utilizes the same filtering
method of the above-mentioned reports/letters. When selected, the update
query will ask the user to enter the percentage (35%, 45%, etc...) of the
records to be updated. The update query will then update certain
controls/fields within the respective applicant records.

What I am trying to accomplish is when the report or "update letters" are
selected and the user enters the percentage as required, I would like the
"Update Query" to run using the same filter as the "Update Letters" and
update the records selected without having two separate procedures.

Anyone know how I could combine the report with the update query?

Right now I have this in the Report_Open sub:

Private Sub Report_Open (Cancel As Integer)
On Error GoTo Report_Open_Err
DoCmd.Maximize
DoCmd.ApplyFilter "Enter Percentage Filter Query"
DoCmd.OpenQuery "Public_Letter_Sent_Update_Query", acViewNormal,
acReadOnly
DoCmd.Close
Report_Open_Exit:
Exit Sub
Report_Open_Err:
MsgBox Error$
Resume Report_Open_Exit
End Sub


Both query's use the same filter where the control [Qual_Pct] = [Enter
Percentage %]. The above method was the only way I could figure out how to
get them to run at the same time, however, is asks to "Enter Percentage %"
twice. I am trying to get this to only ask for the "Percentage" once.

Any ideas or suggestions will be greeeeaaaaatly appreciated. I thank you
in
advance for any time you may commit to this.