By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Lock fields when current record is not a new record

P: 9
When a form is opened fields are locked. When a new record is added (by pressing an add new record button), fields are unlocked. When the record is saved the fields are then locked again.

However, if a user click the add new record button then the user uses the navagation button at the bottom of the screen to navigate to a previous record, the locked fields are unlocked because a new record was going to be created but in fact never was. How can I prevent this from happening?

My create new record code is as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnAddNewRec_Click()
  2.  
  3.     DoCmd.GoToRecord , , acNewRec
  4.  
  5.     If Me.NewRecord Then
  6.     Me.btnSaveNewRec.Visible = True
  7.     Me.[txtCoordArea].Locked = False
  8.     Me.[Full Name].Locked = False
  9.     Me.[Badge ID].Locked = False
  10.     Me.[SSN Last 5].Locked = False
  11.     Me.[Term Date].Locked = False
  12.     Me.[Requester Name].Locked = False
  13.     Me.[Supplier].Locked = False
  14.  
  15.     Else
  16.     If Not Me.NewRecord Then
  17.     Me.[txtCoordArea].Locked = True
  18.     Me.[Full Name].Locked = True
  19.     Me.[Badge ID].Locked = True
  20.     Me.[SSN Last 5].Locked = True
  21.     Me.[Term Date].Locked = True
  22.     Me.[Requester Name].Locked = True
  23.     Me.[Supplier].Locked = True
  24.  
  25.     End If
  26.  
  27.     End If
  28.  
  29. End Sub
  30.  
My Save New Record Code is:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. On Error GoTo ErrorMessage
  3.  
  4. Dim LResponse As Integer
  5.  
  6.     If Me.Dirty Then LResponse = MsgBox("Do you wish to SAVE your changes?", vbYesNo)
  7.  
  8.         If LResponse = vbYes Then    'User chose Yes - Updated
  9.         [txtDateRecordUpdated].Value = Now()
  10.         [RecUpdated].Value = True
  11.         [txtRecordUpdatedBy].Value = Forms!frmUtility!Full_Name
  12.         DoCmd.RunCommand acCmdSave
  13.  
  14.     Else    'User chose No - Not Updated
  15.  
  16.     End If
  17.  
  18.     Me.[txtCoordArea].Locked = True
  19.     Me.[Full Name].Locked = True
  20.     Me.[Badge ID].Locked = True
  21.     Me.[SSN Last 5].Locked = True
  22.     Me.[Term Date].Locked = True
  23.     Me.[Requester Name].Locked = True
  24.     Me.[Supplier].Locked = True
  25.  
  26.     Me.btnSaveNewRec.Visible = False
  27.  
  28. Exit_Form_BeforeUpdate:
  29.     Exit Sub
  30.  
  31. ErrorMessage:
  32.     MsgBox Err.Description
  33.     Resume Exit_Form_BeforeUpdate
  34.  
  35. End Sub
  36.  
Any assistance would be greatly appreciated.
Nov 14 '13 #1
Share this Question
Share on Google+
5 Replies


P: 9
After I took a short break and stepped away from my coding, I realized I could perform this function using the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. '
  3.    If Not Me.NewRecord Then
  4.       Me.btnSaveNewRec.Visible = False
  5. '
  6.       Me.[txtCoordArea].Locked = True
  7.       Me.[Full Name].Locked = True
  8.       Me.[Badge ID].Locked = True
  9.       Me.[SSN Last 5].Locked = True
  10.       Me.[Term Date].Locked = True
  11.       Me.[Requester Name].Locked = True
  12.       Me.[Supplier].Locked = True
  13. '
  14.     End If
  15. '
  16. End Sub
  17.  
Nov 14 '13 #2

zmbd
Expert Mod 5K+
P: 5,287
Chip.
Glad you found the on_current event of the form.

Might I make a few suggestions to clean up your code:

Refering to JUST The first block of code in the original posting:
1) Take lines 5 thru 24 in 1st code block (btnAddNewRec_Click) of post 1
1a) move these to a new sub within the form's code and name it "toggle_field_state"
1b) Alter your code to this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnAddNewRec_Click()
  2.  
  3.     DoCmd.GoToRecord , , acNewRec
  4.     Call toggle_field_state
  5.  
  6. End Sub
  7.  
2) in the on current event of the form reuse your state toggling code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     Call toggle_field_state
  4.  
  5. End Sub
3) There's no error trapping here. May not really be needed; however, I prefer to have error trapping in all my code just to keep things from the user.

4) Lines 15 and 16 can be combined into the "ELSEIF" structure, or, indeed, better yet line 16 can be deleted as the current record is either new or not; thus, no real need to test it twice, in either case you will need to remove the "ENDIF" associated with line 16 located at line 25.

If the second block of code was a second question then you need to split that question into a new thread.

And I haven't looked at your second post closely; however, you really should get used to indenting things properly. It makes the code easier to follow... I've done this for you in the second post. (^-^).

-z
Nov 14 '13 #3

ADezii
Expert 5K+
P: 8,599
The General Idea is as follows:
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Dim ctl As Control
  3.  
  4. For Each ctl In Me.Controls
  5.   ctl.Locked = (Not Me.NewRecord)
  6. Next
  7.  
  8. 'Can Restablish Error Checking here...
Nov 14 '13 #4

zmbd
Expert Mod 5K+
P: 5,287
Nice loop, one I use myself provided that I want every control locked, including the command buttons :)
Although, I usually toss in a check for the control type, the control name, or for some value in the tag property so that I don't lock the command button.
Nov 14 '13 #5

NeoPa
Expert Mod 15k+
P: 31,186
I would go with the following to ensure only new records can be added :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Me.AllowEdits = Me.NewRecord
  3. End Sub
Nov 14 '13 #6

Post your reply

Sign in to post your reply or Sign up for a free account.