• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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?
0
SteveL13
Asked:
SteveL13
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now