Report pulls one record from open form.

A

Adam Thwaites

I currently use this line of code to output reports in .doc format in my
database:

DoCmd.OutputTo acOutputReport, ReportName, ".rtf", PathAndFileName

Where ReportName lets say is 'Test Drive' and the PathAndFileName is 'C:\Mr
Anderson - Test Drive.doc'

I have several forms that use this line of code so for the query I can't
specify the text box for it to pull the PrimaryID from, I have to tell it to
prompt the user for the PrimaryID of the record.

I'v seen code like this used but it doesn't work for this particular output
method
Reports![Test Drive].Filter = "PrimaryID = " & Me![txtPrimaryID]
Reports![Test Drive].FilterOn = True

How can I make it so the report is output like this but so it doesn't have
to ask for the PrimaryID?
 
J

JA Peachrose

In the query that runs your report add a field that states this in the
criteria line:
[forms]![form_name]![txtPrimaryID]
 
6

'69 Camaro

Hi, Adam.
I have several forms that use this line of code so for the query I can't
specify the text box for it to pull the PrimaryID from, I have to tell it to
prompt the user for the PrimaryID of the record.
Click to expand...

One may set a user-defined variable before opening any report that the query
can read whenever it opens. This way, no particular form must be open and no
particular primary key or control name need be used. For an example of how
to do this in your query, please see the link to the tip, "How to use a
user-defined VBA variable as criteria in a query" at the bottom of the
"Queries" section on the following Web page:

http://www.access.qbuilt.com/html/how-to_tips.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Adam Thwaites said:
I currently use this line of code to output reports in .doc format in my
database:

DoCmd.OutputTo acOutputReport, ReportName, ".rtf", PathAndFileName

Where ReportName lets say is 'Test Drive' and the PathAndFileName is 'C:\Mr
Anderson - Test Drive.doc'

I have several forms that use this line of code so for the query I can't
specify the text box for it to pull the PrimaryID from, I have to tell it to
prompt the user for the PrimaryID of the record.

I'v seen code like this used but it doesn't work for this particular output
method
Reports![Test Drive].Filter = "PrimaryID = " & Me![txtPrimaryID]
Reports![Test Drive].FilterOn = True

How can I make it so the report is output like this but so it doesn't have
to ask for the PrimaryID?

--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK
Click to expand...
 
A

Adam Thwaites

As I said, I have more than one form that uses the report so I can't use that
line
[forms]![form_name]![txtPrimaryID]

--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK


JA Peachrose said:
In the query that runs your report add a field that states this in the
criteria line:
[forms]![form_name]![txtPrimaryID]
--
JA Peachrose


Adam Thwaites said:
I currently use this line of code to output reports in .doc format in my
database:

DoCmd.OutputTo acOutputReport, ReportName, ".rtf", PathAndFileName

Where ReportName lets say is 'Test Drive' and the PathAndFileName is 'C:\Mr
Anderson - Test Drive.doc'

I have several forms that use this line of code so for the query I can't
specify the text box for it to pull the PrimaryID from, I have to tell it to
prompt the user for the PrimaryID of the record.

I'v seen code like this used but it doesn't work for this particular output
method
Reports![Test Drive].Filter = "PrimaryID = " & Me![txtPrimaryID]
Reports![Test Drive].FilterOn = True

How can I make it so the report is output like this but so it doesn't have
to ask for the PrimaryID?

--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK
Click to expand...
Click to expand...
 
A

Adam Thwaites

I don't have access to most sites on the net including that one. Would you be
able to post the solution off that site or possibly e-mail me the contents of
the page to
adamDOTthwaitesATmercedesDOTcoDOTuk
--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK


'69 Camaro said:
Hi, Adam.
I have several forms that use this line of code so for the query I can't
specify the text box for it to pull the PrimaryID from, I have to tell it to
prompt the user for the PrimaryID of the record.
Click to expand...

One may set a user-defined variable before opening any report that the query
can read whenever it opens. This way, no particular form must be open and no
particular primary key or control name need be used. For an example of how
to do this in your query, please see the link to the tip, "How to use a
user-defined VBA variable as criteria in a query" at the bottom of the
"Queries" section on the following Web page:

http://www.access.qbuilt.com/html/how-to_tips.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Adam Thwaites said:
I currently use this line of code to output reports in .doc format in my
database:

DoCmd.OutputTo acOutputReport, ReportName, ".rtf", PathAndFileName

Where ReportName lets say is 'Test Drive' and the PathAndFileName is 'C:\Mr
Anderson - Test Drive.doc'

I have several forms that use this line of code so for the query I can't
specify the text box for it to pull the PrimaryID from, I have to tell it to
prompt the user for the PrimaryID of the record.

I'v seen code like this used but it doesn't work for this particular output
method
Reports![Test Drive].Filter = "PrimaryID = " & Me![txtPrimaryID]
Reports![Test Drive].FilterOn = True

How can I make it so the report is output like this but so it doesn't have
to ask for the PrimaryID?

--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK
Click to expand...
Click to expand...
 
6

'69 Camaro

Hi, Adam.
I don't have access to most sites on the net including that one. Would you be
able to post the solution off that site
Click to expand...

Sure. Here it is:

How to use a user-defined VBA variable as criteria in a query.

Q: Is it possible to use a global variable as criteria in a query?

A: You can use a variable as criteria in a query, but you'll need to do some
prep work first. And this variable doesn't even need to be global. A
module-level variable is just fine, and it's preferable too, because it's
better for code maintainability and modularity.

In a standard module, define your module-level variable, then define a
public function that sets the value of this variable, then define a public
function that retrieves the value of this variable. Next, create your query
that uses this "get" function as the criteria for selecting records.

When you need to run this query, first call the "set" function in the
standard module to assign a value to the variable, then run the query. You
won't be prompted to enter a value when the query runs.

Example code in a standard module (need to add error handling to functions):

Option Compare Database
Option Explicit

Private m_nAcctYear As Integer

Public Function setAcctYear(nYear As Integer)
m_nAcctYear = nYear
End Function

Public Function getAcctYear( ) As Integer
getAcctYear = m_nAcctYear
End Function

------------------------------

Example SQL statement:

SELECT *
FROM DeptSales
WHERE (DeptSales.AcctYear = getAcctYear( ));

------------------------------
HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Adam Thwaites said:
I don't have access to most sites on the net including that one. Would you be
able to post the solution off that site or possibly e-mail me the contents of
the page to
adamDOTthwaitesATmercedesDOTcoDOTuk
--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK


'69 Camaro said:
Hi, Adam.
I have several forms that use this line of code so for the query I can't
specify the text box for it to pull the PrimaryID from, I have to tell it to
prompt the user for the PrimaryID of the record.
Click to expand...

One may set a user-defined variable before opening any report that the query
can read whenever it opens. This way, no particular form must be open and no
particular primary key or control name need be used. For an example of how
to do this in your query, please see the link to the tip, "How to use a
user-defined VBA variable as criteria in a query" at the bottom of the
"Queries" section on the following Web page:

http://www.access.qbuilt.com/html/how-to_tips.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Adam Thwaites said:
I currently use this line of code to output reports in .doc format in my
database:

DoCmd.OutputTo acOutputReport, ReportName, ".rtf", PathAndFileName

Where ReportName lets say is 'Test Drive' and the PathAndFileName is 'C:\Mr
Anderson - Test Drive.doc'

I have several forms that use this line of code so for the query I can't
specify the text box for it to pull the PrimaryID from, I have to tell it to
prompt the user for the PrimaryID of the record.

I'v seen code like this used but it doesn't work for this particular output
method
Reports![Test Drive].Filter = "PrimaryID = " & Me![txtPrimaryID]
Reports![Test Drive].FilterOn = True

How can I make it so the report is output like this but so it doesn't have
to ask for the PrimaryID?

--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK
Click to expand...
Click to expand...
Click to expand...
 
A

Adam Thwaites

Thanks Camaro, works like a charm
--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK


'69 Camaro said:
Hi, Adam.
I don't have access to most sites on the net including that one. Would you be
able to post the solution off that site
Click to expand...

Sure. Here it is:

How to use a user-defined VBA variable as criteria in a query.

Q: Is it possible to use a global variable as criteria in a query?

A: You can use a variable as criteria in a query, but you'll need to do some
prep work first. And this variable doesn't even need to be global. A
module-level variable is just fine, and it's preferable too, because it's
better for code maintainability and modularity.

In a standard module, define your module-level variable, then define a
public function that sets the value of this variable, then define a public
function that retrieves the value of this variable. Next, create your query
that uses this "get" function as the criteria for selecting records.

When you need to run this query, first call the "set" function in the
standard module to assign a value to the variable, then run the query. You
won't be prompted to enter a value when the query runs.

Example code in a standard module (need to add error handling to functions):

Option Compare Database
Option Explicit

Private m_nAcctYear As Integer

Public Function setAcctYear(nYear As Integer)
m_nAcctYear = nYear
End Function

Public Function getAcctYear( ) As Integer
getAcctYear = m_nAcctYear
End Function

------------------------------

Example SQL statement:

SELECT *
FROM DeptSales
WHERE (DeptSales.AcctYear = getAcctYear( ));

------------------------------
HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Adam Thwaites said:
I don't have access to most sites on the net including that one. Would you be
able to post the solution off that site or possibly e-mail me the contents of
the page to
adamDOTthwaitesATmercedesDOTcoDOTuk
--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK


'69 Camaro said:
Hi, Adam.

I have several forms that use this line of code so for the query I can't
specify the text box for it to pull the PrimaryID from, I have to tell it to
prompt the user for the PrimaryID of the record.

One may set a user-defined variable before opening any report that the query
can read whenever it opens. This way, no particular form must be open and no
particular primary key or control name need be used. For an example of how
to do this in your query, please see the link to the tip, "How to use a
user-defined VBA variable as criteria in a query" at the bottom of the
"Queries" section on the following Web page:

http://www.access.qbuilt.com/html/how-to_tips.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

I currently use this line of code to output reports in .doc format in my
database:

DoCmd.OutputTo acOutputReport, ReportName, ".rtf", PathAndFileName

Where ReportName lets say is 'Test Drive' and the PathAndFileName is 'C:\Mr
Anderson - Test Drive.doc'

I have several forms that use this line of code so for the query I can't
specify the text box for it to pull the PrimaryID from, I have to tell it to
prompt the user for the PrimaryID of the record.

I'v seen code like this used but it doesn't work for this particular output
method
Reports![Test Drive].Filter = "PrimaryID = " & Me![txtPrimaryID]
Reports![Test Drive].FilterOn = True

How can I make it so the report is output like this but so it doesn't have
to ask for the PrimaryID?

--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK
Click to expand...
Click to expand...
Click to expand...
 
6

'69 Camaro

You're welcome!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


Adam Thwaites said:
Thanks Camaro, works like a charm
--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK


'69 Camaro said:
Hi, Adam.
I don't have access to most sites on the net including that one. Would
you be
able to post the solution off that site
Click to expand...

Sure. Here it is:

How to use a user-defined VBA variable as criteria in a query.

Q: Is it possible to use a global variable as criteria in a query?

A: You can use a variable as criteria in a query, but you'll need to do
some
prep work first. And this variable doesn't even need to be global. A
module-level variable is just fine, and it's preferable too, because it's
better for code maintainability and modularity.

In a standard module, define your module-level variable, then define a
public function that sets the value of this variable, then define a
public
function that retrieves the value of this variable. Next, create your
query
that uses this "get" function as the criteria for selecting records.

When you need to run this query, first call the "set" function in the
standard module to assign a value to the variable, then run the query.
You
won't be prompted to enter a value when the query runs.

Example code in a standard module (need to add error handling to
functions):

Option Compare Database
Option Explicit

Private m_nAcctYear As Integer

Public Function setAcctYear(nYear As Integer)
m_nAcctYear = nYear
End Function

Public Function getAcctYear( ) As Integer
getAcctYear = m_nAcctYear
End Function

------------------------------

Example SQL statement:

SELECT *
FROM DeptSales
WHERE (DeptSales.AcctYear = getAcctYear( ));

------------------------------
HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


Adam Thwaites said:
I don't have access to most sites on the net including that one. Would
you be
able to post the solution off that site or possibly e-mail me the
contents of
the page to
adamDOTthwaitesATmercedesDOTcoDOTuk
--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK


:

Hi, Adam.

I have several forms that use this line of code so for the query I
can't
specify the text box for it to pull the PrimaryID from, I have to
tell it to
prompt the user for the PrimaryID of the record.

One may set a user-defined variable before opening any report that
the query
can read whenever it opens. This way, no particular form must be
open and no
particular primary key or control name need be used. For an example
of how
to do this in your query, please see the link to the tip, "How to use
a
user-defined VBA variable as criteria in a query" at the bottom of
the
"Queries" section on the following Web page:

http://www.access.qbuilt.com/html/how-to_tips.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a
message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember that
questions answered the quickest are often from those who have a
history of
rewarding the contributors who have taken the time to answer
questions
correctly.


:

I currently use this line of code to output reports in .doc format
in my
database:

DoCmd.OutputTo acOutputReport, ReportName, ".rtf", PathAndFileName

Where ReportName lets say is 'Test Drive' and the PathAndFileName
is 'C:\Mr
Anderson - Test Drive.doc'

I have several forms that use this line of code so for the query I
can't
specify the text box for it to pull the PrimaryID from, I have to
tell it to
prompt the user for the PrimaryID of the record.

I'v seen code like this used but it doesn't work for this
particular output
method
Reports![Test Drive].Filter = "PrimaryID = " &
Me![txtPrimaryID]
Reports![Test Drive].FilterOn = True

How can I make it so the report is output like this but so it
doesn't have
to ask for the PrimaryID?

--
Kind Regards,
Adam Thwaites
Access Database Designer
Manchester, UK
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I am using VBA, how do you test for new record in a form 1
Parameter Query 2
need help exporting a report and setting the location of the file... 3
Export Report a HTML 0
Unable to hide report using acHidden 1
Individual Report to each Staff member 0
Printing Report for one a single record - Several problems 5
Visual Basic for Open Form, set value of object as "" 1

Top