Subform with VBA

[gg80]

I am using access 2007. I am trying to run a module using data on a subform. I am using a module because several other forms require the same code. I can’t make it work and am testing by just trying to get a simple msgbox the showing the value of just one control on the subform



My "simple" test is:

On the master form, I click a button and code is:
Call ModuleName(“Forms!FormName.SubformName.form”) (I also put “form” in front of subform’s name)

On the subform code is:
Public Sub ModuleName(EX)
With EX
Msgbox .SubformControlName
End with
End sub

I am missing something, but don’t know what. Any help much appreciated.

[ranman256]

youve mixed a lot of things here....
putting things in quotes makes them strings, NOT controls: ModuleName(“Forms!FormName.SubformName.form”)
you may want:
Call ModuleName(Forms!FormName.SubformName.form)

and you may not want that,
do you want the name of the subform IN the master form: 'SubformName'
or form name alone thats used as the subform 'frmMySubform' (not what its called in master form: 'SubformName' )
Call ModuleName(Forms!FormName.SubformName.form)

Public Sub ModuleName(EX)
  msgbox ex.SourceObject
End sub

or dont use the module at all and just call the object using the full path:
msgbox Forms!frmClients!subFrm.Form.SourceObject

[ssanfu]

You cannot "Call" a module. A module is a container that holds subroutines/functions.

If you have a button on a main form and you want to display a message box with the value of a control on a sub form, you would use something like

Public Sub ButtonName1_Click()
    MsgBox Me!Subform1.Form!ControlName
End Sub

where "ME!" refers to the main form, "Subform1" refers to the name of the sub form container (the sub form control name) and "ControlName" is the name of the control that you want to see the value.

or

Sub Test_Click()  '<<-- button name
    Call GetValue("frmJobs", "sfPay", "JobID")   '(main form name, sub form name, control name)
End Sub


Sub GetValue(MF As String, SF As String, CN As String)
    Dim sForm As String

    sForm = "Forms!" & MF & "!" & SF & ".Form!" & CN
    ' Debug.Print sForm
    MsgBox Eval(sForm)
End Sub

See Refer to Form and Subform properties and controls

[gg80]

This is my third try at posting a reply-not sure what doing wrong. Thanks very much-works!

[gg80]

My 2nd try at replying. Thanks much. In this case, I much want to use a module for various reasons.. Other reply works, but I will try this one too.

[gg80]

I now think that my original replies have disappeared and the two that I replaced them with appear to be attached to the other offered solution. Not sure what is happening. In case you didn't get a reply-Thanks again.

Your solution works fine. Note that I am using a module because the same code (much more detail than example of msgbox) will be used by other forms, so a module is best way to go.

Much appreciated

[ssanfu]

I was not saying don't use standard modules, I was saying you do not and cannot "CALL" a module, nor should you use the module name in anything.

In my example, "Sub GetValue" can be put in a standard module because there is no specific reference to a form using the "Me" keyword.

Happy the code helped you.


Good luck with your project..........