Creating autonumbers in forms

Question

I creating this code for a form that I want to auto number, but it is not working. It comes up with a message about missing data member

On Error GoTo ProcErr

  If Me.NewRecord Then

    Dim strWhere As String

    Dim varResult As Variant

    strWhere = "Permit# Like ""*" & Format(Date, "yy") & """"

    varResult = DMax("Permit#", "TASKS", strWhere)

    If IsNull(varResult) Then

    Me.Permit# = "001-FI-" & Format(Date, "yy")

    Else

    Me.Permit# = Format(Val(Left(varResult, 3)) + 1, "000") & _

                      Right(varResult, 6)

    End If

  End If

ProcExit:

    Exit Sub

ProcErr:

    MsgBox "Error #" & Err.Number & ", " & Err.Description & " - Form_Current "

    Resume ProcExit

End Sub

Answer 1

I'm not a big fan of composite keys. I prefer using autonumber datatypes as my primary keys. This insures uniqueness and makes joins easier. You can still impose uniqueness over a combination of fields by using a multi-field unique index.

Ken's example and mine (from my blog) are different methods to accomplish the same thing. Look at both and choose what you feel works best for you.

Answer 2

I agree with Scott that single column structured keys like this should be avoided.  You can use a composite key of the year and the generated number.  You might also care to take a look at CustomNumber.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

which includes an illustration of how to generate sequential numbering per group (the year in your case).  In the event of a conflict in a multi-user environment this is handled in the form's Error event procedure using a variation on the method published by Roger Carlson at the link given in my demo.

Answer 3

An autonumber is a specific datatype in Access that generates a unique number. So you aren't "creating" an autonumber, but a sequential number.

As Tom noted, by using the octothorpe (#) in your field name you need to surround it in brackets.

Also, I would not store the Permit as you are doing. Please check out my blog on Sequential Numbering. It details how to create a sequential number. The techniques are similar to what you are doing, But its much better to keep the sequential number as a separate field and assemble the permit # by concatenating the components.

Also you need to commit the record immediately after generating the sequence number to avoid duplication.

Answer 4

Since your field name uses funny characters, you have to wrap it in angular brackets:

strWhere = "[Permit#] Like   etc.

The other thing you can do when the error appears again is to hit Ctrl+Break. This will activate the debugger. The instruction pointer will be on the MsgBox line.

Below the "Resume ProcExit" write:

Resume

Drag the instruction pointer to that line. Hit F8 to step. You are now on the offending line. Use the debug tools to inspect variables etc. and the problem should become apparent.