The value of 'Reports!rptInvoice!fldDepartment.Value' does not show on report

I am auto-generating a report with the following code

DoCmd.OpenReport "rptInvoice";, acViewDesign

SQLStr = "SELECT * FROM qryPatron_Shared " & _
"INNER JOIN tmp_qryJob " & _
"ON qryPatron_Shared.fldPatronID = tmp_qryJob.fldPatronID " & _
"WHERE qryPatron_Shared.fldPatronID='" & rst!fldPatronID & "'"
Reports!rptinvoice.RecordSource = SQLStr
DoCmd.OpenReport "rptInvoice";, acViewPreview
Reports!rptinvoice!fldDepartment.Value = _
DLookup("[fldDeptFullName]", "qryDepartmentListing";, "[fldDeptCode]='" & _
Reports![rptinvoice]![fldDept] & "'")
DoCmd.Close acReport, "rptInvoice";, acSaveNo

The "Reports!rptInvoice!fldDepartment.Value = " line gets the right
value (the name of the department) stored in the reports field
"fldDepartment." The returned value does not, however, show up on the
report. I have tried refresh, repaint, and requery to no effect. The
field remains blank.

Access 2007 vba
The data is being looked up in a foreign table not part of the
recordsource.
This code is applied to control the form from a command button on a
menu form.

Suggestions appreciated.

Messages in this topic (1) Reply (via web post) | Start a new topic

.

After a couple of days trying, I may have just figured this one out.

--- In helpwithvb%40yahoogroups.com">helpwithvbyahoogroups.com, "wharmon97" <wmhLists...> wrote:
&gt;
> I am auto-generating a report with the following code
>;
>
> DoCmd.OpenReport "rptInvoice";, acViewDesign
>
> SQLStr = "SELECT * FROM qryPatron_Shared " & _
> "INNER JOIN tmp_qryJob " & _
> "ON qryPatron_Shared.fldPatronID = tmp_qryJob.fldPatronID " & _
> "WHERE qryPatron_Shared.fldPatronID='" & rst!fldPatronID & "'&quot;
> Reports!rptinvoice.RecordSource = SQLStr
&gt; DoCmd.OpenReport "rptInvoice";, acViewPreview
> Reports!rptinvoice!fldDepartment.Value = _
> DLookup(&quot;[fldDeptFullName]", "qryDepartmentListing";,
"[fldDeptCode]='" & _
> Reports![rptinvoice]![fldDept] & "'&quot;)
> DoCmd.Close acReport, "rptInvoice";, acSaveNo
>
>
> The "Reports!rptInvoice!fldDepartment.Value = " line gets the right
&gt; value (the name of the department) stored in the reports field
&gt; "fldDepartment." The returned value does not, however, show up on the
> report. I have tried refresh, repaint, and requery to no effect. The
> field remains blank.
&gt;
> Access 2007 vba
> The data is being looked up in a foreign table not part of the
> recordsource.
> This code is applied to control the form from a command button on a
> menu form.
&gt;
>
> Suggestions appreciated.
>

Messages in this topic (2) Reply (via web post) | Start a new topic

.

Messages in this topic (3) Reply (via web post) | Start a new topic

.