Thanks for coming back.
The complete function is listed below
The purpose is to add, amend or delete a boat to all the races in that
series.
The boat is added from a form that calls this function after update and
after delete of a boat in the series.
There are a number of races in a series found in a table jnSeriesRaceMaster.
When I add a boat to the series, it looks at a table jnSeriesRaceMaster in
the first SQL to find all the races in the series. It then checks if the
boat is in the race. Assuming the boat has not been entered in the race it
works round to the AddIt: label.
I have played about putting the breakpoint on different lines in this
section and providing the breakpoint is before the line !Handicap =
SeriesHandicap it works fine. If the breakpoint is on this line or after it
fails.
Function OtherRaces(SeriesID&, BoatID&, ADDAmendDelete%)
Dim MyDb As Database
Dim RaceSet As Recordset, BoatSet As Recordset
Dim SQLStg As String, Msg As String
' Check and modify all races that this boat is in, subject to Statu
SQLStg = "SELECT Series.SeriesID, Series.SeriesName, RaceMaster.*,
Status.Status, "
SQLStg = SQLStg & "jnRaceMasterResults.StatusID "
SQLStg = SQLStg & "FROM Series INNER JOIN ((Status INNER JOIN
(RaceMaster "
SQLStg = SQLStg & "INNER JOIN jnRaceMasterResults "
SQLStg = SQLStg & "ON RaceMaster.RaceMasterID =
jnRaceMasterResults.RaceMasterID) "
SQLStg = SQLStg & "ON Status.StatusID = jnRaceMasterResults.StatusID) "
SQLStg = SQLStg & "INNER JOIN jnSeriesRaceMaster "
SQLStg = SQLStg & "ON RaceMaster.RaceMasterID =
jnSeriesRaceMaster.RaceMasterID) "
SQLStg = SQLStg & "ON Series.SeriesID = jnSeriesRaceMaster.SeriesID "
SQLStg = SQLStg & "WHERE Series.SeriesID = " & SeriesID & ";"
' All the races in that series
Set MyDb = CurrentDb
Set RaceSet = MyDb.OpenRecordset(SQLStg)
RaceSet.MoveFirst
NextRace:
If RaceSet.EOF Then GoTo Form_AfterUpdate_Exit ' No Races
in Series
SQLStg = "SELECT jnBoatRaceMaster.* FROM jnBoatRaceMaster "
SQLStg = SQLStg & "WHERE (RaceMasterID = " & RaceSet!RaceMasterID
SQLStg = SQLStg & " AND BoatID = " & BoatID & ");"
' Find the boats (if any) that are in the race found above
Set BoatSet = MyDb.OpenRecordset(SQLStg)
If ADDAmendDelete = 3 Then ' Delete
If BoatSet.EOF Then ' Not in race
GoTo FindNextRace
Else
If RaceSet!StatusID < 4 Then ' Not yet started so OK
to delete
BoatSet.Delete
Else ' Check status in race
If BoatSet!Entered = False Then ' not entered so ok to
delete
BoatSet.Delete
Else
MsgBox "This boat has entered " & RaceSet!RaceName & "
and can not be deleted from the race"
End If
End If
GoTo FindNextRace
End If
End If
If BoatSet.EOF Then ' Not in race
If RaceSet!StatusID < 4 Then ' Not yet started so OK to add
GoTo AddIt
End If ' Race started, finished
calculated etc.
Msg = "Race " & RaceSet!RaceName & " status is " & RaceSet!Status &
Chr$(13)
If RaceSet!StatusID = 4 Then ' Started
Msg = Msg & "This boat will added, but not entered." & Chr$(13)
Msg = Msg & "To rank this boat as a starter, go to the
appropriate race sheet"
MsgBox Msg, vbInformation, "Not Yet a Starter"
GoTo AddIt
End If
If RaceSet!StatusID > 4 Then ' Finished or calculated,
etc
Msg = Msg & "If you add this boat, all results will have to be
recalculed." & Chr$(13)
Msg = Msg & "and their positions will change." & Chr$(13)
Msg = Msg & "Are you absolutely certain you wish to add "
Msg = Msg & ELookup("BoatName", "Boat", "BoatID = " & BoatID) &
Chr$(13)
Msg = Msg & "to " & RaceSet!RaceName
If MsgBox(Msg, vbInformation + vbYesNo, "Not Yet a Starter") =
vbYes Then
GoTo AddIt ' Breakpoint here woerks OK
Else
GoTo FindNextRace
End If
End If
Else ' In race
On Error Resume Next ' already on file
If RaceSet!StatusID < 4 Then ' Not yet started so OK to
add
With BoatSet
.Edit
!ClubID = ClubID
!RacingNo = SeriesRacingNo
!Handicap = SeriesHandicap
!DivisionID = SeriesDivisionID
.Update
End With
End If
End If
GoTo FindNextRace
AddIt: ' Add New boat to Race
With BoatSet
.AddNew ' Breakpoint here works OK
!RaceMasterID = RaceSet!RaceMasterID ' Breakpoint here works OK
!BoatID = BoatID
!ClubID = ClubID
!RacingNo = SeriesRacingNo ' Breakpoint here works OK
!Handicap = SeriesHandicap ' Breakpoint here FAILS
!DivisionID = SeriesDivisionID ' Breakpoint here FAILS
.Update
End With
FindNextRace:
RaceSet.MoveNext ' Find Next Race
GoTo NextRace
Form_AfterUpdate_Exit:
BoatSet.Close
Set BoatSet = Nothing
RaceSet.Close
Set RaceSet = Nothing
End Function
YOU ARE NOT GOING TO BELIEVE THIS
I have just changed the order to ie loaded the Handicap before the RacingNo
and it WORKS - weird
AddIt: ' Add New boat to Race
With BoatSet
.AddNew
!RaceMasterID = RaceSet!RaceMasterID
!BoatID = BoatID
!ClubID = ClubID
!Handicap = SeriesHandicap
!RacingNo = SeriesRacingNo
!DivisionID = SeriesDivisionID
.Update
End With
Thanks for your help
Phil
"Bob Quintal" <bq******@generation.net> wrote in message
news:e2******************************@news.teranew s.com...
"Phil Stanton" <ph**@stantonfamily.co.uk> wrote in
news:3f***********************@mercury.nildram.net :
I have got the following snippet of code to add a record from
a form
With BoatSet
.AddNew
!RaceMasterID = RaceSet!RaceMasterID
!BoatID = BoatID
!ClubID = ClubID
!RacingNo = SeriesRacingNo
!Handicap = SeriesHandicap
!DivisionID = SeriesDivisionID
.Update
End With
If I stop execution at the .AddNew line everything is OK
If I just let it run, the .Upadate line fails and I get a
duplicate key error message. I know there is no error.
If when I get this error I check on the code values, !ClubID
is 63 while ClubID is 1. Somehow I don't appear to be getting
to a new record. It is as if there has not been enough time to
prepare a clean record unless I artificially delay things.
Any thoughts gratefully received. Using AK2000 with Windows XP
Thanks
Phil
The code you presented looks fine. The problem could lie with the
recordset you are opening, (boatset). It might be based on a non-
updateable query, or improperly declared, defined or opened.
Could you post the snippet where you make that happen?
Bob Q