Form instance using variable for name

  • Thread starter Thread starter Larry
  • Start date Start date
L

Larry

I have a subroutine that investigates controls on a form, returning
information about those controls. The subroutine accepts the name of
the form, which I then assign to a form object using SET frm =
FORMS(strFormName), but of course, if the form isn't open, this doesn't
work.

I need to be able to investigate an instance of the form, whether it's
open or not, so I can search each control. If it's open, I can't
disturb the form, that's why I want a new instance.

I have tried using the AllForms collection of CurrentProject, but I
can't assign the object that returns to a Form variable. I have also
tried using the Documents collection, with the same results of not
being able to convert it to a form variable.

I could probably check to see if the form is loaded, and use the
statement above, then if it's not open, open it hidden then use the
statement above, but that seems cumbersome. Is there any better way to
do it?

Thanks,
Larry

BTW, this is Access 2003.
 
Ok, well my thought of a work around, doesn't work because some of the
forms have subforms, which I recursively open. You can't open the
subform if the parent form is already opened.

I was hoping to close the form, after assigning it to the form
variable, but the controls can't be accessed when the form isn't open.

argh!
 
Could you just open it if necessary:

Dim bCloseItAgain As Boolean

If Not CurrentProject.AllForms(strFormName).IsLoaded
DoCmd.OpenForm strFormName, WindowMode:=acHidden
bCloseItAgain = True
End If

'do something useful.

If bCloseItAgain Then
DoCmd.Close acForm, strFormName
End If
 
I tried something similar (hince my follow up comment). Here's the code
I tried:
If fIsLoaded(strFormName) Then
Set frm = Forms(strFormName)
Else
DoCmd.OpenForm strFormName, acDesign, , , acFormReadOnly,
acHidden
Set frm = Forms(strFormName)
blnClose = True
End If

I tried to open it in Design mode (so the open event code associated
with the forms wouldn't fire), but then I got the message about the
form being opened already in design mode.
 
Allen,

I just realized (after reading my post) that my "hince" comment sounded
a little rude. I didn't mean it to be though! I also just saw your post
was at the same time as my follow-up, so you probably didn't see that
before you posted.
 
Allen,

I just realized (after reading my post) that my "hince" comment sounded
a little rude. I didn't mean it to be though! I also just saw your post
was at the same time as my follow-up, so you probably didn't see that
before you posted.
 
If I open the macro in form view (the same code I posted without the
"acDesign"), then the underlying code fires (which is not a good thing
in this case). The subforms refer to the parent form in their open even
and if they are opened alone, that causes an error to be thrown.
 
Your other option would be to iterate through the Forms collections, looping
through the controls of each one to find any of type acSubform, and testing
its SourceObject if it matches strFormName.

The code would need to be recursive to handle subforms at any nested depth.

Long way 'round for a shortcut.
 
I'm already doing that, but the Forms collection are only open forms.

Here is the entire subroutine I have so far. It works for the "main"
form I need to process, but it doesn't process it's subforms (yet).

Public Sub FindRequired(strFormName As String)
'
' Find fields that should be required, based on the label for each
field on the form starting
' with an asterisk.
'
Dim frm As Form

Dim ctl As Control

Dim lbl As Label

Dim blnClose As Boolean

' Use static variables to capture the label and controlsource
names, to be able to make
' this work across recursive calls (for sub forms)
Static strControlList As String, _
strLabelList As String

' If this is the main form, reset the static variables
If strFormName = "frmPersonnel" Then
strControlList = ""
strLabelList = ""
End If

' If the form is loaded, assign it to the form variable
If fIsLoaded(strFormName) Then
Set frm = Forms(strFormName)
Else
' ----- DOESN'T WORK!
' The form is not loaded, so load it then assign it to the form
' DoCmd.OpenForm strFormName, , , , acFormReadOnly, acHidden
' Set frm = Forms(strFormName)
' blnClose = True
End If

' If there is not a form for some reason, exit the subroutine
If frm Is Nothing Then
Exit Sub
End If

' Loop on all the controls on the form
For Each ctl In frm.Controls
' If this is a TextBox or ComboBox process it
If TypeOf ctl Is TextBox Or _
TypeOf ctl Is ComboBox Then
' If it's visible and it has a control attached to it (the
label) then process it
If ctl.Visible And _
ctl.Controls.Count > 0 Then
' The label will be the first control in the
collection, so grab it
Set lbl = ctl.Controls(0)
' If the first character in the labels caption is an
asterisk, it's a required
' field, so capture the label caption (sans asterisk)
and control source
If Left(lbl.Caption, 1) = "*" Then
strControlList = strControlList & ";" &
ctl.ControlSource
strLabelList = strLabelList & ";" &
Mid(lbl.Caption, 2)
End If
End If
ElseIf TypeOf ctl Is SubForm Then
' This is a subform control, so recusively call this
subroutine, passing the
' subform's form name
Call FindRequired(ctl.Form.Name)
End If
Next ctl

' Control processing is finished, if this is the main form, process
what was found
If strFormName = "frmPersonnel" Then
' Strip off the leading semi-colon from the lists
strControlList = Mid(strControlList, 2)
strLabelList = Mid(strLabelList, 2)
Debug.Print strLabelList
Debug.Print strControlList
End If

' If this form was opened during the process, close it
If blnClose Then
DoCmd.Close acForm, strFormName
End If

Set frm = Nothing
End Sub
 
If you find a subform loaded, you can reference it as:
Forms![Form1].Form.[Form2].Form.[Form3].Form
where Form1 is the main form, Form2 is the first subform, and Form3 is the
nested subform inside that.

Alternatively, you might pass a reference to the form to your procedure
instead of the name of the form, e.g.:

Function GetMyForm(strFormName As String, bCloseItAgain As Boolean) As Form
'all your code to find the thing here, open it if necessary, and return
the reference:
GetMyForm = Forms![Form1].Form.[Form2].Form.[Form3].Form
End Sub
Function DoStuff(strFormName)
Dim frm As Form
Dim ctl As Control
Dim bCloseItAgain as Boolean

Set frm = GetMyFOrm(strFormName, bCloseItAgain)
With frm
Debug.Print frm.Name
For Each ctl In frm.Controls
Debug.Print, ctl.Name
Next
End With

If bCloseItAgain Then
DoCmd.Close acForm, strFormName
End If
End Sub

I'm not really clear on the reasons you are trying to do this while things
are open and being used, but hopefully that helps.
 
Thanks, I'll see if I can put this into action.

What I'm doing is finding users in the database that are missing
"required" data. I'm using the main input form to tell me which fields
are required. The form tells the users what's required by having a
leading asterisk in the label for the field. When the administrators of
the data run this code, the form will probably be closed, but if it's
not, I want to be able to handle that situation too.
 
Okay.

If the field really is required at the engine level, the record won't save
if left blank, of course.

If you want a warning when the field is left blank, Form_BeforeUpdate would
do that.

If you already have the asterisk beside your quazi-required fields, in
Form_BeforeUpdate you could iterate through the text boxes and look at:
Instr(ctl.Controls(0).Caption, "*")

If you record the name of the user who last edited the record (in a field
for the purpose, written silently in Form_BeforeUpdate), you could later
create a query testing what fields are null and how last edited the record.

I guess those things are all different from what you need.
 
This worked.

I added a second optional parameter to the subroutine which is a form
variable. Then I uncommented the code that didn't work and open the
form in design mode, if it's not open. But now, I check that optional
paramter first, and if a form is passed in, I just use that form
instead of trying to set it in the code.
If frm Is Nothing Then
' If the form is loaded, assign it to the form variable
If fIsLoaded(strFormName) Then
Set frm = Forms(strFormName)
Else
' The form is not loaded, so load it then assign it to the
form
DoCmd.OpenForm strFormName, acDesign, , , acFormReadOnly,
acHidden
Set frm = Forms(strFormName)
blnClose = True
End If
End If


On the line where I recursively call the subroutine, I simply added the
form as the second parameter in the call:
Call FindRequired(ctl.Form.Name, ctl.Form)
 
Back
Top