Jo:
You still haven't answered the question I posed in the final paragraph of my previous post, so I'm having to assume the numbers are distinct per 'code' not over all. Assuming this to be the case you could do it in the AfterUpdate event procedure of the PatternNumber control on the main form, coupled with code in the form's AfterInsert event procedure, so that once you've entered the letters into it, the code then firstly adds the formatted new number to the letters and then, in the AfterInsert event procedure, inserts a new row into the SerialNumbers table. So the relevant code for the form's module would be
Option Compare Database
Option Explicit
' declare module level variables to hold
' pattern 'code' and next number
Dim varCode As Variant
Dim lngNextNumber As Long
Private Sub Form_AfterInsert()
Dim strSQL As String
' insert new row into SerialNumbers table
strSQL = 'INSERT INTO SerialNumbers(PatternCode,PatternNumber) ' & _
'VALUES('' & varCode & '',' & lngNextNumber & ')'
CurrentDb.Execute strSQL, dbFailOnError
End Sub
Private Sub PatternNumber_AfterUpdate()
Dim strCriteria As String
Dim strSQL As String
varCode = Me.PatternNumber
If Not IsNull(varCode) Then
' has a number been added to the letters?
If Not IsNumeric(Right(varCode, 4)) Then
' if so get next number for letter 'code'
strCriteria = 'PatternCode = '' & varCode & ''
lngNextNumber = Nz(DMax('PatternNumber', 'SerialNumbers', strCriteria), 0) + 1
' add formatted numbers to letters and assign to PatternNumber control
Me.PatternNumber = Me.PatternNumber & Format(lngNextNumber, '0000')
End If
End If
End Sub
If you do get stuck trying to implement this you can mail me at:
kenwsheridan<at>yahoo<dot>co<dot>uk
and I can let you have a simple little form with the code in place. That should get you stated, though I don't like having the two tables storing what are in essence are duplicates of the same data. It's redundancy, and as such, leaves far too much room for anomalies to creep in.
One thing to note about this is something which tends to be common to this type of automatic number generation. If you add a new pattern number and save the new record, and then for some reason change it to another one, the old number will not be reused when you next add a new pattern of the same 'code'.
There are other potential pitfalls. A user could enter a complete alphanumeric code into the control in the form, in which case the code to insert the number would not be generated. Moreover, the complete alphanumeric value would be assigned to the varCode variable, so a spurious row would be inserted into the SerialNumbers table. Steps could be taken against this, but you'd have to jump through all sorts of coding hoops to do so. This is what happens I'm afraid when you play fast and loose with the relational model, whereas if you stick to a properly normalized structure along the lines I suggested the data becomes self-protecting by virtue of the constraints inherent in the logical model.