Edit button.

Question

I was wondering if there was a way, (without vba) to lock and unlock a child subform/subreport via a toggle button. I want to make a big red EDIT MODE button, so people know when they can edit the data. I can see "Locked" under the data tab in the property sheet but can not seem to find anything that can edit property data.

Answer 1

Why not learn some simple VBA commands and expand your knowledge. There are tons of examples in the Help system. It's not as difficult as you might think. The code you would use is less typing than it took you to post your question.

Toggle on:

Me!Subformname.Form.AllowAdditions = True

Me!Subformname.Form.AllowDeletions = True

Me!Subformname.Form.AllowEdits = True

Toggle off:

Me!Subformname.Form.AllowAdditions = False

Me!Subformname.Form.AllowDeletions = False

Me!Subformname.Form.AllowEdits = False

Answer 2

Even using a Toggle you will have to program the Toggle to do something but as ATGNWT said it isn't really hard. You just need an example to follow. Here is one

Use the Wizard and Choose the Option Group and within choose your Labels and that you want it to be Toggle Buttons. Here is an image.

Then just click in Design View the First Toggle and select in the Properties Sheet the ... Next to the On Mouse Down Event and add code like this.

Private Sub Toggle301_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.DataEntry = False
    Me.AllowEdits = False
    Me.AllowAdditions = False
    Me.AllowDeletions = False
End Sub

Then for the other something like

Private Sub Toggle302_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.DataEntry = False
    Me.AllowEdits = True
    Me.AllowAdditions = True
    Me.AllowDeletions = True
End Sub

Hth

---

Chris Ward

Answer 3

You can use an embedded macro for this.

• Create a Toggle Button, name it "tglLock", Caption Lock/Unlock
• Set it's default value to true (locked)
• Now create an embeded macro for the Form Before Update event.
• Add following code to the macro:
  1. command: If, expression: Controls("tglLock")=True
  2. command: MessageBox, Message "not allowed"
  3. command: CancelEvent

Now open your form. The toggle will be locked (activated/pressed down)

You now can change any data. But if you want to save it you get a message box with "not allowed".

If you now unlock the Toggle (appears to be up) you can save the data.

No VBA code involved, but an embedded macro that will also run if you don't have VBA enabled.
There could be other possibilities with a command button and an invisible checkbox on the form but the mechanism remains the same. I choosed the Togglebutton because it can hold the State True and False and looks like a Command Button. You also may change the backcolor of the Toggle button in another macro that fires when you click it.

HTH

Henry

Answer 4

How do I setup the 'toggle on' and 'toggle off' parts. I am using a toggle button, because I can make it bright red while it is on. the button returns -1 while edit mode is supposed to be on and 0 when it is supposed to be off.  I thought maybe a onclick() if(button=-1) (toggleon commands) else (toggleoff commands). I'm not sure it should be that or something like while (button=-1) (toggleon) else (toggleoff) somewhere else on the form.

Although when I try the onclick() it runs the true part (when I first click it) then runs the false part (for the second click) which is what I want, but after that it doesn't send either.. I put a msgbox "TRUE"/"FALSE" so it would return something I could see, and after the initial on/off it stops sending the msgbox.

EDIT: Also do I need to setup in code for the toggle off commands to be run at load of form? or do I need to set 'locked=Yes' in the data tab for the child, or does it matter? I tried to put an if(button=-1)[...] into the "on enter" but it makes the form crash with runtime error 28, out of stack space. Before the error the child flashes like a strobe light. I forgot to put me. before the buttonname.

When I put it into Private Sub SubReport_Enter() If[...] I get: Method or data member not found.

Thanks guys, I don't know what I was afraid of, VBA seems to be easy. Here is the code which accomplished exactly what I wanted.

Private Sub EDITMODE_Click()
If Me.EDITMODE = -1 Then
    Me.SubReport.Locked = False
    MsgBox "False"
Else
    Me.SubReport.Locked = True
    MsgBox "True"
End If
End Sub

Answer 5

Henry,

I can't see how this will work. Could you explain further? I don't use a lot of Macros so I may be missing something. Here is what I have.

Form's Before Update Event

The only thing this does for me is switch the color of the Toggle control between Silver / Blue and the Records are always locked, regardless of the toggle position.

Van, since you proposed this as answer maybe you can also help me understand.

Thank You!

---

Chris Ward

Answer 6

You need probably a newer version of Access that allows embedded macros for events. They look slightly different

It works on A2010 without VBA

Henry

Answer 7

You need probably a newer version of Access that allows embedded macros for events. They look slightly different

It works on A2010 without VBA

Henry

Embedded Macros are in A2007 which I am using.

---

Chris Ward

Answer 8

Private Sub EDITMODE_Click()
If Me.EDITMODE = -1 Then
    Me.SubReport.Locked = False
    MsgBox "False"
Else
    Me.SubReport.Locked = True
    MsgBox "True"
End If
End Sub

A SubReport? Not a SubForm?

Glad you got your answer.

---

Chris Ward

Answer 9

So then A2007 doesn't have the same Embedded Macros as A2010. They look completely different. It works in A2010 the way I described. I never worked with A2007. So I can't say what the Macros there can do. In later Versions you can do it without VBA. And this was one of the main requirements. I for myself would do it with VBA, too. But there are environments where VBA isn't an option.

Time to upgrade?

Henry

Answer 10

@ Henry, Macros in A2007 and A2010 work exactly the same just the GUID is different.

@UpTide, Since you are opting for VBA now I would suggest an alternative that may be functionally better.

Since you want the Records of the SubForm to not be editable until you click a button, I would suggest that is better controlled on the SubForm itself rather than the MainForm. So yourcode should be in the SubForm's Code Page and the Toggle should also be on the SubForm. The code should be two parts to cover when the SubForm is (1) loaded or when records are switched and (2) When you Click the Button. I offer the following as a solution for you.

Private Sub Form_Current()
    Dim ctl As Access.Control
    Dim blnEnable As Boolean
    On Error Resume Next
    For Each ctl In Me.Controls
        Select Case ctl.Name
            Case "tglLock", "LeaveReport" 
            Case Else
                ctl.Enabled = blnEnable
        End Select
    Next ctl
End Sub

In the line  Case "tglLock", "LeaveReport"  you can replace with whatever controls you want enabled. Remember it is comma delimited.

Private Sub tglLock_Click()
    Dim ctl As Access.Control
    Dim blnEnable As Boolean
    blnEnable = Me.tglLock = 0
    On Error Resume Next
    For Each ctl In Me.Controls
        Select Case ctl.Name
            Case "tglLock", "LeaveReport"
            Case Else
                ctl.Enabled = blnEnable
        End Select
    Next ctl
End Sub

Hth

---

Chris Ward

Answer 11

In this case you didn't create embedded macro. My editor looks completely different than your screenshot. What I see in your screenshot looks more like a standard macro. I'll send a screenshot tomorrow when back in office.

Henry

Answer 12

In this case you didn't create embedded macro. My editor looks completely different than your screenshot. What I see in your screenshot looks more like a standard macro. I'll send a screenshot tomorrow when back in office.

Henry

Henry here are screenshots from A2007
As you can see when you select the Ellipsis next to an Event in the property sheet, you have the choice of building "Image1"

1. Macro Builder
2. Expression Builder
3. Code Builder
   

Choosing the Macro Builder opens an instance of Macro Developer "Image2"
When building is complete, you can see in "Image3"
it clearly shows it as an embedded Macro.

Because it is embedded Macro, it does not appear in the Navigation Pane.

Hth

---

Chris Ward

Answer 13

Hi Chris

Looks like an embedded macro then. Just my editor looks completely different. As I don't have A2007 installed I don't know how you would do this in A2007.

Here a screenshot of my macro for the Form BeforeUpdate event:

Henry

Answer 14

Just realized I didn't display the lower section of the Embedded Macro.

However it still does not function correctly as no matter if the toggle is -1 or 0 changes cannot be committed to the Record.

Can you save a record in your database?

Also a way to tell the difference between an Embedded Macro and not Embedded is in the name of the Macro.

                 Embedded                                     Not Embedded

The embedded Macro lists the Form Name and the Event in the Title bar.

Hth

---

Chris Ward

Answer 15

IIRC you have to write two dots '..' (or 3) to keep the action within the same condition as the previous line. As mentioned alredy: time to upgrade? You will not regrett it ;-)

Henry

Answer 16

Yes of course how insane of me. I rebuilt that 15 times and forgot 15 times the ...

If only I could upgrade to A2010 alas it is not available in the market.

IMHO the Enabled method is better given the options. It is better to know that you can't make a change than to change 20 fields only to find it wont change until after you click the Toggle. Then again, the opposing method could give you a moment of reflection before you Toggle and save.

I guess they both have merit would you say?

---

Chris Ward