Changing Sequences for Table Input

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Back
Top