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):
tblBudgetCategories
catName:Text
catTaxTrack:Yes/No

tblBudgetPeriods
bpdName:Text
bpdBegin:Date
bpdEnd:Date

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

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

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

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
tblBudgetCategories.

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.

Steve
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.