Help creating a function that combines multiple text boxes

[dibblm]

I have a form with 4 columns of 31 text boxes. These are used for
calculating time in and time out.

example of one row..

<Text Box1> <am/pm drop down1> <text box 2> <am/pm2> <Label showing
results1>
<Text Box3> <am/pm drop down3> <text box 4> <am/pm4> <Label showing
results2>
<Text Box5> <am/pm drop down5> <text box 6> <am/pm6> <Label showing
results3>
<Text Box7> <am/pm drop down7> <text box 8> <am/pm8> <Label showing
results4>


after someone enters a time in and a time out I need the results to display
in the label for each row.

Instead of writing code for the event ( after lost focus on the last am/pm)
for each row. like such.
textbox1 + textbox2 = label 1 (But alot more in depth) Can i write a
function that can be called so I dont have to specify each and every text
box and return the results to every label?

 

[John Vinson]

I have a form with 4 columns of 31 text boxes. These are used for
calculating time in and time out.

example of one row..

<Text Box1> <am/pm drop down1> <text box 2> <am/pm2> <Label showing
results1>
<Text Box3> <am/pm drop down3> <text box 4> <am/pm4> <Label showing
results2>
<Text Box5> <am/pm drop down5> <text box 6> <am/pm6> <Label showing
results3>
<Text Box7> <am/pm drop down7> <text box 8> <am/pm8> <Label showing
results4>


after someone enters a time in and a time out I need the results to display
in the label for each row.

Instead of writing code for the event ( after lost focus on the last am/pm)
for each row. like such.
textbox1 + textbox2 = label 1 (But alot more in depth) Can i write a
function that can be called so I dont have to specify each and every text
box and return the results to every label?

WHOA. This form is *really* redundant. What's the form's Recordsource?
Where are you storing this data? If the table hase redundant fields
like this you really need to stop and step back and reconsider! Or are
you using VBA code to move data from this (arguably) user-friendly
form into a properly normalized table?

Rather than using Labels to display the result, consider using a
Textbox. You can set the Control Source property of a textbox to an
expression preceded by an equals sign and it will automatically pick
up the values. For instance, a Control Source

=[TextBox1] + [TextBox2]

in the Control Source of textbox3 will display the sum of the two
values in the other textboxes, and will change as those textboxes are
changed.

John W. Vinson[MVP]

 

[dibblm]

Yes it is redundant. However the data isnt being stored yet into the tables.

Ill explain what this is being used for and you can suggest a better
alternative if you would.

Perhaps I had also given more info than what was needed as I was trying to
explain the best I could.

What this form does is validate against a paper form we use for daycare
providers that manually enter the times in that they watch kids.

Example.

in out
Total
Jan 1 2005 8:00 AM 5:00 PM 9 Hrs.
Jan 2 2005 10:00 AM 3:00 PM 9 Hrs.
Jan 3 2005 7:30 AM 2:00 PM 6.5 Hrs.
Jan 5 2005 8:00 AM 6:00 PM 9 Hrs.

Total Hours 33.5 Hours
where if you notice that Jan 5th time is off by one hour.

I want this form to give the calculations in and out for each day to double
check times that were entered to catch such errors. Thats the reason for my
redundancy as it follows suit with the way the form is layed out that
daycare providers use to enter there times in. The only thing that will be
getting stored in the database would be the total hours. and the provider.

So now that you have seen what I have attempted to do .. Do you still say
that this is redundant where there could be and easier way of tracking this?

I have a form with 4 columns of 31 text boxes. These are used for
calculating time in and time out.

example of one row..

<Text Box1> <am/pm drop down1> <text box 2> <am/pm2> <Label showing
results1>
<Text Box3> <am/pm drop down3> <text box 4> <am/pm4> <Label showing
results2>
<Text Box5> <am/pm drop down5> <text box 6> <am/pm6> <Label showing
results3>
<Text Box7> <am/pm drop down7> <text box 8> <am/pm8> <Label showing
results4>


after someone enters a time in and a time out I need the results to
display
in the label for each row.

Instead of writing code for the event ( after lost focus on the last
am/pm)
for each row. like such.
textbox1 + textbox2 = label 1 (But alot more in depth) Can i write a
function that can be called so I dont have to specify each and every text
box and return the results to every label?

WHOA. This form is *really* redundant. What's the form's Recordsource?
Where are you storing this data? If the table hase redundant fields
like this you really need to stop and step back and reconsider! Or are
you using VBA code to move data from this (arguably) user-friendly
form into a properly normalized table?

Rather than using Labels to display the result, consider using a
Textbox. You can set the Control Source property of a textbox to an
expression preceded by an equals sign and it will automatically pick
up the values. For instance, a Control Source

=[TextBox1] + [TextBox2]

in the Control Source of textbox3 will display the sum of the two
values in the other textboxes, and will change as those textboxes are
changed.

John W. Vinson[MVP]

 

[John Vinson]

Yes it is redundant. However the data isnt being stored yet into the tables.

Ill explain what this is being used for and you can suggest a better
alternative if you would.

Perhaps I had also given more info than what was needed as I was trying to
explain the best I could.

What this form does is validate against a paper form we use for daycare
providers that manually enter the times in that they watch kids.


in out
Total
Jan 1 2005 8:00 AM 5:00 PM 9 Hrs.
Jan 2 2005 10:00 AM 3:00 PM 9 Hrs.
Jan 3 2005 7:30 AM 2:00 PM 6.5 Hrs.
Jan 5 2005 8:00 AM 6:00 PM 9 Hrs.

Total Hours 33.5 Hours
where if you notice that Jan 5th time is off by one hour.

I want this form to give the calculations in and out for each day to double
check times that were entered to catch such errors. Thats the reason for my
redundancy as it follows suit with the way the form is layed out that
daycare providers use to enter there times in. The only thing that will be
getting stored in the database would be the total hours. and the provider.

So now that you have seen what I have attempted to do .. Do you still say
that this is redundant where there could be and easier way of tracking this?

Yes. It is redundant, and there is an easier way of tracking this.

Since the time-spent is derived data and you have the in and out
times, I'd strongly suggest storing the in and out times and
*calculating* the time spent (instead of storing the derived value).
To do so, you can use a Form (based on the Provider table) with a
continuous Subform with four textboxes, bound to three table fields
(CareDate, TimeIn, and TimeOut) and to a calculated expression:

HoursSpent: Round(DateDiff("n", [TimeIn], [TimeOut]) / 60, 1)

This will calculate the time in minutes between timein and timeout,
divide by 60 to get fractional hours, and round the result to one
decimal place. This calculated time can be verified against the paper
time.

The continuous form will give you as many rows as you wish without
having to store multiple textboxes for TimeIn and multiple other
textboxes for TimeOut.

John W. Vinson[MVP]

 

[dibblm]

OK. That sounds much easier than what I was trying. I will try it , However
I have never worked with a continuous form and will have to look at the
design stages in making a form continuous.

Yes it is redundant. However the data isnt being stored yet into the
tables.

Ill explain what this is being used for and you can suggest a better
alternative if you would.

Perhaps I had also given more info than what was needed as I was trying to
explain the best I could.

What this form does is validate against a paper form we use for daycare
providers that manually enter the times in that they watch kids.


in out
Total
Jan 1 2005 8:00 AM 5:00 PM 9 Hrs.
Jan 2 2005 10:00 AM 3:00 PM 9 Hrs.
Jan 3 2005 7:30 AM 2:00 PM 6.5 Hrs.
Jan 5 2005 8:00 AM 6:00 PM 9 Hrs.

Total Hours 33.5 Hours
where if you notice that Jan 5th time is off by one hour.

I want this form to give the calculations in and out for each day to
double
check times that were entered to catch such errors. Thats the reason for
my
redundancy as it follows suit with the way the form is layed out that
daycare providers use to enter there times in. The only thing that will be
getting stored in the database would be the total hours. and the provider.

So now that you have seen what I have attempted to do .. Do you still say
that this is redundant where there could be and easier way of tracking
this?

Yes. It is redundant, and there is an easier way of tracking this.

Since the time-spent is derived data and you have the in and out
times, I'd strongly suggest storing the in and out times and
*calculating* the time spent (instead of storing the derived value).
To do so, you can use a Form (based on the Provider table) with a
continuous Subform with four textboxes, bound to three table fields
(CareDate, TimeIn, and TimeOut) and to a calculated expression:

HoursSpent: Round(DateDiff("n", [TimeIn], [TimeOut]) / 60, 1)

This will calculate the time in minutes between timein and timeout,
divide by 60 to get fractional hours, and round the result to one
decimal place. This calculated time can be verified against the paper
time.

The continuous form will give you as many rows as you wish without
having to store multiple textboxes for TimeIn and multiple other
textboxes for TimeOut.

John W. Vinson[MVP]

 

[John Vinson]

OK. That sounds much easier than what I was trying. I will try it , However
I have never worked with a continuous form and will have to look at the
design stages in making a form continuous.

View the form's Properties; one of them is "Default View". Select
Continuous.

For the view you want, you should put a horizontal row of controls
along the top of the form detail area (you can put labels in the form
Header above each control); drag the bottom of the detail section up
to the bottom of the controls. When you open the form it will look
rather like a datasheet, with multiple rows of controls (as many rows
as there are records in the underlying table, with one extra blank row
for new data).

John W. Vinson[MVP]