Validation rule across tables using form's BeforeUpdate event

[Steve V]

I'm using Access 2000 to build a budgeting/tracking database.
Can I make a validation rule (using VBA) that checks the data as if
the record has already been added?

I've got 5 tables (only the necessary fields are shown here):
tblBudgetCatego ries
catName:Text
catTaxTrack:Yes/No

tblBudgetPeriod s
bpdName:Text
bpdBegin:Date
bpdEnd:Date

tblBudget
bgtPeriod:Text (from tblBudgetPeriod s.bpdName)
bgtCategory:Tex t (from tblBudgetCatego ries.catName)
bgtAmount:Curre ncy

tblTransactions
trnID:Autonumbe r
trnDate:Date
trnType:Text (e.g. cash, charge, check, etc.)
trnReceipt:Yes/No

tblCharges
cgsTransID:Long Integer (from tblTransactions .trnID)
cgsCategory:Tex t (from tblBudgetCatego ries.catName)
cgsAmount:Curre ncy

I've got frmBudgets and sfmBudgetAmount s based on tblBudgetPeriod s and
tblBudget, respecively; frmCashFlow and sfmCashFlowAmou nts based on
tblTransactions and tblCharges, respectively; and frmCategories based on
tblBudgetCatego ries.

I'm having trouble implementing 2 validation rules:
1. Each transaction and charge must correspond to a defined budget period
and budget category amount.
2. If any charge for a transaction corresponds to a category with
catTaxTrack=Yes , then the trnReceipt must be Yes.

I think the solution to one of them will enable me to implement the other in
a similar manner.

Here's what I've done for Validation Rule 1 in frmBudgets:
Private Sub Form_BeforeUpda te(Cancel As Integer)
Dim strSql As String

' Check that all transactions belong to a budget
strSql = "SELECT [qryTransactions &Charges].[trnDate], " & _
"[qryTransactions &Charges].[cgsCategory], " & _
"[qryPeriods&Budg ets].[bpdName], " &_
"[qryPeriods&Budg ets].[bgtCategory] " & _
"FROM [qryTransactions &Charges] " & _
"LEFT JOIN [qryPeriods&Budg ets] " & _
"ON ([qryTransactions &Charges].[trnDate]<= " & _
"[qryPeriods&Budg ets].[bpdEnd]) " & _
"AND ([qryTransactions &Charges].[trnDate]>= " & _
"[qryPeriods&Budg ets].[bpdBegin]) " & _
"AND ([qryTransactions &Charges].[cgsCategory]=" & _
"[qryPeriods&Budg ets].[bgtCategory]) " & _
"WHERE ([qryPeriods&Budg ets].[bpdName] Is Null);"
Set rstCheck = CurrentDb.OpenR ecordset(strSql )
If rstCheck.Record Count > 0 Then
MsgBox ("Cannot save budget period information because it would " & _
"leave cash flow without a corresponding budget")
Cancel = True
End If
End Sub

qryTransactions &Charges is an inner join between tblTransactions and
tblCharges at the database level.
qryPeriods&Budg ets is an inner join between tblBudgetPeriod s and tblBudget at
the database level.
These inner joins must be done separately to avoid "ambiguous outer joins".

The potential beauty with the preceeding sub is that it could be employed
without changes (except for the message) in frmBudgets, sfmBudgetAmount s,
frmCashFlow, and sfmCashFlowAmou nts. The problem with the preceeding sub is
that the SQL query does not consider the record that was just entered, i.e.
the one that is important.

Is there an easy way to get Access to consider the new record? Maybe by
cloning the table, adding the record to the dummy table, and testing there
(if so, how do I do it--I don't have any experience with cloning)? Can I add
the record, test, and delete (if necessary)? Or am I going about this the
wrong way?

Any suggestions, solutions, or examples are appreciated.

Steve