Auto-incrementing number in field containing non sequential numbers

[chazer1]

In a pre-existing Access 2000 club membership database I have a column called 'membership_No'

The pre-existing membership numbers are not all sequential (many numbers are missing) and the field is not auto incrementing. As there is already data in this field I can’t change it to be autoincrementing.

I would like this ‘membership number’ to automatically increase by one every time I open a form I have called 'add a new member'.

 

[Bat17]

Private Sub Form_Current()
If Me.NewRecord Then
Me!membership_No.DefaultValue = Nz(DMax("[membership_No]", "tblMembers"), 0) + 1
End If
End Sub


HTH

Peter

 

[chazer1]

Thanks for the VB script...but I'm unfamilliar with Visual Basic. When I look under the project explorer Microsoft access class objects there is an object called Form_add_a_new_member
When I open this the following code appears:
-------------------------------------
Private Sub Add_a_new_member_click()
On Error GoTo Err_Add_a_new_member_Click

DoCmd.GoToRecord , , acNewRec

Exit_Add_a_new_member_Click:
Exit Sub

Err_Add_a_new_member_Click:
MsgBox Err.Description
Resume Exit_Add_a_new_member_Click

End Sub
Private Sub Exit_Click()
On Error GoTo Err_Exit_Click

DoCmd.Close

Exit_Exit_Click:
Exit Sub

Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click

End Sub

Private Sub Save_Data_Click()

End Sub

Private Sub Label48_Click()

End Sub

Private Sub Membership_Category_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Membership_Number_BeforeUpdate(Cancel As Integer)

End Sub
----------------------

Now should I just paste your code into here or should I insert a new module or class module? Sorry to ask but I have no experience of VB. The membership number and personal details are contained in a table called tblmembers.

Many Thanks

 

[Pat Hartman]

If you would like to convert this to an autonumber (that is my recommendation rather than generating your own number), you can do so by following the following steps:

1. Remove any existing relationships
2. Rename the existing table
3. Create a new empty table with identical definitions except that the key field should be defined as an autonumber.
4. Run an append query that selects all the columns and rows from the existing table and appends them to the new table.
5. Make sure that the primary key is properly defined.
6. Reestablish any relationships.

An append query is the ONLY way to add data with preexisting autonumber values.

 

[chazer1]

Unable to convert discontinous number field to auto incrementing field

Still unable to convert non sequential 'membership numbers' in a field in my 'club membership table' to auto incrementing and keep my pre-existing numbers in this field.

I've tried creating a new table and appending my data to it but the existing membership numbers in the new table are changed.

Also tried the code:

Private Sub Form_Current()
If Me.NewRecord Then
Me!membership_No.DefaultValue = Nz(DMax("[membership_No]", "tblMembers"), 0) + 1
End If
End Sub


but get a message "Runtime error. Object doesn't support this property or method"

Any Idea how to get this to work?

 

[Agnostiker]

Hi Chazer,

if it's not allowed to change the membership numbers, you could create this new table with an autoincrement field and then add empty records (they have only this memb. number), for example, if you have numbers in the range [0...2000] you have to add 2000 new records.
Then you'll update joining numbers with your existing data. After this you can delete still 'empty' records or use them for new members.

Only a small idea...

Willi

 

[Pat Hartman]

chazer1, all you have to do with the append query is to map ALL the fields. So the old key field MUST map to the new autonumber field. It WILL work. If you got new ID's assigned, it was because you didn't append the key value or because you tried to copy and paste the rows rather than append them with a query.

 

[Agnostiker]

Ops, my idea was really stupid, sorry!
I didn't knew before.
Thanks Pat!

Willi

 

[chazer1]

Auto Incrementing Numbers

Thanks Pat.....this works just great.

In access 2000 I just used the copy & paste functions, created a new table (structure only) and then used the paste table as/append data command.

Now I have auto incrementing Membership Numbers!

Many Thanks