report parameter input

  • Thread starter Thread starter June
  • Start date Start date
J

June

My sales staff are entering their own bills in a data
entry form. I have a "Print Report" command button on the
form that the users are to click after they've entered
their last record for the period. So far so good. But the
report being generated is a full report with all entries
for all salesmen rather than one that filters and bases
its report just on the entries of a given user.

Is it possible for me to set it up so that when the "Print
Report" button is clicked, a pop up window that asks for a
value for parameter SalesmanID# to be entered and then
generates a report for just that user? (The SalesID# is
already being recorded in the entry process so the data's
there.)

Thanks for any direction,

June
 
June said:
My sales staff are entering their own bills in a data
entry form. I have a "Print Report" command button on the
form that the users are to click after they've entered
their last record for the period. So far so good. But the
report being generated is a full report with all entries
for all salesmen rather than one that filters and bases
its report just on the entries of a given user.

Is it possible for me to set it up so that when the "Print
Report" button is clicked, a pop up window that asks for a
value for parameter SalesmanID# to be entered and then
generates a report for just that user? (The SalesID# is
already being recorded in the entry process so the data's
Click to expand...


No need for a separate pop up prompt when you already have
the value on the form with the print button. Modify the
button's Click event procedure to use the OpenReport's
WhereCondition argument to specify the SalesmanID. It will
look something like:

. . .
Dim stWhere As String
. . .
stWhere = "[SalesmanID#] = " & txtSalesID
DoCmd.OpenReport stDoc, . . . , _
WhereCondition:= stWhere


Your next question will probably be how to limit the report
to the period being entered. If so, assuming there is a
text box on the form named txtPeriod that contains a numeric
period identifier, then add another condition:

stWhere = "[SalesmanID#] = " & txtSalesID & _
" And [PeriodID] = " & txtPeriod

or, if the period field is a text field:

stWhere = "[SalesmanID#] = " & txtSalesID & _
" And [PeriodID] = """ & txtPeriod & """"
 
Thanks, Marsh. I'm a total novice so your code is Greek to
me, but I'll give this a try today and see how it works
for me. I have a question about the date piece, though.
(You astutely guessed that this was my next issue.)I do
not have a field in my form to designate the billing
period. Instead I have a [DateEntered] field in my main
table that uses Now() to record when the record was added.
(This may be a poor way to do what I'm trying to do, but
my users, god bless them, kept confusing the billing
period with the dates of service and I wanted to eliminate
the billing period field from the form.)

Will the roadmap you sent me still work for this scenario
with me using a date range rather than a text periodID?

thanks again,
June
June said:
My sales staff are entering their own bills in a data
entry form. I have a "Print Report" command button on the
form that the users are to click after they've entered
their last record for the period. So far so good. But the
report being generated is a full report with all entries
for all salesmen rather than one that filters and bases
its report just on the entries of a given user.

Is it possible for me to set it up so that when the "Print
Report" button is clicked, a pop up window that asks for a
value for parameter SalesmanID# to be entered and then
generates a report for just that user? (The SalesID# is
already being recorded in the entry process so the data's
Click to expand...


No need for a separate pop up prompt when you already have
the value on the form with the print button. Modify the
button's Click event procedure to use the OpenReport's
WhereCondition argument to specify the SalesmanID. It will
look something like:

. . .
Dim stWhere As String
. . .
stWhere = "[SalesmanID#] = " & txtSalesID
DoCmd.OpenReport stDoc, . . . , _
WhereCondition:= stWhere


Your next question will probably be how to limit the report
to the period being entered. If so, assuming there is a
text box on the form named txtPeriod that contains a numeric
period identifier, then add another condition:

stWhere = "[SalesmanID#] = " & txtSalesID & _
" And [PeriodID] = " & txtPeriod

or, if the period field is a text field:

stWhere = "[SalesmanID#] = " & txtSalesID & _
" And [PeriodID] = """ & txtPeriod & """"
Click to expand...
 
June said:
Thanks, Marsh. I'm a total novice so your code is Greek to
me, but I'll give this a try today and see how it works
for me. I have a question about the date piece, though.
(You astutely guessed that this was my next issue.)I do
not have a field in my form to designate the billing
period. Instead I have a [DateEntered] field in my main
table that uses Now() to record when the record was added.
(This may be a poor way to do what I'm trying to do, but
my users, god bless them, kept confusing the billing
period with the dates of service and I wanted to eliminate
the billing period field from the form.)

Will the roadmap you sent me still work for this scenario
with me using a date range rather than a text periodID?
Click to expand...


Assuming you can calculate the period's starting and ending
dates in two variables. Let's say these variables are named
dtePeriodStart and dtePeriodEnd, then the where condition
would be:

stWhere = "[SalesmanID#] = " & txtSalesID & _
" And [DateEntered] Between " & _
Format(dtePeriodStart, "\#m\/d\/yyyy") & _
" And " Format(dtePeriodEnd, "\#m\/d\/yyyy")
--
Marsh
MVP [MS Access]


Marshall said:
No need for a separate pop up prompt when you already have
the value on the form with the print button. Modify the
button's Click event procedure to use the OpenReport's
WhereCondition argument to specify the SalesmanID. It will
look something like:

. . .
Dim stWhere As String
. . .
stWhere = "[SalesmanID#] = " & txtSalesID
DoCmd.OpenReport stDoc, . . . , _
WhereCondition:= stWhere


Your next question will probably be how to limit the report
to the period being entered. If so, assuming there is a
text box on the form named txtPeriod that contains a numeric
period identifier, then add another condition:

stWhere = "[SalesmanID#] = " & txtSalesID & _
" And [PeriodID] = " & txtPeriod

or, if the period field is a text field:

stWhere = "[SalesmanID#] = " & txtSalesID & _
" And [PeriodID] = """ & txtPeriod & """"
Click to expand...
Click to expand...
 
Back
Top