Set control properties 4

[sstray]

Can someone help please?
I have a fairly simple form containing about 10 controls.I need all edited controls except one to be locked. I can set the form properties to lock edited records, but cannot unlock the one control I need to edit at a later date.
I have tried using a subform, but that method unfortunately does not work. I am sure this question has been previously asked, and the answer is quite simple, but it eludes me.
Thanks in advance.

 

[FancyPrairie]

Lock the controls individually or disable them. (See the Lock and Enable properties of the controls)

 

[sstray]

Thanks for the thought FancyPrairie.

Perhaps I was not too clear with my requirements.

The first 9 controls need to be available for new records (Add), thereafter, they should be "read only". The last control needs to be avaliable at all times (Edit). I can set the properties for the whole form, which will allow an add, then read only environment. I have not as yet been able to set (unset) the property for the last control.

As previously stated, I tried using a subform, but, as the property for the main form is set to Allow Edits = No, the subform is unusable, (locked).

 

[jebry]

Hi!

In the Form_Current event procedure, use the following code:

Dim cntl As Control

For Each cntl In Me.Controls
If cntl.ControlType = acTextBox Then
If cntl.Name <> &quot;NameOfBoxToEdit&quot; Then
If IsNull(cntl.Value) = No Then
cntl.Locked = True
Else
cntl.Locked = False
End If
End If
End If
Next cntl

I have assumed here that all of the controls in question are text boxes. If they are not, you can add a check for acComboBox, acCheckBox etc.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[FancyPrairie]

What you want to do is to lock the 9 fields if you are not adding a new record, else lock them. I would use somekind of naming convention for my 9 controls. For example, suppose the names all begin with &quot;txt&quot; and end with the numbers 1-9 (txt1, txt2, ... txt9). This is how I would do it via the OnCurrent event of the form.

Private Sub Form_Current()

    Dim ctl As Control
    Dim bolLock As Boolean
    Dim i as Integer

    If (Me.NewRecord) Then bolLock = False Else bolLock = True
    
    For i = 1 To 9
        Me("txt" & i).Locked = bolLock
    Next i
    
End Sub

 

[jebry]

Hi!

I've never used the NewRecord property! Thats cool! I would still loop through the controls as in my first post though, because I try to use names for my controls that make sense to me. But that is just a personal preference.

Jeff Bridgham
bridgham@purdue.edu

 

[FancyPrairie]

If you don't want to use the naming convention I suggested, then you could set the tag property for each of the 9 controls to something and then in the loop work only with those controls whose tag property contains your string. I only suggest this because, otherwise, you're going to have to check for each name in the loop (assuming there are more controls on the form than just the 10).

Private Sub Form_Current()

Dim ctl As Control
Dim bolLock As Boolean
Dim i as Integer

If (Me.NewRecord) Then bolLock = False Else bolLock = True

for each ctl in me.controls
if (ctl.controltype = acTextbox) then
if (instr(ctl.Tag,&quot;YourString&quot>0 then ctl.Locked = bolLock
End if
next

End Sub

 

[jebry]

Hi again!

Yep! The tag property would be perfect for this. In fact, with the tag property you could drop the reference to the controltype as well! Good Work FancyPrairie!

Jeff Bridgham
bridgham@purdue.edu

 

[FancyPrairie]

Thanks for the complement Jeff.

I think you're still going to have to use the controltype check as Jeff suggested in his 1st post. Because labels don't have tags and they are considered a control, so it will error out.

 

[jebry]

Hi!

Actually, I checked that out before answering and Labels do have tags, even the ones Access creates that are attached to text boxes. That's what what makes your solution so elegant. I hope sstray gives you a star at some point, but until then, take one from me! I'll be using this!

Jeff Bridgham
bridgham@purdue.edu

 

[chrissie1]

i just knew one day i was going to use the tag.

Still trying to get you that medal, but a star will have to do for now. Christiaan Baes
Belgium
&quot;What a wonderfull world&quot; - Louis armstrong

 

[FancyPrairie]

Thanks guys!

 

[sstray]

Thank you all for your help!

FancyPrairie, I have adopted your Tag method, and it works perfectly. Just what I needed. I have marked the post as helpful, and again, my sincere thanks.

Steve Raymond