Hi,
I am not great with the coding side of things with Access so I need a bit of help. I hope I can explain it properly.
I have a field that is made up of a Letter Prefix and then a Number Suffix (ie. AA-01). The Letter Prefix is a drop down list and then the Number Suffix is generated by looking at the Products table and adding 1 to the last number given to that Letter prefix. ie so the next number would be generated AA-02 etc.
The below code works well for this - it's located in the form in the Batch ID field. The problem to follow the code.
Private Sub BatchID_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
Cancel = True
Else
Dim prev As Variant
prev = DMax("[SequenceNumber]", "Products", "[BatchID] = " & Me!BatchID)
If IsNull(prev) Then prev = 0
Me!SequenceNumber = prev + 1
End If
End Sub
PROBLEM:
After the Prefix gets to a certain Lettering (ie. AQ) I require the Number suffix to be added in manually and not automatically generated by the above code but I cant figure out how to do it. Can anyone help?
The other idea I had was to create a second table for the Letter AQ onwards without the above code so the Number suffix can just be added in manually...however I need to keep a consistent Report Number over the whole database. And if I have 2 tables for the different Letter Prefix, I dont know how to keep the Report Number sequential between the 2 tables.
I hope I've explained my dilemma well enough and that someone can help me.
Thanks heaps