Ok so I have a form (WorkOrderInput) that is used for enterring work orders into the (CSA Work Orders) table and I am having trouble getting an issue resolved. The (WorkOrderNumber) field holds the work order number that is created by selecting the last (WorkOrderNumber) from (CSA Work Orders) and then adding 1 to it like so:


Dim db As Database
Dim rs As Recordset

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT TOP 1 [CSA Work Orders].[WorkOrderNumber] FROM [CSA Work Orders] ORDER BY [CSA Work Orders].[WorkOrderNumber] DESC")
If rs.RecordCount > 0 Then
x = rs.Fields("workordernumber")
End If

DoCmd.GoToRecord , , acNewRec

Forms!CSAWorkOrdersInput!WorkOrderNumber.Text = x + 1


DoCmd****nSQL "INSERT INTO [CSA Work Orders] (Priority,WorkOrderNumber) VALUES (Forms!CSAWorkOrdersInput!Priority,Forms!CSAWorkOr dersInput!WorkOrderNumber) " _

After it has created the new work order number it then Inserts the info into the table to make sure that WorkOrderNumber is unique and not duplicated if someone else happens to be creating a second work order at the same time.

If the user wants to cancel the current document they are working I have a cancel button that opens up an outlook email letting our vendor know we cancelled the current work order before we sent it to them, they freak if there is a gap inbetween work order numbers. I have tried everything to get Access to Insert the data on the form into the table and set the status to "Cancelled" so we can keep track of cancelled work orders in case we get calls about the work order number in the future, BEFORE it opens the Outlook email and no matter what I do it just wont insert the data. Here is the Cancel button code:


Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim stTicketID As String '-- The ticket ID from form
Dim RecDate As Variant '-- Rec date for e-mail text
Dim RecTime As Variant '-- Rec Time for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stWho As String '-- Reference to tblUsers

Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error

varTo = "vendor@vendor.net"

stSubject = ":: EOC Work Order Cancelled ::"

stTicketID = Me.WorkOrderNumber
RecDate = Me.[Date Received]
RecTime = Me.[Time Received]

stText = "The EOC created the following work order, but cancelled it before they sent it to you." & Chr$(13) & _
Chr$(13) & stTicketID & Chr$(13) & _
RecDate & Chr$(13) & _
RecTime & Chr$(13)

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

Originally I had this statement at the beginning of the Cancel button code before the DoCmd.Save command:

Forms!CSAWorkOrdersInput![Status].Text = "Cancelled"

DoCmd****nSQL "UPDATE [CSA Work Orders] SET [Type of work] = Forms!CSAWorkOrdersInput![Type of Work] WHERE [CSA Work Orders].[WorkOrderNumber] = Forms!CSAWorkOrdersInput!WorkOrderNumber " _

Can anyone bail me out of my misery here please?