locking records issues (1 Viewer)

[Db-why-not]

Hello,

I want to create a button that locks individual records in my database on the forms, so accidental edits don't happen. I used the code and followed the instructions from this website http://allenbrowne.com/ser-56.html.
The code does work, but it either locks all the records or unlocks all the records, not just individual records. You have to click the button to unlock or lock every time. The way I have my forms set up. I have buttons that open up subforms in a subform container. The subforms don't lock/unlock automatically when they open/load from the Main form. I have to press the unlock button to change each subform that I open. I tried putting this code =LockBoundControls([Form],True) on the On Load setting for each subform. Then every subform that is opened is always locked when it first loads in the subform container.

I want each subform to open locked if the locked button is selected or open unlocked if the unlocked button is selected. I want each individual record when it is pulled up to already be locked or unlocked when I scroll through all my records on my forms. I made a field called Locked_Record in one of my New_Patients tables that is a YES/NO field. I want to use this field to determine if my forms will be locked or unlocked depending on what record is being viewed in my forms.
I have different lookup fields on my main form that cannot be locked, the code I was using was working with those fields.

Can I modify the code I am using from the http://allenbrowne.com/ser-56.html. website or do I need to use completely different code to get the function that I want? Thank you.

 

[theDBguy]

Hi. Programming is all about logic. If you can apply your logic to Allan's code, then you should be able to modify it to suit your needs.

 

[HiTechCoach]

In Access, a subform control is used to embed a form into a Parent form. You do not open subforms. They are loaded automatically with the Parent (main) form loads.

The code from Allen Browne is designed to work with a form that has subform control on the form,

... The way I have my forms set up. I have buttons that open up subforms in a subform container. The subforms don't lock/unlock automatically when they open/load from the Main form. I have to press the unlock button to change each subform that I open.

It sounds like you are open additional forms from yu main form. The additional forms opened from the main form may include subform controls.

If this is true, then your deign logic does bot what the standard Parent form with subform control logic.

Can I modify the code I am using from the http://allenbrowne.com/ser-56.html. website or do I need to use completely different code to get the function that I want? Thank you.

If it were my project using your design method (logic), I would use some different code.

If you want to completely lock a form from edits, then I simply set the form's AllowEdits property to false. To unlock, set the AllowEdits property to True

In some of my Access applications, I use a New, Edit, Save, and Delete buttons on my forms to help prevent accidentally editing data.

In other Access applications, when a user edits an existing I use the form's before update event to confirm they want to save the changes before the changes are saved.

 

[bastanu]

Just a stab in the dark as we don't know your exact design, but have you tried using something simple like this:

Private Sub Form_Current()
Me.AllowEdits = Not Me.Locked_Record 'where Locked_record is your Yes/No field
End Sub

Cheers,
Vlad

 

[zeroaccess]

I set allowedits = false until a button is clicked.

 

[Db-why-not]

Just a stab in the dark as we don't know your exact design, but have you tried using something simple like this:

Private Sub Form_Current()
Me.AllowEdits = Not Me.Locked_Record 'where Locked_record is your Yes/No field
End Sub

Cheers,
Vlad

I haven't tried that yet. I'm thinking of having a checkbox field called record_lock on my main form. In the on load event If record_lock field is checked then it would lock certain fields on my form. Then I will have a button that will check or uncheck that record_lock field. After update event I would have code that locks or unlocks fields. Each of my subforms I think I will have to add the same code on On Current event. I haven't written the code yet but I will try this week. I will post it once I get it working. I'm still pretty new to VBA coding but I have been learning a lot from looking at other people's code and just googling and using this forum. It would be nice to contribute and share some code that someone else might use.

 

[bastanu]

Good luck with your project, locking/unlocking individuals controls gets a bit more complicated, please feel free to examine and use my free utility/code that allows custom access levels: http://forestbyte.com/ms-access-utilities/fba-custom-access-levels/

Cheers,
Vlad

 

[Db-why-not]

I wrote some VBA code and got it to work for my main form. I created a Record_Lock checkbox that is on my main form. I created a toggle button that opens a popup message form where you can click ok or cancel. The pop up message form gives you a message That says you are unlocking or locking a record and if you clicked it on error then press cancel with an ok or cancel button. If you select ok it runs the following code: then closes the form. If you click cancel, nothing happens and the pop up form closes without running the code.

Private Sub Cmd_OK_Click()

'Checks or unchecks the Record_lock checkbox
If Forms![New_PT_Main_Form].Form.chbx_Record_lock.Value = True Then
Forms![New_PT_Main_Form].Form.chbx_Record_lock.Value = False
Else
Forms![New_PT_Main_Form].Form.chbx_Record_lock.Value = True
End If

'Locks records for editing if Record_Lock is checked
If Forms![New_PT_Main_Form].Form.chbx_Record_lock.Value = True Then
Forms![New_PT_Main_Form].Form.Chron.Locked = True
Forms![New_PT_Main_Form].Form.Research_ID.Locked = True
Forms![New_PT_Main_Form].Form.Rct_Red.Visible = True 'Makes a Red rectangle visible so you can tell that records are locked for editing
Forms("New_PT_Main_Form").Pt_Status.Form.Cmb_status.Locked = True
Forms("New_PT_Main_Form").Pt_Status.Form.chbx_Still_Admitted.Locked = True
Forms("New_PT_Main_Form").Pt_Status.Form.chbx_PT_all_data_validated.Locked = True
Forms("New_PT_Main_Form").Pt_Status.Form.txtbx_Data_last_updated.Locked = True

Else
Forms![New_PT_Main_Form].Form.Chron.Locked = False
Forms![New_PT_Main_Form].Form.Research_ID.Locked = False
Forms![New_PT_Main_Form].Form.Rct_Red.Visible = False ' Hides the red rectangle when records can be edited
Forms("New_PT_Main_Form").Pt_Status.Form.Cmb_status.Locked = False
Forms("New_PT_Main_Form").Pt_Status.Form.chbx_Still_Admitted.Locked = False
Forms("New_PT_Main_Form").Pt_Status.Form.chbx_PT_all_data_validated.Locked = False
Forms("New_PT_Main_Form").Pt_Status.Form.txtbx_Data_last_updated.Locked = False

End If

'Closes the Lock Message Box
DoCmd.Close
End Sub


I still need to write the code for all my subforms that my buttons open in my subform container, but I got the main form to work. I was excited I did it all on my own.

 

[isladogs]

In situations where you need to manage a group of controls at one e.g. To lock multiple controls, I would recommend using the Tag property.
Doing so would reduce the code in the If and Else sections of your code to one line each.
See my example app showing how this is done: Set Controls

 

[Db-why-not]

Hello,
I got my forms mostly working how I want them with record locking but Im having some issues with 2 of my forms. I have my main form with the Record_lock checkbox. IF it is checked yes, then it triggers all the forms and subforms to not AllowEdits or lock specific controls. Two of my forms keep causing the forms to freeze up though. My main form has a subform container "Display"on it. There are also buttons on my main form that open up different forms within the subform container. I have a button called microbiology. It opens up Micro_results2 form in the subform container. Micro_results2 form has two other subforms nested on it. Micro_details form(has another nested form micro_reports within it) and Micro_results_form.

After I start to enter new data on the Micro_results2 form as soon as I get off of the controls(date_results, Lab_type, positive_lab) and when I try to enter in data in the Results control memo field my forms stop working. I get any error messages, IT just wont let me enter any more data or look at other records. I have to close out of the form to get the form to work again.

It also does this for my US_CXR_CT2 Form. This form is also a nested form. When I click on the US_SCan button it has a subform container called US_display, there are buttons CXR/CT Finding button that opens US_CXR_CT2 Form within it.
On this form there are also 3 nested subforms. I think it has to do with the memo field I have on this form also.
I have US_CXR_CT_finding form, US_CXR_CT_Imaging details(Nested subform US_imaging_reports which has a memo field on it), US_CXR_CT_Imaging_list form.

I will post some of the code that I have for the Micro_Results2 form.

VBA Code for my Micro_results2 form Option Compare Database Option Explicit Private Sub Form_Current() 'Locks/unlocks subform container for editing if chbx_Record_Lock is checked from the main form Forms("New_PT_Main_Form").Display.Form.Opt_Micro.Locked = Forms![New_PT_Main_Form].Form.chbx_Record_lock Forms("New_PT_Main_Form").Display.Form.Date_Last_Checked_labs.Locked = Forms![New_PT_Main_Form].Form.chbx_Record_lock 'Reveals or hides the subforms depending on if tests were completed If Opt_Micro.Value = 1 Then Forms("New_PT_Main_Form").Display.Form.Micro_details.Form.Visible = True Forms("New_PT_Main_Form").Display.Form.Micro_Results_Form.Form.Visible = True Else Forms("New_PT_Main_Form").Display.Form.Micro_details.Form.Visible = False Forms("New_PT_Main_Form").Display.Form.Micro_Results_Form.Form.Visible = False End If End Sub Private Sub Opt_Micro_AfterUpdate() 'Updates the dates, when changes are made to the form Forms("New_PT_Main_Form").Pt_Status.Form.txtbx_Data_last_updated = Now() Me.txtbx_M_YN_Last__Updated = Now() 'Reveals or hides the Micro forms depending on if tests were completed If Opt_Micro.Value = 1 Then Forms("New_PT_Main_Form").Display.Form.Micro_details.Form.Visible = True Forms("New_PT_Main_Form").Display.Form.Micro_Results_Form.Form.Visible = True Else Forms("New_PT_Main_Form").Display.Form.Micro_details.Form.Visible = False Forms("New_PT_Main_Form").Display.Form.Micro_Results_Form.Form.Visible = False End If End Sub Private Sub Opt_Micro_DblClick(Cancel As Integer) 'Changes the Radio button to the No selection option If Forms("New_PT_Main_Form").Display.Form.Opt_Micro.Locked = False Then Forms("New_PT_Main_Form").Display.Form.Opt_Micro.Value = 3 End If 'Reveals or hides the subforms depending on if patient has any labs If Opt_Micro.Value = 1 Then Forms("New_PT_Main_Form").Display.Form.Micro_details.Form.Visible = True Forms("New_PT_Main_Form").Display.Form.Micro_Results_Form.Form.Visible = True Else Forms("New_PT_Main_Form").Display.Form.Micro_details.Form.Visible = False Forms("New_PT_Main_Form").Display.Form.Micro_Results_Form.Form.Visible = False End If End Sub

Just wondering if my code is causing the problem. Im not getting any error messages, the form just stops working. Its only for the Micro_results2 form and the US_CXR_CT2 form that cause my forms to stop working. When I add new data anywhere else its fine. I really suspect if might have something to do with my memo fields/long text fields on those forms.

Just wondering if anyone can help me. I can post the forms and database also if that would be helpful. Thanks.

 

[Db-why-not]

Just a stab in the dark as we don't know your exact design, but have you tried using something simple like this:

Private Sub Form_Current()
Me.AllowEdits = Not Me.Locked_Record 'where Locked_record is your Yes/No field
End Sub

Cheers,
Vlad

I took your code and used it in my forms. Its less code. Thanks.

 

[Pat Hartman]

Some information that will make this job easier and hopefully cause you to move your code to more appropriate events.

1. subforms are loaded PRIOR to mainforms so if you are running code in the mainform to lock a subform, it runs AFTER the subform loads which may or may not have an impact.
2. There is absolutely NO reason you have to lock every record when only the CURRENT record is editable so, if you want to control this with a button on the main form, put the code that does the locking, in the Current event of the subform. As soon as a record becomes "current", it is locked or unlocked based on the code that runs in the current event of the SUBFORM.
3. Setting AllowEdits to No also locks unbound search boxes which is a PITA.
4. You can STOP ANY update in any form by putting code in the Form's on Dirty event so you don't even have to "lock" anything. In the dirty event, you can check the setting on the main form and if it says, no edits, undo the update and that's that.
Me.Undo
5. Leaving AllowDeletes as No is usually appropriate. That doesn't prevent buttons or unbound controls from working. You can use a button to toggle the AllowDeletes property.