Sandra Smith
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.
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).
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.
If frmName.Controls(ctlName). Locked = True Then
ctlName is a control, not a control name. Either change the type or the code.
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.N ame).Locke d = 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
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.N
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.
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.valu e, 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?
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.valu
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?
ASKER
mbizup, I will try your suggestion.
Sandra
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).Lo cked = 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
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).Lo
Exit Sub
End If
dtStart = Nz(Forms(frmName)(ctlName)
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
ASKER
Now I get an Application defined or object-defined error at the line
dtStart = Nz(frmName.ctlName.value, 0)
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(ctlNam e).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
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).
Exit Sub
End If
dtStart = Nz(frmName.Controls(ctlNam
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?)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Sandra
ASKER
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
Sandra
which line is raising the error ?