Replacing AutoNumber functionality

[antifashionpimp]

Hello,

I’m looking for a few tips as an inexperienced access database programmer.

I have a table that contains field such as PersonNumber(primary key), Surname and Name. The field type for person number is Long Integer.
Then, I have a form, with which I want to add a new record to this table, without using Access’s New… navigation button. I want the next available PersonNumber to appear on the form in a TextBox say txtPersonNumber. Then text boxes for the person' name are blank and provided for user input, and once OK is hit, a new record must be created and added to the table.
What is an efficient way of doing this? Must I write code that looks at the last value of PersonNumber in the table, and then add 1 to that number?
I know that there is the Autonumber field type, but I do not want to use this.

Please help!

Thanks in advance!

 

[maxmangion]

the functionality you're looking for is exactly what the autonumber does ... therefore why are you trying to create code for something which already exists ?

 

[antifashionpimp]

Well, I don't want to use AutoNumber for the following reason: if the user is in the middle of adding a new record, and then cancels, the number generated by AutoNumber is lost (forever?).

Would have been great to use it, but not in this case I think.

I am thinking of using this now:

txtPersonNumber = 1 + DMax("PersonNumber", "tblPersons")

 

[maxmangion]

txtPersonNumber = 1 + DMax("PersonNumber", "tblPersons")

in this way if you have 100 records and deleted record 56 for example, if you're going to add a new record you still are going to get 101 and not 56.

 

[antifashionpimp]

Chances that I will delete records are not very high. Just want sequential numbering assigned to people.

thanks for ur advice anyway

 

[Grimmers]

Maxmangion,

I have a need to do what the displayed code doesn't do!!

How do I get number 56 to be the next available number in an "auto number" situation?

cheers,



David

 

[Mile-O]

You shouldn't need to see the autonumber anyway.

 

[Grimmers]

But what if...

I want to fill in unused membership numbers?

cheers,


David

 

[cable]

Well, I don't want to use AutoNumber for the following reason: if the user is in the middle of adding a new record, and then cancels, the number generated by AutoNumber is lost (forever?).

ok but then you say:

Chances that I will delete records are not very high. Just want sequential numbering assigned to people.

then surely autonumber is fine, if your not bothered by the missing 56, then why does it matter that a few canceled records occur.

Also are you sure thats the behaviour?, i thought that if you undo before the record is written no autonumber will be missed?

 

[Mile-O]

Also are you sure thats the behaviour?, i thought that if you undo before the record is written no autonumber will be missed?

As soon as a record is "opened" it has the next possible number reserved; if the record is not saved then the reserved number is lost and the next possible number will be the next number reserved.

 

[antifashionpimp]

Hey, I didn't want too rip the AutoNumber function to shreds! Just wasn't too sure of using it. That function I gave earlier (txtPersonNumber = 1 + DMax("PersonNumber", "tblPersons") )works fine...

 

[Pat Hartman]

Autonumber works the way it does for a reason. That reason is to avoid generating duplicate id's in a multi-user environment. Your code could potentially generate duplicate id's in a multi-user environment and the earlier in the process that you generate the number, the more likely you are to generate a duplicate.

When you generate your own autonumbers, you should include error trapping to identify a duplicate before attempting to add it so you can generate a new number.