473,325 Members | 2,771 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Can't add record

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
2 3115
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Scott269 | last post by:
So I've got an old MS Works database I imported into Access. I needed a primary key so I created a record number field that was just the record number I manually inserted when I entered it in the...
1
by: Scott269 | last post by:
So I've got an old MS Works database I imported into Access. I needed a primary key so I created a record number field that was just the record number I manually inserted when I entered it in the...
3
by: happy | last post by:
/* Book name : The prodessional programmers guide to C File name : E:\programs\tc\iti01\ch09\main\01setupm.c Program discription: file setuping -up -Version 01-ver01-W Logic ...
4
by: sparks | last post by:
I am trying to fix a database that someone did about 4 yrs ago in access97. The main table just contains demographics and is on the main form of the database. It has a subform on a tab that...
2
by: dasilva109 | last post by:
Hi guys I am new to C++ and need urgent help with this part of my code for a uni coursework I have to submit by Thursday //ClientData.h #ifndef CLIENTDATA_H #define CLIENTDATA_H #include...
11
khalidbaloch
by: khalidbaloch | last post by:
hi : all Friend i am a new member of this comunity as well in php myqsl i want learn that how to dispaly three or two record from a mysql table for example .. mysql table category has 21 columns,...
4
by: etuncer | last post by:
Hello All, I have Access 2003, and am trying to build a database for my small company. I want to be able to create a word document based on the data entered through a form. the real question is...
1
by: Donald Calloway | last post by:
********** Situation ********** I have a table which contains two text fields which represents the first and last name of clients. A third field in each record holds the text representing an...
4
by: aaronyoung | last post by:
I have created custom navigation buttons and Record Number indicators on several forms that are used to review and update records based on a query. My On Current event to update the "Record X of...
7
by: Neil | last post by:
Was working in A2003 and noticed that the Save Record item on the Records menu was not available when the record was not dirty. In A2000, Save Record was always available. (This is a problem for me...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.