DMax() for sequential autonumber

[banza]

DMax() and Module for sequential autonumber

Hello Access friends,
Trying to have a sequential autonumber for the ScreenID with the DMax () function.
Please advise on what is wrong with the following :
=Nz(DMax("[ScreenID]","[Screenprep]","[ScreenID] = '" & [CarModel] & "-" & Left$([Category],1) & "'")+1,0)
Neither putting this code in control source or beforeupdate event of the form is not working.

I have looked around and from previous posts in the forum come up with this module. But it is not working either:
Public Function NewScreenID() As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim ScreenID As String
Dim CarModel As String
Dim Category As String

On Error GoTo Err_Execute

Set db = CurrentDb()


Set rst = CurrentDb.OpenRecordset("SELECT Max([Screenprep].[ScreenID]) AS MaxScreenID from [Screenprep];", dbOpenSnapshot)

If IsNull(rst!MaxScreenID) Then
NewScreenID = [CarModel] & "-" & Left$([Category], 1) & Format(1, "0000")
Else
NewScreenID = rst!MaxScreenID + 1
End If


rst.Close
Set rst = Nothing
Set db = Nothing

NewScreenID = ScreenID

Exit Function

Err_Execute:
'An error occurred, return blank string
NewScreenID = ""
MsgBox "An error occurred while trying to determine the next sequential number to assign."


End Function

In advance thank you for your time.Can someone please guide me on how to sort this out?

 

[WayneRyan]

banza,

A few problems here.

First, you don't have a static length for your CarModel field.
For example; Mustang, Pinto, LTD.

If you add the Category character you start having data like:

LTDS1
LTDS2
LTDS3
PintoS1
PintoS2
MustangS1
MustangC1
MustangC2
.
.
.
MustangC12

Now we have multiple problems. Any code is going to have a problem
extracting the [Category] and/or sequence number. Also, when you
sort them you'll get: MustangC1, MustangC10, MustangC11 ... MustangC2
People won't like seeing that the "11" entry is before the "2" entry.

The solution?

Keep the fields separate: [CarModel], [Category] and [ScreenID]

The ONLY place that you will need any code is in your form's BeforeInsert
event:

ScreenID = Nz(DMax("[ScreenID]", _
                   "[Screenprep]", _
                   "[CarModel] = '" & Me.CarModel & "' And " & _ 
                   "[Category] = '" & Me.Category & "'"), 0) + 1

Then when you want retrieve it for display, use a query:

DisplayField: [CarModel] & [Category] & [ScreenID]

They are so much easier to assemble than take apart.

Wayne