Protecting/unprotecting form...going the next step

[gddrew]

I have read other messages here and have learned how to create a button and use it to protect and unprotect a form.

Use the following code for the form's On Load event:

    With Me
      .AllowAdditions = False
      .AllowDeletions = False
      .AllowEdits = False
    End With

Create a button on the form labeled "Edit" and code the On Click event as such:

    With Me
      .AllowAdditions = True
      .AllowDeletions = True
      .AllowEdits = True
    End With

As is, this works ok...better than nothing. But there are a couple of things that could be improved, specifically:

1. I added a second button to click after the edit was complete to try set the form back to read-only. It didn't work. Appears that the only way to reset the form to read-only is to go to another record.
2. There is a drop-down list in the form's footer. The purpose of this form is to find a specific record by last name and display it without manually scrolling through all records. Problem is, when the form is read-only, this search field doesn't work.

Any suggestions (especially with code) would be greatly appreciated.

 

[LarryDeLaruelle]

gddrew:

It is a more cumbersome method to use, but I set the locked property of each object on the form to True in the On Open event and then unlock them when the edit command or add record command buttons are pressed. I reset them to locked when the save or undo command buttons are clicked.

This requires an extra effort to type in the code to set them locked (in the On Open event) but then I do copy/paste to each of the other events and find/replace to change the status to true or false as necessary.

This works for me but I would be interested in hearing how others address this issue.

Hope this helps.
Larry De Laruelle
larry1de@yahoo.com

 

[gddrew]

Larry:

Could you please provide a code example? Also, I am using Access for Office XP (though the default format is Access 2000).

Thanks,
Greg

 

[gusbrunston]

Hi Greg:

I have a "Save and lock" button with the following code that works for me:

Private Sub cmdSaveAndLock_Click()
On Error GoTo Err_cmdSaveAndLock_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    With Me
        .AllowEdits = False
    End With

Exit_cmdSaveAndLock_Click:
    Exit Sub

Err_cmdSaveAndLock_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveAndLock_Click
    
End Sub

Good luck. Gus Brunston An old PICKer, using Access2000
gustrel@aol.com

 

[LarryDeLaruelle]

Greg:

Happy to oblige. The sample below is incomplete since it doesn't have the on error and exit coding; I cut that off to keep the post shorter and only have a couple of the form's controls shown to give you the idea.

Essentially, when the form opens it sets the locked property of the controls to true so that the user cannot make any changes unless they click on edit (for the current record) or add for a new record. I also set the enabled property of the control buttons so that they can only use the save/undo buttons while editing or adding records.

Private Sub Form_Open(Cancel As Integer)
fraActive.Locked = True
cboType.Locked = True

cmdAdd.Enabled = True
cmdEdit.Enabled = True
cmdSave.Enabled = False
cmdUndo.Enabled = False
cmdClose.Enabled = True

Private Sub cmdAdd_Click()
fraActive.Locked = False
cboType.Locked = False

cmdAdd.Enabled = False
cmdEdit.Enabled = False
cmdSave.Enabled = True
cmdUndo.Enabled = True
cmdClose.Enabled = False

Private Sub cmdEdit_Click()
fraActive.Locked = False
cboType.Locked = False

cmdAdd.Enabled = False
cmdEdit.Enabled = False
cmdSave.Enabled = True
cmdUndo.Enabled = True
cmdClose.Enabled = False

Private Sub cmdSave_Click()
fraActive.Locked = True
cboType.Locked = True

cmdAdd.Enabled = True
cmdEdit.Enabled = True
cmdSave.Enabled = False
cmdUndo.Enabled = False
cmdClose.Enabled = True

Private Sub cmdUndo_Click()
If Dirty Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If

fraActive.Locked = True
cboType.Locked = True

cmdAdd.Enabled = True
cmdEdit.Enabled = True
cmdSave.Enabled = False
cmdUndo.Enabled = False
cmdClose.Enabled = True

As I indicated earlier, you really only need to type in the code once then use the copy paste and edit/replace to modify for each of the other events.

Good luck. Let me know if this needs any additional clarification.

Larry De Laruelle
larry1de@yahoo.com

 

[gddrew]

Thanks for the input guys. I must not be explaining myself very well because none of this seems to work for me.

 

[LarryDeLaruelle]

Greg:

I reread your original post to make sure I understood what you were asking. I have encountered the same problem with look-up combos if you set the form's edit property to false. That's why I use the locked property on a control by control basis -- basically locking all of the data controls except the combo look-up.

I hate leaving one of these 'unfixed', so . . .

What's not working? And, what are you trying to do?

Let me know and I'll see if I can figure out a solution.
Larry De Laruelle
larry1de@yahoo.com

 

[gddrew]

Larry:

Thanks for your patience. When it comes to actual coding, I'm very new out of the gate. I probably just don't understand where to put some things.

First of all, here's a sample of my form:

When I load the form, I essentially want it to be read-only. The only fields that should not be protected are the Membership ID field (since it's an autonumber and is protected by default), and the Search for a member combo box.

This is all the code that's behind the form at this time:

Option Compare Database

Private Sub cboSearch_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[MemberID] = " & Str(Nz(Me![cboSearch], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Form_Open(Cancel As Integer)

End Sub

If I were to set the form to read only, the search combo box wouldn't work. Therefore I want it unprotected but all other fields protected. I want it so that an "Update" button has to be clicked before the form will allow any edits. Then either by clicking a "Save" button or by moving to another record, the fields become protected again.

I think I just don't understand where to put the code on the form.

Thanks for your help. If it would help you, I can zip the DB and upload it to a website so you can download.

 

[gusbrunston]

Dear Greg:

I never saw a form display on a thread like that. Can you give me a quick idea how you did that?

Tbanks very much. Gus Brunston An old PICKer, using Access2000
padregus@attbi.com

 

[gddrew]

Gus:

It's easy to do:

1. Take a screen capture and save as a .gif or .jpg file.
2. Upload to a web server.
3.

 

[gddrew]

Oops! Hit the submit button in error...
Step 3. Include the following tag in your message

"[img]http://www.mysite.com/images/image.gif[/img]"

where what follows the "

http://"

is the URL to your website. For example, the code for my form image is "

http://home.att.net/~drewgre/form.gif."

BTW, the instructions for this appear on the page that displays if you click the Preview Post button.

Regards,
Greg

 

[gusbrunston]

Thank you. Gus Brunston An old PICKer, using Access2000
padregus@attbi.com

 

[LarryDeLaruelle]

Greg:

The code sample I sent probably wasn't the best way to demonstrate what I intended to convey.

I have a small sample database that will gives a much better view of how I protect the existing data from inadvertant changes.

If you are interested, give me an e-mail address and I'll send it to you.
Larry De Laruelle
larry1de@yahoo.com

 

[gddrew]

Larry:

My e-mail address is drewgre@worldnet.att.net.

Many thanks,
Greg