Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-03-25
3
Medium Priority
?
947 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 40

Accepted Solution

by:
PatHartman earned 1000 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 1000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

877 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