How to start counter at 0001

[nunan]

Hi

I have used the following code for a number of years with no issue an initially, the data for this database was imported from excel with unique ID numbers already listed so I just had to set the counter to carry on from the last ID number which was over 1000. However, I am creating a new database and will be entering all the details manually so I need the ID number to start at 0001 and increment to 0002, 0003 etc on each new entry. The problem is no matter what number I type in, it always removes the zeros from the start.

Any help appreciated.

Thanks

Option Compare Database
Option Explicit

Function Next_Custom_Counter()

On Error GoTo Next_Custom_Counter_Err

Dim rs As ADODB.Recordset
Dim NextCounter As Long

Set rs = New ADODB.Recordset

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

'Get the next counter.
NextCounter = rs!NextAvailableCounter

rs!NextAvailableCounter = NextCounter + 1
NextCounter = rs!NextAvailableCounter
rs.Update

rs.Close

Set rs = Nothing

Next_Custom_Counter = NextCounter

Exit Function


Next_Custom_Counter_Err:
MsgBox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End
End Function

 

[PHV]

Use a number format like "0000"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nunan]

Hi

Thanks for the reply.

I have tried this in the tables and forms associated with the action but it doesn't work.

The data is entered through a form, when the members name is added, it takes an operator of 'mem' and adds the next available number from the counter table creating a full membership code, for instance 'mem4'. What I want it to do is show as 'mem0004' so that the numbers will always be the same length.

Thanks

 

[PHV]

mem" & Format(Next_Custom_Counter, "0000")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nunan]

Thanks I appreciate your help, where do I place this?

 

[dhookom]

where do I place this?

What I want it to do is show as 'mem0004'

You place it where you want to display it.

Duane
Hook'D on Access
MS Access MVP