Solved

MS Access 2010 Check Box Value Based on Comparison of Two Date Fields in Two Different Tables

Posted on 2014-03-25
3
910 Views
Last Modified: 2014-03-25
Hello Experts.  

I need a little help trying to find the correct event, as well as the correct vba to change the value of a check box.

I have two tables:
tbl1
tbl2

There is a date field in each:
[tbl1].[stDt]
[tbl2].[endDt]

There is a checkbox in tbl2 - [tbl2].[cb2].

If [tbl2].[endDt] is greater than [tbl1].[stDt] then the value of the check box in tbl2 should change from False to True.

I tried the following:
Private Sub Form_Current()
Me.[tbl2].[cb2] = False
If [tbl2].[endDt] > [tbl1].[stDt] Then
    Me.[tbl2].[cb2] = True
End If
End Sub

Open in new window


But the issue I think I have is that [tbl1].[stDt] is not on the form with [tbl2].[endDt].  I am placing the code in the form for tbl2, where the check box resides along with the endDt.

Which event should I be using?  Or do I have this all messed-up?

Here's the error code:
Run-time error '2465':
Enterprise Audit Database can't find field '|1' referred to in your expression.

Any help would be greatly appreciated.

Thanks,
J
0
Comment
Question by:ferguson_jerald
3 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
Comment Utility
The error you are getting is caused because the second table is not part of the recordsource for the form as you surmised.  We need to back up from that point.  I could tell you how to get the second table to be part of the form's RecordSource but that is akin to helping someone who is suicidal put live ammo in his gun.

The problem with what you are trying to do is that as soon as you save the record, the data is old and unreliable.  Someone else could change the date in the other table making your flag invalid.

In relational databases we store data only once.  We then use queries to join tables together to turn that data into information.  When you need to know the value of the "flag", you can obtain it in real time by creating a query that joins the two tables and creates a "calculated" column by comparing the two dates.

Select tbl1.*, tbl2.*,  IIf([tbl2].[endDt] > [tbl1].[stDt], True, False) As cb2
From tbl1 Inner Join tbl2 on tbl1.PK = tbl2.FK;
0
 
LVL 20

Assisted Solution

by:GrahamMandeno
GrahamMandeno earned 250 total points
Comment Utility
Hi Jerald

Presumably there are multiple records in both tbl1 and tbl2.  First you will need to define how the records are related - for example, do the tables each have a field containing a common key value.  If so, define a relationship between the two tables.

Then, create a query, comprising both your tables joined on the related fields.  This query can include whatever fields you need from tbl1, including [stDt], and from tbl2, including [endDt].

Your query can also include a calculated field to indicate whether [tbl2].[endDt] is greater than [tbl1].[stDt]:

MyField: [tbl2].[endDt] > [tbl1].[stDt]

(you can give this field a meaningful name)

You should not store a calculated field like this in your table, so  [tbl2].[cb2] is now redundant.

Graham Mandeno [Access MVP 1996-2014]
0
 

Author Comment

by:ferguson_jerald
Comment Utility
Thanks for the answers.  The query works as needed.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now