Passing Variable/Function values to Form Controls

[pretzel]

Hi, I know this is possible, but have come to a dead end on all I've tried. I
have read all I could find on this site as well as on-line help.

I'm creating a db for my Swim Club which holds Times for members for up to
39 possible combinations of distance and stroke. I'm storing the Time info as
two separate fields (Min & Sec) and having it display in a form using
expressions like =[sc50mB/Fly-m] & ":" & [sc50mB/Fly-s] in the Control for
the TextBox on the form, so far so good.

Times can not be edited directly on the form (which in a way is a good
thing), so I can create a button for each of the 39 possabilities to open an
edit form, to edit the two fields. I have tested using a separate "Edit Form"
for each button which works, but is a lot of forms(39!!). I'd rather so
something smarter (more efficient) and use one Edit Form (EditSwimTime), and
then pass information from each of the 39 buttons to the EditSwimTime Form.

I've created a separate module with code like:
Global strCourse As String, strLS As String, strDist As String, strStroke As
String

Function GetCourse() As String
GetCourse = strCourse
End Function

From here I can display GetCourse in a Control for a TextBox on the
EditSwimTime form and this works great.

What I can't do is pass the information to the form of the time I want to
display (times are held in a table). To display the value of minutes, I have
tried using;

Function GetMinTime() As String
GetMinTime = strLS + strDist + strStroke + "-m"
End Function

And then placed =GetMinTime() in a Control for appropriate TextBox on
EditSwimTime. Unfortuanately, it just displays the "sc50mB/Fly-m" rather than
returning the value of the minutes field in the table (called ShortCourse).

If I use =[GetMinTime()] then I get "#Name?" in my TextBox.

Any ideas?

 

[Allen Browne]

Before we can answer the question you asked (about how to get the interface
to work), we need to ensure the data is being stored the best way in can in
your table.

I assume the times for combinations such as:
Distance (e.g. 50 metres, 100 metres, ...)
StrokeType (e.g. freestyle, butterfly, backstroke, medley, ...)
and you are storing values for each swimmer.

You will need at least these tables:

Swimmer table, with fields:
SwimmerID AutoNumber autonumber. primary key
Surname Text
Firstname Text
...

StrokeType table, with fields:
StrokeTypeID Text (24) name of the stroke. primary key.

SwimTime table, with fields:
SwimTimeID AutoNumber (primary key)
SwimmerID relates to Swimmer.SwimmerID (who swam)
Distance Number (Size: long integer. the swim distance in
metres.)
StrokeTypeID relates to StrokeType.StrokeTypeID (what stroke)
SwimDate Date/Time (when the swimmer swam this time.)
Seconds Currency (time in seconds and fractions of a
second.)

There may be other tables as well, if you need to record the event where
this happened, or relays consisting of teams of swimmers, but let's ignore
those for now. The structure above lets you query for anything you want,
e.g. to compare swimmers, show best/worst times, calculate averages, and
decide which stroke is most competitive for any swimmer.

Note that the SwimTable records the time as seconds in a Currency type
field. This field is accurate to exactly 4 decimal places, so allows you to
store results below milliseconds. To *display* this result in minutes and
seconds, you would just use a text box bound to:
=[Seconds] \ 60 & Format([Seconds] Mod 60, "\:00.000")
The formula is explained at:
http://allenbrowne.com/casu-13.html

If you do not mind writing some code it would also be possible to place an
unbound text box on your form behind that calculated text box. In the Got
Focus event of the calculated text box, SetFocus to the unbound one, and
copy the text in. The user can then type another value such as:
2:03.567
into the unbound box, and you can use its AfterUpdate event to convert that
into 123.567 seconds, and write that value to the (hidden) Seconds field, so
it is stored in actual seconds and fractions of a second. (You will also
want to use the Undo event of the form to restore the OldValue of the
Seconds field.) The unbound control has its TabStop property set to No, so
the calculated one takes focus as you tab through the form and the correct
control gets focus. It even works correctly in a continuous form or
datashseet.

This should solve all your problems, including:
- avoiding the 39 fields for the different combinations of distance and
stroke;
- coping with new distances and strokes you have not planned for;
- the fact that the date/time field cannot handle fractions of a second (as
is not suitable anyway);
- storing the data as a true number, but providing the display the user
expects;
- easy querying for things you have not even thought of yet;
- storing the data in a way that is correctly normalized.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I know this is possible, but have come to a dead end on all I've
tried. I
have read all I could find on this site as well as on-line help.

I'm creating a db for my Swim Club which holds Times for members for up to
39 possible combinations of distance and stroke. I'm storing the Time info
as
two separate fields (Min & Sec) and having it display in a form using
expressions like =[sc50mB/Fly-m] & ":" & [sc50mB/Fly-s] in the Control for
the TextBox on the form, so far so good.

Times can not be edited directly on the form (which in a way is a good
thing), so I can create a button for each of the 39 possabilities to open
an
edit form, to edit the two fields. I have tested using a separate "Edit
Form"
for each button which works, but is a lot of forms(39!!). I'd rather so
something smarter (more efficient) and use one Edit Form (EditSwimTime),
and
then pass information from each of the 39 buttons to the EditSwimTime
Form.

I've created a separate module with code like:
Global strCourse As String, strLS As String, strDist As String, strStroke
As
String

Function GetCourse() As String
GetCourse = strCourse
End Function

From here I can display GetCourse in a Control for a TextBox on the
EditSwimTime form and this works great.

What I can't do is pass the information to the form of the time I want to
display (times are held in a table). To display the value of minutes, I
have
tried using;

Function GetMinTime() As String
GetMinTime = strLS + strDist + strStroke + "-m"
End Function

And then placed =GetMinTime() in a Control for appropriate TextBox on
EditSwimTime. Unfortuanately, it just displays the "sc50mB/Fly-m" rather
than
returning the value of the minutes field in the table (called
ShortCourse).

If I use =[GetMinTime()] then I get "#Name?" in my TextBox.

Any ideas?

 

[pretzel]

Hi Allen

Thanks for the quick response!

The combinations are as u suggest, F/S, Br/S, Bk/S, B/Fly and IM. Distances
are 25m and up, and there is Short Course (25m pool) and Long Course (50m
pool).

I've created several tables, so I'll try & give u an outline.
ShortCourse: times(currently 2 fields)/dates/venue/gala
LongCourse: times(currently 2 fields)/dates/venue/gala
SwimmerInfo: FName/LName/DoB/Address/etc
TownInfo: Town where swimmer lives
Gala : Type of Gala (Carnivals in the UK are called Gala's),
Championship/Regional
Venues :Where the pool is eg:Brighton/Eastbourne/Crystal Palace/etc

Note that I had split the Times into ShortCourse and LongCourse tables (went
from one to two tables) early on as I had exceeded the number of fields (I
recall).

In my ShortCourse and LongCourse tables I gave the Primary Key the same
name, that is SwimmerID, and in the SwimmerInfo table called it ID. Not a
good naming convention :-( does this sound OK or should I try to alter it?

Note that in your text you say to have a SwimTimeID and a SwimmerID, I just
have SwimmerID (autonumber.primary key). Should I sense that I have done
something incorrect? The relationships appear to work, although I did notice
that I had to enter times in the (ID) order that swimmers appeard in the
SwimmerInfo table.

Note that I don't have a table for the strokes (5), although I could. My
existing ShortCourse and LongCourse tables combine the stroke and distance
into one field (50mF/S etc). Will split this as suggested. Who knows, perhaps
they'll make another stroke! At least its better convention.

I had read about storing the time as a Currency from other postings you (and
other MVP's) had contributed to, but was then left with the issue of updating
the fields, which I can understand the concept but have difficulty with the
syntax (at the moment). Had viewed your site on this a couple of weeks ago
(and book marked it).

I'd like to write some code and have some experiece with C++/Java/OO from a
UNI course. So I'll give it a spin and see what happens. Looks like enought
to keep me busy for a while

Thanks
-----
pbw

Before we can answer the question you asked (about how to get the interface
to work), we need to ensure the data is being stored the best way in can in
your table.

I assume the times for combinations such as:
Distance (e.g. 50 metres, 100 metres, ...)
StrokeType (e.g. freestyle, butterfly, backstroke, medley, ...)
and you are storing values for each swimmer.

You will need at least these tables:

Swimmer table, with fields:
SwimmerID AutoNumber autonumber. primary key
Surname Text
Firstname Text
...

StrokeType table, with fields:
StrokeTypeID Text (24) name of the stroke. primary key.

SwimTime table, with fields:
SwimTimeID AutoNumber (primary key)
SwimmerID relates to Swimmer.SwimmerID (who swam)
Distance Number (Size: long integer. the swim distance in
metres.)
StrokeTypeID relates to StrokeType.StrokeTypeID (what stroke)
SwimDate Date/Time (when the swimmer swam this time.)
Seconds Currency (time in seconds and fractions of a
second.)

There may be other tables as well, if you need to record the event where
this happened, or relays consisting of teams of swimmers, but let's ignore
those for now. The structure above lets you query for anything you want,
e.g. to compare swimmers, show best/worst times, calculate averages, and
decide which stroke is most competitive for any swimmer.

Note that the SwimTable records the time as seconds in a Currency type
field. This field is accurate to exactly 4 decimal places, so allows you to
store results below milliseconds. To *display* this result in minutes and
seconds, you would just use a text box bound to:
=[Seconds] \ 60 & Format([Seconds] Mod 60, "\:00.000")
The formula is explained at:
http://allenbrowne.com/casu-13.html

If you do not mind writing some code it would also be possible to place an
unbound text box on your form behind that calculated text box. In the Got
Focus event of the calculated text box, SetFocus to the unbound one, and
copy the text in. The user can then type another value such as:
2:03.567
into the unbound box, and you can use its AfterUpdate event to convert that
into 123.567 seconds, and write that value to the (hidden) Seconds field, so
it is stored in actual seconds and fractions of a second. (You will also
want to use the Undo event of the form to restore the OldValue of the
Seconds field.) The unbound control has its TabStop property set to No, so
the calculated one takes focus as you tab through the form and the correct
control gets focus. It even works correctly in a continuous form or
datashseet.

This should solve all your problems, including:
- avoiding the 39 fields for the different combinations of distance and
stroke;
- coping with new distances and strokes you have not planned for;
- the fact that the date/time field cannot handle fractions of a second (as
is not suitable anyway);
- storing the data as a true number, but providing the display the user
expects;
- easy querying for things you have not even thought of yet;
- storing the data in a way that is correctly normalized.

--

 

[Allen Browne]

Okay, glad it was a help.

All your tables look good, except the ShortCourse and LongCourse. The lookup
table for the style will help, but these 2 tables with their many repeating
fields are wrong. You desperately need to change these.

The table I called SwimTime aims to store the same data. It contains a
record for every time anyone swims and you record a time. For example, if
John Smith swims the 100m backstroke, that's one record in the table, If
John also swims the 100m butterfly, that's another record in the same table.

The fields include:
SwimTimeID primary key (just an autonumber).
SwimmerID this field relates to the primary key of your SwimmerInfo
table.
StrokeType the kind of swim style called for.
Seconds the time they took to swim
Do you see that SwimTimeID is the primary key of the table, and SwimmerID is
a foreign key? The foreign key appears for as many events as the person has
swam in.

Since you have a table for Gala, it may be that you are recording only
results at a carnival. If so, one carnival consists of many events (races),
and one event has many entrants (swimmers) so you may actually need several
further tables with one-to-many relationships to represent this correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

Thanks for the quick response!

The combinations are as u suggest, F/S, Br/S, Bk/S, B/Fly and IM.
Distances
are 25m and up, and there is Short Course (25m pool) and Long Course (50m
pool).

I've created several tables, so I'll try & give u an outline.
ShortCourse: times(currently 2 fields)/dates/venue/gala
LongCourse: times(currently 2 fields)/dates/venue/gala
SwimmerInfo: FName/LName/DoB/Address/etc
TownInfo: Town where swimmer lives
Gala : Type of Gala (Carnivals in the UK are called Gala's),
Championship/Regional
Venues :Where the pool is eg:Brighton/Eastbourne/Crystal Palace/etc

Note that I had split the Times into ShortCourse and LongCourse tables
(went
from one to two tables) early on as I had exceeded the number of fields (I
recall).

In my ShortCourse and LongCourse tables I gave the Primary Key the same
name, that is SwimmerID, and in the SwimmerInfo table called it ID. Not a
good naming convention :-( does this sound OK or should I try to alter it?

Note that in your text you say to have a SwimTimeID and a SwimmerID, I
just
have SwimmerID (autonumber.primary key). Should I sense that I have done
something incorrect? The relationships appear to work, although I did
notice
that I had to enter times in the (ID) order that swimmers appeard in the
SwimmerInfo table.

Note that I don't have a table for the strokes (5), although I could. My
existing ShortCourse and LongCourse tables combine the stroke and distance
into one field (50mF/S etc). Will split this as suggested. Who knows,
perhaps
they'll make another stroke! At least its better convention.

I had read about storing the time as a Currency from other postings you
(and
other MVP's) had contributed to, but was then left with the issue of
updating
the fields, which I can understand the concept but have difficulty with
the
syntax (at the moment). Had viewed your site on this a couple of weeks ago
(and book marked it).

I'd like to write some code and have some experiece with C++/Java/OO from
a
UNI course. So I'll give it a spin and see what happens. Looks like
enought
to keep me busy for a while

Thanks
-----
pbw

Before we can answer the question you asked (about how to get the
interface
to work), we need to ensure the data is being stored the best way in can
in
your table.

I assume the times for combinations such as:
Distance (e.g. 50 metres, 100 metres, ...)
StrokeType (e.g. freestyle, butterfly, backstroke, medley, ...)
and you are storing values for each swimmer.

You will need at least these tables:

Swimmer table, with fields:
SwimmerID AutoNumber autonumber. primary key
Surname Text
Firstname Text
...

StrokeType table, with fields:
StrokeTypeID Text (24) name of the stroke. primary key.

SwimTime table, with fields:
SwimTimeID AutoNumber (primary key)
SwimmerID relates to Swimmer.SwimmerID (who swam)
Distance Number (Size: long integer. the swim distance in
metres.)
StrokeTypeID relates to StrokeType.StrokeTypeID (what stroke)
SwimDate Date/Time (when the swimmer swam this time.)
Seconds Currency (time in seconds and fractions of a
second.)

There may be other tables as well, if you need to record the event where
this happened, or relays consisting of teams of swimmers, but let's
ignore
those for now. The structure above lets you query for anything you want,
e.g. to compare swimmers, show best/worst times, calculate averages, and
decide which stroke is most competitive for any swimmer.

Note that the SwimTable records the time as seconds in a Currency type
field. This field is accurate to exactly 4 decimal places, so allows you
to
store results below milliseconds. To *display* this result in minutes and
seconds, you would just use a text box bound to:
=[Seconds] \ 60 & Format([Seconds] Mod 60, "\:00.000")
The formula is explained at:
http://allenbrowne.com/casu-13.html

If you do not mind writing some code it would also be possible to place
an
unbound text box on your form behind that calculated text box. In the Got
Focus event of the calculated text box, SetFocus to the unbound one, and
copy the text in. The user can then type another value such as:
2:03.567
into the unbound box, and you can use its AfterUpdate event to convert
that
into 123.567 seconds, and write that value to the (hidden) Seconds field,
so
it is stored in actual seconds and fractions of a second. (You will also
want to use the Undo event of the form to restore the OldValue of the
Seconds field.) The unbound control has its TabStop property set to No,
so
the calculated one takes focus as you tab through the form and the
correct
control gets focus. It even works correctly in a continuous form or
datashseet.

This should solve all your problems, including:
- avoiding the 39 fields for the different combinations of distance and
stroke;
- coping with new distances and strokes you have not planned for;
- the fact that the date/time field cannot handle fractions of a second
(as
is not suitable anyway);
- storing the data as a true number, but providing the display the user
expects;
- easy querying for things you have not even thought of yet;
- storing the data in a way that is correctly normalized.