Wrong record printed

[google3luo359]

I have the following VB code for a printer button on a Tab page:

Dim strWhere As String
strWhere = "[AEPID] = [AEP10qry]![AEPID]"
DoCmd.OpenReport "rptAEP10", acViewPreview, , strWhere

but... instead of printing the correct AEPID record, it is printing
*another* AEPID record.

Would anyone have ideas as to why this might be happening?
Will I have to implant that print button on the subform with the
records, or can it stay on the Tab control?

Thanks in advance! Ric

 

[Allen Browne]

Concatenate the value of the field on the form into the WhereCondition
string:
strWhere = "[AEPID] = " & Me.AEPID

If AEPID is a Text type fied (not a Number field), you need extra quotes:
strWhere = "[AEPID] = """ & Me.AEPID & """"

Further suggestions and info:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

 

[google3luo359]

Concatenate the value of the field on the form into the WhereCondition
string:
strWhere = "[AEPID] = " & Me.AEPID

If AEPID is a Text type fied (not a Number field), you need extra quotes:
strWhere = "[AEPID] = """ & Me.AEPID & """"

Thanks Allen.
As I pointed out in my original post near the end, there is a subform
on the form.
Consequently I couldn't use Me.AEPID.

I tried:
strWhere = "[AEPID] = " & Forms![fmAEP]![sfmTab09].AEPID

but I got: 'Object doesn't support this property or method'

Basically the report has the correct query recordset to pull up the
right records.
In those records is one record that needs to be displayed. It's on the
screen, nice and bright. The subform which houses most of the info has
the AEPID which identifies the specific record.

TIA Ric

 

[Allen Browne]

You're missing the ".Form" bit:
strWhere = "[AEPID] = " & Forms![fmAEP]![sfmTab09].Form.AEPID

Explanation in:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

If that still fails, open the main form in design view, right-click the edge
of the subform control and choose Properties. What is the Name of the
subform control? It can be different from the name of the form it contains
(its SourceObject.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Concatenate the value of the field on the form into the WhereCondition
string:
strWhere = "[AEPID] = " & Me.AEPID

If AEPID is a Text type fied (not a Number field), you need extra quotes:
strWhere = "[AEPID] = """ & Me.AEPID & """"

Thanks Allen.
As I pointed out in my original post near the end, there is a subform
on the form.
Consequently I couldn't use Me.AEPID.

I tried:
strWhere = "[AEPID] = " & Forms![fmAEP]![sfmTab09].AEPID

but I got: 'Object doesn't support this property or method'

Basically the report has the correct query recordset to pull up the
right records.
In those records is one record that needs to be displayed. It's on the
screen, nice and bright. The subform which houses most of the info has
the AEPID which identifies the specific record.

TIA Ric

 

[google3luo359]

You're missing the ".Form" bit:
strWhere = "[AEPID] = " & Forms![fmAEP]![sfmTab09].Form.AEPID
....
If that still fails, open the main form in design view, right-click the edge
of the subform control and choose Properties. What is the Name of the
subform control? It can be different from the name of the form it contains
(its SourceObject.)

Hi Allen,

I tried the above but it didn't work. You were right about not having
the correct name for the subcontrol. But when I corrected it, it still
didn't work.

But I'm happy to report that I found a solution! YES!!!

I stripped things down to troubleshoot.
I opened up the *subform* that's on the troublesome form.
I made a new report from it. File/SaveAs/Report....

The new report behaved properly. I could make changes/additions to it
and nothing happened to the subform that it was created from.

The best part though was when I inserted your code to pull up the
desired record and it *worked* ! Finally!!!

So now I'm just dressing up this Report to make it look like the
original form.
The end is near!!!

Ric

 

[Allen Browne]

Good troubleshooting. Well done!

 

[google3luo359]

Good troubleshooting. Well done!

Thanks Allen for your help!

One more quick question. This is very minor.
All record printing is working very well now.

One interesting thing is happening though.
If I click the command button to print a blank record I get the
following:
Extra ) in query expression '([AEPID] = )' .

I tried turning off errors just for the printing part of the VB code
but the above error still comes through.
Do you know what it's about?

Ric

 

[Allen Browne]

That makes sense. If there is no number to include in the string, then the
string is malformed, exactly like your example.

To avoid that, use IsNull() to test if the control has a value. The article
at:
http://allenbrowne.com/casu-15.html
tested for NewRecord, because we were using the primary key which is only
Null at a new record.

Alternatively, use Nz() to supply a value that will not be found, e.g.:
strWhere = "([AEPID] = " & Nz([AEPID],0) & ")"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Good troubleshooting. Well done!

Thanks Allen for your help!

One more quick question. This is very minor.
All record printing is working very well now.

One interesting thing is happening though.
If I click the command button to print a blank record I get the
following:
Extra ) in query expression '([AEPID] = )' .

I tried turning off errors just for the printing part of the VB code
but the above error still comes through.
Do you know what it's about?

Ric

 

[google3luo359]

That makes sense. If there is no number to include in the string, then the
string is malformed, exactly like your example.

To avoid that, use IsNull() to test if the control has a value. The article
at:
http://allenbrowne.com/casu-15.html
tested for NewRecord, because we were using the primary key which is only
Null at a new record.

Alternatively, use Nz() to supply a value that will not be found, e.g.:
strWhere = "([AEPID] = " & Nz([AEPID],0) & ")"

Thanks Allen, I went with the IsNull() test and it works perfectly.

Ric