Problems with auto number assignment

[jkfeagle]

I have created a Medical Leave Case Management db and I am trying to have the db assign a case number automatically. The case could have a U1 or U2 prefix depending on which factory it pertains to and that will be based on a field on the main form (frmPersonalEntry). The case number will be shown on a subform (frmMedicalHistory). The case number is to be in the format U12002001. Where 2002 is obviously the year and 001 is an incrementing counter. Below is the code I am using but there are some issues with it that I can’t seem to figure out. First, I had not considered how to get it to differentiate between plants so that it increments each plants cases based on the previous one to that plant rather than between the two but when I look at it, can’t figure out a good way to do it. Secondly, the subform has buttons that allow the user to step through the cases for an individual as well as bring up a new record. However, when new record is selected, the case number comes up 0 instead of assigning a new case number. Can anyone help here?

Private Sub Form_Current()
Dim intYearDiff As Integer
intYearDiff = (Format(Date, "yyyy") - (Mid((DMax("[CaseNum]", "tblMedicalHistory")), 4, 4)))
If Forms![frmPersonalEntry]![frmMedicalHistory].Form.NewRecord Then
Select Case intYearDiff

Case 0
Forms![frmPersonalEntry]![frmMedicalHistory].Form![CaseNum] = Me.Plant & Mid((DMax("[CaseNum]", "tblMedicalHistory")), 4, 7) + 1

Case 1
Forms![frmPersonalEntry]![frmMedicalHistory].Form![CaseNum] = Me.Plant & Format(Date, "yyyy") & "001"

End Select
End If

End Sub

For the new record selection sub, the above code is entered after (Obviously without the same Sub header):

DoCmd.GoToRecord , , acNext

 

[dynamictiger]

First, I had not considered how to get it to differentiate between plants so that it increments each plants cases based on the previous one to that plant rather than between the two but when I look at it, can’t figure out a good way to do it.

One approach maybe to alter the structure so that the two factories have seperate tables and these are related to the record.

tblFactoryRecord
IDNumber

tblOtherFactoryRecord
IDNumber

Relationship

MainRecord - tblFactoryRecord
MainRecord - tblOtherFactoryRecord

Looking at your code and reading the post I think there is a Logic error.

Your post says the case number should change based on a new record being selected. However, your code reads to assign a new case number only if the year is different by 1. Is this what you intended?

 

[jkfeagle]

I was really hoping to keep the data all in one table so it wouldn't be redundant. Also, I was trying to get the loading of a new record as the trigger for running the code rather than write new code just for that situation. This code is written to be modular so that for whatever reason it runs, it provides either the next case number or the first one for the year. (Hence the year check) Thanks anyway.

 

[dynamictiger]

I haven't really thought this through, but you could change your recordsource to a query, make the query fields your autonumbering field.