Locking number fields by date 1

[techkenny1]

hi all,

I have a continous form on which there is a date field, and 4 number fields. The number fields are used to input hours worked.
This is an invoice form.

The number fields are [blue]d1,n1,sat,sun[/blue]. The date field is, [blue]SDate[/blue]

What I would like to happen is that when the date field shows a saturday or sunday then the number fields, D1,N1 are locked, preventing the user from putting the wrong data into them.


Many thanks,

kp

 

[kjv1611]

Well, the code you'd need would be soemthing like:

  If DayOfWeek(DateField) = "Sunday" OR DayOfWeek(DateField) = "Saturday" Then
    D1.Enabled = False
    N1.Enabled = False
  Else
    D1.Enabled = True
    N1.Enabled = True
  End If

Well, that's the general idea anyway... the "DayOfWeek" may not be a function... it may be using the Format() function, and it being a variable within that function... I forget. You can search the Access VBA Help files for the details there... or Google.

Let me know if that gets you where you need to be, or if you need more help..

 

[MajP]

call from the forms on current event

Public Sub lockUnlock()
If Weekday(sdate) = vbSunday Or Weekday(sdate) = vbSaturday Then
Me.d1.Locked = True
Me.n1.Locked = True
Else
Me.d1.Locked = False
Me.n1.Locked = False
End If
End Sub

untested.

 

[techkenny1]

Hi Guys,
Many thanks for your replies.

I'm just not sure how to implement this. Do I need a command button to get this code to work?

Many thanks,

kp

 

[MajP]

The form has an On Current event. This event occurs everytime you go to a new record. In the forms property window, go to the events tab. Double click the onCurrent event at the ellipsis.

 

[dhookom]

The code in the On Current event would be:

Private Sub Form_Current()
    LockUnLock
End Sub

The code for the procedure/sub could be:

Public Sub lockUnlock()
  Dim booLock as Boolean
  booLock = (Weekday(Me.sdate) = vbSunday Or Weekday(Me.sdate) = vbSaturday)
    Me.d1.Locked = booLock
    Me.n1.Locked = booLock
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Hi dhookom, Majp,

Many thanks for your replies.

I have set up the code as your instructions,

Private Sub Form_Current()
LockUnLock
End Sub[/blue]

And I copied this code into the form;
[blue]Public Sub lockUnlock()
Dim booLock as Boolean
booLock = (Weekday(Me.sdate) = vbSunday Or Weekday(Me.sdate) = vbSaturday)
Me.d1.Locked = booLock
Me.n1.Locked = booLock
End Sub[/blue]

When the form opens it comes up with a debug issue.

[blue]booLock = (Weekday(Me.sdate) = vbSunday Or Weekday[/blue]

This is the line that is the debug issue. I am not sure how to solve this problem. Hopefully I have got it right. Could I have some further help please

kp

 

[PHV]

Public Sub lockUnlock()
Dim booLock As Boolean
If IsDate(Me!sdate) Then
  booLock = (Weekday(Me!sdate) = vbSunday Or Weekday(Me!sdate) = vbSaturday)
End If
Me!d1.Locked = booLock
Me!n1.Locked = booLock
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[techkenny1]

Hi PHV,
Many thanks for your reply.
I copied your code into the form and while no debug issue comes when a date ie:Sat,2 Jan10 is entered into (SDate) it will not lock D1 and N1.
What error could I be making?

Many thanks,
kp

 

[dhookom]

Are you updating SDate and expecting something to happen after update? If so, you need to call the code in the after update event of SDate in addition to the On Current.

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Hi dhookom,
Many thanks for your reply.
I did follow your instructions and it worked,except that it allowed me to input data, but not change it.

So I put the [blue]lockUnlock[/blue] code on the [blue]ongotfocus[/blue] event of D1 and N1.

It now works great. Is this the right thing to do?

kp

 

[dhookom]

I didn't think you could disable a control when it had the focus. You haven't told us how/when the values in the controls get added/updated. Typically, I use the On Current of the form combined with the after update of control(s).

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Hi dhookom,
Many thanks for your reply.
What hapens is that the user opens the invoice form they select a date from a calendar which is (SDate) and then tab along filling in client details etc and then put into D1, N1 Sat Sun the hrs worked.

After using your code when I put in a Sat date then tabbed along filling in client details and when it came to putting in the hrs in D1, it allowed me to.

When I went to change the hrs in D1 it had locked that field.



Putting [blue]unlockLock[/blue] on the [blue]ongotfocus[/blue] event of [blue]D1[/blue] when a sat/sun date is selected it will not allow any data input to [blue]D1[/blue].

But using any other date it allows me to
Does this make sense?

kp

 

[dhookom]

date from a calendar which is (SDate)

can you share more? The update event of a text box won't get trigger if you use code to update the value.

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Hi dhookom,
I'm still a novice at this db stuff, but I'll try to give all the details.

The invoice form has a main form and a subform. The subform is continous form. Its linked by [blue]InvoiceNo[/blue] to the main form. It's fields consist of a date field(SDate) some text boxes which holds relevant client data like names ID's etc.

There are also 4 texboxes which are a number data type, with the field size set to double. Plus 4 currency fields which gives values, such as GST, invoice totals.

The date is selected by using a date picker which inserts the date into the date field (SDate).

After using your code which I copied into the afterUpdate and into the On current Event, this is what happened;
I was able to select a date using the date picker and using the tab key advance to [blue]D1[/blue](inserting hrs).
It allowed me to put data into [blue]D1[/blue] with a Sunday Date. This I was trying not to do.

The reason for wanting to do this is that the users are inserting weekend hrs into the day hours.(Carelessness!) So we lose money. I want to force the users to get it right.

So to stop this I put the [blue]unlockLock code[/blue] in the [blue]On Got Focus[/blue] event of [blue]D1[/blue].

Now when I select a date which is a sat/sun from the date picker and then move to [blue]D1[/blue], it will not allow me to insert any data. This is what I want. But I can insert into sat/sun fields.

If this is not suposed to work then I dont have answer expect that it is what I wanted to achieve, unlesss there is a more correct way to do it.

I have tried to explain what I have done. any further discussion would be welcomed. Its still a learning curve for me.

kp

 

[dhookom]

I believe I clearly understood all of what you mentioned. Again, I expect the After Update event of the text box is not fired when a value is not typed into the text box. You stated you use a date picker but we don't know if this is an ActiveX control or a form or what.

To test this, just add a msgbox to the After Update of the SDate text box:

   MsgBox "After Update of SDATE"

Then try select a date in the text box to see if the message pops up.

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Thank you dhookom,

I did as you asked and no message pops up.
The date picker I have used is from Stephen Lebans Version 3
which was created in 2003.
I have included the blurb from the module
[blue]'How it Works:
' The Month Calendar is created directly with the
' API's contained in the Common Controls DLL. In this manner we bypass
' the DatePicker ActiveX control, which is simply a wrapper for these
' calls anyway. This removes any problems from distribution and
' especially version issues of using the ActiveX control[/blue]

kp

 

[dhookom]

Can I assume you know why the After Update event code doesn't work?

I don't know what if any code is used when implementing Stephens calendar. I would expect there might be some line that sets the value of the SDate control.

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Hi dhookom,
No I'm not sure why the After Update event code doesn't work

This is the code used to input the data from Lebans Date picker..

[blue]Private Sub SDate_DblClick(Cancel As Integer)
Dim blRet As Boolean
Dim dtStart As Date, dtEnd As Date

dtStart = 0
dtEnd = 0

blRet = ShowMonthCalendar(MC, dtStart, dtEnd)
If blRet = True Then
Me.SDate = dtStart

Else

End If
End Sub[/blue]

in the Form..
[blue]Option Compare Database
Option Explicit
Private MC As clsMonthCal[/blue]


in the form On Load event
[blue]Set MC = New clsMonthCal
MC.hWndForm = Me.hWnd[/blue]


in the UnLoad event
[blue]Private Sub Form_Unload(Cancel As Integer)
If Not MC Is Nothing Then
If MC.IsCalendar Then
Cancel = 1
Exit Sub
End If
Set MC = Nothing
End If

End Sub[/blue]

I have looked at the module, but don't really know what I'm looking for

kp

 

[dhookom]

I believe the SDate is being set in this code:

Private Sub SDate_DblClick(Cancel As Integer)
  Dim blRet As Boolean
  Dim dtStart As Date, dtEnd As Date

  dtStart = 0
  dtEnd = 0

  blRet = ShowMonthCalendar(MC, dtStart, dtEnd)
  If blRet = True Then
    Me.SDate = dtStart[green]  '<===Here[/green]

   Else

  End If
End Sub

You would need to add the call to the LockUnLock sub after the noted line

Private Sub SDate_DblClick(Cancel As Integer)
  Dim blRet As Boolean
  Dim dtStart As Date, dtEnd As Date

  dtStart = 0
  dtEnd = 0

  blRet = ShowMonthCalendar(MC, dtStart, dtEnd)
  If blRet = True Then
    Me.SDate = dtStart
    LockUnLock   [green]  '<===Here[/green]
   Else

  End If
End Sub

Duane
Hook'D on Access
MS Access MVP