I have a database that we have been using for about 2 years It has now been discovered that if two individuals are in the database at the same time and create a new log that there is duplicate, triplicate, etc. numbers being created; however, the person to close the record first gets the number and the other users lose the number as if it was never created; however, users don't know this because they don't have access to the back door of the database.Basically what I'm trying to do is create this so that the users will get new numbers each time the form is open and if two users access the form at the same time that the number will change to the next available number after the first user closes the database...Please help.When the first form is opened to create a new log # the following code is in the form
rivate Sub Form_BeforeInsert(Cancel As Integer) Dim mTable As String, mField As String, mYearPart As Long, mNextNumber As Long 'number is returned as 6 characters: YY#### 'where YY is the last 2 digits of the year '#### is the next number for that year 'D is the DSCR Code 'format code to display this number should be "00D-0000" mTable = "DSCR" mField = "DSCRNumber" mYearPart = Right(CStr(Year(Me.DSCRYear)), 2) * 10000 mNextNumber = Nz(DMax(mField, mTable, mField & ">=" & mYearPart), 0) If mNextNumber = 0 Then mNextNumber = mYearPart End If mNextNumber = mNextNumber + 1 Me.DSCRNumber = mNextNumberEnd SubI've added the following code to the afterinsert event of the form (per another newsgroup):'force the record to be saveddocmd.runcommand accmdsaverecord' Check that the record is the only one with that IDDO WHILE dcount("DSCRNumber", "TABLENAME", "[DSCRNUMBER] = " & me.numDSCRNumber) > 1 then ' is bigger than 1 - must be another one there ' increment number me.numDSCRNumber = me.numDSCRNumber + 1 ' force save again docmd.runcommand accmdsaverecord ' loop back to top and perform dcount check again...loopWell I was all excited that this was the resolution to my problem but it didn't work. Here is the problems:1. When I put the data into the "afterinsert" event it didn't like the "then" statement in this line, which I removed... DO WHILE dcount("DSCRNumber", "TABLENAME", "[DSCRNUMBER] = " & me.numDSCRNumber) > 1 then2. When I entered the data into the form and click the button to go to the main menu I received this error... "Method or data member not found" It highlighted....numDSCRNumberI assumed this was suppose to be one of my field "DSCRNumber" so I removed the num from the front of the name for each time it showed in the code.3. Then I had two users try to enter data and get "2" different numbers. The first user finished and got number 07D-0155 the second user finished and got no number. When I went back out and looked at the DSCR table...there was only a number 07D-0155 for the first user and nothing for the second user.Please help me! I feel so confused and lost.
rivate Sub Form_BeforeInsert(Cancel As Integer) Dim mTable As String, mField As String, mYearPart As Long, mNextNumber As Long 'number is returned as 6 characters: YY#### 'where YY is the last 2 digits of the year '#### is the next number for that year 'D is the DSCR Code 'format code to display this number should be "00D-0000" mTable = "DSCR" mField = "DSCRNumber" mYearPart = Right(CStr(Year(Me.DSCRYear)), 2) * 10000 mNextNumber = Nz(DMax(mField, mTable, mField & ">=" & mYearPart), 0) If mNextNumber = 0 Then mNextNumber = mYearPart End If mNextNumber = mNextNumber + 1 Me.DSCRNumber = mNextNumberEnd SubI've added the following code to the afterinsert event of the form (per another newsgroup):'force the record to be saveddocmd.runcommand accmdsaverecord' Check that the record is the only one with that IDDO WHILE dcount("DSCRNumber", "TABLENAME", "[DSCRNUMBER] = " & me.numDSCRNumber) > 1 then ' is bigger than 1 - must be another one there ' increment number me.numDSCRNumber = me.numDSCRNumber + 1 ' force save again docmd.runcommand accmdsaverecord ' loop back to top and perform dcount check again...loopWell I was all excited that this was the resolution to my problem but it didn't work. Here is the problems:1. When I put the data into the "afterinsert" event it didn't like the "then" statement in this line, which I removed... DO WHILE dcount("DSCRNumber", "TABLENAME", "[DSCRNUMBER] = " & me.numDSCRNumber) > 1 then2. When I entered the data into the form and click the button to go to the main menu I received this error... "Method or data member not found" It highlighted....numDSCRNumberI assumed this was suppose to be one of my field "DSCRNumber" so I removed the num from the front of the name for each time it showed in the code.3. Then I had two users try to enter data and get "2" different numbers. The first user finished and got number 07D-0155 the second user finished and got no number. When I went back out and looked at the DSCR table...there was only a number 07D-0155 for the first user and nothing for the second user.Please help me! I feel so confused and lost.