Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    santon's Avatar
    santon is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    44

    Lock the old records from editing (form view)

    Hello, I need a help with locking the record from editing in form view.


    I have buttons with command to go to "Previous record" and "Next record" (arrows left and right on the picture)

    Click image for larger version. 

Name:	snip.JPG 
Views:	38 
Size:	23.5 KB 
ID:	43581

    In the lower left corner you can see the field "Datum" which contains the date of the record.
    I want to lock the older records from editing (disable).
    Today's date records can be edited, but everything from the past can only be viewed, but not modified.

    Is there a way to do this?
    I don't want users messing up the older records.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in the form ON CURRENT event
    lock the form from edits if the date is wrong:

    Private Sub Form_Current()
    Me.AllowEdits =
    txtDateFld = date()
    end sub



  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use conditional formatting for the controls you want to lock. This will lock controls on all records over 5 days old

    expression is....[Datum]<date()-5

    set the condition to disabled


    alternatively in the form current event put

    me.allowedits=[Datum]>=date()-5

    but you might find the disables controls you still want to be able to edit.

  4. #4
    santon's Avatar
    santon is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    44
    Quote Originally Posted by ranman256 View Post
    in the form ON CURRENT event
    lock the form from edits if the date is wrong:

    Private Sub Form_Current()
    Me.AllowEdits =
    txtDateFld = date()
    end sub
    I've tried this and it won't work, as soon as I enter the form view it shows me the error.



    Ajax
    use conditional formatting for the controls you want to lock. This will lock controls on all records over 5 days old

    expression is....[Datum]<date()-5

    me.allowedits=[Datum]>=date()-5

    The conditional formatting works, but the second part of your answer isn't.
    I have typed that expression into "On current" in my form and nothing happens after that, I can still edit everything.





  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I have typed that expression into "On current" in my form and nothing happens after that, I can still edit everything
    suggest show what you have actually typed

    as soon as I enter the form view it shows me the error.
    what is 'the error'

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by santon View Post

    ...I have typed that expression into "On current" in my form and nothing happens...
    You can't enter the code in the On Current Property (Properties - Events - On Current) which I assume is what you've done, from your post...it has to be in the VBA code module of your Form...as ranman256 posted:

    Code:
    Private Sub Form_Current()
      Me.AllowEdits = txtDateFld = date()
    End Sub
    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    santon's Avatar
    santon is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    44
    Quote Originally Posted by Missinglinq View Post
    You can't enter the code in the On Current Property (Properties - Events - On Current) which I assume is what you've done, from your post...it has to be in the VBA code module of your Form...as ranman256 posted:
    I know that, I have selected Event procedure then entered the code and it doesn't work.
    Click image for larger version. 

Name:	event procedure.png 
Views:	29 
Size:	2.5 KB 
ID:	43603

    I have tried these two options, now there is no error, but I can still edit everything and it is very slow in form view, almost impossible to do anything.

    Click image for larger version. 

Name:	Captures.JPG 
Views:	29 
Size:	14.0 KB 
ID:	43604
    Click image for larger version. 

Name:	kod 2.JPG 
Views:	29 
Size:	14.2 KB 
ID:	43605

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Try
    Code:
    Me.AllowEdits = (Datum = Date)
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    santon's Avatar
    santon is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    44
    Quote Originally Posted by Welshgasman View Post
    Try
    Code:
    Me.AllowEdits = (Datum = Date)

    It returns this error
    Click image for larger version. 

Name:	Capture.JPG 
Views:	27 
Size:	27.5 KB 
ID:	43610

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is Datum the name of the field or the control? or both

    Also, is the field always populated (i.e. default is date() for new records)

    always helps if you post the actual code you have used - for all we know you have a typo

    you said the conditional formatting works, so it has to be something to do with your form or data

  11. #11
    santon's Avatar
    santon is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    44
    Quote Originally Posted by Ajax View Post
    is Datum the name of the field or the control? or both -->

    Also, is the field always populated (i.e. default is date() for new records)

    always helps if you post the actual code you have used - for all we know you have a typo

    you said the conditional formatting works, so it has to be something to do with your form or data


    Datum is both, control and field name.

    Also tried this code, return same error as I posted screenshot before.


    Click image for larger version. 

Name:	1.JPG 
Views:	22 
Size:	12.6 KB 
ID:	43626



    Conditional formatting now also doesn't work and the form view is incredibly slow.
    I don't know what's going on.
    Had to restore backup, tried conditional formatting again - NOT WORKING
    I don't even know how it worked the first time

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Well corruption *might* be an issue as that should work fine.?

    Try a new fomr with just that code with enough to test.

    Try a /decompile as well, then a compile.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    santon's Avatar
    santon is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    44
    Quote Originally Posted by Welshgasman View Post
    Well corruption *might* be an issue as that should work fine.?

    Try a new fomr with just that code with enough to test.

    Try a /decompile as well, then a compile.
    I tried on different form, it worked, both the conditional formatting and the code.
    But when I try to navigate between records, when I am the end and there is nothing left it gives me these two errors. (I am on my last record and I click the button for "Next record" )

    Click image for larger version. 

Name:	er 1.JPG 
Views:	21 
Size:	14.6 KB 
ID:	43627
    Click image for larger version. 

Name:	er 2.jpg 
Views:	20 
Size:	14.4 KB 
ID:	43628

  14. #14
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    So perhaps check for Null for a control, or perhaps EOF of the recordsetclone, or even Me.NewRecord ?

    You should not be proceeding past the EOF unless you decide to add a record.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  15. #15
    santon's Avatar
    santon is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    44
    Quote Originally Posted by Welshgasman View Post
    So perhaps check for Null for a control, or perhaps EOF of the recordsetclone, or even Me.NewRecord ?

    You should not be proceeding past the EOF unless you decide to add a record.

    I'm sorry can you explain it to me little simpler?
    I'm really a noob, recently started learning about VBA and Access and I don't fully understand your advice

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Lock a query for editing - run only
    By paul83 in forum Queries
    Replies: 3
    Last Post: 06-14-2016, 10:30 AM
  2. How to temporary lock form for editing
    By aonsu209 in forum Forms
    Replies: 5
    Last Post: 01-15-2015, 07:59 AM
  3. Editing Visio drawing in Form view
    By jmejorada360 in forum Import/Export Data
    Replies: 2
    Last Post: 08-10-2012, 10:15 AM
  4. lock the editing of combobox text
    By Grooz13 in forum Forms
    Replies: 14
    Last Post: 10-23-2011, 02:08 PM
  5. Replies: 0
    Last Post: 03-26-2007, 12:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums