Send variables from modal to main form

[grahamvb]

Hello Access Programmers,

I have a form that has a button that opens a modal form on top allowing the user to perform a task, when done the modal form closes, returning the user to the underlying form to continue their work.

That modal form creates some information that I would like to transmit to the underlying form.

In this thread, http://www.access-programmers.co.uk/forums/showthread.php?t=198302 bob suggests a public function on the main form called by a line on the modal form. Here is what I created to accomplish this.

Main Form

Public Sub SetCombos(varEID As Variant, varCID As Variant)
 
    Debug.Print "varEID: " & varEID
    Debug.Print "varCID: " & varCID
 
End Sub

Modal (popup) Form

Private Sub btnSave_Click()
 
    DoCmd.RunCommand acCmdSaveRecord
 
[COLOR=green]' call public procedure on main form[/COLOR]
    varEID = Me.EID
    varCID = Me.CID
    [COLOR=red]Forms!frmMain.SetCombos(varEID, varCID)[/COLOR] [COLOR=green]' Access will not accept this line[/COLOR]
[COLOR=green]                                          ' (Compile error: Expected: =)[/COLOR]
 
    DoCmd.Close acForm, "frmPopup", acSaveNo
 
End Sub

Access does not like the red line. Where am I going wrong?
Thanks for looking at this.

 

[TJPoorman]

Try with no parenthesis.

Forms!frmMain.SetCombos varEID, varCID

 

[Uncle Gizmo]

public sub won't work it has to be a function..

 

[ChrisO]

Actually, public Subs are fine.

Chris.

 

[Uncle Gizmo]

Are you sure Chris? I'm on a Chromebook and can't check at the moment but I'm pretty sure you have to change subs to functions?

 

[ChrisO]

Yep, their fine.

Public Subs could not be called directly from the property sheet (well they could but they would be called more than once) so they had to be Functions for that. (I think that was fixed after XP/Access2003).

But Subs are okay for the use they are being used for here.

Chris.

 

[grahamvb]

Thank you TJPoorman.

This works flawlessly passing variables from the modal form to the main form.

Main Form:

Public Sub SetCombos(varEID As Variant, varCID As Variant)
 
    Debug.Print "varEID: " & varEID
    Debug.Print "varCID: " & varCID
 
End Sub

Modal Form:

Private Sub btnSave_Click()
 
    DoCmd.RunCommand acCmdSaveRecord
 
' call public procedure on main form
    varEID = Me.EID
    varCID = Me.CID
    Forms!frmMain.SetCombos varEID, varCID
 
    DoCmd.Close acForm, "frmPopup", acSaveNo
 
End Sub

Thanks to all of you who looked at this.

 

[Uncle Gizmo]

Well that's a good save for me because it was something I was going to blog about!

The essence of the blog was going to be to tell people to always develop with functions and not subroutines because subroutines had certain limitations!

One being not able to call them as I incorrectly indicated in this post!

 

[Uncle Gizmo]

This was bothering me, my assumption that a sub procedure could not be called, only a Function.

I knew there was an issue related to sub or function running, and I happened to find the following which I now see is what led me to this incorrect belief.

Basically you cannot call a sub procedure from a macro, you have to call a function. If you want to call a sub procedure you can by wrapping it in a function.

You can test this yourself by copying the provided code into a test form in your access database. Create a command button that calls a macro and use the macro's RunCode Action to run the function fX() Don't forget the brackets!

Public Function fX()
Call fY
End Function

Public Sub fY()
MsgBox " >>> " & " TEST"
End Sub

Now change your macro so instead of calling the function fX, it calls the Sub procedure fY and it won't work.

This code will also run if you put it in a module.

There may be a problem if you try and run the macro from a menu command on a form or report. I haven't tested this, it might be worth someone having a look at and reporting on because the wording on the Microsoft site isn't very clear.

See more here in the Microsoft help:-
RunCode Macro Action
https://msdn.microsoft.com/en-us/library/office/ff834373(v=office.15).aspx

One final thing; in the help the "Tip" explains how to wrap a sub in a function. In other words in a roundabout way, it is saying that you can't run a sub from a macro.