By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,775 Members | 2,658 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,775 IT Pros & Developers. It's quick & easy.

Can't add record

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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



Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.