Forms in MS Access

MikeGinMn
MikeGinMn used Ask the Experts™
on
In a form I have a sub form that displays data based on a drop down in the main form.  It shows the results of the query fine in the subform.  After entering results from an event I would like to be able to click on a command button that would calculate Place based on the final score.  The subform can have either 1 record or up to eight records.  Their best score is stored in a field call [Final score]  and I have a field called [Place] to store its value.  Right now I look at the best value and have a drop down with values 1st to 8th place - plus a few others like DQ, Did Not Finish etc.  and choose the correct value.  Can this be done with a procedure tied to a command button or am I looking at to big of a solution to the problem?  If I can can you point me in the right direction on how to do it?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
So you're already calculating the scores and placing them in a column in the table? If so, you can just Order your query to show the results in the correct order:

SELECT * FROM YourQuery ORDER BY YourScoreField DESC

The "DESC" is (obviously) a Descending sort. To use an Ascending sort, just remore the DESC (ASC is the default, and is not required).

You can then add a numbering fields to the results if you'd like, depending on the strucutre of the table. Can you give a little more info about that?

Author

Commented:
The field Place contains the value 1st Place, 2nd Place etc which the drop down gets from a tables called Choices-Places.  The only ones I would want calculated would be 1st thru 8th, with the others being put in by hand depending on situation

Example -  for 50 meter dash

Jim Q                    10.3
Dave W                 10.2
Sally H                   11.1
Deb G                    10.0
Tom Z                     10.5
Mike G                    did not finish

would work out to

Deb G                     10.0           1st Place
Dave W                   10.2           2nd Place
Jim Q                       10.3           3rd Place
Tom Z                      10.5           4th Place
Sally H                      11.1          5th Place
Mike G                                       Did Not Finish (Manually choosen)
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
So ORDER BY the "second" colum. This would put everything in the correct order. From there, if you want values like "1st" and "2nd", you'll have to jump through some hoops for this, and we'd have to know a little more about your actual table structures and such (i.e. field names, field types, etc).
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
The main thrust of your Q has been answered be LSM consulting...

But as he says, ..please note:
<if you want values like "1st" and "2nd", you'll have to jump through some hoops for this,>

If you really need this in a report you could try something like this (see the attached DB)
(Working off of LSM's post of sorting by the race time...)
...But here it is not something that you might consider "easy" as you have to know VBA and Report design.

There may always be a more elegant way though...

JeffCoachman
Access-EEQ27677977-ReportFunctio.mdb
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Agreed on the report - this is the simplest way to show data in this fashion, and is the proper use of the proper object.

Author

Commented:
Actually it is for a form not a report.  And as you said I figured it won't be easy.  Was hoping to get an idea where to start and maybe some examples on how - but I appreicate your input
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<Actually it is for a form not a report.>

Not trying to get you to select my post if it is not what you need...
But do you really *need* it in the form?, or would it just "be nice" if it were in the form?
You can always run the report directly after you do your data entry in the form to see (and Print the rankings.
Remember, a report gives you more options with regards to Sorting Grouping, Subtotals, Page breaks, Headings, ...ect


I am sure there is a way to "Rank" the forms Recordset results, (then use some version of my function to display the rankings) but this is beyond me...
But I am sure some searching here or on Google will give you some good links on this...

Again, not Easy, ...so if the report technique is beyond you, the form\rank Records technique will be just as challenging...

JeffCoachman

Author

Commented:
It needs to be in a form - I'm trying to make a way to run a meet without running off tons of paper reports, going green.
 The goal is to make the only "paper" we use would be label(s) that are printed out at the awards area to be placed on the ribbon with their name, delegation and best results for each person within that division.

 On the form I would like it to sort final score from best to worst so that the places can be easily done by a volunteer worker.

Example: When the form first comes up it would be in order of lane assignment, after the results for each participant is entered within that division (the subform only shows one division at a time)  I would like to sort that division (subform) by results (Final score) and eventually would rank from 1st to nth place (but am realizing that this function is going to be chore), so we can just go down list and in place choose the right place from a dropdown for now and from there another volunteer at the awards area they would bring up that division and print the labels and stick them to the ribbons

Hope this help clarify what I'm trying to accomplish
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<It needs to be in a form - I'm trying to make a way to run a meet without running off tons of paper reports, going green. >
You don't always have to "Print" a report, you can view it just like you can a form...


<When the form first comes up it would be in order of lane assignment, after the results for each participant is entered within that division (the subform only shows one division at a time)  I would like to sort that division (subform) by results (Final score) and eventually would rank from 1st to nth place (but am realizing that this function is going to be chore), so we can just go down list and in place choose the right place from a dropdown for now and from there another volunteer at the awards area they would bring up that division and print the labels and stick them to the ribbons>

Sure, you can do all of that with a form, then "Preview" the report (that displays the Report Label "Ribbons"), then print them...

Author

Commented:
But unless I'm mistaken I can't enter the results on a report - hence the need for a form
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
That was my point.
Do the data entry in the form, then immediately open the report to "See" what you will print.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
To chime in:

Tables are for storing data

Queries are for manipulating/combining data

Forms are for entering/editing data

Reports are for viewing data

Tables and queries are standalone objects, and have no event models associated. Forms and Reports have very rich event models, but those event models are very specific to the normal type of activies you'll find with them. For example, Forms do not employ a "print-specific" sort of event model, and Reports do not support a "data entry-specific" sort of event model.

In other words, if you try to get a Form to act like a Report, you're barking up the wrong tree.

As Jeff said, use a Form to enter the data, and then use a Report to view the data. You can still be "green" (if you buy into that sort of thing) by just viewing the report onscreen. You can even have a "report refresh" button the Form that will close/reopen the report as needed.

Author

Commented:
I don't think I'm explaining this right - Lets try this again

I have a form that is used for the finish line of a event.  Each race is what is called a division and this app would only show one division (race) at a time. After each participant finishes the race a volunteer enters their time as well as place for them on the form (sometimes the volunteer recording may instead of asking who finished 1st get info - 2nd get info etc - they may ask for the results of lane 2 and then lane 3 (this isn't the most effective way to do it - but can be done that way)).  So now we have all of the results from that race entered if the 2nd method is used the person has to go thru and place them.  Where it get tricky is if someone was either fast or slow on the stop watch.  If place was recorded, the times need to be relative (hence 3rd place couldn't have a better time than 2nd or 1st place) or if the second method is used a wrong time will affect place from that point on. I would like a way that would sort the subform of this finish line app to rearrange that division/race so that it can be a visual confirmation on the from before we print the labels (see rather user intensive to switch to a report - look at it - switch back to the form - make changes - switch to report - verify info - etc.
If you have every worked a track meet (especially a Special Olympics one) it is organized chaos at best.  I'm just trying to keep assembly line functionality going (the athletes are use to this concept and helps them keep focus/engaged - I stage - I go to start line - I compete - I finish - I go to awards - I stage there - I get called up - I get award - Mom/Dad/friends applause - I am done for that event - I wait for my next event)
I hope this makes it clearer.  It understand what you are saying about the report - I do that at the awards area - this is just to help make sure prior to awards the info coming to them is correct.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Perhaps I am missing something...

This is what i am proposing:
You open a form and enter the person and the times.
No "Finishing" info is ever entered or visible on this form, ...as all of this info will be *calculated* and displayed in the report.

Then you open a report and it *automatically* displays in the way you like.
(with the rankings in order)

If you need anything more than this, then perhaps another experts will step in...

Author

Commented:
I think we're close.  So after their time is entered how does 1st, 2nd and so forth get added so will be part of file that prints at awards on labels like this - also I have to be able to make a choice for place for DQs, Did not finish etc.  
Thats is why I thought it would be best do do with a form since for the awards area to know about participation note we would still have to send a piece of paper to them (and I would like to eliminate that)
What we have now is a form on paper (a report) that they write on results, place or DQ notes - send it to awards -  The paper form has the following

Name            Lane    Qualifying        Final                Place
John Doe         2             7.98        ___8.93 _          ___1_____ <--- We write in last 2
Dave Smith      3             7.65        ___8.9__           ___2_____ <--- We write in last 2
Mike Jone        4             8.00        ________          ____DQ___ <--- Mike is DQ because he
                                                                                               ran across field instead of
                                                                                               on the track
etc........

it is sent to awards where they have labels premade with info except results and place on - we write that info from sheet on to labels and then attach to ribbons and make presentations.  

Special Olympics - MN - 09
Joe Doe
Delegation Name
Shot Put - Div 1
8.93 M - 1st Place  

So in my example John would get 1st, Dave 2nd, etc and Mike gets a participation ribbon
As you see there is a lot of human interaction to accomplish this.  The fact that there is more info besides 1st- nth place made me think I would need to do a form rather than a report.  (Hope this clears it up more) and at end of meet that file will be eventually exported with results and sent to next level of possible competition

I can sort it when it comes out best to worst - but it won't have the place - If I can get that on the label - with it on there there would be a check for someone to visually see to be sure everything is OK before we present.  This also helps us keep mom/dad/friends happy if we missed/incorrectly finished them.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
On the form, you would only enter the Event, the person and the Time.

*You will not see or enter any rankings on the form*
They will be presented on the report only...
This is basically what my original sample displayed, I only added in the form here.

Note that your new requirement of "DQ" was not in your original post, and only complicates this more.
As yiu did not clarify the difference between (DQ and "Did Not finish", "Did not Place")
You must now consider adding a "Result" (Finished or DQ)
Then the report becomes much more complicated because a Grouping must be added...
...also note that the output format you now specify, is different from what you originally specified...

see new attached sample

But see this new sample.
The report can be tweaked, but note that *No Ranking* info is entered or seen on the form...
Access-EEQ27677977-ReportFunctio.mdb

Author

Commented:
I see what you saying but does quite allow me to do what I want - without going into a lot of explaining sometimes on the form you have to tweek the results to match the outcome - I like the way your module does the sort and may change my report to match what you suggest

In the very first entry I talk about DQ and Did not finish etc. but thats neither here or there -   I think we got to caught up in the 1st - nth preview thingy.  Lets go back to basics with a simple question

Can I re-sort a subform which is part of a form after I enter data into that subform based on data entered into that subform in form mode without leaving the form and if yes how?

 If no (and takes ok too)then the only way to do this is to ask for results in order of finish and leave my form as is
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<an I re-sort a subform which is part of a form after I enter data into that subform based on data entered into that subform in form mode without leaving the form and if yes how? >
???
I have no idea what that means...

Perhaps you should just post a sample of your database?
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman

Author

Commented:
Here it is - I've removed any personal info - but left what is needed (results from last year).

 If you look at "Finish line"
1.   Choose a Division - 100MDash-09 - after you pick division the subform is filled with info
2.   List of participant's comes up
3.   At finish line they would enter Final score and mark place
          (this form is sorted by event, division, lane)

What I would like to do is have that form look like "What I'd like" after clicking a commandbutton or how ever - as you can see after you choose "100MDash-09" now the subform is sorted by Event, Division, Final Score so that Place can be checked to make sure info entered is correct (This way if second place had a faster time than 1st - it could be adjusted so that times correlate to place correctly)
GMS-Dayof-Web.accdb
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
...Sorry but I really don't understand your interface here...
-There is no existing data, so I am unsure of what to enter.
-Why the buttons to insert numbers...? (and *all* the associated code)
-No comboboxes to validate entries...
-incomplete query clause in your 'Place' combobox
...etc

What is your experience with Form design?

No offense, but because of your insistence to select the "Place" manually, (instead of calculating it in the final output (a report), you could end up with all types of invalid data.
- a user could enter any "Place" for an "Score"
- or enter the same Place more than once.
- or forget (skip) a Place
- or leave the Place blank...
...etc

Your quote:
<(sometimes the volunteer recording may instead of asking who finished 1st get info - 2nd get info etc - they may ask for the results of lane 2 and then lane 3 (this isn't the most effective way to do it - but can be done that way)). >

As I see it, the data entry person should not be entering what "Place" a person was.
All they should be entering is the Time (Final Score?).
Based on the time, the "place" will be calculated...

Sorry, ...but you will have to explain to me why the "Place" should be selected manually, considering all of the pitfalls I listed above...

Again, the sample I submitted does this precisely.
You enter the "raw" data on the form. (User never enters a "Place")
The Report display exactly as you specified. (The correct "Place" is displayed on the report, and can be printed)
What is wrong with that?

JeffCoachman

Author

Commented:
Have you ever worked a Track meet?  I'm guessing not so this may be part of our communication issue.  I do like your function for the report and would use it else where

I included data with the db so if you pull up a division you should see what was the results from last year.  I just changed names to initials and removed any personal info - take 100MDash-09 there will come up with 5 names - The info for their race is entered and places listed

As for place - someone has to verify what person finished lets say 2nd and they have a time of 18.16 (same as on form) - and a different person picks someone different for third but they were fast on stopping the watch and the time they have is 18.12 (different than on form - it has 18.49).  With your way the wrong person would be given 2nd and 3rd places in respective (which would be incorrect), even though they were picked BB and GD your way if the times that the watch says were entered it would be GD and BB for 2nd - 3rd .

Unless you use a totally automated timing system there is no way to eliminate human error - even then some one has to pick the place finishes either live or from a video/picture of finish this way we can make use the data actually matches the physical results

The buttons on the bottom are for use with a tablet - so that I don't have to purchase a on screen keyboard and to save battery

As for choosing a place twice or skipping etc - that is part of what eventually the form will stop.  In fields events you could end up with a tie and then you would have to skip a place etc.  In timing events place is EVERYTHING and time is secondary hence the need to verify data matches results

As for a form design - what does that have to do with anything - if you must know it is self taught and I actually work in the real world.  As programmers instead of forcing users to make use of what you as a programmer think is right does nothing more than turn people off to making use of technology.  Try putting yourself in their shoes and you would see many a thing in a different light since most things done now with electronic forms  were originally done with paper and pencil.  The purpose of technology is to improve life not make things  more difficult for people

As for the question I'm closing it - since everyone says do it in a report it therefore can't be done in a form which is OK

Author

Commented:
I like some of the things mentioned and would use them elsewhere - hence I awarded equal points and gave best answer selection to LSM since that what JC said to do - even though the actual answer to my question is <br /><br />It can't be done in a form

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial