Handle Multiple Textboxes with 1 code (1 Viewer)

123dstreet

Registered User.
Local time
Today, 15:27
Joined
Apr 14, 2010
Messages
122
Hello,

I am creating a Timesheet/Payroll database, on the main timesheet form there are about 100 text boxes where the user can enter their hours (10 days in the pay period x 10 different possible tasks.

In order to update the total hours immediately, I require the following for each textbox:
Code:
Private Sub Text20_AfterUpdate()
Me.Refresh
End Sub

I don't feel like writing this for all 100 text boxes, it seems there should be a more efficient way of writing this code.

Any ideas?

D
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:27
Joined
Oct 29, 2018
Messages
21,358
Hi. Why would you need to do that? Where is the total located?
 

123dstreet

Registered User.
Local time
Today, 15:27
Joined
Apr 14, 2010
Messages
122
The total is located at the bottom of each column in an unbound textbox, but unless a refresh is done the total isn't updated. I'd like it updated in real time as soon as the user enters their hours.

I'm sure I could be doing a lot differently, but if there's a possible way to do this that would make my life easier.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:27
Joined
May 21, 2018
Messages
8,463
Public function MyRefresh()
'Me.recalc 'May need a recalc but probably not
Me.Refresh
end Function
In design view select all texboxes. In the after update event put in
=MyRefresh()
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:27
Joined
Oct 29, 2018
Messages
21,358
The total is located at the bottom of each column in an unbound textbox, but unless a refresh is done the total isn't updated. I'd like it updated in real time as soon as the user enters their hours.

I'm sure I could be doing a lot differently, but if there's a possible way to do this that would make my life easier.

Can you post the Control Source for one of the Totals? Just curious...
 

June7

AWF VIP
Local time
Today, 14:27
Joined
Mar 9, 2014
Messages
5,423
I see MajP got in first what I was thinking. The function would have to be behind the form and need not be Public, can be Private.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:27
Joined
Oct 29, 2018
Messages
21,358
The total is located at the bottom of each column in an unbound textbox, but unless a refresh is done the total isn't updated. I'd like it updated in real time as soon as the user enters their hours.

I'm sure I could be doing a lot differently, but if there's a possible way to do this that would make my life easier.
Hi. I'm curious about this, so I made a short video. You'll see that as soon as I enter a number in the textbox, the total value in the bottom box is automatically updated. In design view, you'll see those boxes are unbound and there are no events in them.
 

Attachments

  • TotalDemo.zip
    372 KB · Views: 110

June7

AWF VIP
Local time
Today, 14:27
Joined
Mar 9, 2014
Messages
5,423
Total doesn't update until you click on another textbox (or Enter or Tab). At least that's what I see in your video as well as in my own test.

Now that I tried it out, code doesn't work. I forgot AfterUpdate doesn't trigger until some action such as click elsewhere or Enter or Tab, all of which move focus. I really don't see any way to do an 'immediate' refresh while still in textbox. In which case recalculation should happen without any code. Unless the total textbox is located on another form then record must first be committed to table.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:27
Joined
Oct 29, 2018
Messages
21,358
Total doesn't update until you click on another textbox (or Enter or Tab). At least that's what I see in your video as well as in my own test.

Now that I tried it out, code doesn't work. I forgot AfterUpdate doesn't trigger until some action such as click elsewhere or Enter or Tab, all of which move focus. I really don't see any way to do an 'immediate' refresh while still in textbox. In which case recalculation should happen without any code. Unless the total textbox is located on another form then record must first be committed to table.
Hi June. That's correct, the textbox has to be "updated" first before you can see the total also get updated. In the original post, the OP asked how to use the AfterUpdate event code all at once for 100 textboxes, and I was thinking why is code even necessary?
 
Last edited:

123dstreet

Registered User.
Local time
Today, 15:27
Joined
Apr 14, 2010
Messages
122
This works perfectly:
Code:
In design view select all textboxes. In the after update event put in
=Refresh

Without the refresh, the unbound textboxes with the total don't update at all unless I close and re-open the form, I'm clearly missing something here?


D
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:27
Joined
Oct 29, 2018
Messages
21,358
This works perfectly:
Code:
In design view select all textboxes. In the after update event put in
=Refresh
Without the refresh, the unbound textboxes with the total don't update at all unless I close and re-open the form, I'm clearly missing something here?


D
Hi. Glad to hear you got a solution. Just curious though, did you see the video I posted? It doesn't use any code, but I'm not sure if it's the same setup as yours.
 

123dstreet

Registered User.
Local time
Today, 15:27
Joined
Apr 14, 2010
Messages
122
Yes I just checked out your video, I am using the identical NZ([Text0],0) equation in an unbound textbox, but my entry textboxes are controlled where yours are unbound, maybe this is the difference?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:27
Joined
Oct 29, 2018
Messages
21,358
Yes I just checked out your video, I am using the identical NZ([Text0],0) equation in an unbound textbox, but my entry textboxes are controlled where yours are unbound, maybe this is the difference?
What do you mean by "controlled?" I could convert my demo to use bound textboxes, and I suspect it will behave the same without using any code. I'll let you know...
 

123dstreet

Registered User.
Local time
Today, 15:27
Joined
Apr 14, 2010
Messages
122
Yes I meant to say bound.

I'm curious why I need to refresh then...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:27
Joined
Oct 29, 2018
Messages
21,358
Yes I meant to say bound.

I'm curious why I need to refresh then...
Hi. As I expected, it didn't make any difference. Please see attached...
 

Attachments

  • TotalDemo.zip
    26.3 KB · Views: 101

123dstreet

Registered User.
Local time
Today, 15:27
Joined
Apr 14, 2010
Messages
122
TimesheetFormScreenshot.jpg

Here is a screenshot of my form in Design View.
 

123dstreet

Registered User.
Local time
Today, 15:27
Joined
Apr 14, 2010
Messages
122
Yes this is multi-user with front and backend split.

I'll stick with the global refresh it seems to work well.

Thanks for your help!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:27
Joined
Oct 29, 2018
Messages
21,358
Yes this is multi-user with front and backend split.

I'll stick with the global refresh it seems to work well.

Thanks for your help!
Sounds good. Good luck!
 

Users who are viewing this thread

Top Bottom