Assign next sequence # inside a form

[Bjesparz]

I need to assign a new, sequential, Work Order number inside the WO# field of my Orders table. I also want to to have control over the number assignment using a double click button in the Order form. (Note: I'll use the same process to assign an Invoice number as well.

I want control over the numbers because not all entries will require a Work Order number. (By the way all records are tracked with an AutoNumber)

I have a table called SysCode # with a field called Last WO# which is used to track the last number I used. By activating the "Next Work Order #" button, I want the SysCode table field: Last WO# to updated by adding 1, and then insert the new number into WO# field in the Orders table.

Please help me figure this problem out

[NeoPa]

When assigning a number, use DMax() of existing records then add 1.

[Bjesparz]

When assigning a number, use DMax() of existing records then add 1.

Thanks for the reply. How can activate this using a form buttom?

[PEB]

In the form bouton choose on click property and assign event procedure int window trhat appears:

 
Dim last_number
dim mydb as database
dim myr as recordset
Set mydb=CurrentDb()
Set myr = mydb.openrecordset("SELECT Number_Column, MyNumber FROM SysTable WHERE MyNumber='WorkOrders';")
myr.movefirst
myr.edit
myr("[Number_Column]")=myr("[Number_Column]")+1
Me![WorkOrderSequence]=myr("[Number_Column]")
myr.update
myr.close
mydb.close
 
 

Take care my frien to do the replacements as it needed....

:)