By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,474 Members | 3,141 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,474 IT Pros & Developers. It's quick & easy.

Validation rule across tables using form's BeforeUpdate event

P: n/a
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):


bgtPeriod:Text (from tblBudgetPeriods.bpdName)
bgtCategory:Text (from tblBudgetCategories.catName)

trnType:Text (e.g. cash, charge, check, etc.)

cgsTransID:Long Integer (from tblTransactions.trnID)
cgsCategory:Text (from tblBudgetCategories.catName)

I've got frmBudgets and sfmBudgetAmounts based on tblBudgetPeriods and
tblBudget, respecively; frmCashFlow and sfmCashFlowAmounts based on
tblTransactions and tblCharges, respectively; and frmCategories based on

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_BeforeUpdate(Cancel As Integer)
Dim strSql As String

' Check that all transactions belong to a budget
strSql = "SELECT [qryTransactions&Charges].[trnDate], " & _
"[qryTransactions&Charges].[cgsCategory], " & _
"[qryPeriods&Budgets].[bpdName], " &_
"[qryPeriods&Budgets].[bgtCategory] " & _
"FROM [qryTransactions&Charges] " & _
"LEFT JOIN [qryPeriods&Budgets] " & _
"ON ([qryTransactions&Charges].[trnDate]<= " & _
"[qryPeriods&Budgets].[bpdEnd]) " & _
"AND ([qryTransactions&Charges].[trnDate]>= " & _
"[qryPeriods&Budgets].[bpdBegin]) " & _
"AND ([qryTransactions&Charges].[cgsCategory]=" & _
"[qryPeriods&Budgets].[bgtCategory]) " & _
"WHERE ([qryPeriods&Budgets].[bpdName] Is Null);"
Set rstCheck = CurrentDb.OpenRecordset(strSql)
If rstCheck.RecordCount > 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&Budgets is an inner join between tblBudgetPeriods 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, sfmBudgetAmounts,
frmCashFlow, and sfmCashFlowAmounts. 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.

Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.