How sequence by 1 for subsequent records

This will be tricky I'm sure.

I have a form which is used to record a project.  A project can have more than one TransRecord which is a text field on the form.

So, for example, Project #1 may have a TransRecord (a text field), that is "TransRecord#1".  Then the same project, Project #1, could have a TransRecord that is "TransRecord #2" and so forth.

But also on this form is a 4 digit number field formatted to look like "0001", "0002", "0003" and so on.

Here's what I need to do... for the first record of a unique TransRecord I want the 4 digit number to automatically populate to read "0001".  Then for the next TransRecord I want the 4 digit number field to be "0002".  And so on.

I expect that the code will have to be in the afterupdate event of the TransRecord field but I can't begin to know how to code this.

Can someone help?
SteveL13Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You can use DMax to determine the "highest" value in a specific column in a table:

Dim MyMax As Double

MyMax = DMax("FieldContainingTheNumber", "YourTable", "TransferRecord=" & Me.YourTransferRecordField)

MyMax would then contain the highest value found, and you could then add 1 to it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.