Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

Duplicate form ID numbers in database

DKinsley
Level 9
Level 9


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();


}



1 Reply
DKinsley
Level 9
Level 9

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?