Value in a text box automatically

[Guest]

I have a form which is made up of a Tbl.Decs and a subform Results Query
which is joined by a field called "Horse".
Tbl.Decs has 4 fields ie: Course, Dist, Rtime & Horse, the Results Query has
numerous fields which also includes a Course, Dist , Horse & Atime field.
At the moment I am manually entering the Atime data in a text box called
Best_ Time.
Is there anyway that this can be done automatically bearing in mind that I
want to find the Best_Time the Horses have run over that Course and Distance,
if so how.
Appreciate any help with this.

 

[Steve Schapel]

Dave,

I can't quite grasp the relationship between the main form and the
subform. On the face of what you have told us so far, it seems like an
unnecessary complication to be using a subform at all. What are the
details of the query that the subform is based on? Is this query based
on the same table as the main form's record source? Is the Best_Time
the minimum Rtime from the Decs table for each Horse/Course/Dist
combination? If so, this should be obtainable using a Totals query
based on the Decs table, or else a calculated expression in the control
source of the textbox on the form, using a DMin() function.

 

[Guest]

Hi Steve
Thanks for your reply and sorry for the confusion.
The Results Query is not based on the same Table as the main form's record
source.
The Best_Time is the minimum "Atime" from the Results Query for each of the
Horse/Course/Dist combinations.
Hope thats a bit clearer.

 

[Steve Schapel]

Hi Dave,

I need to do a fair bit of guessing and imagining here. But assuming
that your subform is continuous view, and lists all of the ATimes for a
particular Horse/Course/Dist, then you can put an unbound textbox in the
Footer section of the subform, with Control Source like this...
=Min([ATime])

 

[Guest]

Hi Steve
Thanks once again for your help.
I did what you suggested but #NAME? error came up.
Afraid i'm only a novice working with Access at the moment as part
of a hobby.
Can I send you the Form to look at by e-mail.
Thanks again.

Hi Dave,

I need to do a fair bit of guessing and imagining here. But assuming
that your subform is continuous view, and lists all of the ATimes for a
particular Horse/Course/Dist, then you can put an unbound textbox in the
Footer section of the subform, with Control Source like this...
=Min([ATime])

--
Steve Schapel, Microsoft Access MVP

Hi Steve
Thanks for your reply and sorry for the confusion.
The Results Query is not based on the same Table as the main form's record
source.
The Best_Time is the minimum "Atime" from the Results Query for each of the
Horse/Course/Dist combinations.
Hope thats a bit clearer.

 

[Steve Schapel]

Dave,

This is in the footer of the subform, right? And the [ATime] field is
in the subform? And the subform is based on the Results Query query?
Can you go to the design view of the Results Query, select SQL from the
View menu, and then copy/paste the SQL of the query into your reply post?

 

[Guest]

Hi Steve
SQL as requested.
SELECT Myraces2.RACEID, Myraces2.COURSE, Myraces2.DATE, Myraces2.DIST,
Myraces2.CLASS, Myraces2.TYPE, Myraces2.RTIME, [TBTN]*0.2+[RTIME] AS ATIME,
Myraces2.RAN, Myrunners2.FIN, Myrunners2.TBTN, Myrunners2.HORSE,
Myrunners2.DRAW, Myrunners2.TRAINER, Myrunners2.AGE, Myrunners2.WGT,
Myrunners2.JOCKEY, Myrunners2.ODDS, Myrunners2.OHM, Myrunners2.OWN
FROM Myraces2 INNER JOIN Myrunners2 ON Myraces2.RACEID = Myrunners2.RACEID
ORDER BY Myraces2.RACEID, Myraces2.DATE DESC , Myrunners2.FIN;
Thanks

 

[Steve Schapel]

Thanks, Dave. And the answers to my other questions... This is in the
footer of the subform, right? And the [ATime] field is in the subform?

 

[Guest]

Steve
This is in the footer of the subform, "No",its in the top r/h corner of
form, this
is where i've been entering the Atime data manually.
And the [Atime] field is in the subform "Yes".

Thanks, Dave. And the answers to my other questions... This is in the
footer of the subform, right? And the [ATime] field is in the subform?

--
Steve Schapel, Microsoft Access MVP

Hi Steve
SQL as requested.
SELECT Myraces2.RACEID, Myraces2.COURSE, Myraces2.DATE, Myraces2.DIST,
Myraces2.CLASS, Myraces2.TYPE, Myraces2.RTIME, [TBTN]*0.2+[RTIME] AS ATIME,
Myraces2.RAN, Myrunners2.FIN, Myrunners2.TBTN, Myrunners2.HORSE,
Myrunners2.DRAW, Myrunners2.TRAINER, Myrunners2.AGE, Myrunners2.WGT,
Myrunners2.JOCKEY, Myrunners2.ODDS, Myrunners2.OHM, Myrunners2.OWN
FROM Myraces2 INNER JOIN Myrunners2 ON Myraces2.RACEID = Myrunners2.RACEID
ORDER BY Myraces2.RACEID, Myraces2.DATE DESC , Myrunners2.FIN;
Thanks

 

[Steve Schapel]

Dave,

For my suggested approach to work, i.e. using the Min() function, you
will need to put the textbox for this in the Header or Footer section of
the subform.

 

[Guest]

Hi Steve
That works but not exactly as I intended, but can now experiment.
Thanks very much for your help and perseverance.
Appreciated

Dave,

For my suggested approach to work, i.e. using the Min() function, you
will need to put the textbox for this in the Header or Footer section of
the subform.

--
Steve Schapel, Microsoft Access MVP

Steve
This is in the footer of the subform, "No",its in the top r/h corner of
form, this
is where i've been entering the Atime data manually.
And the [Atime] field is in the subform "Yes".

 

[tony]

The results query could be set to select just one record using an
ascending sort on the Atime

I've been programming for horse race analysis for over 20 years

Enjoy it, its the best game on earth!

 

[Steve Schapel]

Ok, Dave, let us know if any further help required.