manual autonumbering...

[Tim L]

I've got a database that *had* autonumbering; this number is used as a 'membership number' for each member in the database. Unfortunately someone else decided that it would be a 'good' idea to set the membership number field to Number and then discovered that he couldn't change it back to autonumber (and has duly been chastised and lambasted).

We need the membeship number field to increment by 1 each time a new record is added so I had to find a new method to set the autonumber field (we can't just remove the membership number field and replace it with an autonumber field because the membership numbers are fixed to individual records).

My solution was to create a query that found the current highest membership number, to have this displayed in a hidden listbox and to take this value, add one and put that in the membership number field when the 'add record' button is clicked.

Although this works I can't help thinking that there is a more elegant solution, can anyone suggest how I could have achieved this entirely in code?

Tim

 

[Milothicus]

i = DMax("MembershipNo", "tblMembers") + 1
Me.txtMembershipNo = i

"MembershipNo" is the name of the field in the table that includes the membership number

"tblMembers" is the name of the table that includes the membership number

me.txtMembershipNo is the name of the control (i assumed a text field) on your form that you'd like to enter the new number into.

dmax is a maximum within a domain (in this case the table)