If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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:

If Me.RecLocked.Value = True Then
Call LockRecord(False)
Else
Call LockRecord(True)
End If

Now, again for the Main Form (frmSTUDENTS), Set the Form'sKeyPreview property to Yes then copy/paste this code into the Form'sKeyDown event:

Code:

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.

UGH...anyways...you can see all this crap in action within the small attached Access proggy.

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?