I have a form with the following coding on click of a control. I am trying to always set ActiveXCtl0 to the current Monday when clicked. With this code, sometimes it works and sometimes it doesn't. can someone tell me what is wrong with my code?


Dim stDocname As String, bteSte As Byte, bteEnd As Byte
Dim dtest As Date, dteEd As Date
dtest = Me.ActiveXCtl0.Value
dteEd = Me.ActiveXCtl12.Value
bteSte = DatePart("w", dtest)
bteEnd = DatePart("w", dteEd)

Dim ctl0 As Control
Set ctl0 = Me!ActiveXCtl0

ctl0 = Date

If bteSte = 1 Then
Me.ActiveXCtl0 = Date - 6
Exit Sub
ElseIf bteSte = 3 Then
Me.ActiveXCtl0 = Date - 1
Exit Sub
ElseIf bteSte = 4 Then
Me.ActiveXCtl0 = Date - 2
Exit Sub
ElseIf bteSte = 5 Then
Me.ActiveXCtl0 = Date - 3
Exit Sub
ElseIf bteSte = 6 Then
Me.ActiveXCtl0 = Date - 4
Exit Sub
ElseIf bteSte = 7 Then
Me.ActiveXCtl0 = Date - 5
Exit Sub
ElseIf bteStet = 2 Then
Me.ActiveXCtl0 = Date
End If

After I get this to work I want ActiveXCtl12 to set to the Saturday exactly 4 weeks later. any suggestions on that too??

Michele,

why are you using "Exit Sub" after each If Then statement? You only need one....
Why not use Select Case?

Greetings,

RV

I copied some of this from a consultant that helped us out a while back and I'm not sure why he did that. How do I use select case.
I'm having a problem if I push the command button more than once. It doesn't seem to go through the if then statements the 2nd timee. It only sets the dates to current. How can I fix that too?

Yeah, that's what a lot of consultants do...
Lots of talk, lots of billing and lots of work you wouldn't hope for..
I know what I'm talking 'bout as being one myself (not in IT business, I'm just kinda familiar with IT business and mainly made nyself acquinted with Access as a hobby).
So I can't garantee this code is one hundred percent OK... It should work though....

Copy this code in your code.
Replace "ButtonWhatver" by the name of your button:

Private Sub ButtonWhatever_Click()
On Error GoTo Err_ButtonWhatever_Click

Dim stDocname As String, bteSte As Byte, bteEnd As Byte
Dim dtest As Date, dteEd As Date
dtest = Me.ActiveXCtl0.Value
dteEd = Me.ActiveXCtl12.Value
bteSte = DatePart("w", dtest)
bteEnd = DatePart("w", dteEd)

Dim ctl0 As Control
Set ctl0 = Me!ActiveXCtl0
ctl0 = Date

If bteSte = 1 Then
Me.ActiveXCtl0 = Date - 6
ElseIf bteSte = 3 Then
Me.ActiveXCtl0 = Date - 1
ElseIf bteSte = 4 Then
Me.ActiveXCtl0 = Date - 2
ElseIf bteSte = 5 Then
Me.ActiveXCtl0 = Date - 3
ElseIf bteSte = 6 Then
Me.ActiveXCtl0 = Date - 4
ElseIf bteSte = 7 Then
Me.ActiveXCtl0 = Date - 5
ElseIf bteStet = 2 Then
Me.ActiveXCtl0 = Date
End If

Exit_ButtonWhatever_Click:
Exit Sub

Err_ButtonWhatever_Click:
MsgBox Err.Description
Resume Exit_ButtonWhatever_Click

End Sub

You can replace your if..then statements by a Select Case.
Full Code:

Private Sub ButtonWhatever_Click()
On Error GoTo Err_ButtonWhatever_Click

Dim stDocname As String, bteSte As Byte,
bteEnd As Byte
Dim dtest As Date, dteEd As Date
dtest = Me.ActiveXCtl0.Value
dteEd = Me.ActiveXCtl12.Value
bteEnd = DatePart("w", dteEd)

Dim ctl0 As Control
Set ctl0 = Me!ActiveXCtl0
ctl0 = Date

Select Case DatePart("w",Me.ActiveXCtl0.Value)

Case 1
Me.ActiveXCtl0 = Date - 6
Case 2
Me.ActiveXCtl0 = Date
Case 3
Me.ActiveXCtl0 = Date - 1
Case 4
Me.ActiveXCtl0 = Date - 2
Case 5
Me.ActiveXCtl0 = Date - 3
Case 6
Me.ActiveXCtl0 = Date - 4
Case 7
Me.ActiveXCtl0 = Date - 5
End Select

Exit_ButtonWhatever_Click:
Exit Sub

Err_ButtonWhatever_Click:
MsgBox Err.Description
Resume Exit_ButtonWhatever_Click

End Sub


Suc6,

RV





[This message has been edited by RV (edited 03-21-2002).]

THANK YOU- It is working well now.
I am trying to make the ActiveXCtl12 go to exactly 4 weeks after the Monday of the current week and not having much luck with it. Can I add more cases or should I add on to Cases 1-7?

Michele,

glad things worked out fine.

>I am trying to make the ActiveXCtl12 go to exactly 4 weeks after the Monday of the current week and not having much luck with it. Can I add more cases or should I add on to Cases 1-7?<

As this is always what you want, just add the code you need before Select Case.
I'll try to create some code you could use.

Greetings,

RV

Michele,

>I am trying to make the ActiveXCtl12 go to exactly 4 weeks after the Monday of the current week<

Try this code and put it before the Select Case:

Me.ActiveXCtl12=[Date_order]+2-Weekday([Date_order])+28

By default Monday is the second day of the week, that's why I add 2.
If Monday is not the second day of your weeks, look in the helpfile of Access for "Weekday" and adapt the code.

Suc6,

RV