Link to home
Start Free TrialLog in
Avatar of dennis dickson
dennis dickson

asked on

Ms Access set Form name using a variable

Below is my code:
Public Function getFormName(frmName)
Dim frmDispatch As Form
Set frmDispatch = Forms!dispatchBoard!Board1.Form              '' This line works fine"    "Please see the problem below"
getFormName = frmDispatch
End Function
The Problem:
In the line "set frmDispatch". I don't know the subform (Board1 is a subform example only). The subForm name  i need to process is passed to the function (frmName is the variable passed to the function)
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

If i understood clearly you want to set frmDispatch  to the subform
if this is true then the easiest way is (if not)
open the subformy you want in design view
hit ALT+F11 to enter the VBE (the code editor)...just close the VBE...now your subform has code behind and can referenced easily like
Set frmDispatch =Form_NameOfYourSubform

Open in new window

If your subform has spaces in its name (i don't like them) you should refer it as
Set frmDispatch =[Form_Name Of Your Subform]

Open in new window

It could be:

Set frmDispatch = Forms!dispatchBoard(frmName).Form  

Open in new window

but only if the subform control  has the same name as the subform it holds.
If that is not the case, you must loop the subform controls and, for each, check the value of the property SourceObject to locate which control holds the subform named frmName.
Ok, lets clarify some terminology, names, and objects references first:
Your function:
Public Function getFormName(frmName)

Open in new window

indicates that the variant variable frmName is supplied as an input to your function that returns a variant.  

Your function code indicates that we know you want the function to return a form object, but since you don't reference frmName in the function code, we don't knowing if the input is a string or an object, which creates a situation where I would need to create code to handle either one.  

Also, we don't know if your function is in the form module that has the subform, or if it is in another module of some kind, which would make it difficult to find the main form with the subform control that has frmName as its source object.  If we have to iterate through controls or reference forms and controls, how do we do that if we don't know what's going on.

Please provide a little more detail.  The solution requires it.
The only way I know to retrieve a form based on its name is trough the forms collection. This one hold all the forms currently open.
Sample code:
Public Sub GetFormByName(ByVal Name As String) As Access.Form
On Error Goto Error
    Set GetFormByName = Forms(Name)
Exit Sub
Error:
    Debug.Print "The form " & name & " is not open or does not exist."
    Err.Raise err.number, err.Source, err.Description, err.HelpFile, err.HelpContext
End Sub

Open in new window


Also, your function's name is confusing, or should be refined. Actually, it make thing it retrieve the form's name given as argument wich is a non sens (Give me the name of the form named "BlackCat").
Wouldn't GetFormByName be a better choice ?

As for you code, you might misunderstand something:
Forms!dispatchBoard!Board1.Form
It actually set a reference to the form held in the control named Board1 wich is itself held in the form named dispatchBoard, wich is itself held in the Forms collection.
So Board1 is not the name of a form (as you think), but the name of a control.
If you need the name of the form, that's Forms!dispatchBoard!Board1.Form.Name that you need.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.