Using * variable to change set the property of multiple controls

[Nangasaur]

Using * wildcard to set enabled property of multiple controls

So I'm using the following code to check if a field has data in it, and if it does, it's enabled property is automatically set to False on form load.

Private Sub Form_Load()
 
If Form![PID1] = Null Then
Form![PID1].Enabled = True
Else
Form![PID1].Enabled = False
End If
End Sub

My question here is, I have the following controls: PID1, PID2, PID3, PID4, etc all the way up to PID16. I'd like to use a wildcard, either *, or whatever else works, to set the .enabled property to False if any of those fields from my table that the form is bound to contains data.

The above code works just great for what I am needing, but I'd like to have it work for all the PID* controls, rather than just one and have to write an IF statement for every single control.

Any advice?

 

[HiTechCoach]

Re: Using * variable to set enabled property of multiple controls

So I'm using the following code to check if a field has data in it, and if it does, it's enabled property is automatically set to False in form load.

Private Sub Form_Load()
 
If Form![PID1] = Null Then
Form![PID1].Enabled = True
Else
Form![PID1].Enabled = False
End If
End Sub

My question here is, I have the following controls: PID1, PID2, PID3, PID4, etc all the way up to PID16. I'd like to use a variable, either *, or whatever else works, to set the .enabled property to False if any of those fields from my table that the form is bound to contains data.

The above code works just great for what I am needing, but I'd like to have it work for all the PID* controls, rather than just one and have to write an IF statement for every single control.

Any advice?

I would recommend using the On Current event.

As far as I know it is not possible to use a wildcard when referencing controls.

It is possible to calculate a controls name.

I would use a loop liek this:

Private Sub Form_Current()
 
Dim I as Long

For I = 1 to 16

If IsNull( Me("PID" & I ))  Then
    Me("PID" & I ).Enabled = True
Else
    Me("PID" & I ).Enabled = False
End If

Next I

End Sub

Or I would write it like this:

Private Sub Form_Current()
 
Dim I as Long

For I = 1 to 16

    Me("PID" & I ).Enabled = IsNull( Me("PID" & I ))

Next I

End Sub

*** Note: I use the On Current Event.

 

[Nangasaur]

I also found this post:
http://www.access-programmers.co.uk/forums/showthread.php?t=155972&highlight=control+wildcard

And from that, I wrote the following code, which is similiar to your post above:

Private Sub Form_Load()
    Dim IntI As Integer
 
    For IntI = 1 To 10
        With Me("PID" & IntI)
            If .Value = Null Then
                .Enabled = True
            Else
                .Enabled = False
            End If
        End With
    Next
End Sub

But strangely, it wasn't looping and checking if each one was null, but rather, saw that PID1 was not null, and set all the PID controls (PID1 - PID16) to disabled. Any guesses as to why that wasn't working properly?

I went with your second suggestion, and it worked great, exactly as I expected it to. Thank you. I have been struggling with this all day long and my lack of VBA knowledge was really hindering how I was wanting to research the problem, as simple as it is

Why do you suggest I use On Current, rather than On Load? I merely ask, so as to help me learn what it is I'm doing.

Edit: Also, with the second method you suggested, I'd like to expand on it even further, where it enables the FIRST control field found as being null, but disables all further control fields.

For example.

PID1 is not null, PID2 is not null, and PID3 is not null. PID4 through PID16 are null.

I would like to disable PID1 through PID3, enable PID4, and then disable PID5 through PID16.

As I said, I'm currently using your second suggestion to disable the controls I want disabled. I'm researching (as best I can with the limited VBA knowledge I have) to understand exactly how this method works. I'm afraid that the answer is probably so simple, that I'm just not understanding the logic of how it works.

My current thinking is adding math to the expression somewhere, something like I + 1 thru I + (16 - CurrentPIDControl).

 

[HiTechCoach]

Why do you suggest I use On Current, rather than On Load? I merely ask, so as to help me learn what it is I'm doing.

The On Current event is the event that fire after the the data is loaded from the form's recordsource into the bound controls to populate the form. It is the best event to use to ensure that the data has been loaded into the bound controls before checking any bound controls. It also fires with every new record.

 

[Nangasaur]

The On Current event is the event that fire after the the data is loaded from the form's recordsource into the bound controls to populate the form. It is the best event to use to ensure that the data has been loaded into the bound controls before checking any bound controls. It also fires with every new record.

Makes sense to me, thank you. I was using the On Load event since I was only using this form as a subform, which only loads from the parent form each time a new record is selected. I don't even want to go into half of why this particular problem was burning me most of the day, since it stemmed from trying to run this procedure from the parent form, when it finally dawned on me to try to just play with the subform itself, and it finally started working.

Any advice for my second question in my first reply to you?

 

[Galaxiom]

Rather than specifically referring to the controls by a fixed number they can be referenced in a For Each loop. This way you can add more controls without having to change the code loop.

 Dim ctrl As Control
 
For Each ctrl in Me!formname
    If Left(ctrl.name, 3) = "PID" Then
        whatever code
    End If
Next

Alternatively, you can name the controls arbitrarily and set the Tag property of the controls to be processed. This string is then tested in the loop to determine whether to include it.

 

[Nangasaur]

So this is what I've done to make my entire expression work how I want it to. Please advise me if this is the proper way to do it, and if not, any advice on a better way to accomplish the goal would be great.

I've set the controls to be DISABLED by default, and run the following code:

Private Sub Form_AfterInsert()
Dim I As Long
For I = 1 To 16
 
    If Me("PID" & I - 1) <> "" Then
 
        Me("PID" & I).Enabled = IsNull(Me("PID" & I))
 
    Else
 
        Me("PID" & I).Enabled = False
 
    End If
 
Next I
End Sub
Private Sub Form_Current()
Dim I As Long
For I = 1 To 16
 
    If Me("PID" & I - 1) <> "" Then
 
        Me("PID" & I).Enabled = IsNull(Me("PID" & I))
 
    Else
 
        Me("PID" & I).Enabled = False
 
    End If
 
Next I
End Sub

If it finds PID1 is not null, it goes to the next I, doing nothing to PID1. On the next I, if it finds PID2 IS null, it then looks to see if PID1 is not null, and if it isn't it sets PID2 enabled to true, and goes to the next I. If it finds that PID1 is null, it goes to the next I. For this to work properly, I of course had to add a field to my table labeled "PID0", due to the (I - 1) part of the expression. It works, but something tells me I shouldn't really have to be creating a PID0 field in my table with a default value <> null. It's obviously a field I won't be using, so it's unnecessary.

Advice?

edit: I also had to add the code to the After Insert event, since I'm using this form as a subform, or at least it seems it's what I had to do. Before I had it in the After Insert event, it was leaving the proper control from the previous record enabled, as well as enabling the record for the current control.

 

[Galaxiom]

I usually include the default .Enabled = False as part of the OnLoad event procedure.

Otherwise there is a danger that the form can be loaded in Form mode then switched to Design mode and saved, losing the intended defaults.

While it is best to avoid this sequence it can happen and setting the .Enabled = False with the OnLoad Event is good insurance.

 

[Nangasaur]

I usually include the default .Enabled = False as part of the OnLoad event procedure.

Otherwise there is a danger that the form can be loaded in Form mode then switched to Design mode and saved, losing the intended defaults.

Ya know, I've noticed this quite a few times while playing with my form designs. I'm guessing this is just some deep seeded bug in access, or is there some string of events occuring that intentionally remove the defaults?

While it is best to avoid this sequence it can happen and setting the .Enabled = False with the OnLoad Event is good insurance.

Thanks, I'll add that too.

 

[Galaxiom]

It isn't a bug.

You just switch to Design view to take a quick look at the properties of an object after opening the form in Form view. Next thing you change something forgetting that you got there through Form view and, hey, the default is changed.

You just click Save when asked to save changes. It is so easy to do.

 

[Nangasaur]

I wanted to thank you folks so much for your help. You gave me awesome advice and examples, and just enough info to help me figure out how to do this properly. I got it to work exactly as I want, with no hitches or hangups, and even without adding unnecessary fields to my table (the PID0 field) to make it work right. I now only have one procedure on my form, instead of two, to keep my controls switching from disabled to enabled, and back again as I want them to.

Private Sub Form_Current()
Dim I As Long
For I = 2 To 16
    
    Me!PID1.Enabled = False
    
    Me("PID" & I).Enabled = False
        
    Me!PID1.Enabled = IsNull(Me!PID1)
          
    If Me("PID" & I - 1) <> "" Then
    
        Me("PID" & I).Enabled = IsNull(Me("PID" & I))
        
    Else
    
        Me("PID" & I).Enabled = False
        
    End If
    
Next I
End Sub

I read somewhere else that it is not recommended to use the "" method of searching for null entries, but I could not for the life of me find proper syntax to get my expression to work properly with the word Null, when combined with <>. "" works great for what I need when combined with <>.

Thanks again!

 

[boblarson]

Just an FYI for the future.

You can check for a null by using

IsNull(Me.YourControl)

You can check for an empty string by using

If Me.YourControl = ""

OR you can check for BOTH at the same time by using something like:

If Len(Me.YourControl & "") = 0 Then

or

If Nz(Me.YourControl, "") = "" Then