Call a function with 2 Variables

Question

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,

Gustav · Accepted Answer · 2022-03-29T08:27:09.410

1

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

edited Mar 29 '22 at 08:27

answered Mar 28 '22 at 17:13

Gustav

53,498
7
29
55

I seem to be getting a compile error with this, "Expected list separator or )". I've played around it and cannot get this to work. I also would call it with this 'Call Populate([Form])' but I cannot get the code to save first, its just red. – Tom TK Mar 29 '22 at 07:50
1

Yes, remains from copy-paste, sorry. Corrected. – Gustav Mar 29 '22 at 07:57
'Code Dropdowns 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) MsgBox (ctl) End Sub – Tom TK Mar 29 '22 at 08:11
Above is the exact code copied from my DB and Its still not working unfortunately, I appreciate the help though, Also done msgbox(ctl.name) & msgbox("string") – Tom TK Mar 29 '22 at 08:12
1

Can't tell. Works great here for a textbox. – Gustav Mar 29 '22 at 08:16
My apologies, I don't know what I did but copying and pasting it back in works. I made no changes. Anyway I appreciate the help! How would I call this if it was in a function? Call FunctionName([Form], [Ctl])? – Tom TK Mar 29 '22 at 08:22
1

See extended answer, please. – Gustav Mar 29 '22 at 08:27

Call a function with 2 Variables

1 Answers1