Run code after text box is calculated. (1 Viewer)

scottrie2

Registered User.
Local time
Today, 15:42
Joined
Dec 27, 2018
Messages
15
I have a client that does not want to use the Access navigation buttons. Instead wants to have toggle buttons. On the subform there can be up to 6 children. I have set up the buttons and have the code to turn them on or off as I move from child to child. I have created a subroutine I call TurnOnButton (#) that turns on that button and turns the others off. The values can be 0 (no children) to 6. I also have a text box on the subform called ChildCount that has a control source of =Count(*) that has the number of children already entered.

Here is the problem. When the form is brought up I want to light up either the 0 (if no children are entered) or the 1 if there are any children entered. I can't figure out where to put the code. I have tried to put the code on the On Open or On Current property of the subform, but it appears that the Count is calculated after those events have happened. Where should I put the code? Or is there a better way to approach the problem.

Code being used:
If Me.ChildCount = 0 Then
TurnOnButton (0)
Else
TurnOnButton (1)
End If
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:42
Joined
Oct 29, 2018
Messages
21,358
Hi,


I would still try to use the Current event of the subform but probably do the recount as well instead of just looking at the calculated textbox.


Is this related to this other thread?
 

scottrie2

Registered User.
Local time
Today, 15:42
Joined
Dec 27, 2018
Messages
15
I am not sure what you mean. I get that you want me to add code to the On Current event of the subform but what do you mean about doing a recount?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:42
Joined
Oct 29, 2018
Messages
21,358
Hi,


For example, let's say in the Current event of the subform, you could try something like:


If Me.Recordset.RecordCount = 0 Then
...
 

scottrie2

Registered User.
Local time
Today, 15:42
Joined
Dec 27, 2018
Messages
15
Got it! Thanks. Here is the code that worked for me:

Private Sub Form_Current()
If Me.Recordset.RecordCount > 0 Then
TurnOnButton (1)
Else
TurnOnButton (0)
End If
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:42
Joined
Oct 29, 2018
Messages
21,358
Congratulations! Glad to hear you got it to work. Good luck with your project.
 

scottrie2

Registered User.
Local time
Today, 15:42
Joined
Dec 27, 2018
Messages
15
Well, I thought this was solved, but it caused another problem. Because this is set to on current, the only buttons that light up is 0 and 1. If I click on button 2 the code on the on current turns it back to 1. I need to find a way to use this code on an event that ONLY happens when the record is first brought up, not when changes are being made on the form or buttons are being pressed on the subform.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:42
Joined
Oct 29, 2018
Messages
21,358
Hi,

What do you mean with “when the record is first brought up?” What makes it different when you bring them up any other times? Maybe you can add another condition to the If statement to look for the difference.
 

scottrie2

Registered User.
Local time
Today, 15:42
Joined
Dec 27, 2018
Messages
15
On Current runs whenever I click on a button in the subform. When I click on button 2 (to bring up the 2nd child) the On Current event fires and sets the button back to 1. The only condition I can think of would be to add an invisible text box that would initially have 1 value and then change if any actions are taken on the form...
I would rather not have to add another text box if I can find a different solution.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:42
Joined
Oct 29, 2018
Messages
21,358
Okay, it sounds like you only want the code to run when you click on button 1 and not when you click on button 2. If so, maybe instead of using the Current event, you can use the Click event of button 1.


Just a thought...
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 19:42
Joined
Jul 9, 2003
Messages
16,245
I would rather not have to add another text box if I can find a different solution.

You could use a Custom Property instead of a text-box if you've running out of space on your Form. In fact I prefer to use custom properties these days, instead of what I call the "yellow perils"... Yellow perils - refers to the colour of the hidden control, traditionally you make them yellow so they stick out like sore thumbs in design view.. I mention in passing "Custom Properties" in my video here:-

Multiple Reports - Nifty Access
 

scottrie2

Registered User.
Local time
Today, 15:42
Joined
Dec 27, 2018
Messages
15
I was able to add the code to the main form button that navigates from one record to the next. That way it only fires when the record is first brought up. Thanks for pointing me in the right direction with the RecordCount code.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:42
Joined
Oct 29, 2018
Messages
21,358
Hi,

You’re welcome. Half the time is finding out which code to use and the other half is finding out where to put it. Cheers!
 

Users who are viewing this thread

Top Bottom