Using an If statement for every record in a subform (1 Viewer)

Lkwdmntr

Registered User.
Local time
Yesterday, 18:24
Joined
Jul 10, 2019
Messages
277
I used the following code and It locked my field, but for whole subform, not each record. How would I go about doing this.

If IsNull(Me.Tab1.Value) Then
Me.StartTimeChallengeYes.Locked = True
Me.StartTimeChallengeNo.Locked = True
Else
Me.StartTimeChallengeYes.Locked = False
Me.StartTimeChallengeNo.Locked = False
End If

If IsNull(Me.Tab2.Value) Then
Me.ActivityChallengeYes.Locked = True
Me.ActivityChallengeNo.Locked = True
Else
Me.ActivityChallengeYes.Locked = False
Me.ActivityChallengeNo.Locked = False
End If​
 

June7

AWF VIP
Local time
Yesterday, 17:24
Joined
Mar 9, 2014
Messages
5,423
Are these checkbox controls? Programmatically setting control properties affects every instance of control on form.

Why do you have a Yes and a No?

Conditional Formatting can be used on textboxes and comboboxes.
 

Lkwdmntr

Registered User.
Local time
Yesterday, 18:24
Joined
Jul 10, 2019
Messages
277
Yes, they are Checkboxes, which makes it hard to conditionally format them. Maybe I can loop through the records? Not sure how I would do that. I read somewhere about using "Expression is", but can't find a good format for the expression. IIf[Null] [Sub_MonSTChallenges.ActivityChallengesYes.Locked = True] AND [Sub_MonSTChallenges.ActivityChallengesNo.Locked = True]

It would go something like that, but can't figure it out.
 

June7

AWF VIP
Local time
Yesterday, 17:24
Joined
Mar 9, 2014
Messages
5,423
But why do you have a Yes checkbox and a No checkbox? A yes/no field should have only one checkbox bound to it.
 

Lkwdmntr

Registered User.
Local time
Yesterday, 18:24
Joined
Jul 10, 2019
Messages
277
J7 - That is just the way my form is set up. My pointing system gives them achievement points just for checking the box. This is why I have to have a Yes and a No box. Not to mention I need the visual for the clients to see.

Not sure why you keep questioning my design. My boss likes it.
 

Lkwdmntr

Registered User.
Local time
Yesterday, 18:24
Joined
Jul 10, 2019
Messages
277
dogs - Thanks, but I don't think I can use this. My checkboxes are linked to tables and need to be visible and able to check and uncheck. Interesting stuff though. Maybe I can use it in the future. Thanks. Still gotta work through this problem.
 

June7

AWF VIP
Local time
Yesterday, 17:24
Joined
Mar 9, 2014
Messages
5,423
I question when I don't understand.

Simply cannot programmatically manage controls as you want for continuous or datasheet form. Setting property affects all instances of control - doesn't matter if you 'loop' records, same result.

The 'Expression Is' feature you mention is most likely from Conditional Formatting.

I expect Colin's idea could be made to work with code and hidden controls bound to fields.

If you want to provide db for analysis, follow instructions at bottom of my post.
 

isladogs

MVP / VIP
Local time
Today, 01:24
Joined
Jan 14, 2017
Messages
18,186
The first link effectively allows CF with checkboxes which a previous answer indicated you might like to do.
Although the second link requires an unbound checkbox, an after update or click event could be used to update the actual field.

Anyway, I'll leave it in your hands. Good luck

EDIT
Just realised you have two parallel threads at once..https://www.access-programmers.co.uk/forums/showthread.php?t=307837
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:24
Joined
Feb 19, 2002
Messages
42,981
You never said and no one ever asked - what event is this code in?

It should work as you intend if it is in the subform's Current event.

I understand J7's question regarding why yes and now are separate controls. Logically, you need one control with two states. However, since you don't want a default of No, you want someone to actively choose Yes or No, then I would use an Option group with just Yes and No as the two options. You end up with a single control with only one value, but it is a positive action and can result in a value of 0 or -1 which would be the same as you get for a checkbox but visually you get two checkboxes. Just define the field as required and to provide better error messages for the user, check for a value in the control in the FORM's BeforeUpdate event. Cancel the event if the control is empty.

Code:
If Me.fraYesNo & "" = "" Then
    Msgbox "Yes or No is required.",vbOKOnly
    Me.fraYesNo.SetFocus
    Cancel = True
    Exit Sub
End If
 
Last edited:

Micron

AWF VIP
Local time
Yesterday, 21:24
Joined
Oct 20, 2018
Messages
3,476
If in a continuous form, each option would have to be in it's own frame, but the frame doesn't have to be visible I guess. Should work if each frame is bound to a table field. However that would allow user to check both, and option buttons themselves can't be bound. So a way to not allow both might be required if that's a problem. Also, once checked, a single option button can't be unchecked AFAIK, so 2 in 2 frames would be required after all. Probably would look weird.
 
Last edited:

Micron

AWF VIP
Local time
Yesterday, 21:24
Joined
Oct 20, 2018
Messages
3,476
Again, assuming it's a continuous form, this is what seems to work if you use bound checkboxes
Code:
Private Sub Check32_AfterUpdate()
If Nz(Me.Check34,"") = "" Then Me.Check34 = 0
End Sub

Private Sub Check34_AfterUpdate()
If Nz(Me.Check32,"") = "" Then Me.Check32 = 0
 End Sub
Checking one unchecks the other and clicking the same one again simply unchecks it. So I'd forget the frame and option group I think.
EDIT - happened to click on this today and raised a null error. I forgot that checkboxes are sometimes neither true or false. Have modified the code to deal with Nulls via Nz. Seems to work better now.
 
Last edited:

Users who are viewing this thread

Top Bottom