Need help desperately, Please Help

[Robert Nusz @ DPS]

I have an application that uses Microsoft Access 2003 Front end with IBM
Mainframe DB/2 backend tables. Primary table is TST_FR_CASE_RECORDS with
following definition known to Access:

Field Name Data Type
CASE_NUM_YR Numeric (first part of primary key)
CASE_NUM Numeric (second part of primary key)
other fields left unnamed....
This combination of CASE_NUM_YR and CASE_NUM make up the primary key for
TST_FR_CASE_RECORDS DB/2 table and is known to Access as Index Name
FRCASERA.

There is a secondary table named: TST_FR_CASE_OTHERS which contains summary
data for matching primary records in table TST_FR_CASE_RECORDS. There is a
Master / Child link between these two tables on both CASE_NUM_YR field and
CASE_NUM field in both tables.

Table TST_FR_CASE_OTHERS has this as key field:

field name data type

CASE_NUM_YR numeric(first part of primary key for secondary field)
CASE_NUM numeric(second part of primary key for secondary
field)
SEQ_NUM numeric(third part of primary key for secondary
field)

This combination of CASE_NUM_YR & CASE_NUM & SEQ_NUM make up the primary key
for this table known to Access as FRCASEOD Index name.

I have an Data Entry form named FR_CR_E that is a multi-page tabbed form.
The first three pages of form FR_CR_E allows entry of primary table records
based on primary key of combined fields CASE_NUM_YR & CASE_NUM.

A fourth page of form FR_CR_E is a subform that was added to this page. Its
sole use is to allow matching summary records to primary tables as needed if
there are any. (you may have summary records, you may not).

Problem is I want to use DMax to determine the record sequence number field
(SEQ_NUM) of each new record added to this table. I've tried the following
code, but it does not give me the correct SEQ_NUM on first record of each new
CASE_NUM.

Private Sub Form_Open(Cancel As Integer)

Dim strSeqNum As Integer
strSeqNum = 0
Me.Prev_Seq_No = strSeqNum

Private Sub Command36_Add_New_Record_Click()
On Error GoTo Err_Command36_Add_New_Record_Click
Me.Prev_Seq_No = Me!SEQ_NUM
Me.Prev_Vehicle_Cde = Me!VEHICLE_CDE
Me.Prev_Driver_Cde = Me!OTHER_CDE
Me.Prev_Case_Num = Me.CASE_NUM
DoCmd.GoToRecord , , acNewRec
Exit_Command36_Add_New_Record_Click:
Exit Sub

Private Sub Form_Load()
Me!CASE_NUM_YR.DefaultValue = Nz(Me.OpenArgs, "")
Me!CASE_NUM.DefaultValue = Nz(Me.OpenArgs, "")
If Me.CASE_NUM <> Me.Prev_Case_Num Then
MsgBox "Sub Form_Load Reset Me.Prev_Seq_No"
Me.Prev_Seq_No = strSeqNum
End If
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSecondaryKey As String
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "You Must Enter The Case Record First"
Else
strSecondaryKey = !CASE_NUM_YR & !CASE_NUM
Me.SEQ_NUM = Nz(DMax("SEQ_NUM", "TST_FR_CASE_OTHERS",
strSecondaryKey), 0) + 1
End If
End With
End Sub

I have other Unbound text fields on the screen to display previous sequence
number (which has a Default Value of 0), but also code that should 0 at the
start this field, named Prev_Seq_No.

Plus a Previous Case Number field (also unbound text box) named
Prev_Case_Num that would catch and hold previous case number entered.

When the subform first appears, both the Prev_Seq_No field and Prev_Case_Num
fields appear properly initialized, and the bound text field for SEQ_NUM is
blank. Upon first record for a new case (CASE_NUM_YR & CASE_NUM) is entered
(by a change to a required field in this record, the SEQ_NUM field gets
updated to reflect supposedly the correct sequence number. Problem is it is
not the correct number on the first record of each case, (it's random --- 5,
21, 22, etc. )

I have also tried this statement with no luck:

Me.SEQ_NUM = Nz(DMax("FRCASEOD","TST_FR_CASE_OTHERS",strSecondaryKey), 0) +1

and still get invalid record count.

Any suggestions would be greatly appreciated.