Record Count & Sequence Numbers

[Robert Nusz @ DPS]

I have a tabbed form with a Sub-Form on one panel, updates two tables. Both
tables A and Table B are linked via a 2-part key field as Master & Child
Links. First field of primary key is named CASE_NUM_YR and is 4 position
numerical year like 2004 or 2005. Second part of primary field is named
CASE_NUM and is numerical, (1 to 99999) etc. Table A will have primary
record of case # 2004109 and table B would or could have multiple secondary
records keyed as 2004109001, 2004109002, 2004109003, etc. The record
sequence number identifies non duplicate data being added to case file. I
want to be able to leave the field SEQ_NUM invisible (hidden) from user, and
to let the entry of records update this SEQ_NUM field.

I have attempted to use the following code:

Dim strWhere As String
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the Primary Case Record First."
Else
strWhere = "CASE_NUM = " & !CASE_NUM
Me.SEQ_NUM = Nz(DMax("SEQ_NUM", "Fr_Case_Other_Srtd", strWhere), 0) + 1
End If
End With

which works fine if I only want to compare on the second half of the primary
key field. This will cause problems as to keeping more than 1 year of data
with recycled case number each year starting at 1. 20041 would match 20051
if only checking on CASE_NUM. I need to be able to join the CASE_NUM_YR
(2004 or 2005) with the CASE_NUM value and create a comparable 20041 to 20041
match condition.

What do I have to do to code this in VB. I'm not VB literate and would like
some help.

Thanks in advance.