Solved

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

Posted on 2014-03-25
3
936 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 37

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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 …

739 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