TINA-printing a specific record from a subreport

[Tina Marie]

Hi ... I have a main form and then a nested form ... on the subform I can
pick an name (which has been calculated to display lastname, firstname)
multiple times ... now ... per name, I want a command button that will print
that name's information ...

I created the command button ... I created a macro that opens 'on click'
event ... it opens a report ... the 'where' clause is:

field from query of report=forms!subform name!subform field

keep getting an error ... help!

 

[Arvin Meyer MVP]

Code when running from within the subform should look like:

DoCmd.OpenReport "ReportName" , , "[Field from query] =" & Me.[subform
control name]

Make sure that you surround names that have space with square brackets, and
observe the quote marks.

 

[tina]

Arvin's response is correct. but if you're using a macro rather than VBA,
i'm not sure the Me reference will work. if not, try just the name of the
control in the subform, as

field from query of report=subform field

using the brackets as Arvin instructed. if that doesn't do it, try a full
reference, as

field from query of
report=Forms!FormName!SubformControlName.Form!ControlInSubform

that would be all on one line in the macro WhereCondition argument,
regardless of linewrap here. in fact, in a macro, the easy way to build the
argument is to click on the WhereCondition "line" in the Action Arguments
section of the macro, then click the Build (...) button. in the Expression
Builder dialog, click in the big box in the top half of the window, and type
the "left" side of the expression, as

field from query of report=

then double click the Forms folder (left-hand column in bottom half of
Builder), then ditto the LoadedForms folder, ditto your form's name folder,
then ditto again the subform name folder. in the middle column, scroll down
to the name of the control you need to refer to, and double click it. the
Builder should add the full syntax to the box at the top, as

field from query of
report=Forms![FormName]![SubformControlName].Form![ControlInSubform]

hth

 

[Tina Marie]

Hi Arvin ... ok .. will try it .. may have a problem because field i'm using
to match is a calculated field which switces the first and last name around
and therefore in a query environment would want quotes around it (comma is in
there) .. let you know ... thanks!!
--
Thanks!!

T. Marie

Code when running from within the subform should look like:

DoCmd.OpenReport "ReportName" , , "[Field from query] =" & Me.[subform
control name]

Make sure that you surround names that have space with square brackets, and
observe the quote marks.

 

[Arvin Meyer MVP]

So for text criteria the code would read:

DoCmd.OpenReport "ReportName" , , "[Field from query] ='" & Me.[subform
control name] & "'"

To make that clearer, I'll add spaces between the single and double quotes:

"[Field from query] = ' " & Me.[subform control name] & " ' "
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin ... ok .. will try it .. may have a problem because field i'm
using
to match is a calculated field which switces the first and last name
around
and therefore in a query environment would want quotes around it (comma is
in
there) .. let you know ... thanks!!
--
Thanks!!

T. Marie

Code when running from within the subform should look like:

DoCmd.OpenReport "ReportName" , , "[Field from query] =" & Me.[subform
control name]

Make sure that you surround names that have space with square brackets,
and
observe the quote marks.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi ... I have a main form and then a nested form ... on the subform I
can
pick an name (which has been calculated to display lastname, firstname)
multiple times ... now ... per name, I want a command button that will
print
that name's information ...

I created the command button ... I created a macro that opens 'on
click'
event ... it opens a report ... the 'where' clause is:

field from query of report=forms!subform name!subform field

keep getting an error ... help!

 

[Tina Marie]

Okay Arvin ... will try ... thanks again ... Tina
--
Thanks!!

T. Marie

So for text criteria the code would read:

DoCmd.OpenReport "ReportName" , , "[Field from query] ='" & Me.[subform
control name] & "'"

To make that clearer, I'll add spaces between the single and double quotes:

"[Field from query] = ' " & Me.[subform control name] & " ' "
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin ... ok .. will try it .. may have a problem because field i'm
using
to match is a calculated field which switces the first and last name
around
and therefore in a query environment would want quotes around it (comma is
in
there) .. let you know ... thanks!!
--
Thanks!!

T. Marie

Code when running from within the subform should look like:

DoCmd.OpenReport "ReportName" , , "[Field from query] =" & Me.[subform
control name]

Make sure that you surround names that have space with square brackets,
and
observe the quote marks.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Hi ... I have a main form and then a nested form ... on the subform I
can
pick an name (which has been calculated to display lastname, firstname)
multiple times ... now ... per name, I want a command button that will
print
that name's information ...

I created the command button ... I created a macro that opens 'on
click'
event ... it opens a report ... the 'where' clause is:

field from query of report=forms!subform name!subform field

keep getting an error ... help!

 

[Tina Marie]

Hi Arvin ...

When I tried your code and I know you do this solely in vba/sql ... it
opened OneNote and still showed all records ... not the specific one ..

Would the code be slightly different if I used a macro ... 'openreport'
command ... opening the report called: rptExternalTranslatorInfo .... and in
the where clause the field in the underlying query of the report is called:
strExternalTranslationBy ...

here is my code:

[qryExternalTranslatorInfo]![strExternalTranslationBy]="[Forms]![frmRequestForService]![sfrmExternalInfo]![strExternalTranslationBy]'"

doesn't work ... not too sure about the quotes single/double or whether they
are needed in a where clause on the left and/or right .. help!! Tina

(e-mail address removed)
--
Thanks!!

T. Marie

So for text criteria the code would read:

DoCmd.OpenReport "ReportName" , , "[Field from query] ='" & Me.[subform
control name] & "'"

To make that clearer, I'll add spaces between the single and double quotes:

"[Field from query] = ' " & Me.[subform control name] & " ' "
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin ... ok .. will try it .. may have a problem because field i'm
using
to match is a calculated field which switces the first and last name
around
and therefore in a query environment would want quotes around it (comma is
in
there) .. let you know ... thanks!!
--
Thanks!!

T. Marie

Code when running from within the subform should look like:

DoCmd.OpenReport "ReportName" , , "[Field from query] =" & Me.[subform
control name]

Make sure that you surround names that have space with square brackets,
and
observe the quote marks.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Hi ... I have a main form and then a nested form ... on the subform I
can
pick an name (which has been calculated to display lastname, firstname)
multiple times ... now ... per name, I want a command button that will
print
that name's information ...

I created the command button ... I created a macro that opens 'on
click'
event ... it opens a report ... the 'where' clause is:

field from query of report=forms!subform name!subform field

keep getting an error ... help!

 

[John W. Vinson]

Hi Arvin ...

When I tried your code and I know you do this solely in vba/sql ... it
opened OneNote and still showed all records ... not the specific one ..

Would the code be slightly different if I used a macro ... 'openreport'
command ... opening the report called: rptExternalTranslatorInfo .... and in
the where clause the field in the underlying query of the report is called:
strExternalTranslationBy ...

here is my code:

[qryExternalTranslatorInfo]![strExternalTranslationBy]="[Forms]![frmRequestForService]![sfrmExternalInfo]![strExternalTranslationBy]'"

doesn't work ... not too sure about the quotes single/double or whether they
are needed in a where clause on the left and/or right .. help!! Tina

This will find all records where strExternalTranslationBy is literally equal
to the text string
[Forms]![frmRequestForService]![sfrmExternalInfo]![strExternalTranslationBy]
and of course there won't be any!

I think you need instead either

[strExternalTranslationBy]=[Forms]![frmRequestForService]![sfrmExternalInfo].Form![strExternalTranslationBy]

so that Access will expand the form reference and find the CONTENT of the form
field, or

[strExternalTranslationBy]="'" &
[Forms]![frmRequestForService]![sfrmExternalInfo].Form![strExternalTranslationBy]
& "'"

to extract the value from the form control and construct a string containing
it (delimited by ' quotes).

I'm presuming that strExternalTranslationBy is actually a text value, not a
Combo Box with a (hidden) numeric translator ID.