Access Sequentially numbered column that resets with each new sample

Apr 5, 2017
Reaction score
Hi all.
I'm new to the forum and have only dabbled in VBA coding, but I've an application that almost works and I'm hoping that the solution is only a small tweak with the coding.
I'm setting up a double entry platform for data verification. I have set up two identical sets of tables, queries and forms and I'm using a union query to compare data tables. Two sets of tables need a sequential number column to link each table with it's doppelganger. I using a DMax function to insert a sequential number in the sub-form's property sheet On Dirty. The code works great except it doesn't reset the number with each new sample but continues on from the last number entered. Below is what I currently have:

Private Sub Form_Dirty(Cancel As Integer)
Me.FishNumber = Nz(DMax("[FishNumber]", "Catch1"), 0) + 1
End Sub

I'm trying to have the sequential number (i.e., FishNumber) reset to "1" with each new sample (i.e., Sample1ID), which is an autonumber from my Sample table and links with my Catch1 table. If anyone has a suggestion it would really be appreciated.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question