Autonumber based on a variable field.

[Winnie]

I'd like to have a autonumber(datatype=text) created
based on the first letter of the variable from another
field on the same table but don't know how. For example:

CaseNo Network
B1 BigBoy
B2 BigGirl
V1 Vteam
V2 Vgroup

The CaseNo should be first letter of variable "Network" +
an auto serial number for letter each group.

Please help.

 

[Allen Browne]

Winnie, it would simplify thing greatly if you could just use a Number in
CaseNo. This avoids a whole raft of problems such as B10 sorts before B2 and
choosing ranges of values. You can still print the desired combination on
your report with a text box that has its ControlSource set to:
Left([Network], 1) & [CaseNo]

If you are happy to do that, you can assign the next available case number
in the BeforeUpdate event procedure of your form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If IsNull(Me.Network) Then
Cancel = True
MsgBox "Network required."
Else
Me.CaseNo = Nz(DMax("CaseNo", "MyTable", _
"Network = """ & [Network] & """"), 0) + 1
End If
End If
End Sub

 

[Tim Ferguson]

This avoids a whole raft of problems such as B10 sorts before B2 and
choosing ranges of values.

Not to mention the update problems when "BigBoy" turns into "Adolescent"
and you have to change all those B34's into A34's -- always assuming that
there wasn't an A34 in the first place. Etc etc.


Tim F