Capture Form Field To Display Report

[EarlCPhillips]

I currently print a report that displays selected activity data about a
person. It requires entering the person's [User ID], the [Start Date] and
[End Date] to print all user activity between entered dates. Each bracketed
variable must be entered to generate the report. A "Print" command button
prepares the report rather than using the Print function as that prints all
activity, which is sometimes quite lengthy.

I wish to capture the ID already displayed on the form via variable Me!txtID
instead of using [User ID] to generate the report. How do I capture Me!intID
and place it in [User ID] without operater intervention when the Print button
is pressed. They will always need to enter the dates since the report will
depend on the content of each person's activity rows.

Earl Phillips
Ex-Mainframer Volunteering at Access Coding
Harvesters Community Food Bank

 

[Klatuu]

You can filter what a report presents in the OpenReport method using the
Where argument. If you don't have text boxes on the form for the start and
end dates, put them there. It is a little cleaner that having the use have
to respond to paramenter boxes. Build a Where String based on the values in
the text box. Since I don't know the names of the fields in your table, I
will make some up. You will need to change them to suit your names.

strWhere = "[UserName] = '" & Me.txtID & "' And [ActivityDate] Between #" &
Me.txtStartDate & "# And #" & Me.txtEndDate & "#"

The use the OpenReport Method:

DoCmd.OpenReport "MyReportName", acNormal, , strWhere

 

[EarlCPhillips]

From your responce, I coded:
strWhere = "[UserID] = '" & Me!txtID & "'"
'which produces the string "[UserID = '12345'"
DoCmd.OpenReport "rptCSWork", acViewPreview, , strWhere, acWindowNormal

It still asks for UserID input when strWhere is included in the DoCmd. The
UserID comes from the main form (and table) and the many activities are from
a subform (activity table), one for each day of activity. I have left out
the coding of the start and end dates because there is a different range of
activity records required for each individual, not always starting from the
first row and not always ending with the last row. This generates a report
back to the courts reporting the dates and times people performed their
required community service work here.

Earl Phillips
Ex-Mainframer Confused About Access
Harvesters Community Food Bank
--
Trying To Feed The Hungry

You can filter what a report presents in the OpenReport method using the
Where argument. If you don't have text boxes on the form for the start and
end dates, put them there. It is a little cleaner that having the use have
to respond to paramenter boxes. Build a Where String based on the values in
the text box. Since I don't know the names of the fields in your table, I
will make some up. You will need to change them to suit your names.

strWhere = "[UserName] = '" & Me.txtID & "' And [ActivityDate] Between #" &
Me.txtStartDate & "# And #" & Me.txtEndDate & "#"

The use the OpenReport Method:

DoCmd.OpenReport "MyReportName", acNormal, , strWhere

I currently print a report that displays selected activity data about a
person. It requires entering the person's [User ID], the [Start Date] and
[End Date] to print all user activity between entered dates. Each bracketed
variable must be entered to generate the report. A "Print" command button
prepares the report rather than using the Print function as that prints all
activity, which is sometimes quite lengthy.

I wish to capture the ID already displayed on the form via variable Me!txtID
instead of using [User ID] to generate the report. How do I capture Me!intID
and place it in [User ID] without operater intervention when the Print button
is pressed. They will always need to enter the dates since the report will
depend on the content of each person's activity rows.

Earl Phillips
Ex-Mainframer Volunteering at Access Coding
Harvesters Community Food Bank

 

[Klatuu]

I assume you meant 'which produces the string "[UserID] = '12345'"
If [UserID] is not a text field, change it to
strWhere = "[UserID] = " & Me!txtID

[UserID] needs to be a field in the record source for the report. That is
what it is filtering on.

There is no reason you can't put the dates you want to include on the main
form. Since you are reporting on only one User, Filtering on the dates is
going to apply only to the selected user.

From your responce, I coded:
strWhere = "[UserID] = '" & Me!txtID & "'"
'which produces the string "[UserID = '12345'"
DoCmd.OpenReport "rptCSWork", acViewPreview, , strWhere, acWindowNormal

It still asks for UserID input when strWhere is included in the DoCmd. The
UserID comes from the main form (and table) and the many activities are from
a subform (activity table), one for each day of activity. I have left out
the coding of the start and end dates because there is a different range of
activity records required for each individual, not always starting from the
first row and not always ending with the last row. This generates a report
back to the courts reporting the dates and times people performed their
required community service work here.

Earl Phillips
Ex-Mainframer Confused About Access
Harvesters Community Food Bank
--
Trying To Feed The Hungry

You can filter what a report presents in the OpenReport method using the
Where argument. If you don't have text boxes on the form for the start and
end dates, put them there. It is a little cleaner that having the use have
to respond to paramenter boxes. Build a Where String based on the values in
the text box. Since I don't know the names of the fields in your table, I
will make some up. You will need to change them to suit your names.

strWhere = "[UserName] = '" & Me.txtID & "' And [ActivityDate] Between #" &
Me.txtStartDate & "# And #" & Me.txtEndDate & "#"

The use the OpenReport Method:

DoCmd.OpenReport "MyReportName", acNormal, , strWhere

I currently print a report that displays selected activity data about a
person. It requires entering the person's [User ID], the [Start Date] and
[End Date] to print all user activity between entered dates. Each bracketed
variable must be entered to generate the report. A "Print" command button
prepares the report rather than using the Print function as that prints all
activity, which is sometimes quite lengthy.

I wish to capture the ID already displayed on the form via variable Me!txtID
instead of using [User ID] to generate the report. How do I capture Me!intID
and place it in [User ID] without operater intervention when the Print button
is pressed. They will always need to enter the dates since the report will
depend on the content of each person's activity rows.

Earl Phillips
Ex-Mainframer Volunteering at Access Coding
Harvesters Community Food Bank

 

[EarlCPhillips]

You were correct in your assumption about the closing bracket . I have also
tried:
Dim strWhere As String
Dim intID As Integer
Dim strDocName As String

strDocName = "rptCSWork"
intID = Cint(Me!txtID)
strWhere = "[UserID] = " & intID 'creating strWhere = "[UserID] =
12345"
DoCmd.OpenReport strDocName, acViewPreview, , strWhere, acWindowNormal

Whether I present the where clause containing a literal or containing a
number, it still responds the same. I have used the prompt to be sure the
where clause is after the correct comma. There must be some very small
difference between what you are recommending and what I am doing that I am
not seeing or do not understand. I will keep trying variations until I get
it to work. I understand the difference between passing literals and passing
a number in the SQL code, with Access being very particular about what it
will accept. I will get back to this on Wednesday as I only volunteer Mon,
Wed and Fri.

Earl Phillips
Ex-Mainframer nearly exasperated with Access
--
Trying To Feed The Hungry

I assume you meant 'which produces the string "[UserID] = '12345'"
If [UserID] is not a text field, change it to
strWhere = "[UserID] = " & Me!txtID

[UserID] needs to be a field in the record source for the report. That is
what it is filtering on.

There is no reason you can't put the dates you want to include on the main
form. Since you are reporting on only one User, Filtering on the dates is
going to apply only to the selected user.

From your responce, I coded:
strWhere = "[UserID] = '" & Me!txtID & "'"
'which produces the string "[UserID = '12345'"
DoCmd.OpenReport "rptCSWork", acViewPreview, , strWhere, acWindowNormal

It still asks for UserID input when strWhere is included in the DoCmd. The
UserID comes from the main form (and table) and the many activities are from
a subform (activity table), one for each day of activity. I have left out
the coding of the start and end dates because there is a different range of
activity records required for each individual, not always starting from the
first row and not always ending with the last row. This generates a report
back to the courts reporting the dates and times people performed their
required community service work here.

Earl Phillips
Ex-Mainframer Confused About Access
Harvesters Community Food Bank
--
Trying To Feed The Hungry

You can filter what a report presents in the OpenReport method using the
Where argument. If you don't have text boxes on the form for the start and
end dates, put them there. It is a little cleaner that having the use have
to respond to paramenter boxes. Build a Where String based on the values in
the text box. Since I don't know the names of the fields in your table, I
will make some up. You will need to change them to suit your names.

strWhere = "[UserName] = '" & Me.txtID & "' And [ActivityDate] Between #" &
Me.txtStartDate & "# And #" & Me.txtEndDate & "#"

The use the OpenReport Method:

DoCmd.OpenReport "MyReportName", acNormal, , strWhere

:

I currently print a report that displays selected activity data about a
person. It requires entering the person's [User ID], the [Start Date] and
[End Date] to print all user activity between entered dates. Each bracketed
variable must be entered to generate the report. A "Print" command button
prepares the report rather than using the Print function as that prints all
activity, which is sometimes quite lengthy.

I wish to capture the ID already displayed on the form via variable Me!txtID
instead of using [User ID] to generate the report. How do I capture Me!intID
and place it in [User ID] without operater intervention when the Print button
is pressed. They will always need to enter the dates since the report will
depend on the content of each person's activity rows.

Earl Phillips
Ex-Mainframer Volunteering at Access Coding
Harvesters Community Food Bank

 

[Klatuu]

This technique does work. It is used all the time. Basically, what you want
to pass it is:
TableFieldName = FormControlName
Both data types must be the same.
If the Table Field data type is text, then the Form Control Value must be
enclosed in quotes. If the Table Field data type is numeric, it should not
be enclosed at all, and if it is a date type, it should be enclosed in #.

Text:
strWhere = "[SomeField] = '" & Me.txtSomeControl & "'"
Numeric:
strWhere = "[SomeField] = " & Me.txtSomeControl
Date:
strWhere = "[SomeField] = #" & Me.txtSomeControl & "#"

You were correct in your assumption about the closing bracket . I have also
tried:
Dim strWhere As String
Dim intID As Integer
Dim strDocName As String

strDocName = "rptCSWork"
intID = Cint(Me!txtID)
strWhere = "[UserID] = " & intID 'creating strWhere = "[UserID] =
12345"
DoCmd.OpenReport strDocName, acViewPreview, , strWhere, acWindowNormal

Whether I present the where clause containing a literal or containing a
number, it still responds the same. I have used the prompt to be sure the
where clause is after the correct comma. There must be some very small
difference between what you are recommending and what I am doing that I am
not seeing or do not understand. I will keep trying variations until I get
it to work. I understand the difference between passing literals and passing
a number in the SQL code, with Access being very particular about what it
will accept. I will get back to this on Wednesday as I only volunteer Mon,
Wed and Fri.

Earl Phillips
Ex-Mainframer nearly exasperated with Access
--
Trying To Feed The Hungry

I assume you meant 'which produces the string "[UserID] = '12345'"
If [UserID] is not a text field, change it to
strWhere = "[UserID] = " & Me!txtID

[UserID] needs to be a field in the record source for the report. That is
what it is filtering on.

There is no reason you can't put the dates you want to include on the main
form. Since you are reporting on only one User, Filtering on the dates is
going to apply only to the selected user.

From your responce, I coded:
strWhere = "[UserID] = '" & Me!txtID & "'"
'which produces the string "[UserID = '12345'"
DoCmd.OpenReport "rptCSWork", acViewPreview, , strWhere, acWindowNormal

It still asks for UserID input when strWhere is included in the DoCmd. The
UserID comes from the main form (and table) and the many activities are from
a subform (activity table), one for each day of activity. I have left out
the coding of the start and end dates because there is a different range of
activity records required for each individual, not always starting from the
first row and not always ending with the last row. This generates a report
back to the courts reporting the dates and times people performed their
required community service work here.

Earl Phillips
Ex-Mainframer Confused About Access
Harvesters Community Food Bank
--
Trying To Feed The Hungry


:

You can filter what a report presents in the OpenReport method using the
Where argument. If you don't have text boxes on the form for the start and
end dates, put them there. It is a little cleaner that having the use have
to respond to paramenter boxes. Build a Where String based on the values in
the text box. Since I don't know the names of the fields in your table, I
will make some up. You will need to change them to suit your names.

strWhere = "[UserName] = '" & Me.txtID & "' And [ActivityDate] Between #" &
Me.txtStartDate & "# And #" & Me.txtEndDate & "#"

The use the OpenReport Method:

DoCmd.OpenReport "MyReportName", acNormal, , strWhere

:

I currently print a report that displays selected activity data about a
person. It requires entering the person's [User ID], the [Start Date] and
[End Date] to print all user activity between entered dates. Each bracketed
variable must be entered to generate the report. A "Print" command button
prepares the report rather than using the Print function as that prints all
activity, which is sometimes quite lengthy.

I wish to capture the ID already displayed on the form via variable Me!txtID
instead of using [User ID] to generate the report. How do I capture Me!intID
and place it in [User ID] without operater intervention when the Print button
is pressed. They will always need to enter the dates since the report will
depend on the content of each person's activity rows.

Earl Phillips
Ex-Mainframer Volunteering at Access Coding
Harvesters Community Food Bank