Could someone point me in the right direction, please? I'm trying to lock all textboxes on a form (and eventually subform) via the click event of a button. Instead of hardcoding the lock for each textbox I would like to just loop through. I'm getting runtime error #13 "Type mismatch" with this code. I tried adding "next textbox" and got the same error. WHere did I err?

Thank you,

Loralee

Dim frmForm As Form
Dim txtBox As TextBox

Set frmForm = Me

For Each txtBox In frmForm
    txtBox.Locked = True
Next

End Sub

What you really want is a Control Array, but I don't think VBA supports that. Try this, I modified it from some code I found in the Excel VBA forum.

dim bx as object
for each bx in controls
   if left(bx.name,1)="t" then
      bx.Locked=true
   end if
next

Let me know if that works, if not, I know I have done this before, it's just a matter of finding out where!

Mike

Mike
EchoVue.com

LoraLee,

This works:

    Dim ctl As Control
    For Each ctl In Me.Controls
       With ctl
       Select Case .ControlType
           Case acTextBox
               ctl.locked = true
       End Select
       End With
    Next

Kevin


dartcoach

If you are wanting to prevcnt a users from changing the data on a form, why not just set the form's and subform's AllowEdits property to false.

Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com

Thank you all!
As far as the allowedits property = false, I tried that in a previous project and couldn't get it to work. Is there another argument somewhere that needs to be set also so it works?

Loralee

Loralee,
Do you want these textboxes always locked? Or just when someone clicks a button, or if they enter a particular value into another field?

Kevin

dartcoach

I want to lock the textboxes (default) and supply a button that will unlock them. The button will only be visible if user has certain permissions. It's just a little more protection for our data. I'd rather loop through the collection than hardcode for each txtbox.

[It's not an elegant solution but opening the form as readonly via docmd caused subforms that are not populated to be blank. (Really blank- No objects visible- my uneducated guess is the foreign key on the SF is null and it falls flat on load??)]

Any ideas?

Thanks,

Loralee

OK, Loralee --,
In the on open, or on load event of the form, the code I gave you earlier put inside an IF condition along with making the button visible. If the user has authority, the button is visible, along with making the fields available for editing. Make sense?

Kevin

dartcoach

You may have checkboxes and option groups in there you may want to lock later on. Instead of locking the textboxes, put this on the button's ON CLICK event (assuming only authorized people can see it):

Me.AllowAdditions = Not Me.AllowAdditions
Me.AllowEdits = Not Me.AllowEdits
Me.AllowDeletions = Not Me.AllowDeletions

This will toggle the read/write on or off depending on the state they are just before you click the button. On the form's ON OPEN event, make sure you turn them off as the default.

Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False

You could also put a label (white letters) somewhere and do this to it. Whenever the editing, etc. is allowed you can do this

Me.lblRW.Caption = "EDIT"
Me.lblRW.BackColor = vbGreen

and when you DON'T allow editing

Me.lblRW.Caption = "READ ONLY"
Me.lblRW.BackColor = vbRed


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks, Greg. I'll give it a try this weekend.

Is there another property I have to also toggle somewhere? Last year I was playing with those properties and couldn'tget them to protect the data, and thought perhaps there was something else I missed ?????

Loralee