Set Start Value for Order Number

[scouser]

Guys I have been having a dabble but am unsure how to proceed. I currently have a tblOrders, OrderID = AutoNumber. I have added a field 'OrderNumber'. I want to set the initial value of OrderNumber to 1000. I have butchered some code:Module modSetNum (No Laughing!!)

Public Sub SetNum ()
Dim dblOrderNo As Double
Dim dblEnquiryNo As Double

dblOrderNo = Nz(DMax("InvoiceNo", "tblInvoice") + 1, "1000")
dblEnquiryNo = Nz(DMax("InvoiceNo", "tblInvoice") + 1, "1000")
End Sub

Right how do I get the above to work? I will need to amend my order form to show the OrderNumber not current OrderID field. I have no idea how to call the module to set the value for my OrderNumber!! (or if the module code actually works!!!)
Cheers,
Phil.

 

[norman]

Guys I have been having a dabble but am unsure how to proceed. I currently have a tblOrders, OrderID = AutoNumber. I have added a field 'OrderNumber'. I want to set the initial value of OrderNumber to 1000. I have butchered some code:Module modSetNum (No Laughing!!)

Public Sub SetNum ()
Dim dblOrderNo As Double
Dim dblEnquiryNo As Double

dblOrderNo = Nz(DMax("InvoiceNo", "tblInvoice") + 1, "1000")
dblEnquiryNo = Nz(DMax("InvoiceNo", "tblInvoice") + 1, "1000")
End Sub

Right how do I get the above to work? I will need to amend my order form to show the OrderNumber not current OrderID field. I have no idea how to call the module to set the value for my OrderNumber!! (or if the module code actually works!!!)
Cheers,
Phil.

You don't need any code to do this, make the field autonumber and follow the instructions in Access help - autonumber.
Change the starting value of an incrementing AutoNumber field

For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number..................

HTH
Norman

 

[scouser]

Cheers

Thanks for that. I will have a look at the help. Never been much good putting the help menu examples to good use!!

 

[scouser]

Primary Key Problem

I did as suggested. All was going well until I attempted to remove the PK from OrderID field. (See attached Jpeg). No matter what I do to the Relationship table I can't remove the error!! I am attempting to amend a number in an existing table, not new.
Cheers,
Phil.

 

[neileg]

I don't think Norman's idea is good, even if you can get it to work. The use of an autonumber in a multiuser environment will lead to non-continuous numbering. An autonumber value will be set when a new record is started by a user. The next user will then get the following number. If the first user abandons the record, then that autonumber will not be allocated to a record, but the following user's autonumber will be retained, thus leaving a gap.

A better idea is to use DMax to find the largest existing number, but only allocate that when the record is complete and committed. Applying an index, no duplicates, will ensure that simultaneous saves will not result in duplicate numbers.

There may be other solutions.

If you want to commence your numbering at 1000, then seed your table with a dummy record with a number of 999. You can arrange to either delete this later, or exclude it from any queries.

 

[scouser]

Dirty Hacker

Neil any chance of some sample code and how to implement the Dmax option? How will that tie in with my current set-up? At the moment all my data is test data so I can delete EVERYTHING if required!!

I need to implement this in 2 tables:
tblOrders - PK = OrderID / AutoNumber
tblEnquiries - PK = EnquiryID / AutoNumber

I also have tblOrderServices - PK = ID & a foreign Key OrderID (Same for tblEnquiries.......)

Hope the additional info helps!!!!
Phil.

 

[neileg]

I suppose you could have two buttons on your form that collects the data from the use. Set one to undo the record if the user wants to close without saving the record. The second one should be the Save button. In here I would requery the control that holds the order number. The control should have its data source set to =DMax(tblMyTable.Ordnerno)+1 (substituting the relevant atble and field names). If you think a changing number in here would confuse the user, set the control to invisible and pop up a message box displaying the order number.

You may have to play around with this a bit to get it to work. I'm just a hacker, you see, not a proper programmer!

 

[scouser]

All working

Picked this up on my travels:

If Me.NewRecord Then
    On Error Resume Next
    Me!OrderID.DefaultValue = Nz(DMax("[OrderID]", "tblOrders"), 999) + 1
End If

Not sure what the 'Resume Next' bit does!!
Phil.

 

[neileg]

Yes, good. Setting the value if no record is found to 999 is better than my idea of seeding the table.

I suspect that this code frogment has come from a For..Next loop, and the resume next ensures that the process isn't halted on an error.