I am trying to use the following code to reset a custom counter:
(I forgot where I got the code but it's not my own and I did not remove
and credits, this is how it came)
Function Next_Custom_Counter()
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'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_Custom_Counter_Err
Dim rs As ADODB.Recordset
Dim NextCounter As Long
Dim PrefixA As String
Dim PrefixB As String
Set rs = New ADODB.Recordset
'Open the ADO recordset.
rs.Open "CounterTable", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
'Get the next counter.
NextCounter = rs!nextavailablecounter
PrefixA = rs!CurrentPrefix 'stored in table as format(Date,"yymmm")
PrefixB = Format(Date, "yymmm")
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
'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.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
If StrComp(PrefixA, PrefixB, vbTextCompare) = 0 Then
MsgBox ("same")
GoTo Increase
Else
GoTo Reset
MsgBox ("different")
End If
Increase:
rs!nextavailablecounter = NextCounter + 1
NextCounter = rs!nextavailablecounter
rs.Update
rs.Close
Set rs = Nothing
Next_Custom_Counter = NextCounter
Exit Function
Reset:
rs!nextavailablecounter = "0000"
rs.Update
rs.Close
Set rs = Nothing
Exit Function
[/code]
Nothing happens during the if statement. It will not even pass along
the custom counter to the call from the form. What I understood from
the A2K helpfile is that if the strings were equal it would return a 0.
What I want it to do is if the prefixb (IE: 05APR) is the same as the
current prefix (IE:05APR) then nothing happens and the counter
continues on. If there is a difference (IE: different year or month)
the counter is reset to 0.
Any help would be greatly apprecetaed as midnight trips to work to
reset the number manualy is getting old.
Dave
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''
'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