Table AutoIncrement Problems

[dojones]

I have inherited a paradox 5.0 database from 1995. One table called PerfM has a structure of:
[Perf#] [smallint],
[Perf_date] [smalldatetime],
[Perf_time] [nvarchar] (10),
[Perf_comms] [nvarchar] (25),
[AB] [nvarchar] (1),
[C] [nvarchar] (1),
[History] [nvarchar] (1),
[HistDate] [smalldatetime],
[Winterfest] [nvarchar] (76)

Now when i view the table there is an extra field called PerfM that seems to be using an AutoIncrement function.

Here's my problem, when using a form to insert into this table i want this "PerfM" value to be set to the highest existing number + 1, instead all inserts set "PerfM" = 1 and increase + 1 on "PerfM" for all other records in the table. Since the original developer seemingly uses this "PerfM" field as a sort field in many other forms throughout the application, it causes problems.

Here is the entire code that executes when using the "Add Performance" button which causes the problem. I'd much prefer to fix the problem here instead of redefining the sort field on multiple forms.

This never has been a problem for the previous 10 years but have only seend this problem since moving the app to newer XP/2000 workstations.

Thanks so much for taking the time...Doug

-----Code Below-------

method pushButton(var eventInfo Event)
var
xtc, kidtc, perfTc, whoTc, charcTc TCursor
i SmallInt
kids, xtras Table
endVar
if DateVal = "" then
msgInfo("Blank Date", "Please enter Date")
return
endIf
if TimeVal = "" then
msgInfo("Blank Time", "Please enter Time")
return
endIf

if perfTc.attach(perfm) then
switch
case perfTc.locate("Perf_Date", DateVal, "Perf_Time", TimeVal) :

msgInfo("Date/Time already exists", DateVal)
return
case perfTc.locate("Perf#", NumVal) :
msgInfo("Performance # already exists", NumVal)
return

otherwise :
msgInfo("Creating Performance", DateVal + " and " + TimeVal)
perfTc.edit()

perfTc.insertRecord()
perfTc.Perf# = NumVal
perfTc.Perf_Date = DateVal
perfTc.Perf_Time = TimeVal
perfTc.Perf_Comms = MessageVal
perfTc.endEdit()
message("Building Who Perform Records")
switch
case not whoTc.open("Whopf.db") :
msgStop("Open Error", "Whopf.db")
return
case not charcTc.open("Charc.db") :
msgStop("Open Error", "Whopf.db")
return
otherwise :

for i from 1 to 90
whoTc.edit()
whoTc.insertRecord()
whoTc.Perf# = NumVal
whoTc.Part# = charcTc.Part#
whoTc.SDesc = charcTc.SDesc
whoTc.Prn_disp = charcTc.prn_disp
if whoTc.endedit() then
message("Posted ", whoTc.Part#)
else
msgStop("Post Failure", whoTc.Part#)
endIf

charcTc.nextRecord()
endFor
whoTc.endedit()
whoTc.close()
charcTc.close()
message("Sucessfull Creation of WHOPF ")
endSwitch

endSwitch
;;;; build kid who perf (kwhopf)
if kids.attach("Charc.db") then
kids.setIndex("ptype") ; use ptype index
kids.setFilter("K", "KG") ; select kids & kidgroups
kidtc.open(kids)
whoTc.open("KWhopf.db")
for i from 1 to 97
whoTc.edit()
whoTc.insertRecord()
whoTc.Perf# = perftc.perf#
whoTc.Part# = kidTc.Part#
whoTc.SDesc = kidTc.SDesc
whoTc.Prn_disp = kidTc.prn_disp
if whoTc.endedit() then
message("Posted ", whoTc.Part#)
else
msgStop("Post Failure", whoTc.Part#)
endIf

kidTc.nextRecord()
endFor
kidtc.close()
whotc.close()

else
msgStop("attach error", "CHARC.DB")
endIf
;;; now build xtras who perform (xwhopf)
if xtras.attach("Charc.db") then
xtras.setIndex("ptype") ; use ptype index
xtras.setFilter("X", "X") ; select xtras
xtc.open(xtras)
if not whoTc.open("xwhopf.db") then
msgStop("Open Failure", "XWHOPF.DB")
endIf
for i from 1 to 6
whoTc.edit()
whoTc.insertRecord()
whoTc.Perf# = perftc.perf#
whoTc.Part# = xtc.Part#
whoTc.SDesc = xtc.SDesc
whoTc.Prn_disp = xtc.prn_disp
if whoTc.endedit() then
message("Posted ", whoTc.Part#)
else
msgStop("Post Failure", whoTc.Part#)
endIf

xtc.nextRecord()
endFor
xtc.close()
whotc.close()

else
msgStop("attach error", "CHARC.DB")
endIf
perfTc.close()

else
msgStop("Attach Error", "perfm")
endIf

;;;; update counter
if perfNoTC.lockRecord() then
perfNoTc.PerformanceNo = NumVal.value ; update counter to latest
NumVal.value = NumVal + 1 ; use next number for update

perfNoTc.unlockRecord()
else
msgStop("Problem", "Couldn't lock PERFNUM Table!")
endIf

endmethod

 

[TonyMcGuire]

So, you have a table named 'perfM.db', and a field in the table named 'perfM'?

I've never heard of an autoinc behaving as you describe.

Perhaps this is an example of why everyone steer people away from autoinc, and suggest a longint with roll-your-own incremented meaningless integers. Based on your code, it looks like you are doing that anyway.

I'd change that autoinc to longint, which SHOULD preserve current values. Then increment that field yourself. It could still be used as the field for sorting, as you wish.


Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.