servers,forms and auto#

[Durien512]

guys i need some quidance...

i have a server setup that i need some help with

i have a database file that holds 1 inventory table.

Then i have 2 other database files that represent 2 retail stores.

my problem is that the store files have a form which acts like a sales invoice. The sales order number is an autonumber. this worked fine in the past with local network setups, but now going to a server the issue is that the sales order numbers cannot be the same from one store to the other. Since its related to the inventory table it steals the invoice from one store to the other..

i tried formatting the autonumbers differently.. like /100 on one and /3000 on the other. And they are still the same autonumber anyway.. the format doesnt mean much other than a different display..

is there a way to control the auto numbers to be in a specified range of numbers...

i would like to be able to say for store 1 the sales order numbers can only be from 1000-5000 and store 2 6000-9000...

is it not possible with autonumbers?

ho can i do this?

 

[FoFa]

Autio number you can assign a starting number and an increment is all. Sounds like you need to include a store identifier now. Might not be the easiest way to do what youwant, but it is better over all.

 

[Durien512]

how can you assign a starting number to an auto #? i was under the impression that that was not possible

 

[FoFa]

how can you assign a starting number to an auto #? i was under the impression that that was not possible

OK, confusing SQL server and Access, my bad. Or maybe it was older versions of access, I can't remember.
Maybe for what you want you would be better off just writing a VBA function to assign the next number and use it.

 

[Durien512]

yeap..i ended up using dmax... and assigning a starting number to each db,,

Private Sub Form_BeforeInsert(Cancel As Integer)

On Error GoTo lerror

Dim r As Recordset, db As Database, x As Double
Set db = CurrentDb
Set r = db.OpenRecordset("CUSTOMER SALES DATA********")

x = DMax("[orderID]", "CUSTOMER SALES DATA********") + 1

Me.OrderID = x

lerrorexit:
Exit Sub

lerror:
MsgBox Err.DESCRIPTION
GoTo lerrorexit

End Sub

 

[Banana]

A caveat emperor-

If this is a multi-user database (you say it's server-ready, so I'd assume so), DMax() will create duplicate values if two users query at same time and try to save the record. If you can design it so the number is created as it is being saved, this may help reduce the possibility of duplicating values.