Using MS Access/Macros condition

Advertisement


Question
QUESTION: Hello.
I have a table [tblDashboard] that stores values and has one [Checked] date field. I'm trying to create a macro that checks if the dashboard values have been updated today or not. If not then calculate & store values. If so then skip the update. Pretty basic right?
I've spent hours trying to understand why the validation step doesn't work. Opening the table and setting focus works because that's how I'm left but no condition I set causes any further execution no matter what. Even "1=2" doesn't evaluate. I got the "can't find control" error until I named the macro step and now I get nothing. What am I doing wrong?

ANSWER: First, what version of Access. Second, how do you tell if the record has been updated? Third, where do you get the values you

A macro may not be the best tool for what you want to do.

Scott<>

---------- FOLLOW-UP ----------

QUESTION: Hi Scott.
MS Access 2003.
The only step on TRUE is a MsgBox saying so and then StopMacro. On FALSE it's a MsgBox saying so.
The [Checked] field contains 04-Apr-13 and I wanted to check <Now() or something like that but since it didn't work I tried every different version and then simple "1=2" tests.
I'm definitely open to other suggestions but I wanted to run some update queries on TRUE and maybe send some emails. The purpose is to update this [tblDashboard] table, inspect and possibly report on the statistics.
Thanks.

Answer
OK, In Access 2003, macros are almost worthless. I would not spend any time with them and use VBA. Using the same event you are using to run your macro use the Code Builder and enter something like:

If Me.Checked < Date() Then
   DoCmd.OpenQuery "queryname"
   Me.Checked = Date()
End If

You can run as many queries as you need to within the IF statement. Then finish off by updating the date

I use Date() instead of Now() because I assume you are populating Checked with just a date. Since Now() includes the time, if the date is todays date, then using Now will always make it True.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.