Auto Incrementing a number field (not autonumber field)

[helraizer1]

I have a database with 4 tables: tblMembers, tblRides, Session table and Reservation table. One of the fields in the Members table is set to number, but now I have all relationships set up and the data inserted, but one problem is now that when I add a new user it doesn't increment.. Is there any way with VBA, that I can grab the last CustomerID from the table and add one to it, and insert that as the new ID?

I was thinking to grab the last CustomerID of using

Dim SQL As String
 
SQL = "SELECT CustomerID FROM tblMembers ORDER BY CustomerID DESC;"
 
DoCmd.RunSQL SQL

That would simply get the value... the part of using it as a new ID may be doable with an INSERT INTO query.

how would I then increment the value from the query by one?

Sam

[Rabbit]

That would not return a value. DoCmd.RunSQL does not return any values. Plus, your current code will throw an error because your SQL string is not an action query.

Use the DMax function to return the highest number and then add 1 to it for the new number.

[missinglinq]

Where CustID is the textbox holding the incremented number, something like:

If Me.NewRecord Then
   Me!CustID = Nz(DMax("CustomerID", "tblMembers"),0) + 1
End If

Linq ;0)>

[PotatoChip]

Where CustID is the textbox holding the incremented number, something like:

If Me.NewRecord Then
   Me!CustID = Nz(DMax("CustomerID", "tblMembers"),0) + 1
End If

Linq ;0)>

I am using the exact same function but, I need my number to read 010001. I added the code with a few records already in the database with that number format but all my new records drop the first zero e.g 10002. I know it must be super simple but how do I get Access to add the zero at beginning?

Thanks!

[missinglinq]

You need to wrap it in the Format() function. For a six digit number it'd be something like this

If Me.NewRecord Then
   Me!CustID = Format(Nz(DMax("CustomerID", "tblMembers"),0) + 1,"000000")
End If

I think I got the parens right, but can't check right now. Post back if you have problems.

Welcome to TheScripts!

Linq ;0)>

P.S. Should we call you "Lays" or "Ruffles?"

[PotatoChip]

You need to wrap it in the Format() function. For a six digit number it'd be something like this

If Me.NewRecord Then
   Me!CustID = Format(Nz(DMax("CustomerID", "tblMembers"),0) + 1,"000000")
End If

I think I got the parens right, but can't check right now. Post back if you have problems.

Welcome to TheScripts!

Linq ;0)>

P.S. Should we call you "Lays" or "Ruffles?"

It doesn't work. :(
If I enter it the way you suggest, all I get is 0. I tried typing "010000" and all I get is 1000 and when I try going to a new record, I still get 1000. Thanks for the attempt, though.

Ruffles

[jaxjagfan]

It doesn't work. :(
If I enter it the way you suggest, all I get is 0. I tried typing "010000" and all I get is 1000 and when I try going to a new record, I still get 1000. Thanks for the attempt, though.

Ruffles

Make sure the data type of the CustomerID field is Text. And if you are entering the number in a control such as a text box - make sure it isn't using a number format.

[PotatoChip]

Make sure the data type of the CustomerID field is Text. And if you are entering the number in a control such as a text box - make sure it isn't using a number format.

It's set to Text, not Number. I'm starting to give up on the whole damn thing!

[blyxx86]

It's set to Text, not Number. I'm starting to give up on the whole damn thing!

The function I use here works like this...

Function get_callnumber()
    dim LP 
    LP = 1
    while(LP = 1)
 
        ' Instantiate a Recordset object, get the next avai call #
        Set rsCALL = Server.CreateObject("ADODB.Recordset")
        strSQL = "select CALLNUMBER from CONFIG"
        Session("lastSQL")= strSQL
        rsCALL.Open  strSQL, objConn, 3, 3
 
        callnumber = Cstr(rsCall("CALLNUMBER"))
        new_callnumber = Int(rsCall("CALLNUMBER")) + 1
        rsCALL.close
 
        strSQL = "update CONFIG set CALLNUMBER = '" & Cstr(new_callnumber) & "' "
        session("lastSQL")= strSQL
        rsCALL.Open  strSQL, objConn, 3, 3
 
        ' Instantiate a Recordset object, check if call # is already exists
        strSQL = "select * from SERVICECALLS where CALLNUMBER = '" & callnumber & "' "
        rsCALL.Open  strSQL, objConn, 3, 3
 
        if (rsCall.EOF) then
            LP = 0
            get_callnumber = callnumber
        end if
 
        set rsCALL= nothing
    wend
End Function
 

It works by storing the last (known) good "CallNumber " in a table called CONFIG. Then when the function is called, it generates the new number based on the number in the record in CONFIG, and compares it to the table to make sure it doesn't exist. If it does exist it loops until it finds one that is valid and it then updates the CONFIG table with this last (known) good "CalLNumber ".

Of course, you can perform some formating on the number as a string, for example using the "Format" function to ensure that the number is in the specified pattern you want. You will want to use the CStr(Format(get _callnumber()," 000000")) For example.. I hope this hels or guides you a ways.

[missinglinq]

If your ID field is text, you have to use the Val() function to convert it to a number in order to add to it. This will work for a text ID "number."

If Me.NewRecord Then        
Me!CustID = Format(Nz(DMax("val([CustomerID])", "tblMembers"),0) + 1,"000000")
End If

Linq ;0)>