Link to home
Start Free TrialLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

pass form name and control name to procedure

I have a calendar procedure that works well.  However, since I use it in so many forms, I was trying to get it to be one called procedure passing only the form name and control name to it.  HOwever, I keep getting a "The control number you speciifed is greater than teh number of controls". which I have no ide means. I am calling the procedure by using the below.  Also, can this even be done?

Private Sub HRStartDate_Click()
    Call ShowCalendar(Me, Me.HRStartDate)
End Sub

The actual procedure is:

Public Sub ShowCalendar(frmName As Form, ctlName As Control)
'Used for the MonthCalendar control
Dim blRet As Boolean
Dim dtStart As Date
Dim dtEnd As Date
Dim ctl As Control

'First check to see if the control is locked, if it is, then cannot show
'calendar as field should not be changed.

If frmName.Controls(ctlName).Locked = True Then
    Exit Sub
End If

dtStart = Nz(frmName.ctlName.value, 0)
dtEnd = 0

blRet = ShowMonthCalendar(mc, dtStart, dtEnd)
If blRet = True Then
    frmName.ctlName = dtStart
Else
' Add any message here if you want to
' inform the user that no date was selected
End If
End Sub

PS:  This is code from, I think Allen Browne's site, it works as he originally created, I am just trying to ttake it a step further.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

what changes did you make ?
which line is raising the error ?
If frmName.Controls(ctlName).Locked = True Then
ctlName is a control, not a control name. Either change the type or the code.
try this:

Public Sub ShowCalendar(frmName As Form, ctlName As Control)
'Used for the MonthCalendar control
Dim blRet As Boolean
Dim dtStart As Date
Dim dtEnd As Date
Dim ctl As Control

'First check to see if the control is locked, if it is, then cannot show
'calendar as field should not be changed.

If frmName.Controls(ctlName.Name).Locked = True Then
    Exit Sub
End If

dtStart = Nz(frmName.ctlName.value, 0)
dtEnd = 0

blRet = ShowMonthCalendar(mc, dtStart, dtEnd)
If blRet = True Then
    frmName.ctlName = dtStart
Else
' Add any message here if you want to
' inform the user that no date was selected
End If
End Sub
I missed cluskitt's post - which explains the reason behind the code change I posted.
Avatar of Sandra Smith

ASKER

It errors out at the   If frmName.Controls(ctlName).Locked = True Then     line.

This is the original version:

'Used for the MonthCalendar control
Dim blRet As Boolean
Dim dtStart As Date, dtEnd As Date

dtStart = Nz(Me.txtDateApproval.value, 0)
dtEnd = 0

blRet = ShowMonthCalendar(mc, dtStart, dtEnd)
If blRet = True Then
    Me.txtDateApproval = dtStart
Else
' Add any message here if you want to
' inform the user that no date was selected
End If

Cluskitt, nice advice, but just how and where?  What changes - that is, what changes to the code would you suggest?
mbizup, I will try your suggestion.

Sandra
try this
Private Sub HRStartDate_Click()
    Call ShowCalendar(Me.Name, "HRStartDate")
End Sub


Public Sub ShowCalendar(frmName As String, ctlName As String)
'Used for the MonthCalendar control
Dim blRet As Boolean
Dim dtStart As Date
Dim dtEnd As Date
Dim ctl As Control

'First check to see if the control is locked, if it is, then cannot show
'calendar as field should not be changed.

If Forms(frmName)(ctlName).Locked = True Then
    Exit Sub
End If

dtStart = Nz(Forms(frmName)(ctlName).value, 0)
dtEnd = 0

blRet = ShowMonthCalendar(mc, dtStart, dtEnd)
If blRet = True Then
    Forms(frmName)(ctlName) = dtStart
Else
' Add any message here if you want to
' inform the user that no date was selected
End If
End Sub
Now I get an Application defined or object-defined error at the line

dtStart = Nz(frmName.ctlName.value, 0)
The confusion is that in the declaration of ShowCalendar, the 2nd argument uses "ctrlName" as the argument name, but is defined as a control.  I believe that should be defined as a string (see below).  Then, you might want to relook the way you are calling the subroutine.  My Guess is that HRStartDate is the name of the control source for that control, but the controls name is probably txt_HRStartDate or something like that (at least it will be if you are using a standard naming convention for your controls).

Private Sub HRStartDate_Click()
    Call ShowCalendar(Me, "txt_HRStartDate")
End Sub

The actual procedure is:

Public Sub ShowCalendar(frmName As Form, ctlName As String)
'Used for the MonthCalendar control
Dim blRet As Boolean
Dim dtStart As Date
Dim dtEnd As Date
Dim ctl As Control

'First check to see if the control is locked, if it is, then cannot show
'calendar as field should not be changed.

If frmName.Controls(ctlName).Locked = True Then
    Exit Sub
End If

dtStart = Nz(frmName.Controls(ctlName).value, 0)   '<<modified this line
dtEnd = 0

blRet = ShowMonthCalendar(mc, dtStart, dtEnd)
If blRet = True Then
    frmName.Controls(ctlName) = dtStart
Else
' Add any message here if you want to
' inform the user that no date was selected
End If
End Sub
The easiest way to accomplish this is to do this:
Public Sub ShowCalendar(ByRef frmName As Form, ByRef ctlName As Control)

This means that any change you make to the frmName or the ctlName will be reflected on the original form and control. That means you don't have to call the controls the way you are doing. You can just call on ctlName directly:
dtStart = Nz(ctlName.value, 0)
ctlName = dStart

You might want to specify which type of control it is, though (Calendar?)
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Cap, will download.  I tried your version, but was wondering, if the procedure dimensions frmName as a string and I pass Me.Name, is that correct?  

Sandra
Actually, this version of the calendar was simplier than what I was tring to do and did solve the problem.  I simply could not get the way I was approaching it to work.  Thank you.

Sandra