Changing Sequences for Table Input

[Guest]

Hi Everyone. I have a database for tracking audits and one table has basic
information about the audit - reference, business, dates, auditors, etc - and
another table in which I hold the risks / recommendations.

In the second table, I have a field which mimics the reference number from
the main audit table. Also, there is a sequence which refers to the
risk/recommendation. So for example, one audit reference may result in one,
50 or no recommendations.

What I can't do is find a way of resetting the sequence number for each new
audit area as shown in the mock up below:

Audit Area RiskRef
ISA001 1
ISA001 2
ISA001 3
ISA002 1
ISA002 2
ISA002 3
ISA002 4

Anyone have any ideas how I can reset the sequence number in this way?

Thanks in advance for any posts.

Best

Peter

 

[Guest]

Use the DMax function to find the next available number for RiskRef. Using
the code below, if this is a new entry, it will start with 1:

Me.txtRiskRef = Nz(DMax("[RiskRef]","OtherTable", "[Audit Area] = '" &
Me.Parent.txtAuditArea & "'"),0) + 1

This assumes that you are using a form/subform configuration with the Audit
Area being the main form and the RisRef being the subform.