Please Help

[StupidStudent]

Hi all fisrtly Wicked forum loads of great info!!
I was wondering if anyone could help me i am new to databases and have managed to cobal together something for a uni project however i have hit a wall.

I have a supplier order form which has a txt box called SuppOrderNum which is the primary key. I wanted this to be "SO1" and then "SO2" and so on. (This is due to not being allowed to use auto number??) Would anyone know an easy way to do this PLEASE !! I have looked every where bt have no idea what to look for

THANKS

 

[boblarson]

Check out the DMax function to get the last number assigned and add one to it. Autonumber doesn't guarantee a consecutive number anyway. It only guarantees a UNIQUE number.

 

[StupidStudent]

Thanks for super quick reply.....
would this need to be added to an event??

 

[boblarson]

Yes, you would figure out when you want to assign it and make the call. The best time is as late as possible, in this case, so that you don't wind up with many gaps in your numbering. If somone starts a record and then cancels or doesn't finish, then that number is not taken and someone else can have it. If you assign it right away and someone else starts a record while the other one is being added, then the next number would be assigned to the second person but if the first person canceled the entry of their record then you would lose that number as the DMax would take the next number which would be the one after the number the second person had.

You will still likely end up with a few gaps, even if you assign it late in the process, but you will have far fewer.

 

[StupidStudent]

thanks again you have been a life saver !!!!

 

[StupidStudent]

hi me again thanks again for support b4 it seems that DMax is the best way to go however it just wont work ???

if anyone has any time this is what i have on the txt box SuppOrderlnNum....

Private Sub SuppOrderlnNum_AfterUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!SuppOrderlnNum = Nz(DMax("SuppOrderlnNum", "Supplier Order Line"), 0) + 1
End If


End Sub

THANKS!!