Right click menu pass variable to public function

[jabarlee]

Hail to the community!
I've encountered the following problem:

I have a report that I need to export to pdf with a somehow custom procedure. Ideally, using a right click menu command.
In order to test my export procedure I created a test button with the following code that works fine (In case you find any mistakes, please do comment):

Private Sub Command25_Click()
On Error GoTo Report_klikklak_Error
    Dim PdfPath As String
    PdfPath = DLookup("MedPrescComment", "tbl_TEMP_MedPrComm", "ID = 2") & "\" 'This value is set in the frm_MedsEdit
    Dim fdialog As Office.FileDialog
    Set fdialog = Application.FileDialog(msoFileDialogSaveAs)
    With fdialog
        .AllowMultiSelect = False
        .Title = "Choose the export location and filename"
        .InitialView = msoFileDialogViewList
        .InitialFileName = PdfPath & Pt_FullName & ".pdf"  ' Use the name of the patient as filename
        If .Show = True Then
            'check if the file exists and overwite or exit sub
            If Dir(.SelectedItems(1)) <> "" Then
                If MsgBox("File Exists, Overwrite?", vbQuestion + vbYesNo, "Overwrite?") = vbYes Then
                    DoCmd.OutputTo acOutputReport, "rpt_MedsPresc", acFormatPDF, .SelectedItems(1), False, "", , acExportQualityPrint
                Else
                    Exit Sub
                End If
            Else
                DoCmd.OutputTo acOutputReport, "rpt_MedsPresc", acFormatPDF, .SelectedItems(1), False, "", , acExportQualityPrint
            End If
        End If
    End With
Exit Sub
    
Report_klikklak_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Report_klikklak, line " & Erl & "."
    
Set fdialog = Nothing
End Sub

Now, I try to create my custom right click menu. Since it has to do only with this report, I've put it in the "On Open" event. And I have created a test public sub - in a module - to test the passing of parameters:

Private Sub Report_Open(Cancel As Integer)

    On Error GoTo Report_Open_Error

    
     ' Create the right-click menu
    Dim NewMenuBar As Office.CommandBar
    Dim PdfPath As String
    PdfPath = DLookup("MedPrescComment", "tbl_TEMP_MedPrComm", "ID = 2") & "\" 'This value is set in the frm_MedsEdit

    On Error Resume Next
    Application.CommandBars("PrescBar").Delete
    On Error GoTo 0
    
    Set NewMenuBar = CommandBars.Add("PrescBar", 5, False, True)
    'add two native controls
    NewMenuBar.Controls.Add 1, 15948, , , True
    NewMenuBar.Controls.Add 1, 247, , , True
    'add custom export-to-pdf function
    With NewMenuBar.Controls.Add(1)
        .Caption = "Export to PDF"
        .FaceId = 3
        .Parameter = PdfPath & Pt_FullName & ".pdf" ' Define the filename's path as parameter to pass to the function ***It cannot find Pt_FullName
        .OnAction = "TicToc" ' call the -test- public function "TicToc with the above parameter
    End With
    
    
    On Error GoTo 0
    Exit Sub
Report_Open_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Report_Open, line " & Erl & "."

End Sub

The test sub:

Public Sub TicToc()
MsgBox CommandBars.ActionControl.Parameter
End Sub

It would work ok, except that the menu creation routine cannot find the "Pt_FullName" variable (that is a field of the bound table). I also tried Me.Pt_FullName , nothing. The same variable works ok in the test button. Also, the .Parameter passes correctly the value to the public function (when I try a string or anything else)

Any ideas ?

[CJ_London]

the form or report will have no knowledge of the field until the load event, so suggest try the load event

[Welshgasman]

See https://support.microsoft.com/en-us/...86553682f9#bm5

When you open a report that is based on a query, Access triggers the Open event for the report before it runs the underlying query. As a result, you can set the criteria for the report by using a macro or event procedure that responds to the Open event. For example, the macro or event procedure can open a custom dialog box in which you enter report criteria.

[jabarlee]

Thank you both for your answers.

@CJ_London: Originally I had the procedure running at the load event, but the same thing happens, it is still unaware of the field
@Welshgasman: I had a small clue about that, in the open event I run a small public function to check user permissions (I haven't included it in my example above)

[jabarlee]

Coming back to this, it's driving me mad:

I've stripped down all the code, now I just have this:

Private Sub Report_Load()

    On Error GoTo Report_Load_Error
    Dim balbal As String"
    balbal = Me.txt_Pt_FullName 'This actually returns the correct string. The textbox "txt_Pt_FullName" has as data source the "Pt_FullName" field of the underlying query
    Dim balbal2 As String
    balbal2 = Me.Pt_FullName ' This returns Null. It gives an error that it cannot find the field specified
    Dim frmName As String

    On Error GoTo 0
    Exit Sub

Report_Load_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Report_Load, line " & Erl & "."
    
End Sub

I have no idea why this is happening. My other reports do not have this issue

[Welshgasman]

Well, do you have a control called pt_fullname?
I do not believe field names are valid to refer to? Might be wrong, just I have never tried to use them like that.

Easy to test, try another field and also try the control that has that as it's source in it's place?

[darkwind]

I do not believe field names are valid to refer to?

This is correct - it's not valid to refer to a field value directly, even though the environment kinda makes you think it is.

If you want to get the underlying value directly for some reason, you'll need to get it via a recordset. Something like:

balbal2 = Me.Form.Recordset("Pt_FullName") 

I don't understand why you would want to do this, though. Me.txt_Pt_FullName is bound to pt_FullName, so the two values should be the same.

Just a small comment: there's no reason to include OnError Goto 0 right before Exit Sub. Error handling is only valid within the scope of the procedure it is set in and, to some extent, the procedures it calls. So even if Report_Load() is called from SomeFunction, the error handling you set in Report_Load won't apply to SomeFunction.

[jabarlee]

@Welshgasman: Well, I have been referring to the field names like that since I started this project (it's my first and only) with no problems so far. I might not have been clear before, the same method does not produce an error with my other reports/forms. Besides that, the auto-completion suggestions form Access after "Me." always include the field names, so I never guessed that it might not be proper

@darkwind: this was done just for testing purposes, I wanted to check what works and what not. Please, have in mind that in various similar reports (of the same project) the "Me.Pt_FullName" referral to to the [Pt_FullName] field of the bound query worked fine
* I guess you're right about the error handling, it seems I messed that up

[Welshgasman]

No. The auto completion refers to controls. A lot of times they can be the same as the field names that are their source. Especially if created with the wizard.

[davegri]

Worth a try:
balbal2 = [Pt_FullName]

This will refer to the field in the report's recordsource query (or table if so be) rather than the report control.

[jabarlee]

For arguments sake, I'm posting two screenshots that show that the Me. auto-completion shows the underlying fields too (I don't have controls with those names, I always put prefixes to controls (e.g. txt_ ,cmb_, lbl_ etc)
Anyway, that's not the matter because I've tried every one of your advice and nothing that should work, actually does (yes, I found the workaround of referring to the value of the textbox instead of the value of the field, but that's not how it should be)
I maybe have a corrupt report or something? I'll build it from scratch and report back

[Welshgasman]

OK, my sincere apologies. I have just tried it and get the same result, though TBH I have only ever referred to controls with Me ?
I do not use Access much anymore, but if I did, that would likely not change.

Seems I cannot offer anything to help here, so I will step away.

[jabarlee]

Dear Welshgasman, thank you for even taking the time to deal with my problems, it is an ever-learning world we live in!

Even though I tried to create that report from scratch, it still doesn't wok this way. I don't have the courage to investigate it anymore, since I have a workaround

[moke123]

take a look here https://nolongerset.com/right-click-menus-in-access/

scroll down to the part about using .parent