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]

I'm just wondering why in the form you're using CustID but in the DMax you're using CustomerID. Should they not be the same? Aside from that, the code should work fine.

[missinglinq]

CustID is the name of the control on the form. CustomerID is the name of the field in the table. Despite the fact that Access will name the control the same as the field, if you drag the field from the Field List Box to the form, many experts believe it's good policy to change the name of the control to something different. There are times when Access will get confused if they're both the same.

And yeah, the code is tested and functions fine.

Linq ;0)>

[Rabbit]

CustID is the name of the control on the form. CustomerID is the name of the field in the table. Despite the fact that Access will name the control the same as the field, if you drag the field from the Field List Box to the form, many experts believe it's good policy to change the name of the control to something different. There are times when Access will get confused if they're both the same.

And yeah, the code is tested and functions fine.

Linq ;0)>

I see. If I do that, which is rare because I'm lazy, I like to prefix it with a control identifier like txt or lbl.

[sierra7]

Hi

If I want to 'pad-out' a value with zeros, say next CustID = 1234 (a number) but I want 0001234 I suggest;-

Dim CMax as Long
 
CMax = Nz(DMax("[CustomerID]","tblMembers"),0) + 1
 
Me!CustID = Right((100000000 + CMax), 7)

I would probably write it in one line without using CMax but t's easier to read like this.

When using a Dynamic Lookup function I usually check that the field name bit, [CustomerID], has square brackets around even if it hasn't spaces in it.

S7

[PotatoChip]

Not better way to start a Wednesday at work than with a happy dance. It works perfectly! Thanks for all the help!