10-31-07, 20:28 #1Registered User
- 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.
11-01-07, 04:44 #2Stuck on my opinions...
When clicked, I need the SAVE CHANGES button to (a) make the current record locked (only if it is NOT a new record)
- Join Date
- Nov 2003
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
Me.RecLocked.Value = True Call LockRecord(True) MsgBox "This Record is now Locked.", vbInformation, "Record Locked"
Now, in the Main Form's OnCurrent event, copy and paste this code:
If Me.RecLocked.Value = True Then Call LockRecord(False) Else Call LockRecord(True) End If
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
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.
Self Taught In ALL Environments.....And It Shows!
12-12-13, 16:46 #3Registered User
- 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!
12-13-13, 00:45 #4Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
Last edited by Missinglinq; 12-14-13 at 21:46.Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007