Having problem with MultiUser Counter

[a24t42]

I am having problems getting a custom counter for multi user to work.
I am trying to follow Microsoft's article #210194 - How to create a
Mulituser custom counter but am not having any luck.

Here is what I have -
A table called CounterTable with one field - NextAvailableCounter.
This is the primary field, long number.

A table called frm3PHPad that has a field called ID - Primary key,
long number.

For the form, I have the following code set on BeforeUpdate
------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Function Next_Custom_Counter()
On Error GoTo Next_Custom_Counter_Err
Dim MyDB As Database
Dim MyTable As Recordset
Dim NextCounter As Long
'=================================================================
'Open table and get the current value of "Next Available Number , ""
'increment the value by 1, and save the value back into the Table
'=================================================================
Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("CounterTable")
MyTable.Edit
NextCounter = MyTable("NextAvailableCounter")
'=================================================================
'The next line can be changed to conform to your custom counter
'preferences. This example increments the value by 1 each time.
'=================================================================
MyTable("NextAvailableCounter") = NextCounter + 1
MyTable.Update
Next_Custom_Counter = NextCounter
Forms!frm3PHPad![ID] = NextCounter
Exit Function
'================================================================
'The following error routine should be replaced with a Custom
'error routine. This example only resumes execution when an error
'occurs. If a record locking error occurs this is fine; however,
'any non-record locking error will result in an infinite loop.
'================================================================
Next_Custom_Counter_Err:
MsgBox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End
End Function
End Function


What I am trying to do is get the field [ID] to act as the counter,
for which the number is looked up in the table CustomTable and stored
in the [ID] field.

I hope that makes sense. Any help would be appreciated.

 

[Terry]

I suspect that the ID field you are trying to update is an Autonumber field
which is not user updatable. Is it a LONG or is it an Autonumber (which is a
long)? It can only be updated if it is defined as Long and the new value is
unique as it is a PrimaryKey.

Assuming ID field is Long and NOT Autonumber an easier way may be:
NextCounter = nz(Dmax("ID","<tbl name of table containing ID field>"),0)+1

The nz function kicks in if the DMax returns a null and gives a value of 0
so the new record number would be 1.
This method uses a lot less code but the DMax function like all the
aggregate functions are not super flash. It looks at the table containing the
value you want and returns the highest value it can find and increments it by
1. The down side is it ignores any numbers which have been discarded (So too
does Autonumber).
Another down side is if User 1 gets a number, say 232 and leaves the record
open which s/he has a cup of coffee and User 2 gets the next number which
User 1 is at coffee and saves the record, User 1 will get an error when s/he
tries to save his/her record. The best way of handling this is to get the
number at the last possible minute during the save and advise the User of the
number after the save.
If you are trying to get the ID to advise the user just use Autonumber and
when assigning the values during the save set a variable to the ID. eg
Rs.Addnew
lngID = RS!ID
rs!Fld1 = Val1
Rs!Fld2 = Str2
...
Rs.Update
msgbox "The new ID is " & lngID
HTH
Terry

I am having problems getting a custom counter for multi user to work.
I am trying to follow Microsoft's article #210194 - How to create a
Mulituser custom counter but am not having any luck.

Here is what I have -
A table called CounterTable with one field - NextAvailableCounter.
This is the primary field, long number.

A table called frm3PHPad that has a field called ID - Primary key,
long number.

For the form, I have the following code set on BeforeUpdate
------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Function Next_Custom_Counter()
On Error GoTo Next_Custom_Counter_Err
Dim MyDB As Database
Dim MyTable As Recordset
Dim NextCounter As Long
'=================================================================
'Open table and get the current value of "Next Available Number , ""
'increment the value by 1, and save the value back into the Table
'=================================================================
Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("CounterTable")
MyTable.Edit
NextCounter = MyTable("NextAvailableCounter")
'=================================================================
'The next line can be changed to conform to your custom counter
'preferences. This example increments the value by 1 each time.
'=================================================================
MyTable("NextAvailableCounter") = NextCounter + 1
MyTable.Update
Next_Custom_Counter = NextCounter
Forms!frm3PHPad![ID] = NextCounter
Exit Function
'================================================================
'The following error routine should be replaced with a Custom
'error routine. This example only resumes execution when an error
'occurs. If a record locking error occurs this is fine; however,
'any non-record locking error will result in an infinite loop.
'================================================================
Next_Custom_Counter_Err:
MsgBox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End
End Function
End Function


What I am trying to do is get the field [ID] to act as the counter,
for which the number is looked up in the table CustomTable and stored
in the [ID] field.

I hope that makes sense. Any help would be appreciated.