We help IT Professionals succeed at work.

Microsoft Access edit record button for form & sub-form.

bjmarcley
bjmarcley used Ask the Experts™
on
Hi All:

I would like my records to be locked upon opening and use an edit button to unlock the form & sub-form for editing.  

My button only opens the main form for editing.  

Could anyone help with the code to unlock both the main form & sub-form.  

Thanks in advance!

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Aman SubhanOracle Database Administrator

Commented:
You need to set value of the field to locked=1
and then set a macro to target a button to unlock it!!!

well my friend did that a time ago... and i dont noe da exact code of it...
so i'll contact my friend and see what i can do!!
Have you tried

Me.AllowEdits = True
Me!subformcontrol.Form.AllowEdits = True
Top Expert 2016

Commented:
if suggestion of fionafenton does not do it,
upload a copy of the db..
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
 Attached is some routines that I use.  This is old code and it could stand a cleanup, but it does work.

  StdLockForm is setup to only lock controls in the detail section of a form, as I have a "control" panel in the form header that I wish to remain active.  In the past, before tab controls were available, I also had buttons in the footer to emulate a multi-tab form.   That's no longer needed (as we have a tab control now), but the lock/unlock code hasn't been changed (this is actually code from Access 2.0 days<g>).
  You can also use a controls Tag property to flag which controls you want to lock/unlock (by looking for specific text in that property) or name controls in a specific way
 The CRUD buttons (Create, Read, Update, Delete) buttons are a little more complex to do.  I used a module level variable to hold the forms status and SetFormMode() to set the mode (Add, Edit, Inquiry/View).  I've attached that as well.   Last, I set the buttons available in the control panel based on the form mode with the procedure StdVisible().
  Of course there are many ways to do this and this is just one way.
HTH,
JimD.

Function LockForm(FormName As String)
  
  Dim frm As Form

  Set frm = Forms(FormName)
  LockForm = StdLockForm(frm, True)

End Function

Function UnLockForm(FormName As String)
  
  Dim frm As Form

  Set frm = Forms(FormName)
  UnLockForm = StdLockForm(frm, False)

End Function

Function StdLockForm(frm As Form, intState As Integer)
  
  'Lock/unlock all the fields in the detail section of the form that are enabled.

  Dim intL As Integer              'Generic loop counter
  Dim intCount As Integer          'Number of controls on the form
   
  intCount = frm.Count             'Find out how many controls are on the form

  On Error Resume Next
  For intL = 0 To intCount - 1      'Step thru all controls on the form
    If (frm(intL).Section = False) Then 'Don't do any control execpt those in the detail section
        If (frm(intL).Enabled = True) Then frm(intL).Locked = intState   'Set 'Locked' property to proper state
    End If
  Next intL
  On Error GoTo 0

End Function

Sub SetFormMode(frm As Form)

    If IsNull(frm.RecordSource) Then
      gstrFormMode = "U"
    Else
      If InStr(frm.Caption, "- Adding record") Then
        gstrFormMode = "A"
      ElseIf InStr(frm.Caption, "- Editing record") Then
        gstrFormMode = "E"
      ElseIf InStr(frm.Caption, "- Inquiry/View only") Then
        gstrFormMode = "I"
      ElseIf InStr(frm.Caption, "- Viewing record") Then
        gstrFormMode = "V"
      Else
        gstrFormMode = "?"
      End If
    End If

End Sub

Function StdVisible(frm As Form, strState As String)
    
  Dim rstForm As Recordset
  
  Const Routine = "StdVisible"
  Const Version = "1.2.1"
    
    ' Make buttons visible/invisible according to strState
    ' Options are:
    '  "Default" - All buttons except 'Save' and 'Cancel' visible, Lookup box visible.
    '  "AddEdit" - Only 'Save' and 'Cancel' buttons visible (for Add/Edit mode).
    '  "Close  " - Only 'Close' button is showing.  Used for Inquiry Only mode.
    '  "None   " - All buttons not visible.

  On Error Resume Next
  
  Select Case (strState)
    Case "Default"
      ' Empty Recordset?
      Set rstForm = frm.RecordsetClone
      
      If rstForm.RecordCount = 0 Then
        'Only ADD and CLOSE button should be visible.
        frm!lblLookupLabel.Visible = False
        frm!ctlLookup.Visible = False
        frm!ctlLookupControl.Visible = False
        frm!ctlNavBtnFirst.Visible = False
        frm!ctlNavBtnPrev.Visible = False
        frm!ctlNavBtnNext.Visible = False
        frm!ctlNavBtnLast.Visible = False
        frm!ctlAddBtn.Visible = True
        frm!ctlEditBtn.Visible = False
        frm!ctlCloseBtn.Visible = True
        frm!ctlDeleteBtn.Visible = False
        DoCmd.GotoControl "ctlAddBtn"          'Get focus off 'Save' button so we can...
      'ElseIf rstForm.RecordCount = 1 Then
      '  'Make everything visible except next/prev/first/last
      '  frm!lblLookupLabel.Visible = True
      '  frm!ctlLookup.Visible = True
      '  frm!ctlLookupControl.Visible = True
      '  frm!ctlNavBtnFirst.Visible = False
      '  frm!ctlNavBtnPrev.Visible = False
      '  frm!ctlNavBtnNext.Visible = False
      '  frm!ctlNavBtnLast.Visible = False
      '  frm!ctlAddBtn.Visible = True
      '  frm!ctlEditBtn.Visible = True
      '  frm!ctlCloseBtn.Visible = True
      '  frm!ctlDeleteBtn.Visible = True
      '  DoCmd.GotoControl "ctlEditBtn"         'Get focus off 'Save' button so we can...
      Else
        'Make everything visible
        frm!lblLookupLabel.Visible = True
        frm!ctlLookup.Visible = True
        frm!ctlLookupControl.Visible = True
        frm!ctlNavBtnFirst.Visible = True
        frm!ctlNavBtnPrev.Visible = True
        frm!ctlNavBtnNext.Visible = True
        frm!ctlNavBtnLast.Visible = True
        frm!ctlAddBtn.Visible = True
        frm!ctlEditBtn.Visible = True
        frm!ctlCloseBtn.Visible = True
        frm!ctlDeleteBtn.Visible = True
        DoCmd.GotoControl "ctlEditBtn"         'Get focus off 'Save' button so we can...
      End If
      
      frm!ctlSaveBtn.Visible = False
      frm!ctlCancelBtn.Visible = False

    Case "AddEdit"
      'Make only 'Save' and 'Cancel' buttons visible
      frm!ctlSaveBtn.Visible = True
      frm!ctlCancelBtn.Visible = True

      DoCmd.GotoControl "ctlSaveBtn"      'Get focus off 'Add' or 'Edit' so we can...
      frm!lblLookupLabel.Visible = False
      frm!ctlLookup.Visible = False
      frm!ctlLookupControl.Visible = False
      frm!ctlNavBtnFirst.Visible = False
      frm!ctlNavBtnPrev.Visible = False
      frm!ctlNavBtnNext.Visible = False
      frm!ctlNavBtnLast.Visible = False
      frm!ctlAddBtn.Visible = False
      frm!ctlEditBtn.Visible = False
      frm!ctlCloseBtn.Visible = False
      frm!ctlDeleteBtn.Visible = False

    Case "Close"
      'Make only 'Close' button visible
      frm!ctlCloseBtn.Visible = True

      DoCmd.GotoControl "ctlCloseBtn"
      frm!lblLookupLabel.Visible = False
      frm!ctlLookup.Visible = False
      frm!ctlLookupControl.Visible = False
      frm!ctlNavBtnFirst.Visible = False
      frm!ctlNavBtnPrev.Visible = False
      frm!ctlNavBtnNext.Visible = False
      frm!ctlNavBtnLast.Visible = False
      frm!ctlAddBtn.Visible = False
      frm!ctlEditBtn.Visible = False
      frm!ctlDeleteBtn.Visible = False
    
    Case "None"
      frm!lblLookupLabel.Visible = False
      frm!ctlLookup.Visible = False
      frm!ctlLookupControl.Visible = False
      frm!ctlNavBtnFirst.Visible = False
      frm!ctlNavBtnPrev.Visible = False
      frm!ctlNavBtnNext.Visible = False
      frm!ctlNavBtnLast.Visible = False
      frm!ctlEditBtn.Visible = False
      frm!ctlSaveBtn.Visible = False
      frm!ctlDeleteBtn.Visible = False
      frm!ctlAddBtn.Visible = False
      frm!ctlCancelBtn.Visible = False
      frm!ctlCloseBtn.Visible = False
    
    Case Else
      'Invalid command - ignore.
  
  End Select

  On Error GoTo 0
  
  Set rstForm = Nothing

End Function

Open in new window

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
BTW, here's a picture of one of my forms with the control panel.
JimD.

Region-Capture.bmp

in both your form and subform
go to the properties set them as follows
AllowDeletions = False
AllowAdditions = False
AllowEdits = False


then in your edit button in the onclick events
turn them back on

'-----------------------------------
me.allowDeletions= True
Me.allowAdditions = true
me.allowEdits = true
Me!subformname.Form.AllowDeletions = True
Me!subformname.Form.AllowAdditions = True
Me!subformname.Form.AllowEdits = True
'---------------------------------

hope this helps