auto numbers 2

[drewduncan]

hello everyone!

The project I am working on at the moment has a job form. Each job has a unique identifier - a JobEnquiry Number.

I am using an autonumber to generate the JobEnquiry Number but there is one small problem. The JobEnquiry Number has a prefix to the number, namely: GEO therefore the JobEnquiry Number looks like GEO1, GEO2, GEO3 and so on.

Does anyone know how I can 'add' the prefix to the autonumber? Also can you begin an autonumber at a certain value and then start incrementing from their??

Any help would be greatly greatly appreciated!

Cheers

Drew

 

[danvlas]

1.To add a prefix:
Leave the autonumber as is, and use the expression:
FormattedAutoNumber: "GEO" & [IDField].
In this way it will appear as you want it and is searchable.

2. To start from a specific number:
Change the table name, field name and the starting number in the following append query:
INSERT INTO [YourTableName] ([AutoNumberField]) Values (YourNumber - 1);

Run the query.
Delete the row.

But, if you're going to give AutoNumber a significance for the user, give it up and think of another approach. AutoNumber does not guarantee a continuous sequence! It is only an abstract unique identifier!!!



Daniel Vlas
Systems Consultant

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

 

[fneily]

And one more way:
Go to design view, click on the autonumber field and in the Format Box at the bottom type "GEO"0 (or 00 or 000, etc. depending on how many integers you will have).

Remember what Mr. Dan said: if you delete or cancel an autonumber, it DOES NOT start or FILL IN numbers it already used. What a pain. Make your own primary keys.

Neil

 

[danvlas]

Neil, while I agree that it shows the values you expect, the field is not searchable that way... If you try to search 'GEO001', it won't find anything.




Daniel Vlas
Systems Consultant

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

 

[idd]

Daniel,

where do you use the expression

FormattedAutoNumber: "GEO" & [IDField].

i'm a bit confused ?*!

are you suggesting that we use the autonumber and format it using the expression above.

Idd

 

[danvlas]

Idd: in any query or text box.
for a query: include the table, add a field and just paste the expression. Change 'FormattedAutoNumber' to a name of your choice. And of course, [IDField] to the name of the AutoNumber field.

For a textbox: in a form or report, create an unbound text box.
Control source: ="GEO" & [IDField]

HTH



Daniel Vlas
Systems Consultant

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

 

[MichaelRed]

Also recognize that Autonumber is not suitable for multiuser apps. It Can (does) fail in situations where there are multiple "simultaneous" entries,




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[danvlas]

Michael: While I can't say I'm 100% positive about this, I have not yet encountered a case where AutoNumber failed to be unique (or to be generated), except for corrupt databases. I'd like to learn if there is any known circumstances that 'bust' AutoNumber functionality. And you're right: if it can, it does...

My (personal) opinion is that AutoNumber is great as a unique record identifier and it saves me (lazy me) from reinventing an existing wheel. Of course, if this wheel throws me out of the carriage, I'll have to reconsider my line of action.

I admit it stinks if used for any other purpose.

I have read your FAQ on generating unique numbers. It's great stuff for DAO. However, as I pointed out in my comment, ADO does not (yet) support dbDenyRead nor dbDenyWrite. And Access is moving (has moved already?) rapidly to ADO, even though I don't like it.

Sure, you can always set a reference to DAO, but is it worth the trouble?

Probably the adLockPessimistic would be suitable, but I haven't given it too much thought so far.

Best regards,




Daniel Vlas
Systems Consultant

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

 

[MichaelRed]

Dan,

At some long away and far ago place, I attempted to use "AutoNumber" as the PK for a call center db. during startup, the operators logging incomming calls complained that they couls/would/DID get errors during the entry process. While trying to ferret out the bug, I watched their operation and trapped the error which was 'ye olde duplicate key' thinggy. Obviously this did not occur often - but often enough is even once so it HAD to be fixed. In researching the topic, I found a statement from MS stating that one should use GUID in multiuser apps rather than AutoNumnber. Further 'research' led me to a statement that ~~ Ms. (JET) ' ,,, did not lock a table when obtaining the incremental value for use in the autonumber ,,, ', thus there was the posability that multiuser additions to a recordset (table) could request the 'newvalue' close enough in time such that the return value was the same, however the write to the db would always detect the conflict (duplicate value). Subsquent testing on the application appeared to confirm the statement, which was the original rationale for MY generation og the routine.

"AutoNumber" is no longer an entry in my "help" file, having been superceeded bu "NewValue" which does not include the recommendation which I recall, so this -without re-course to info no longer available to me- cannot be substantiated. My recent 'work' hardly ventures into actual applications, much less the furious pace of call center logging, so I am left with only the memory (rapidly fading?) and the results - as shown in the faq. Several others have challenged my assertion re autonumber generating errors, and I cannot cite reference(s) to prove any part of the process, but remain (painfully) aware of the consequences of the experience and attempt to caution individuals aginst using autonumber (increment) in multiuser situations.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[danvlas]

Well, that's good to know... I'm on my way to 'research' this. And, not seeing it doesn't mean it doesn't exist, so I will not challenge you stating it couldn't happen...

Have a great day!




Daniel Vlas
Systems Consultant

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

 

[checkOut]

In reply to this thread I want to tell 'my strangest thing'
I hope somebody can answer this with experience and give a good solvation.

I have a table with two fields as key (unique of course) and an autonumber to save the order of the rows. I make all visible in a list so the order is important.
Now the following happened: My autonumber is gone back from 38xx to 31xx so most of my records are not unique and bust..
The table structure is::
subLocRef
artRef (both primary key)
fld1
fld2
fld3 (gives the details)
ordered (autonumber)

In spite of the choice of the autonumber I have to found a good solution now.
I better turned every (three) rows with a column Ordered and the values 1,2 and 3. These do the same thrick.

So 3 questions:
1. How can it happen an autonumber isn't unique anymore?
2. How to repair my autonumber?
3. How can I solve this? The length of the 'coupled' details is 1, 2 or 3 so I need the Autonumbered column changed in a column which give my ordering back?

Hope somebody can help!
Gerard

 

[danvlas]

1. Read Michael's post again-it makes sense if you left the AutoNumber's indexed property to 'Yes (Allow Duplicates)'.

As I already said, I never saw it happen, but it doesn't mean it can't...

2. Tough job... Include a new field in your original table and populate it with 1. Then find the duplicates in AutoNumber and manually increment the new field. How many can they be?

Create a make-table query to return the records in the order you need them (order by AutoNumber and the new field). Do NOT include the AutoNumber in the query.
Run the query to transfer the data.
Create an AutoNumber field in it. Make sure the Indexed is Yes (No Duplicates).

This would re-create your unreliable numbering system which will fail again some time...

3.The order of rows is best achieved by a timestamp field, which could be a general date (mm/dd/yyyy hh:nn:ss).
You can order the records by:
Format([TimeStampField],"#,###.000000&quot

AutoNumber does NOT ensure the order of the records, because it is assigned BEFORE saving a record.

HTH



Daniel Vlas
Systems Consultant

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

 

[checkOut]

Thnx for ur reply:

In moment there are no duplicate rows, because the index is stand on (Duplicates No)
The problem is: My users can't work, so I need a quick way.
I think I do the follow:
I wrote an algorith to change the autonumber by numbering 1,2 or 3
then I skip the autonumber and set the querys (only 2 as far as I can see) to the new OrderedField.
And by the insert query set the orderedfield to 1,2 or 3

In my opinion this handle the trick...
Thnx another time,
Ur advices where great...
Gerard

 

[danvlas]

No duplicates, but AutoNumber not unique?? Aha...Now I think I better understand what happened to you. You deleted some N last records and compacted the database. In this way the autonumber was reset to LastRemaining+1.

Never delete the record with the maximum AutoNumber before compacting the database.

Good to know for the future...




Daniel Vlas
Systems Consultant

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

 

[messmerized]

Can I just leap in here and please ask!

Taking Drew's original question a step further. Is it possible to prefix an autonumber with the contents of another field in the same table. ie the initials of an employee so the result will be for example,
DAB102? and store the result (DAB102) in another field in the same table.

Cheers
Dave

 

[danvlas]

Use:

[Field1] & [Field2]

in any expression (query field, text box on a form or report).

No reason to store something that's already there...

Good luck




Daniel Vlas
Systems Consultant

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

 

[cheiboy]

I had a similar problem months ago using alpha's as a prefix, after much reading, I came up with this:
Aut
"UM-"0000 for tblUMCONCERN (used as a control number)

Field Size is Long Integer and in new values are increment, but, when I search for a specific number i.e. UM-0056, it does not find, so I found that it does not look at the prefix but rather just the number.

Hope this Helps.

 

[messmerized]

Danvlas

I seem to have Hi-Jacked Drew's thread here. Sorry Drew.

Tried the suggestion but no luck, What I am trying to do is create a Quote number made up of an employees initials and a number generated automatically, in sequence if possible.

Its the resultant "quote number" that I need to search my database on, so if a customer phones me with a this number I can search the database for the actual quote using that number alone by enterring it into a text box and clicking a command button

The quote form is running a query based on several tables.

Would appreciate any help that you or anyone can offer.

Cheers
Dave

 

[MichaelRed]

faq700-184



MichaelRed
m.red@att.net

Searching for employment in all the wrong places