Question Lock editing based on date and time elapsed

[josephbupe]

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

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

 

[pbaldy]

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]

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?

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]

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]

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

Me.txtJustTime >= #00:20:00#

 

[josephbupe]

Thank you dear Paul. You are a life saver.

 

[pbaldy]

Happy to help!

 

[josephbupe]

Hi,


I need more help here.


I want only to read the minutes as:

Me.txtJustTime >= #00:20:00#

But that keeps changing itself to

Me.txtJustTime >= #12:20:00 AM#

Any ideas how I can do this, please.

 

[isladogs]

Change your default datetime format in Windows settings to use the 24 hour clock

 

[josephbupe]

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

 

[isladogs]

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:

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]

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.

 

[isladogs]

No it wouldn't

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]

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:

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

 

[Minty]

Couldn't the time 20 minutes ago simply be

TwentyMinutesToComply: Dateadd("n",-20,Now())

Or have I used Monday logic and oversimplified?

 

[isladogs]

Now why didn't I think of that....?

 

[josephbupe]

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.

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]

Couldn't the time 20 minutes ago simply be

TwentyMinutesToComply: Dateadd("n",-20,Now())

Or have I used Monday logic and oversimplified?

Yeah thank you.

 

[isladogs]

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]

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