Converting a Sub Procedure to A Function

[KevinC]

Hi everyone.

I have a very simple query (I think).

I have a form with text boxes for the opening and closing times of a
shop for each day of the week.

I am trying to speed up data entry, and as quite a number of the shops
we are dealing with are open 24 hours I thought that I would try to
automate some of the process. To do this I added a check box to the
form (next to each day) where the OnClick event of the check box sets
the corresponding days opening time to a value of "00:01" and the
closing time to "23:59" (this is how we represent 24 hour opening
where I work).

This all works fine as a Sub, however I then realised that I would
have to duplicate the code 7 times (for each day of the week).

I believe that it would be much better to convert the code so that it
is a function - however I don't know how to do this.

Can anyone guide me along here?

In addition it would also be useful to have another check box that
"Sets all days to 24 hours" - if anyone can give tips on this too I
would be most greatful.

Cheers

Kevin

P.S. Here is the sub routine:

Public Sub chkMon24Hrs_Click()
If Me!chkMon24Hrs.Value = True Then
Me!txtMON_OP.Value = "00:01"
Me!txtMON_CLO.Value = "23:59"
Else
Me!txtMON_OP.Value = Null
Me!txtMON_CLO.Value = Null
End If
End

[Klatuu]

It is no big deal to change a sub to a function. You change Sub to Function
and it will automatically change End Sub to End Function. To make it work
for any of the seven controls, you can use Screen.ActiveControl instead of a
control name:

Public Function chkDay24_AfterUpdate()
Dim Ctl As Control

Set Ctl = Screen.ActiveControl

With Me
If Ctl = True Then
!txtMON_OP = "00:01"
!txtMON_CLO = "23:59"
Else
!txtMON_OP = Null

txtMON_CLO.Value = Null
End If

End With

Set Ctl = Nothing
End Function

Note: You don't need to use the Value property for controls. It is the
default property.

Rather than the Click event, Use the After Upate event and in the event's
property box just enter
=chkDay24_AfterUpdate()

--
Dave Hargis, Microsoft Access MVP

[mcescher]

Kevin,

Well, changing a sub to a function is as simple as changing the
declaration statement. But, you don't really need to change this to a
function since you're not returning a value, and I believe (Others
please correct me if I'm wrong) you can only attach a Sub to an event
such as OnClick. And, I'm curious why this sub was declared PUBLIC.

One other thing to think about is the immediate if statement IIF.
Assuming your check boxes follow the naming convention that you
started up there, code would be as follows:

Private Sub chkMon24Hrs_Click()
txtMon_OP = IIf(txtMon24Hrs, "00:01", Null)
txtMon_CLO = IIf(txtMon24Hrs, "23:59", Null)
End Sub
Private Sub chkTue24Hrs_Click()
txtTue_OP = IIf(txtTue24Hrs, "00:01", Null)
txtTue_CLO = IIf(txtTue24Hrs, "23:59", Null)
End Sub
Private Sub chkWed24Hrs_Click()
txtWed_OP = IIf(txtWed24Hrs, "00:01", Null)
txtWed_CLO = IIf(txtWed24Hrs, "23:59", Null)
End Sub
Private Sub chkThu24Hrs_Click()
txtThu_OP = IIf(txtThu24Hrs, "00:01", Null)
txtThu_CLO = IIf(txtThu24Hrs, "23:59", Null)
End Sub
Private Sub chkFri24Hrs_Click()
txtFri_OP = IIf(txtFri24Hrs, "00:01", Null)
txtFri_CLO = IIf(txtFri24Hrs, "23:59", Null)
End Sub
Private Sub chkSat24Hrs_Click()
txtSat_OP = IIf(txtSat24Hrs, "00:01", Null)
txtSat_CLO = IIf(txtSat24Hrs, "23:59", Null)
End Sub
Private Sub chkSun24Hrs_Click()
txtSun_OP = IIf(txtSun24Hrs, "00:01", Null)
txtSun_CLO = IIf(txtSun24Hrs, "23:59", Null)
End Sub
Private Sub chkAll24Hrs_Click()
txtMon_OP = IIf(txtAll24Hrs, "00:01", Null)
txtMon_CLO = IIf(txtAll24Hrs, "23:59", Null)
txtTue_OP = IIf(txtAll24Hrs, "00:01", Null)
txtTue_CLO = IIf(txtAll24Hrs, "23:59", Null)
txtWed_OP = IIf(txtAll24Hrs, "00:01", Null)
txtWed_CLO = IIf(txtAll24Hrs, "23:59", Null)
txtThu_OP = IIf(txtAll24Hrs, "00:01", Null)
txtThu_CLO = IIf(txtAll24Hrs, "23:59", Null)
txtFri_OP = IIf(txtAll24Hrs, "00:01", Null)
txtFri_CLO = IIf(txtAll24Hrs, "23:59", Null)
txtSat_OP = IIf(txtAll24Hrs, "00:01", Null)
txtSat_CLO = IIf(txtAll24Hrs, "23:59", Null)
txtSun_OP = IIf(txtAll24Hrs, "00:01", Null)
txtSun_CLO = IIf(txtAll24Hrs, "23:59", Null)
End Sub

Hope this helps,
Chris M.

[mcescher]

Dave,
How would this set the Tuesday, Wednesday, etc... opening and closing
times?

Thanks,
Chris M.

[Klatuu]

Well, when I modify it to work correctly, it will know which control to
affect. What I noticed was the ActiveControl will be the check box, not the
text box, so here is the modification.

Public Function chkDay24_AfterUpdate(strDayContro As string)
Dim Ctl As Control

Set Ctl = Screen.ActiveControl

With Me
If Ctl = True Then

Me.Controls(strDayControl & "_OP") = "00:01"
Me.Controls(strDayControl & "_CLO") = "23:59"
Else
Me.Controls(strDayControl & "_OP") = Null
Me.Controls(strDayControl & "_CLO") = Null
End If
End With

Set Ctl = Nothing
End Function

Then to use it, you would have to include the first part of the name of the
control to be updated in the call:
=chkDay24_AfterUpdate("txtMON")

--
Dave Hargis, Microsoft Access MVP

[KevinC]

Hi Dave.

I added the function to my form code and added the line
=chkDay24_AfterUpdate("txtMON") to the AfterUpdate event. However I
am getting the following error. Any idea why?

The expression After Update you entered as the
event property setting produced the following
error: Microsoft Access can't find the field '_OP'
reffered to in your expression.

[mcescher]

Kevin,

If you copied the code directly, it's missing an "L" at the end of
strDayContro in the declaration line.

[mcescher]

> > Chris M.- Hide quoted text -
>
> - Show quoted text -

Dave,
Awesome function!!!! Thanks for all your input. I always learn
something.

Thanks again,
Chris M.

[Klatuu]

I see I had a misspelling in the code. The first line should be
strDayControl (the l was missing.

--
Dave Hargis, Microsoft Access MVP