Variable in module to reference form

  • Thread starter Thread starter Jordan
  • Start date Start date
J

Jordan

I have a routine in a module that I use to pass and order number and set the
focus to a form, change the contents of a control, then fire its after
update event to update the form. Normally I call the form and controls
specifically, but I want to change the form name to a variable so I can use
the same routine for multiple forms. Here is the code I use now

Function UpdateFormOrderNumber(intOrderNumber as Integer)

Forms!frmOrders.SetFocus
Forms!frmOrders!cmbCurrent.SetFocus
Forms!frmOrders!cmbCurrent = intOrderNumber
Call Form_frmTuningData.cmbCurrent_AfterUpdate

End Function

I want to change it so I can also pass the form name as a variable so I can
use this routine to update any form I have.
 
First, a form will not accept the focus if there are any controls on the form
that can accept the focus.
Second, form events are private subs and cannot be called externally.
You can reference a form in a function or sub in one of two ways. You can
pass the name of the form as text and reference it as

UpdateFormOrderNumber(Me.Name, intOrderNumber)

Function UpdateFormOrderNumber(FormName As String, intOrderNumber as Integer)
Forms(FormName)!!cmbCurrent = intOrderNumber

or you can pass the form as a form object

UpdateFormOrderNumber(Me, intOrderNumber)

Function UpdateFormOrderNumber(frm As Form, intOrderNumber as Integer)
frm!cmbCurrent = intOrderNumber
 
Klatuu,

Forms!FormName.Setfocus is valid but I don't know if it is nesssary to set
the focus to the form before the control the OP wants. You can also use the
Forms() function to set the Form object with the name the OP is trying to
pass.

Jordan, Do this:

Function UpdateFormOrderNumber(strFrm as String, intOrderNumber as Integer)
Dim MyForm as Form

'Take String and set MyForm to that form name
Set Myform = Forms(strFrm)

' Don't know if you need this line, but does not hurt
MyForm.SetFocus
MyForm.cmbCurrent.SetFocus
MyForm.cmbCurrent = intOrderNumber

' To call the After Update subroutine on a form you need to change
' the subroutine to Public instead of Private
Call MyForm.cmbCurrent_AfterUpdate

End Function

Pass the function the name of the form you want to update and the integer
you want to update the control with and you should be all set. Keep in mind
that the form needs to be open. To check if the form is open add this line
at the beginning as well:

Dim Result as Boolean
Result = FormIsLoaded(strFrm)
If Result = False Then
DoCmd.OpenForm strFrm
End If

And here is a function you can put in a module to check if the form is open

Public Function FormIsLoaded(MyFormName As String) As Boolean
'
' Determines if a form is loaded.
'
Dim I As Integer
On Error GoTo HandleErr
FormIsLoaded = False
For I = 0 To Forms.Count - 1
If Forms(I).FormName = MyFormName Then
FormIsLoaded = True
Exit Function ' Quit function once form has been found.
End If
Next I
ExitHere:
Exit Function

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Select
End Function
 
Copied directly from the SetFocus description in VBA Help:
"If a form contains controls for which the Enabled property is set to True,
you can't move the focus to the form itself. You can only move the focus to
controls on the form. In this case, if you try to use SetFocus to move the
focus to a form, the focus is set to the control on the form that last
received the focus."

So, as I said originally, if the form has a control that can accept the
focus, you can't set the focus to it. It will not error, but what is the
point of issuing a command that will do nothing?

You are basically repeating one of the two methods I suggested, but your
nominclature is not correct. Forms() is not a function. Forms is a
reference to the forms collection, so what goes in the parenthises is an
index to a form in the collection. The forms collection contains only open
forms. It can either be a number or it can be the name of the form.

Although it works, I would not recommend making a form event public. It is
within the realm of possibility that if there are other controls on other
forms with the same name, Access could be confused. Not likely, but
possible. It would be better to move the code to a public sub in a standard
module.

The other issue is the OP wants to be able to use this universally. That
being the case, it would be difficult to call a sub using a variable. If it
were a function, you could use the Eval function, but since it is a sub, it
will error out. If the code were in a standard module as a Function, you
could use the Eval function to execute the code. You could then have the
AfterUpdate event call the function rather than duplicate the code.
 
I think you were over complicating the OP's situation. I replied because
you were having the OP use Me.Name for your functions when the OP said that
he wanted to pass another forms name in string form to the function because
this routine was going to be used on some other forms. That said, Me will
not work. Nor does Access allow you to have two controls on the same form
with the same name.

I suppose I could find those points in the VBA help file and post them as
well, but I would not want you to think I was being rude.
 
That is not what I understood the OP to be saying. He said he wanted to be
able to use the routine for other forms.
My response to you was not intended to be rude at all. It was to correct a
mistatement regarding Form() and I preceived you had a misunderstanding about
how a SetFocus applied to a form object.

Of course you can't have two controls on the same for with the same name,
but the same control name can be used on more than one form. The danger
would be if someone did not fully qualify the control reference and only
referenced the control name, it might be possible for Access to grab the
control from the wrong form if a control's event sub were made public. But,
even though that would be unlikely, putting the code in a standard module
would be a better practice.
 
Back
Top