Question Lock editing based on date and time elapsed (1 Viewer)

josephbupe

Registered User.
Local time
Today, 03:16
Joined
Jan 31, 2008
Messages
247
Greetings,

I want to lock data editing on the details form for the "data" user based on:

whether the date the record was added is equal or greater than today's date and the time elapsed is equal or more than 20 minutes

Code:
If DLookup("[data]", "tblCoordinators", "[userID]='" & Forms!frmEvents_Menus!txtUserID.Value & "'") = -1 Then

If Not IsNull(Me.txtEvent_ID) & Me.txtJustDate <> Now() & Me.txtJustTime <> 00:00:20 Then
Me.AllowEdits=false
 End If


Any help is most welcome.


Thanks
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:16
Joined
Aug 30, 2003
Messages
36,118
For starters, Now() includes the time. Use Date() for just the date. A literal date/time value has to be wrapped in # delimiters: #00:00:20# (and that's 20 seconds, not minutes). You realize "<>" is "not equal to"?
 

josephbupe

Registered User.
Local time
Today, 03:16
Joined
Jan 31, 2008
Messages
247
For starters, Now() includes the time. Use Date() for just the date. A literal date/time value has to be wrapped in # delimiters: #00:00:20# (and that's 20 seconds, not minutes). You realize "<>" is "not equal to"?


Indeed!


So then this should be good to go?


Code:
If DLookup("[data]", "tblCoordinators", "[userID]='" & Forms!frmEvents_Menus!txtUserID.Value & "'") = -1 Then

If Not IsNull(Me.txtEvent_ID) & Me.txtJustDate >= Date() & Me.txtJustTime <> #00:20:00# Then
Me.AllowEdits=false
End If
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:16
Joined
Aug 30, 2003
Messages
36,118
Sorry, you need AND instead of & (which is a concatenation operator, not a logical operator). Try

If Not IsNull(Me.txtEvent_ID) AND Me.txtJustDate >= Date() AND Me.txtJustTime <> #00:20:00# Then

I don't know what's in txtJustTime, but it seems odd to just check that it's not 20 minutes exactly.
 

josephbupe

Registered User.
Local time
Today, 03:16
Joined
Jan 31, 2008
Messages
247
Sorry, you need AND instead of & (which is a concatenation operator, not a logical operator). Try

If Not IsNull(Me.txtEvent_ID) AND Me.txtJustDate >= Date() AND Me.txtJustTime <> #00:20:00# Then

I don't know what's in txtJustTime, but it seems odd to just check that it's not 20 minutes exactly.


Noted


And YES, It should be
Code:
Me.txtJustTime >= #00:20:00#
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:16
Joined
Aug 30, 2003
Messages
36,118
Happy to help!
 

josephbupe

Registered User.
Local time
Today, 03:16
Joined
Jan 31, 2008
Messages
247
Hi,


I need more help here.


I want only to read the minutes as:
Code:
Me.txtJustTime >= #00:20:00#
But that keeps changing itself to
Code:
Me.txtJustTime >= #12:20:00 AM#


Any ideas how I can do this, please.
 

isladogs

MVP / VIP
Local time
Today, 01:16
Joined
Jan 14, 2017
Messages
18,186
Change your default datetime format in Windows settings to use the 24 hour clock
 

josephbupe

Registered User.
Local time
Today, 03:16
Joined
Jan 31, 2008
Messages
247
Change your default datetime format in Windows settings to use the 24 hour clock


That doesn't change the formatting i am getting within the code i posted earlier.


I mean, the windows date formatting was already 24 hour clock i.e 09:45
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 01:16
Joined
Jan 14, 2017
Messages
18,186
The code is modified automatically in VBA so Access can read it correctly.
However that will not affect the time display on your controls & will not prevent your code running correctly.

Its similar to VBA requiring the mm/dd/yyyy (or yyyy-mm-dd) format.
Irritating for the majority of the world's population not living the USA but one of those things you learn to live with

However you could try using:
Code:
If Not IsNull(Me.txtEvent_ID) AND Me.txtJustDate >= Date() AND Me.txtJustTime <> "# & 00:20:00 & #" Then

That will 'stick' but you'll need to test whether the code runs as required
 

josephbupe

Registered User.
Local time
Today, 03:16
Joined
Jan 31, 2008
Messages
247
Surprisingly it doesn't run as expected.


My idea is to disable certain controls after 20 minutes of capturing data. So, i expect that those controls will be disabled when viewing data older than 20 minutes ago.


Please, see attached stripped database file for reference.


I appreciate your help.
 

Attachments

  • Database1.accdb
    512 KB · Views: 63

isladogs

MVP / VIP
Local time
Today, 01:16
Joined
Jan 14, 2017
Messages
18,186
No it wouldn't :rolleyes:

1. Form_Open wouldn't work even if the code was correct as the form hasn't fully loaded
2. Use Form_Current so the enabled state is updated for each record
3. You need to check an elapsed time but are wrongly trying to use a specified time value

You need to check whether the entered datetime < 20 minutes before Now
That may be quite tricky to do but I'll have a look later today if someone else doesn't solve it for you first
 

isladogs

MVP / VIP
Local time
Today, 01:16
Joined
Jan 14, 2017
Messages
18,186
OK - I think its now working

Getting the code to fire reliably was as I expected quite tricky.
This is the code I've used:

Code:
Private Sub Form_Current()

    Dim dteDate As Date
[COLOR="SeaGreen"]    'the next line will give the datetime 20 minutes ago as 1/72 day = 0.0138889[/COLOR]
    dteDate = CDate(CDbl(Now()) - 0.0138889)
  
 [COLOR="seagreen"]  'set the controls enabled by default[/COLOR]
    Me.txtEventName.Enabled = True
    Me.txtEventDate.Enabled = True
  
[COLOR="seagreen"]   'this works for me[/COLOR]
    If Not IsNull(Me.txtEvent_ID) And [B]Me.txtDateEventAdded < dteDate[/B] Then
          Me.txtEventName.Enabled = False
          Me.txtEventDate.Enabled = False
    End If
    
End Sub

Tested using UK date format dd/mm/yyyy - I didn't need to format the section in bold type ...which surprised me.
In fact formatting using US dates failed.

Please can you check it works correctly for you
 

Attachments

  • josephbupe-CR.accdb
    416 KB · Views: 50
Last edited:

Minty

AWF VIP
Local time
Today, 01:16
Joined
Jul 26, 2013
Messages
10,355
Couldn't the time 20 minutes ago simply be
Code:
TwentyMinutesToComply: Dateadd("n",-20,Now())

Or have I used Monday logic and oversimplified?
 

josephbupe

Registered User.
Local time
Today, 03:16
Joined
Jan 31, 2008
Messages
247
OK - I think its now working

Getting the code to fire reliably was as I expected quite tricky.
Please can you check it works correctly for you




Wow! It works as expected. Thank you so much.



Code:
Private Sub Form_Current()
Dim dteDate As Date
    'the next 2 line will give the datetime 20 minutes ago as 1/72 day = 0.138889
    dteDate = CDate(CDbl(Now()) - 0.0138889)
  
                Me.AllowEdits = True
                Me.CommandCalendar1.Visible = True
                Me.CommandCalendar2.Visible = True
                Me.ComboCategory.Enabled = True
                Me.ComboCountry.Enabled = True
                Me.ComboCrime.Enabled = True
                Me.frmEvents_Coordinators!ComboCoordinator.Enabled = True
                Me.frmEvents_Coordinators!CommandRemove.Enabled = True
                Me.subfrmParticipants!ComboParticipants.Enabled = True
                Me.subfrmParticipants!CommandRemove.Enabled = True
                Me.frmEvents_ResPersons!ComboResPerson.Enabled = True
                Me.frmEvents_ResPersons!ComboRole.Enabled = True
                Me.frmEvents_ResPersons!CommandRemove.Enabled = True
'data
    If DLookup("[data]", "tblCoordinators", "[userID]='" & Forms!frmEvents_Menus!txtUserID.Value & "'") = -1 Then
        If Not IsNull(Me.txtEvent_ID) And Me.txtDateEventAdded < dteDate Then
                Me.AllowEdits = False
                Me.CommandCalendar1.Visible = False
                Me.CommandCalendar2.Visible = False
                Me.ComboCategory.Enabled = False
                Me.ComboCountry.Enabled = False
                Me.ComboCrime.Enabled = False
                Me.frmEvents_Coordinators!ComboCoordinator.Enabled = False
                Me.frmEvents_Coordinators!CommandRemove.Enabled = False
                Me.subfrmParticipants!ComboParticipants.Enabled = False
                Me.subfrmParticipants!CommandRemove.Enabled = False
                Me.frmEvents_ResPersons!ComboResPerson.Enabled = False
                Me.frmEvents_ResPersons!ComboRole.Enabled = False
                Me.frmEvents_ResPersons!CommandRemove.Enabled = False
            End If
      End If
 

josephbupe

Registered User.
Local time
Today, 03:16
Joined
Jan 31, 2008
Messages
247
Couldn't the time 20 minutes ago simply be
Code:
TwentyMinutesToComply: Dateadd("n",-20,Now())
Or have I used Monday logic and oversimplified?


Yeah thank you.
 

isladogs

MVP / VIP
Local time
Today, 01:16
Joined
Jan 14, 2017
Messages
18,186
You're welcome but I suggest you replace my dteDate expression with Minty's version
DteDate= Dateadd("n" -20,Now())

That will be much easier to alter if you decide to change from 20 mins to something else later on.
 

josephbupe

Registered User.
Local time
Today, 03:16
Joined
Jan 31, 2008
Messages
247
You're welcome but I suggest you replace my dteDate expression with Minty's version
DteDate= Dateadd("n" -20,Now())

That will be much easier to alter if you decide to change from 20 mins to something else later on.


Yeah, it that already.


Thanks alot
 

Users who are viewing this thread

Top Bottom