Custom Counter Demo help...

[Joe S Slow]

I copied the Custom Counter Demo from the Microsoft website and it works
great.

Now I have a question... rather than creating a new function for each
different counter, I wanted to pass it a string that it can use to look up
the table instead. Problem is that I know I have the syntax wrong. Can
somebody help me out?

What I want is to put in the 'Default value' of a field with
=Next_Counter_New(ID2) and that will look up the tblCounter field ID2 for
the next available number. The problem is if I set CounterString as a string
variable and then I call rs!CounterString, I think it is not translating
that as a variable. How do I make it become a variable?

Function Next_Counter_New(CounterString As String) As String

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'For this code to work, it is required that you reference the:
' Microsoft ActiveX Data Objects 2.x Library
'To reference this library, go to the Tools menu, click
'References, and select the library from the list of available
'references (version 2.1 or higher).
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error GoTo Next_Counter_New_Err

Dim rs As ADODB.Recordset
Dim NextCounter As Long



Set rs = New ADODB.Recordset


'Open the ADO recordset.
rs.Open "tblCounter", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

'Get the next counter.
NextCounter = rs!CounterString
'MsgBox "Next available counter value is " & Str(CounterString)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Open table and get the current value of NextAvailableNumber,
'increment the value by 10, and save the value back into the table
'
'The next line can be changed to conform to your custom counter
'preferences. This example increments the value by 10 each time.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
rs!CounterString = NextCounter + 1
NextCounter = rs!CounterString
rs.Update

'MsgBox "Next available counter value is " & Str(NextCounter)

rs.Close

Set rs = Nothing

Next_Counter_New = 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_Counter_New_Err:
MsgBox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End
End Function

 

[Joe S Slow]

Don't you hate it when you spend hours searching then resort to posting and
on the next search you find the answer you are looking for?

rs(variable) was what I was looking for instead of trying to use rs!variable
to make my function work.