Referencing unbound text box on report

[EffenNewGuy]

I have a data entry form with a command button to open a report form. I open the report using VB, and then I try to populate unbound text boxes on the report, I get no results. I'm thinking I need to requery the report form or something for these values to show up, but I can't use Requery...



MSAccess 2007
Windows Vista

Here's the code:


Public Sub PrintRpt_Click()
On Error GoTo Error_Handler

DoCmd.OpenReport "rpt_Calculation", acViewPreview

Reports![rpt_Calculation]!rptRepeatKey = RepeatKey
Reports![rpt_Calculation]!rptWorkflowKey = WorkflowKey
Reports![rpt_Calculation]!rptUtilizationKey = UtilizationKey
Reports![rpt_Calculation]!rptIBkey = IBKey
Reports![rpt_Calculation]!rptBCkey = BCKey
Reports![rpt_Calculation]!rptCustSel = TempVars("CustName").Value


Exit_Procedure:
Exit Sub
Error_Handler:
MsgBox "Error # " & Err.Number & vbCrLf & Err.Description, vbExclamation, "Application Error"
Resume Exit_Procedure

End Sub

In debug, all the fields have correct values in them, but when I look at the report, they are all blank... All unbound text boxes in the form are named the same way as here - rptRepeatKey, etc...

Please help. Thanks SO SO much!

[boblarson]

You can't do it like that. You will have to have the text box on the report reference the form, not the other way around. So, one of the text boxes on the report would be:

=[Forms]![YourFormNameHere]![RepeatKey]

and then the next

=[Forms]![YourFormNameHere]![WorkFlowKey]

and so on.

[EffenNewGuy]

Hi, Bob! Thanks for the swift reply.

These fields are variables within the program, and not resident on any form (at the moment...), so the form reference does not work for me. I tried that already...

[boblarson]

Hi, Bob! Thanks for the swift reply.

These fields are variables within the program, and not resident on any form (at the moment...), so the form reference does not work for me. I tried that already...

You will need to then create a function for each so you can reference it:

In a Standard Module:

Function GetRepeatKey() As String ' or a different type if numeric
GetRepeatKey = RepeatKey
End Function

And then in the control source you can use

=GetRepeatKey()

[EffenNewGuy]

Okay, I get it. Seems very indirect, but okay ... Thanks so much.

[EffenNewGuy]

Tried the functions ... no work-y. Now I get the "#name?" back on the report where I expect to have a value.

[boblarson]

Tried the functions ... no work-y. Now I get the "#name?" back on the report where I expect to have a value.

Make sure that your text boxes do not have the same name as your field names. Also, did you remember to include the parens in the function call:

=GetRepeatKey()

And, make sure that the functions that pull the information back are in a STANDARD MODULE (not in a form, report or class module) and that the module name is not the same as any of the procedure names. The functions to return the values can't be on anything other than standard modules and that means that the variables also need to be within the same scope.

[EffenNewGuy]

Yes, all functions are Public and the control source reference in the property list includes the (). Now the report shows an empty field. Looks like when the function gets called, the variables get initialized... ?

Alternative strategy - I added these fields to the main form as hidden fields and reference them like I do the non-hidden fields from that form (for the report), but now the report shows #Error. I'm progressing!! hahaha! I'll take a closer look at all my fields and spellings. Don't know what else to do. It seems VERY strange to me that this is so difficult.

Thanks for all your help!

[EffenNewGuy]

The hidden fields worked (after syncing up field names)... although it seems like a very hokey way of doing this. I'll take working vs. non-working any day, though.

thanks again!