Automatically close a form after last record

[Bud Toye]

I use a macro to open a form, copy information from one of the fields, paste that information elsewhere, then go to the next record and do the same again. This procedure works perfectly until the form’s source records are exhausted and then stops.

I want the form to close automatically when all the records in the source table have been processed. When all the records have been processed, there does not seem to be an available ‘event’ for me to close the form. Any suggestions…Thanks

[Rabbit]

Why not use an update query?

[Bud Toye]

Many thanks for your suggestion. Not sure how I could make an update query part of the process. Perhaps there might be a way of generating an error when there are no more records, then use the error event to close th4e form.

[Rabbit]

I meant use an update query instead of what you're doing. If you're just copying one field into another, you can accomplish that using an update query without any code.

[Bud Toye]

Ah yes of course. I wrote this software ten years ago and considered an update query at the time, but I couldn't do it because of a lot of other things going on in the macro that I failed to mention. Good point though, maybe it is time to revisit that possibility. FYI, this came up only now because I am converting all the macros to VB for AC 7 compatibility, and some of the behavior now changes. Thanks, Bud

[Rabbit]

If you could post the code or a description of what the macro does, maybe we can work out a way to use an update query.

[Bud Toye]

I'm actually not updating. I'm populating a new table, and then crunching that table. This is sailboat racing program. Note the Sendkey. That is Sendkeys work around in another module to make this legal for AC7.

You can check out the program at: http://www.toye.us



Function AutoUpdateLast5()
On Error GoTo AutoUpdateLast5_Err

DoCmd.SetWarnings False
' MemberPageToUpdate Table=Fleet Members has Next Button tr-runs this query
DoCmd.OpenForm "UpdateLast5Races", acNormal, "", "", , acNormal
DoCmd.GoToControl "MemberID"
' copy member ID
Sendkey "^{INSERT}", True
DoCmd.CopyObject "", "Last5", acTable, "Last5Master"
' paste member ID when "Last5RacesInDateOrder" dialog box opens
Sendkey "^V {ENTER}", False
DoCmd.OpenQuery "Last5RacesInDateOrder", acNormal, acEdit
DoCmd.OpenQuery "AppendToLast5Compilation", acNormal, acEdit
DoCmd.GoToRecord , "", acNext
DoCmd.GoToControl "Command4"
Sendkey "{ENTER}", False


AutoUpdateLast5_Exit:
Exit Function

AutoUpdateLast5_Err:
Resume AutoUpdateLast5_Exit

End Function

[Rabbit]

I'm having trouble understanding the purpose of the code. Can you describe to me what it's doing and what the result is supposed to be?

[Bud Toye]

Ok if I call you? E-mail your number to: [e-mail]

[Rabbit]

Sorry, but I'm at work. Is it difficult to explain in writing?

[Bud Toye]

I'll try to later.

[Bud Toye]

This program scores races based on corrected time. Corrected time is based on handicaps. Handicaps are calculated after every race and are based on corrected seconds per mile.

The corrected seconds per mile for the most recent five races are used to establish a boat’s next handicap. The race with the highest seconds per mile is thrown out.

This particular procedure generates a report which is uploaded to the yacht club’s website so that each racer can see how his/her handicap was arrived at. You can view an actual report at: http://www.sbyc.org/CHRFHandicapCalculations.html

So, a form is loaded with the first record displayed. The ID number of the boat is copied, then pasted into a parameter append query. The query now knows which boat’s records to process. It lists the most current five races which are appended to a table. It then goes to the next record and repeats the procedure until the five best records of each boat are in the table. This has worked very well for ten years and still does on all versions of access. It runs automatically in only a few seconds. Until I converted all this to vb modules, the form closed by itself. The non vb version of this program returns an error message when it lands on a null record, so I just used the on error event property to close the form.

I am not a programmer so the only tools I could understand were used to accomplish this, such as Sendkeys. I purchased a module from a guy in England (for 29 bucks) that allows the Sendkeys command to work in Access 7, and it works perfectly after you rename them to Sendkey.

This is no big deal, I just don’t want our race director to have to close this form after the job is done.

Thanks,

Bud

[Rabbit]

If I understand correctly, you're appending the top 5 records from one table into another table?

You could probably run a query that does something like this

INSERT INTO tableTop5 (BoatID, RaceID, RaceTime)
SELECT BoatID, RaceID, RaceTime
FROM tableRaceResults AS T1
WHERE RaceID IN
     (
     SELECT TOP 5 RaceID
     FROM tableRaceResults
     WHERE T1.BoatID = BoatID
     ORDER BY RaceTime ASC
     )

[Bud Toye]

Many thanks for this. It might be a challenge for me to get this right, but I'm going to give it a try. If I do get it right, there are a dozen more macros doing exactly this for other calculations. Much appreciated...Bud

[Rabbit]

Let us know if you run into any trouble.

[Bud Toye]

I just can’t make it work because there is just too much going on that you don’t know about.

I think the problem could be solved with one line of code inserted into my current module. It would close the form after the last record has been processed. If I allow ‘additions’ in that form’s properties, the last record will be blank awaiting a new record.

So, all I want to do is close the form “MemberPageToUpdate” after the last record has been processed. When the field named “BOAT” is empty, I want the form to close because there are no more records to process.

I don’t understand a thing about VB as all my modules are converted macros, so I apologize for not being able to make your code work.