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
Code:
= Format(Date(), "yy") & Format (NextNumber(), "0000")
And I have a standard module with this code.
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.