auto number 6

[lancemeroniuk]

Hi all..

How can I have auto number start at a number that I dictate?

Let me know

Thnaks

Lance

 

[Rolliee]

I have heard there is a way to trick the autonumber but it was very obscure. It is not straighforward to do.


rollie@bwsys.net

 

[lancemeroniuk]

Hi Rollie..

Thanks for the input.... but how could you do this? Or is it there a generic script out there for this?

Later

Lance

 

[Rolliee]

I would empty the table and compress the mdb. This brings the autonumber bacck to zero. Then copy and paste the table to make another table without data. In the new table, change the autonumber type to long and set the value what you wish. Then append it to the old now empty, now zero autonumber table. I believe this will work.

Rollie E

 

[lancemeroniuk]

Hi Rollie....

Microsoft could not have made the any harder could they.. All I want to start my invoice number at say 5001. I did'nt think that was too tough to do in access.


Proven wrong again.....

Thanks

Lance

 

[Rolliee]

Lance,

You are welcome. Remember tho, if someone is of help, vote him/ her a star with the "mark this post as a helpful..."

Rollie E

 

[johnbrugman]

Lance,

Are you aware that you have no control on a autonumber field? I understand you want to use it for invoice number purpuses. You'd better use a normal Numeric field instead! Otherwise you can lose invoice number, i.e. when you accidentally add an invoice you want to delete. You then lose the number as well!

For adding a new invoice to your database there must be some code to give the highest invoice number plus one ..

Greetings,

John.

 

[Rolliee]

Lance,

That was a good comment on not using the autonumber. Code can easily be written to create your own. Care must be taken to keep two users f rom creating and using the same one but it is really no big deal.

rollie@bwsys.net

 

[rgibson1969]

Check out the article at

http://support.microsoft.com/default.aspx?scid=kb;en-us;209696

 

[mph1]

Lance,

I do the following to create a new invoice number, but the concept can be used to create any numbering system.

1. Table - Field named Invoice with Data Type number and a
long integer

2. Form - text box with control source set to invoice and
the Defualt Value =GetNextInvoice()

3. Module - Public Function GetNextInvoice()

'get next frees Invoice Number

GetNextInvoice = Nz(DMax("[Invoice]", "[tblName]&quot + 1, 1)

End Function


HTH

Maurie

An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[lancemeroniuk]

Thank you Marie.... thsi exactly what I was looking for...

Works great and easy to use...

Thank you Thank you Thank you

 

[danvlas]

No, I wouldn't do that...

The default value is calculated BEFORE even starting a new invoice. Therefore, two users who start creating a new invoice at approximately the same time will have the same invoice number calculated!

You might be tempted to use the BeforeInsert event. Don't do that, as the DMax function only considers saved records and more than one user could be currently entering new records - they would obtain the same value.

The solution is to allow only one user to generate a value at a given moment and additionally, prevent all other users from calculating a value BEFORE the record is saved to the database.


Create a dummy table with one field and some value in that field. It does not matter what field type or what data you have there.

In the form's module, declarations section, declare a recordset variable.
In the BeforeUpdate event of the form, open the recordset and start an edit in pessimistic mode. If any other user attempts to create an invoice number, he/she will encounter a 3260 error.
Calculate the next invoice number and put the value in the InvoiceNo field.
Close the recordset in the AfterUpdate event of the form.
In this way, each user will only get new invoice numbers-from saved records.

Action flow sequence:
User enters all relevant data
->BeforeUpdate event is triggered
->Check if it is a new record, and if yes
->Attempt to edit DummyTable
->If success, lock DummyTable and get next invoice number
->If failed
->If already retried, abort and deny saving the record
->Go back to attempt to edit the dummy table
->BeforeUpdate event ended
->AfterUpdate event is triggered (the record is saved)
->Release the DummyTable and let other users do their stuff




'==========Full Code=========================
Option Compare Database
Option Explicit
Dim rst As DAO.Recordset

Private Sub Form_BeforeUpdate(Cancel As Integer)
Const conRetries = 5
Dim i As Integer
On Error GoTo ErrHandler
If Not NewRecord Then Exit Sub

start:
Set rst=CurrentDb.OpenRecordset("Select * from DummyTable", dbOpenDynaSet, dbPessimistic)
rst.Edit
Me.InvoiceNo = Nz(DMax("[Invoice]", "[tblName]&quot + 1, 1)
ExitHere:
Exit Sub
ErrHandler:
Set rst = Nothing
i = i + 1
If i = conRetries Then
Beep
If MsgBox(i & " attepmts failed. Retry?",vbYesNo + vbQuestion) = vbYes Then
i = 0
Resume start
Else
Cancel = True
Resume ExitHere
Else
Resume start
End If
End Sub

Private Sub Form_AfterUpdate()
Set rst = Nothing
End Sub
'=========End Code=====================

As a side note, you can keep the last generated number in the dummy table and get the value from there. This can speed up the process:
Instead of:
Me.InvoiceNo = Nz(DMax("[Invoice]", "[tblName]&quot + 1, 1)
you can have:

rst.Fields("FieldInDummyTable&quot = rst.Fields("FieldInDummyTable&quot + 1
Me.InvoiceNo = rst.Fields("FieldInDummyTable&quot

and Update the recordset in the AfterUpdate event. However, you have to trap the error 'Update Without AddNew or Edit':

Private Sub Form_AfterUpdate()
On Error Resume Next
rst.Update
Set rst = Nothing
End Sub

As I said, it's faster, but you have to make sure nobody has access to the 'dummy table', otherwise you can have the numbering sequence reset.

Both ways, user won't know the invoice number until finishing the record.

Good luck,



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Neowynds]

Maurie,

Where would that go and what would the code be? I am looking at making a numbering system for case numbers and need something similar to an invoice number. Also there is very little chance of two users entering records simultaneously on my database since there are only three authorized users.

Thanks

Neowynds

 

[danvlas]

Neowynds:

3 simultaneous users and you think it's very little chance for them to go simultaneously? They will surely step on each other's toes before you know it, take my word for that...
Even one fellow on one workstation can open 2 instances of the program and the stupid computer will think there are 2 different users [LOL]

But...do as you wish, it's your problem after all...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Neowynds]

I see your point, however the nature of this particular database is that the user will not even have it open unless there is a security incident (i.e. fire, theft, etc.) The chances of three officers all filling out incident reports on three things that happened simultaneously is slim to none. They have no access to run reports or queries. They are strictly data entry. Any reports, queries, deletions and whatnot will be handled by myself personally as it is not a very large database and is not going to be getting much larger in the forseeable future. So, I was just wondering if someone can help me write the code for that and where it goes please.

Thank you,
Neowynds

 

[MichaelRed]

Neowynds, the eternal optimist?



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mph1]

Neowynds,
Where would that go and what would the code be? I am looking at making a numbering system for case numbers and need something similar to an invoice number. Also there is very little chance of two users entering records simultaneously on my database since there are only three authorized users.

I am not really sure what you mean by "where would it go and the code." It should go exactly as I indicated. However, keep in mind, if there are multiple users, ie, more than one user has the ability to enter data simultaneously, then Daniel is correct. When creating a database, try to take all possibilities into consideration. Although the possibilites are SLIM, would you want to be responsible if that SLIM occured.

Maurie

An investment in knowledge always pays the best dividends.
by Benjamin Franklin

 

[Eupher]

Neowynds, not to pile on here, but I have to agree with Daniel and Michael. It's your choice, of course, and you are the one who is most familiar with your work environment; but Murphy's Law is always in effect. I would suggest, at a minimum, that you set up your database to open in exclusive mode. If, as you say, there is little chance that multiple users would simultaneously be entering data, then this won't be overly restrictive, but it WILL help prevent the aberration.

HTH...

Ken S.

 

[Neowynds]

Thank you I think I will go with opening in exclusive mode. Just on the offchance. Thanks for the advice.


Neowynds the REALIST. I am not optimistic nor pessimistic but a delightful blend of both. Wich sometimes causes a quarrel in my mind. You dont wanna see me when THAT happens. TRUST ME.

Thanks again for all the help.

Neowynds

 

[Neowynds]

OK,

Not sure if anyone is still monitoring this thread or not, but I have set up my database to be exclusive so that only one person at a time may have the database open.

And these are the steps I took to try to get an autogenerated CaseNumber

Table = SIR

1. Table - Field named CaseNumber with Data Type number and a long integer

2. Form - text box with control source set to CaseNumber and the Defualt Value =GetNextCaseNumber()

3. Module -
Public Function GetNextCaseNumber()

'gets next free CaseNumber

GetNextCaseNumber = Nz(DMax("[CaseNumber]", "[SIR]&quot + 1, 1)

End Function

Instead of generatingp a new number as was described the CaseNumber box on the form instead says #Name?


Can anyone please help

Neowynds