Solved

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

Posted on 2014-03-25
3
939 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 38

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

615 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