Results 1 to 9 of 9
  1. #1
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110

    Lock a field after data has been entered

    I have field "memo" which i am using for a notes box within one of my forms, but what I want to do is when the user has added data into the field on the form they can not go back and edit the details.

    Any help with this would be a great help as soon as I have this notes problem sorted my database is ready to run to the users.

    I am using access 2010 and am not a access expert so please please make it simply.

    Thanks

    Hads

  2. #2
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    The best thing to do here would be to use visual basic.

    Goto your form properties and double click in the 'oncurrent' field (under events) then click the button with the three dots to the right

    You should then get the visualbasic editor appear.

    It should show something like

    Code:
    Option Compare Database
    
    Private Sub Form_Current()
    
    Endsub
    Don't touch the top line, however change the rest to read:

    Code:
    Private Sub Form_Current()
    If Isnull([your_control_name_here] )=False Then
    [your_control_name_here].Locked=True
    End If
    End Sub
    This should then lock it after someone has been into the field and changed it.
    Note; this will also lock it if someone goes in, decides they dont want to type anything and deletes it (makes it zero length string instead).

  3. #3
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110
    That was really helpful, but the only problem I have is that is locks the whole field in every client record, all i want it to do is lock the field for the one record I am working on and not the whole lot.

    If i explain what I am doing and hope it will make more sence

    I have a customer database with around 4000 records, and am using one field "Note1" as a memo so users can add notes for that one record but I do not want them to go back into the field and change it.

    Hope this helps, I am sure it is simply if you understand it :-)

  4. #4
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    My bad the code should read:

    Code:
    Private Sub Form_Current() If Isnull([your_control_name_here])=False Then [your_control_name_here].Locked=True
    else
    [your_control_name_here].Locked=False End If End Sub
    The code checks to see if the control is null (hasnt been used), and if it has, it locks it, otherwise it unlocks it.

    It locked everyone before most likely because the first had been used, and then it had nowhere to go to unlock it.

  5. #5
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110
    Thanks for all your help but I am still getting a problem, when I input the code (I have enclose below) I get a error which high lights "Else" and says "Compile Error: Else Without If"

    (This is what I typed in)

    Option Compare Database (I have not move or edited this bit)

    Private Sub Form_Current ()
    If IsNull ([Note1]) = False Then [Note1].Locked = True
    Else
    [Note1].Locked = False
    End Sub

  6. #6
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110
    Not sure if it makes any odds but I am using Access 2010

  7. #7
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110
    I got it working with one field "note1" but I have more fields in the same doc note2, note3, note4 up to note 18 how do i get them working I have enclosed the working code below for you to look over.

    Option Compare Database


    Private Sub Form_Current()
    If IsNull([Note1]) = False Then
    [Note1].Locked = True
    Else
    [Note1].Locked = False


    End If
    End Sub

  8. #8
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    Believe it or not you already have the basis for what you want a little copy/pasting then rename the control, sorry my code before was on one line, I have some issues using these forums from my work computer (it only runs i.e 6.... joys of my company)

    Code:
    Private Sub Form_Current()
    If IsNull([Note1]) = False Then
    [Note1].Locked = True
    Else
    [Note1].Locked = False
    End If
    If IsNull([Note2]) = False Then
    [Note2].Locked = True
    Else
    [Note2].Locked = False
    End If
    If IsNull([Note3]) = False Then
    [Note3].Locked = True
    Else
    [Note3].Locked = False
    End If
    End Sub
    Keep adding and defining the controls until you get all the ones you want.

  9. #9
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110
    Thanks Mate, Works like a dream.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 97
    Last Post: 05-24-2012, 02:10 AM
  2. Replies: 3
    Last Post: 03-13-2012, 12:02 PM
  3. Replies: 4
    Last Post: 05-28-2011, 01:20 AM
  4. Replies: 4
    Last Post: 01-23-2011, 09:01 PM
  5. Unmatched data entered with data in table
    By boreda in forum Access
    Replies: 0
    Last Post: 07-28-2006, 09:11 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