Solved

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

Posted on 2014-03-25
3
922 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 35

Accepted Solution

by:
PatHartman earned 250 total points
ID: 39954594
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
ID: 39954615
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
ID: 39954743
Thanks for the answers.  The query works as needed.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

813 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

14 Experts available now in Live!

Get 1:1 Help Now