Variable in module to reference form
in [Forms]
|
Prev: Return the week # for a given date?
Next: RoundDown
From: Jordan on 1 Jul 2008 14:03 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.
From: Klatuu on 1 Jul 2008 16:01 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 -- Dave Hargis, Microsoft Access MVP "Jordan" wrote: > 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. > > > >
From: CN on 1 Jul 2008 17:15 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 "Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message news:F1C9C9A5-C71B-4E39-A9D9-5ED6BF2E72C6(a)microsoft.com... > 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 > > -- > Dave Hargis, Microsoft Access MVP > > > "Jordan" wrote: > >> 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. >> >> >> >> >
From: Klatuu on 1 Jul 2008 17:42 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. -- Dave Hargis, Microsoft Access MVP "CN" wrote: > 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 > > > > > "Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message > news:F1C9C9A5-C71B-4E39-A9D9-5ED6BF2E72C6(a)microsoft.com... > > 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 > > > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "Jordan" wrote: > > > >> 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. > >> > >> > >> > >> > > > > > >
From: CN on 2 Jul 2008 13:40
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. "Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message news:C74C347F-9742-4187-84B2-7783AFBF37DB(a)microsoft.com... > 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. > > -- > Dave Hargis, Microsoft Access MVP > > > "CN" wrote: > >> 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 >> >> >> >> >> "Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message >> news:F1C9C9A5-C71B-4E39-A9D9-5ED6BF2E72C6(a)microsoft.com... >> > 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 >> > >> > -- >> > Dave Hargis, Microsoft Access MVP >> > >> > >> > "Jordan" wrote: >> > >> >> 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. >> >> >> >> >> >> >> >> >> > >> >> >> >> > |