MS Access 2007 Public Function for button used on various forms

Question

I know just enough programming to get myself in serious trouble. I'm building a database where I want to run a function that does the same thing each time. The only problem is the button appears on various forms. I know I need to use a public function. I managed to get the function to work as a private sub on one form but when I moved it to a module and made it into a public function, it gave me an error. It states that I have entered an expression that has no value. I suspect the problem is the link between the first form (Various Forms) and the second form (Log-Memo Line Form).

In each form, the common link is the HL# (The Transaction ID#). HLCtrl refers to a text box control on one form (the various parent forms). [HL#] refers to a text box control in the [Log-Memo Line] Form. My other issue is that the HLCtrl control has different names in different forms. Should I rename the controls to be the same name?

This is the code

Public Function Memo_Line()
On Error GoTo Memo_Line_Err

    Call saver
    DoCmd.OpenForm "Log-Memo Line", acNormal, "", "[HL#]=" & "'" & HLCtrl & "'", , acNormal
    Call ClipBoard_SetData([Forms]![Log-Memo Line]![Memo])
    MsgBox ([Form_Log-Memo Line].[Memo] & "---- copied to Clipboard."), vbInformation, "Clipboard Details"
    DoCmd.Close acForm, "Log-Memo Line"

Memo_Line_Exit:
    Exit Function

Memo_Line_Err:
    MsgBox Error$
    Resume Memo_Line_Exit

End Function

Scotch Scotch · Accepted Answer · 2013-05-15T19:53:43

I think it's in this line DoCmd.OpenForm "Log-Memo Line", acNormal, "", "[HL#]=" & "'" & HLCtrl & "'", , acNormal

You're opening the form and filtering it according to HL# = HLCtrl. Assuming HLctrl is a control on one of your forms (the form needs to be open and have a value in there for this to work), I would either pass it as a parameter to your function or if you're always going to be calling this function when the form with hlctrl is open, you could refer to hlctrl by it's whole name : [Forms]![yourFormThatYouWantToSelectaHLNumber]![hlctrl]

If you called this code from the form that contained the control HLctrl, you wouldn't need to use the full name, and you could just say HLCtrl or Me.HLctrl. Since you're calling it from a public function located outside of the form object, you need to tell it which form to look for the HLCTRL

Like DoCmd.OpenForm "Log-Memo Line", acNormal, "", "[HL#]=" & "'" & [Forms]![yourFormName]![HLCtrl] & "'", , acNormal

Also, if it's a number, you probably don't need to wrap it in single quotes.

MS Access 2007 Public Function for button used on various forms

1 Answers