Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007

    Unanswered: Lock record/message alert onclick

    I have a main form (frmSTUDENTS) containing StudentID, StudentName, StudentSurname. There's a dropdown list box that allows teachers to select their student from the list and automatically locate their record.

    There is a subform (frmSUBJECTCOMMENTS), linked to frmSTUDENTS via StudentID. The idea is that the teacher will (a) Search for their student (and thereby retrieve that student's record, (b) select their own name from a drop down list box; (c) select the subject name from a drop down list box and (d) enter comments for that subject in a memo field. One teacher will generally only teach a student once, and therefore they need to enter data one student at a time. One student's record is relevant to one subject at a time.

    When clicked, I need the SAVE CHANGES button to (a) make the current record locked (only if it is NOT a new record) and (b) Launch a warning alert box that tells the user that they must now search for a new student record before proceeding with further changes. I think that this alert box should have an 'OK' and 'Cancel' option, but I am not sure. If they click OK and attempt to edit the same record again, an alert should come up that asks them if they really want to edit the data for StudentSurname, StudentName, and to click 'OK' or 'Cancel' to continue.

    I don't know whether this idea is overkill or if somebody has a better suggestion, but I need to prevent teachers from accidentally writing over their own data (or somebody else's, should they forget to search for their next student's record).

    If somebody could provide the code for this I would be most grateful, as I have no clue at all with VBA.


  2. #2
    Join Date
    Nov 2003
    When clicked, I need the SAVE CHANGES button to (a) make the current record locked (only if it is NOT a new record)
    I'm assuming that the Save Changes button is the final straw for the record and it should be locked so as to protect it from change, at least from the Form or interface level. Including the SubForm. If this is the case then you could:

    Add an additional Yes/No field to your table named RecLocked and set the Format property to True/False and Default property to False

    Now add a CheckBox to your frmSTUDENTS Form and also name it RecLocked. Set its Visible property to False so that it can't be seen (if you like). Bind this additional RecLock CheckBox to the RecLock field in your Table (via ControlSource property). Set the Default property to False.

    Now Copy and Paste the following Private Function into your Main Form's Code Module:

    Private Function LockRecord(ByVal LockIt As Boolean)
         Me.AllowDeletions = Not LockIt
         Me.AllowEdits = Not LockIt
    End Function
    Now...In the Form's code module add the following lines of code within the OnClick event for the Save Changes Command Button.

    Me.RecLocked.Value = True
    Call LockRecord(True)
    MsgBox "This Record is now Locked.", vbInformation, "Record Locked"
    Not done yet......

    Now, in the Main Form's OnCurrent event, copy and paste this code:

    If Me.RecLocked.Value = True Then
       Call LockRecord(False)
       Call LockRecord(True)
    End If
    Now, again for the Main Form (frmSTUDENTS), Set the Form's KeyPreview property to Yes then copy/paste this code into the Form's KeyDown event:

    If Me.AllowEdits = False And (KeyCode >= 32 Or KeyCode <= 126) Then
       'Personaly, I would password protect the this. In other words, I would
       'make the user enter a password before and Edit can take place and I
       'would think three times before I would allow deletions.  :P
       KeyCode = 0
       If MsgBox("Sorry  -  This record is currently locked and therefore" & vbCr & _
                 "you can not edit any fields." & vbCr & vbCr & _
                 "If you really must edit this Record then select the OK" & vbCr & _
                 "button otherwise please select CANCEL.", vbExclamation + vbOKCancel, _
                 "Record Locked") = vbOK Then
          Call LockRecord(False)
       End If
    End If
    Here is what's gonna happen. When the user is finished editing the record, he/she will (hopefully) select the Save Changes button. When this happens the RecLocked CheckBox on the frmSTUDENTS form is checked which in turn of course will set the RecLocked field in Table to True indicating that the record it to be locked if encountered via our Main Form. A message box will be immediately displayed to the user that the record is locked. From that point the record can not be Edited or Deleted. New Records can however be Added.

    As the user scrolls through a record that has been indicated as locked nothing happens...until they try to enter data into one of the Form fields (this is where the Form's KeyDown event comes in). If this is the case then another message is displayed to the User again indicating that the record is locked but also provides the means for them to edit if they select the OK button or not to by selecting the CANCEL button.

    I think this is what you were looking for. can see all this crap in action within the small attached Access proggy.

    Attached Files Attached Files
    Self Taught In ALL Environments.....And It Shows!

  3. #3
    Join Date
    May 2013

    First of all, thank you so much for your detailed instruction. I can use it successfully, but I don't know why I can't do any changes to the first field in my main form. Is there anything that needs to be fixed to correct that?

    Thank you so much!

  4. #4
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Quote Originally Posted by giaosu View Post

    ...I don't know why I can't do any changes to the first field in my main form...
    Because the first Field is the StudentID, which is defined as an Autonumber, and Autonumber Fields are assigned by Access and cannot be changed by the user.

    Linq ;0)>
    Last edited by Missinglinq; 12-14-13 at 20:46.
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts