none
RecordSet Type Assistance RRS feed

  • Question

  • Hello –

     

    I am in need of some assistance with the below code. Code works great; however, the code does not execute on the current record. Code executes, unlocking the form, but  then jumps to the first record…not sure why? I believe it may have something to do with the changing of the RecordSet Type method I am using?

     

    Fyi – form (subform) opens in RecordSet Type: Snapshot (2). This is done to protect data until or unless user needs to edit the data. Then user clicks the Edit button (code below) to unlock the form to edit the data. When user Saves (button), form is set back to RecordSet Type: SnapShot (2) to lock the form (subform). Everything works well except for jumping to the first record when the form unlocks.

     

    Anyone have any ideas as to how I might get the below to unlock the current record only? Maybe there’s a better way of doing this? The below method of changing the RecordSet Type, to basically lock/unlock a form/subform, I found through research and seems to work well with all other scenarios; except this one I am open to suggestions if there might be an easier/better way of doing this?

     

     

    Private Sub Edit_Click()

     

        Const conDynaset = 0 'unlocks form

       

        Me.Save2.Visible = True   ‘button on main form

        Me.Save2.SetFocus   ‘button on main form

        Me.Edit.Visible = False   ‘button on main form

       

        Me![Service_Details_Subform_Main].SetFocus ‘subform

        DoCmd.GoToControl "SvcDate"  ‘subform

        Me![Service_Details_Subform_Main].Form.RecordsetType = conDynaset 'unlock form

     

    End Sub

     

     

    Any ideas or suggestions are always much appreciated. Thank you in advance for your time!



    • Edited by rstreets2 Thursday, May 2, 2019 12:01 AM Typo
    Wednesday, May 1, 2019 11:57 PM

Answers

  • Rather than this:

    Me![Service_Details_Subform_Main].Form.RecordsetType = conDynaset 'unlock form

    use this:

    Me![Service_Details_Subform_Main].Form.AllowEdits = True

    (you probably want to set it to False somewhere else.)
    This is the official way to lock a form for data entry.


    -Tom. Microsoft Access MVP

    • Marked as answer by rstreets2 Thursday, May 2, 2019 10:29 PM
    Thursday, May 2, 2019 1:47 AM

All replies

  • Rather than this:

    Me![Service_Details_Subform_Main].Form.RecordsetType = conDynaset 'unlock form

    use this:

    Me![Service_Details_Subform_Main].Form.AllowEdits = True

    (you probably want to set it to False somewhere else.)
    This is the official way to lock a form for data entry.


    -Tom. Microsoft Access MVP

    • Marked as answer by rstreets2 Thursday, May 2, 2019 10:29 PM
    Thursday, May 2, 2019 1:47 AM
  • You might like to take a look at LockForm.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

    This little demo file illustrates another means of locking/unlocking a form's records.  It does so by setting the Enabled and Locked properties of tagged bound controls.  This, unlike setting the AllowEdits property to False, allows unbound controls such as the navigational multi-select list box in the demo to continue to be enabled.

    When the user navigates to an empty new record, the controls are automatically enabled and the command button which allows the user to toggle between a locked and unlocked state is temporarily hidden.

    Ken Sheridan, Stafford, England

    Thursday, May 2, 2019 11:06 AM
  • Thank you Tom! Your suggestion worked perfectly! Hard to believe through hours of research I did not come across something like this. Will be able to utilize for many other projects to come.

    Thank you, thank you, thank you! Your assistance is always appreciated!

    Thursday, May 2, 2019 10:29 PM
  • Hi Ken -

    Thank you for your response and recommendation. I did come across something very similar to your LockForm-Zip example; which gave me a couple of other ideas also. This method did work well in most instances, but unfortunately not all. I would rather just unlock all fields on the form, rather than selected fields. Tom's method above does this in a simple manner, unlocking/locking the entire form. I also added in some .Allowaddtions, .AllowDeletions, using Tom's method, which made it even more secure; being able to toggle these on and off where and when needed.

    Thank you again, Ken, for your assistance. Much appreciated!

    Best regards


    • Edited by rstreets2 Thursday, May 2, 2019 10:39 PM typo
    Thursday, May 2, 2019 10:38 PM