Help with VBA (1 Viewer)

[aftershokk]

I have a converted macro that runs a query to pull out 1 record with a name that I want to use as the caption of a report for outputting to .pdf.

macro 2 below converted to VBA.

I want to take this result and add to the caption of my report that converts to .pdf below named email macro. I guess a need to assign the result of macro 2 to a variable and then populate into the report caption property but not sure how to do this? thanks

Function Macro2()
On Error GoTo Macro2_Err

DoCmd.OpenQuery "selected_specialist", acViewNormal, acEdit


Macro2_Exit:
Exit Function

Macro2_Err:
MsgBox Error$
Resume Macro2_Exit

End Function

----------------------------------------------------------------
Function email_macro()
On Error GoTo email_macro_Err

DoCmd.OpenReport "Specialist Attendance Report", acViewNormal, "", "", acNormal


email_macro_Exit:
Exit Function

email_macro_Err:
MsgBox Error$
Resume email_macro_Exit

End Function

 

[pbaldy]

You don't need the macro or VBA at all. In a textbox on your report:

=DLookup("FieldName", "selected_specialist")

 

[aftershokk]

I tried in the caption box on the report because that is where the file name comes form when exporting and it did not work?

=DLookup("PHYSICIAN", "selected_specialist")


I just lists this formula in the name of the report??

 

[pbaldy]

It will work in a textbox; I don't think it will work as a caption. You'd have to set the caption to the result of that in the Open event of the report.

 

[aftershokk]

no luck with the text box either, I will keep plugging away

 

[pbaldy]

Just tested it and it worked as expected. Double check the name of the field and the query. If that's not it, can you post the db?

 

[aftershokk]

hi, maybe the attached screenshot helps?

 

[pbaldy]

As noted, you can try code in the open event of the report:

Me.Caption = DLookup("PHYSICIAN", "selected_specialist")