Having trouble with Next Number

[WyzrdX]

Here is what I am doing;



I have db which I am creating for my mothers office. Due to the economy her assistant was laid off and I am trying to help her consolidate several .xls sheets into an access db.

My current issue is this.

When they quote a job she (or her assistant previously) opens the quotes.xls sheet and uses the next open number. These are usually filled in during slow times by the assistant.

The format they use is 10-1234, 10 being the year and 1234 being the # of quotes done for the year to this point.

The quote number is assigned and the customer information is then filled in.

This is what I have atm but it is not working.

I have a seperate tbl "NextNumber" for the quote numbers. The form I have has a field for quote number with this code

= Format(Date(), "yy") & Format (NextNumber(), "0000")

And I have a standard module with this code.

Public Function GetNextNumber() As Long
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
 strSQL = "Select NextNumber From tblNextNumber"
 Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
     With rst
        GetNextNumber = !NextNumber
        .Edit
        !NextNumber = !NextNumber + 1
        .Update
    End With
 Exit_Here:
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Function
 Error_Handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Here
 End Function

I got this information from the MSDN boards.

My 2nd issue is less complicated.

Once a customer accepts a quote a job number is issued. The Job # is just the last 4 of the quote # and the mmyy eg. 1234-1210

I think if I can get the first issue corrected I should be able to get the 2nd one done.

Any help with the first is greatly appreciated and will be returned to others as much as I can.

[RuralGuy]

Let's start by seeing if this link will help: http://www.baldyweb.com/CustomAutonumber.htm

[WyzrdX]

I am checking it out now. Will post back.