Record validation on form based on criteria on 2 tables

[gg]

I have the following code (see below) but it is not working correctly. I
want to test records in one table (ie. Billing table) for a specific
status="Approved" and type="Terminated". If those criteria are met, then I
do not want to write out any more records to that table. However, if it is
still status="Pending" and type="Terminated", then continue to rerun the
process until the status="Approved". How do I switch back and forth from 1
table to the other? I need to compare the criteria in the Billing table, but
change the status on another table (Effective_contract) from "PT" to "T" if
the criteria from the 1st table is validated/met.

Thank you for your help! Happy New Year!



Private Sub ApplyTermination_Click()

If Me.Contract_status = "T" Then
MsgBox ("Contract is already terminated.")
Exit Sub
Else
End If

Dim strSQL As String

strSQL = "Select * FROM Billing INNER JOIN Effective_Contract WHERE
(Billing.Doc_type = Me!Effective_Contract.Doc_type) AND (Billing.Contract_no
= Me!Effective_Contract.Contract_no);"
If rst_status = "A" And rst_type = "T" Then

'Forms!frmContract.RecordSource = strSQL
strSQL = "Select * FROM Effective_contract WHERE (Billing.Doc_type =
Me!Effective_Contract.Doc_type) AND (Billing.Contract_no =
Me!Effective_Contract.Contract_no);"
MsgBox (Contract_no)
Contract_status = "T"
MsgBox (Contract_status)
Else
MsgBox ("Billing already has final Approval on a terminated contract.
No other billing records will be produced.")
Exit Sub
End If

If Not IsNull(Me.Contract_status) Then
If (Me.term_cancel_date <= Contract_end_date) And Not
IsNull(term_cancel_date) Then
If (Me.Contract_status = "PT") Then
MsgBox (" Please run the following reports " + vbCrLf + vbCrLf
+ " Edit List report " + vbCrLf + " Billing Summary report
" + vbCrLf + " Revenue summary report ")
Response = MsgBox(" Are you ready for Billing and Revenue to be
processed for this terminated contract?", vbYesNo)
If Response = vbNo Then
Exit Sub
Else
'This calls the frmProcessTerminations that has the latest
process_month_year date displayed on the screen.
DoCmd.OpenForm "frmProcessTerminations"
End If
Exit Sub
Else
Response = MsgBox("Do you wish to terminate this contract(Y/N)?",
vbYesNo)
If Response = vbNo Then
Exit Sub
End If
End If
Me!Contract_status = "PT"
Else
If IsNull(term_cancel_date) Then
MsgBox ("Enter a termination date.")
Me!term_cancel_date.SetFocus
Else
MsgBox ("Contract has an invalid termination date. Please
re-enter.")
Me!term_cancel_date.SetFocus
End If

Exit Sub
End If
Else
If IsNull(Me.Contract_status) Then
MsgBox ("Please select a valid contract#.")
Exit Sub
Else
Response = MsgBox("Have you validated that this contract can be
terminated (Y/N)?", vbYesNo)
If Response = vbNo Then
Exit Sub
End If
End If
End If
End Sub