MUlti-user custom counter (1 Viewer)

[Jon.N]

I am trying to create a rather unusual custom counter. Any ideas will be greatly appreciated.

I have already tried ideas based on the Microsoft Article 210194 (Formerly (Q210194) and I can't get it to work.

The process is this:
1. Someone creates a record that is essentially details of a project. A project code and a component code are manually entered, e.g. '123' and 'A'.

2. During the course of time someone else comes along and records component changes in the database. They retrieve the record for the project and in a subform (the many end of a 1-M relationship) they record component changes.

This is where the custom counter is raised, (when component changes are added). The format of the custom counter is project code and component code, a hyphen and a count of the previous comonent changes +1, e.g. 123A-1, 123A-2, etc. It is the count that I am having difficulty with.

I have found that when two people are simultaneously trying to raise different changes on the same project and component at the same time the count duplicates, e.g. 123A-3 and 123A-3. I cannot stop this from happening.

As stated already I have adapted the technique suggested in article 210194 to no avail and here is my latest attempt. I am trying to lock the table where the change is recorded rather than trying to lock a counter table:

Dim rs As ADODB.Recordset
Dim NextCounter As Double

Set rs = New ADODB.Recordset

rs.Open "tblChanges", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

NextCounter = DCount("[ChangeID]", "tblChanges", _
"[ProjectCode]&[ChangeCode=" & "'" & Me.[FindBox] & "'")

Forms![frmProject]![subfrmChanges].Form![CustomCounter].Value _
= Me.ProjectCode & "-" & Me.BoardCode & NextCounter + 1

DoCmd.RunCommand acCmdSaveRecord

rs.Close

 

[FoFa]

Given the speed this code would run at, it would be very hard to actually duplicate a value, not impossible, but difficult. Using this type of code to do what you are doing is normal. There are other methods to do this, but each has the same drawback. One option, if you have very large tables, is to index the value, then obtain the max value and increment that. It can use the indexes instead of table scans to return the data and that results in fast code execution. But then you have to handle the "no rows meet your criteria" in code rather than letting count return a zero value to add one to. I have used this method for years, in many applications and have yet to see a duplicate value, but I know it can happen. But given this code most likely will execute in microseconds, two people actually hitting the same row, to generate the same key at basically the same instant seems remote.
The other thing you can do, is index that value and not allow duplicates.

 

[Jon.N]

Agreed, it is difficult to generate duplicate the custom counter. The unfortunate thing is that I am up against some big guns in the company who want everything absolutely bullet-proof. Perhaps we need a new forum, 'designing/coping with company politics'.

This is my first attempt at a custom counter; previously I have managed to convince people not to go there. Your comments have been useful in as much they confirm the code is reasonably sound. I think my solution is to add a routine that looks for duplicate values and then recalculates the counter if a duplicate ever occurs.

Thanks for your help FoFa.

 

[WayneRyan]

Jon,

My first recommendation would be to store the components of the
key in seperate fields. You can always put them together for
reports and so forth. You can generate the trailing number
component if you can sort by date, AutoNumber, or whatever.
That alleviates "holes" in your numbering scheme.

Your current method presents several issues:

Don't use DCount!

Example:

Currently records 1,2,3,4
Delete Record 3
Next record is 4

You need to use DMax:

Next = Nz(DMax("your table info here"), 0) + 1

If you can't have any missing numbers, then you'll have to complicate
matters by looping through a recordset and reassigning numbers using
the form's AfterDelete event:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim ctr As Long

ctr = 1

sql = "Select * " & _
      "From   YourTable " & _
      "Where  ... " & _  <-- Note: Primary Key Conditions
      "Order By ... "    <-- Note: Order by your key field

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sql)

While Not rst.EOF And Not rst.BOF
   rst.Edit
   rst!CustomCounter = rst!ProjectCode & "-" & rst!BoardCode & Format(CStr(Ctr), "00#")
   rst.Update
   ctr = ctr + 1
   rst.MoveNext
   Wend

I'm not too sure about the Format call above. You DO WANT TO pad that number so they
don't sort like:

abc-1
abc-11
abc-2
abc-21
abc-22
abc-3

Maintaining the number as a seperate component alleviates this.

Wayne