Code for incrementing

[Viv1]

Hi,

2 issues I need to find out about:

1)
I have a database which will need to store members details that all have a unique MembershipNo, the Primary Key. There are over 4,000 members already existing with their own MembershipNo, I have to insert these into the member table (from a spreadsheet). I want the database to increment new membership numbers by 1 each time although I also need existing members to be entered in with their correct MembershipNo. Is there any code that can do this without having to use the AutoNumber data type which will mess up the existing membership numbers?

2)
I also have another table called Member_Application which needs to increment by 1 each time a new application is entered. The problem is I have an append query that appends the Member Application fields to the Member fields when an application is successful. To do this the Member table needs the MembershipNo field to be an AutoNumber, as the data is inserted into the fields and a MembershipNo must be generated automatically for this not to cause integrity issues. Basically if it appends to just a number field there is no MembershipNo created and the database sees this as being NULL and throws up an error. The ApplicationNo and MembershipNo are not the same.

If anyone can shed any light as to how to overcome this problem I'd be grateful.

Many Thanks, Viv

 

[hymn]

code for incrementing
Assuming that membershipNo comes afterSurname and the table name is tblperson

Private Sub Surname_AfterUpdate()

MembershipNo = DMax("[MembershipNo]", "tblperson") + 1
End Sub

could you not just inset another field into your table called memberID and make that an AutoNumber

Hope this helps
Hymn

 

[john2thompson]

In reply to 1)

When you have imported your spreadsheet with the current membership numbers, you can use code to increment any new entries to follow on straight from the last membership number in that field.

You can create a select query recordset that is sorted descending (so the largest number is the first record) then, take this first record, and increment the number by 1)

Something like the following:

Dim db As Database
Dim rstNextMemberNumber As Recordset
Dim intNextNumber As Integer
Dim strSQL as String

strSQL = "SELECT * FROM <MemberTable> ORDER BY <MemberTable>.MembershipNo DESC;"

Set db = CurrentDB
Set rstNextMemberNumber = db.OpenRecordset(strSQL)

rstNextMemberNumber.MoveFirst

intNextNumber = rstNextMemberNumber![MembershipNo]
intNextNumber = intNextNumber + 1

rstNextMemberNumber.Close

Set rstNextMemberNumber = Nothing
Set db = Nothing

Or something similar. Is this the kind of thing you were looking for?

Regards,
John