Autonumber Blues 1

[cranebill]

Hello all let me first say any help will help me retain the little hair I have left.

The Problem: I have a table with the Autonumber feature as the data type. The first four numbers signify the year of the order number and the last four numbers are just simple the order form that point on. I am having trouble changing the job numbers from the start of the year on. I tried to manually change it, wrong answer for you cannot modify an autonumber field. So then I tried to change the autonumber field and I could not due to a relationship. Then I copied the database so thsat if I messed up the original was still in tact and deleted the relationship and then changed the autonumber feature to number and manually changed the job numbers, then proceeded to change datatype back to autonumber.... wrong answer again (hence the hair pulling). Im not sure even if I would be able to restore the relationship even if I changed the jobnumbers in the subtable.

Any help would so greatly be appreciated. I know it is possible somehow for someone did it at the change of last year, from 2000 to 2001.

 

[jebry]

Hi!

First, I think that the only way to accomplish what you are trying to accomplish is to create a new table with an autonumber field and make the field begin at 2002000. Do a search on this site to find out how to make the autonumber start at a number other than zero(I forget how because I have never used it, but I know there is a way). You will need to append the records from the old table to the new one, assuming you want them in a combined table.

Second, It sounds like this autonumber is important to your record-keeping and, if that is the case, then you probably shouldn't be using an autonumber at all. Access doesn't manage autonumbers well and can sometimes do unexpected things with them. If you are interested we can discuss other methods of accomplishing what you are trying to do.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[cranebill]

Im open for any suggestions or help, if we can do it without an autonumber feature then so be it and in that sense it will probably update itself for next year and so on. Thanx.

 

[jebry]

Hi again!

If the data is entered through a form you can use the following code in the form current event:

Dim intAutoNumber As Integer

If IsNull(YourTextBox) = True Then
intAutoNumber = CInt(Right(DMax("YourField", "YourTable&quot), 4) + 1
YourTextBox = Format(Date(), "yyyy&quot & Format(intAutoNumber, "0000&quot
End If

I am not sure how to do this at the table level if your users enter information directly to the table. I did find an easy way to format the autonumber correctly, just put this in the format field: \200\2000

hth Jeff Bridgham
bridgham@purdue.edu

 

[valley8338]

I came across this poist and it is exactly what I need to do. I am however a little confused as to where I put this in my form. Where is the form current event?

thanks!!

 

[evalesthy]

See thread 181-197379. Also, FAQ 700-184 by Micheal Red. I personally have never run into problems with autonumber fields but I do respect the arguments made by those who have. You'll have to decide what works for you.