Search code examples
vbadatabasefunctionms-accessms-access-forms

Call a function with 2 Variables

I'm trying to create a dynamic function that's based off on what dropdown you interact with on a form. I need to pass the name of the dropdown to the function along with the form name when I call it.

Here's my current setup which only calls the funciton that passes the form name:

Private Sub KitchenMainCode_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Call Populate([Form])
End Sub

Populate(frm As Form)
'do stuff
End Function

I have 8 dropdowns which means I have to copy paste 8 batches of the same code which isn't ideal, Ideally I would like something like this:

 Private Sub KitchenMainCode_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
        Call Populate([Form], [control.name OR "KitchenMainCode"])
    End Sub
    
    Populate(frm As Form AND dropdown name as name)
    frm.name.value = xyz
    End Function

Even at the least, if I can pass a string Its easier to make 8 Mousedown events than 8 unique functions, which is nearly 50 lines (x8).

Every time I google this, its bringing lots of Excel stuff & for some reason the code that's used isn't compatible with access (probably my issue) but they're both VBA scripts.

Kind regards,

Solution

  • You can do like this:

    Private Sub KitchenMainCode_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Call Populate(Me, Me.ActiveControl, Button, Shift, X, Y)
End Sub


Private Sub Populate(frm As Form, ctl As Control, Button As Integer, Shift As Integer, X As Single, Y As Single)
    ' do stuff
End Function

    Or you could implement WithEvents. An example can be found in my project VBA.ModernTheme.

    To call a function:

    Private Sub KitchenMainCode_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Dim Result As Long  ' as the function returns.

    Result = Populate(Me, Me.ActiveControl, Button, Shift, X, Y)
End Sub


Private Function Populate(frm As Form, ctl As Control, Button As Integer, Shift As Integer, X As Single, Y As Single) As Long
    ' do stuff

    Populate = SomeResultValue
End Function