I have a MS Access 2007 Database with numorous tables. What I need is to create a unique sequencial number for a field that is based off of another field. I have done tons of research and tried different approaches with no avail. In my table: tblRotationMissions, I have a field called RotationTMRNum (number data type, Long) that needs to be autmatically generated but with a field: RotationNum (number data type, Long) with a - then the sequential number. RotationNum actually comes from another table: tblRotations. For example, when someone enters a mission request and enters the RotationNum I then need the RotationTMRNum to autmatically generate with something like: 1401-0001. And so on, then restart when 1402 is entered for RotationNum. Everything that I have read on this has said NOT to do this because of the complexity, but this is what my boss wants. I created another field that I designated as not visible on the form called Sequence (Number data type, Long) for the sequence numbers obviously. I tried the following code on the form for the RotationNum AfterUpdate Event:
Private Sub RotationNum_AfterUpdate()
Me.Sequence = Nz(DMax("[Sequence]", "tblRotationMissions", "[RotationTMRNum]=" & Me.[RotationNum] & "'"), 0) + 1
Me.RotationTMRNum = [RotationNum] & "-" & Format([Sequence], "0000")
When I tried entering in the RotationNum it came up with this error:
Run-time error '3075'
Syntax error in string in query expression '[RotationTMRNum]=1".
And it highlights the first string of code.
I am not too great at code writing so I am sure that is where the problem is or maybe where I put the code? Please help!
Thanks so much,