Duplicate form ID numbers in database

DKinsley

10-07-2015


My form connects to an Access database. When the employee enters their employee ID number and exits that field, the form searches thru the FileName database column to find the largest number. It then adds 1 to that number (maxID), makes the rawValue of the FileName field on the form the same number, adds a new record and updates the database with the new information. It works great with one exception. I keep getting duplicate numbers in the database but it only happens occassionally. I can't figure out why it happens. When it does occurr, both forms were created within a couple minutes of each other. Please inspect my script and see if I have something wrong.


if (Subform1.FileName.rawValue == null || Subform1.FileName.rawValue == ""){


var nMaxID =0;


//Reset DB to first record


var oDB = xfa.sourceSet.DataConnection1;


try{


oDB.first();


}


catch(e){


oDB.open();


oDB.first();


}


//walk thru all records to find latest file ID


while(!oDB.isEOF()){


if(xfa.record.DataConnection1.FileName.value > nMaxID)


var vValue = xfa.record.DataConnection1.FileName.value;


  nMaxID = Number(vValue);


oDB.next();


}


oDB.addNew();


Subform1.FileName.rawValue = (nMaxID + 1);


Hide1.FileName1.rawValue = Subform1.FileName.rawValue;


oDB.update();


Hide1.EmployeeNo.rawValue = Subform1.EmployeeNo.formattedValue;


Hide1.Originator.rawValue = Subform1.Originator.rawValue;


Subform11.FileName.rawValue = Subform1.FileName.formattedValue;


this.resolveNode("OrderDate").rawValue = util.printd("yyyy-mm-dd", new Date());


Hide1.DateTimeExitedEmpNumber.rawValue = Subform4.DateTimeExitedEmpNumber.rawValue;


oDB.update();


oDB.close();


}


else{


//HEADER SECTION


//Reset DB to first Record


var oDB = xfa.sourceSet.DataConnection1;


oDB.open();


try{


oDB.first();


}catch(e){


oDB.open();


oDB.first();


}


//Walk thru all records until test value is matched


var oFile = Subform1.FileName.formattedValue;


var bFound = false;


while(!oDB.isEOF()){


if(xfa.record.DataConnection1.FileName.value == oFile){


bFound = true;


break;


}


oDB.next();


}


if(bFound){


}


Hide1.EmployeeNo.rawValue = Subform1.EmployeeNo.formattedValue;


Hide1.Originator.rawValue = Subform1.Originator.rawValue;


Subform1.FileName.rawValue = Subform1.FileName.rawValue;


oDB.update();


oDB.close();


}



Accepted Solutions (0)

Answers (1)

Answers (1)

DKinsley

16-07-2015

No reply. Okay, let me rephrase my question.

I seem to only get duplicate numbers when two users are creating new forms at very close to the same time. Is there a different way to write this script so the next highest database number (FileName) is saved to the database more quickly so two forms do not find and write the same number?