loop through all Non Blank textboxs on a form

[gint32]

Hi , I need help to loop through all Non Blank textboxs on a form and set the properties to not editable ,meaning if it blankskip over and the textbox has a value then lock the record for editing..many thanks in advance for any light shed on how to do this



Something like the below

Dim Propty  As Properties

For Each Propty In Me.Properties
    If Propty.Item = acTextBox Then
        If Propty.Value <> True Then
             Propty.Value = True
               Propty.enabled  = False
        End If
    End If
Next Propty

[aytee111]

Couldn't you use conditional formatting instead?

[Micron]

if it blankskip over and the textbox has a value then lock the record for editing

Confusing. Why don't you just make the form or record to be not updatable? Please explain better what it is you're trying to do. Lock only textboxes that have data in them? What's the point of locking the record for editing if some controls have values AND disabling or locking controls?

[gint32]

Confusing. Why don't you just make the form or record to be not updatable? Please explain better what it is you're trying to do. Lock only textboxes that have data in them? What's the point of locking the record for editing if some controls have values AND disabling or locking controls?

My apologizes, for the confusion.. What I am trying to do is Yes "Lock only Controls (ie textboxes checkboxs etc) that have data in them"? (nonblanks/isnulls) and leave the rest untouched(meaning open for populating with data or checking.

The main reason is different users populate different parts of the same form at various stages, ...Stage1, ...Stage 2 ...3..4..etc, so once some of the fields are populated, the users can click a save/lock button on the form (to lock-the non blanks), then no one else can edit these populated fields in stage 2 and stage3 I know we could do this with an (if users = "Admin" then bla... bla.. else) but this is not the way I wish to do this, although I might need to use a combination of this and a loop depending on which record is opened for viewing will show different stages of locks.

So I am looking for a loop that'll go through that record and lock populated textfields and checkboxs etc. But, it need to be just for that record "Only" though.

I am not entirely sure if its possible though on a per record basis.

Hope that makes more sense
many thanks in advance once again.

[Micron]

In a test copy of your db, place this in the form's OnCurrent event:

Dim ctl As Control

On Error Resume Next
For each ctl in Me.Controls
  If ctl.Type = acTextbox Or ctl.Type = acCombobox Then
    If Not IsNullEmpty(ctl) Then ctl.Enabled = False
  Else
    If ctl.Type = acCheckBox And ctl<>0 Then ctl.Enabled = False
  End If
Next

Public Function IsNullEmpty(ctl As Control)
IsNullEmpty = False
If IsNull(ctl) or ctl = "" Then IsNullEmpty = True
End Function

NOTES:
You didn't mention radio buttons in an option group, so I didn't worry about those. For the checkbox, I didn't worry about what might happen if you have its Triple State property set to True, hoping that the test for <> 0 will suffice. I also elected to disable the control rather than lock it. Locked is less obvious. If you want them to be locked, change Enabled to Locked.

[ssanfu]

In cases like this, I prefer to use the "Select Case" construct.

    Dim ctl As Control

    On Error Resume Next

    For Each ctl In Me.Controls
        Select Case ctl.Type
            Case acTextBox, acComboBox      ', acListBox
                ctl.Enabled = Not IsNullEmpty(ctl)
            Case acCheckBox              ', acToggleButton, acObjectFrame
                ctl.Enabled = Not (ctl <> 0)
        End Select
    Next

Still requires Micron's "IsNullEmpty()" function.....

[gint32]

In a test copy of your db, place this in the form's OnCurrent event:

Dim ctl As Control

On Error Resume Next
For each ctl in Me.Controls
  If ctl.Type = acTextbox Or ctl.Type = acCombobox Then
    If Not IsNullEmpty(ctl) Then ctl.Enabled = False
  Else
    If ctl.Type = acCheckBox And ctl<>0 Then ctl.Enabled = False
  End If
Next

Public Function IsNullEmpty(ctl As Control)
IsNullEmpty = False
If IsNull(ctl) or ctl = "" Then IsNullEmpty = True
End Function

NOTES:
You didn't mention radio buttons in an option group, so I didn't worry about those. For the checkbox, I didn't worry about what might happen if you have its Triple State property set to True, hoping that the test for <> 0 will suffice. I also elected to disable the control rather than lock it. Locked is less obvious. If you want them to be locked, change Enabled to Locked.

Thanks for your suggestion, Works a treat "but" seems to also greyout the save stage button also ,which prevents further save...but love the work

[gint32]

The case statement loop never seems to enter inside the loop although does step through each control...Sorry I think the suggestion was close though to achieve an alternative route I think.

[Micron]

The case statement loop never seems to enter inside the loop although does step through each control...Sorry I think the suggestion was close though to achieve an alternative route I think.

Do you have any check boxes on the form? It won't if you don't. Other than that, I don't see why it wouldn't.

I don't see why it would disable a button. I'd have to suspect there's an unrelated reason for that
I'm surprised it works because I should have written Public Function IsNullEmpty(ctl As Control) As Boolean

[davegri]

I think this Select Case will work.

 Dim aCont As Control

    'On Error Resume Next

        For Each aCont In Screen.ActiveForm.Controls
            Select Case TypeName(aCont)
                Case  "textbox", "combobox"
                    aCont.Enabled = Not IsNullEmpty(aCont)
                Case "CheckBox"              
                    aCont.Enabled = Not (aCont <> 0)
            End Select
        Next

[Micron]

Thanks for your suggestion, Works a treat "but" seems to also greyout the save stage button also ,which prevents further save...but love the work

Let me/us know if you want to explore this further. A command button should not be disabled by this code. We are talking about a command button and not some sort of textbox you've made to look like a button?

[gint32]

Let me/us know if you want to explore this further. A command button should not be disabled by this code. We are talking about a command button and not some sort of textbox you've made to look like a button?

Yes, I'll need a bit more investigation as unfortunately the code greys out all buttons my form making it a non-workable solution, I tried stepping through but can't figure out why the code see these as a acTextbox nor a acCombo as they are clearly buttons Thanks for your efforts everyone

[ssanfu]

Open the form in design view, click on the "button", then open the properties sheet.
What does it say? Command Button, Text Box, Label???

[Micron]

Thinking I figured it out. Will fix it and post back soon.

[gint32]

open and checked says ...Selection type: Command Button...thanks for your quick response!

I created a new form added two unbound checkboxs and a button...did the same thing again.
on my real form i have checkboxs, combo boxs, textboxs and of course labels(which I need to want to show at all times)