Help with code please

[steve goodrich]

I have a form with six fields bound to a table.
one field is a date field and the other 5 are check boxes.
These boxes need to be checked once per month (not the same date)
I would like to be prompted with a message box if any of the check boxes
haven't been checked for a month or more

I put this code in the 'on open' event of my form which prompts me if the
last date entered is over 30 days old.

If Me.Monthly_Date <= Date - 30 Then
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End If

How do I enter the code if I want the message to appear based on the check
box not being ticked and the date being 30 days old.

I.e. If check box 1 was ticked on 1 July then with the code I've got I
wouldn't be prompted again until 1 Aug but check boxed 2 to5 would be more
than 30 days old

Any help would be much appreciated

Steve

 

[steve goodrich]

Hi, Jut realised that the code entered checks all dates - I need the message
to be displayed only when the latest date is more than 30 days old

 

[John Spencer]

This will return all records where the date is 30 or more days prior and one
or more checkboxes (field1 to field5 in the example) is not checked.

SELECT Monthly_Date
FROM SomeTable
WHERE Monthly_Date < = Date()- 30
AND (Field1=False OR Field2=False OR Field3=False OR Field4=False OR Field5=False)

If you build the query in query design view then you would need to use 5
criteria lines. You would need the same criteria entered 5 times under the
monthly date field and false entered once under each of the checkbox fields.
Each false would have to be entered on a separate criteria line (one false per
criteria line and one false per checkbox field).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[CraigH]

Sorry John this wont work for Steve's situation: he need to find the "Latest"
one for each checkbox. I am sure there is a way to come up with 1 SQL
statement to do this but I will offer another option.

Steve you can use the Dmax function to find the latest one. You will have
to do it for each one but it also offers the ability to do a little more with
informing the person visually.

In the Open Form event for each checkbox.

If Nz(DMax("CheckDate", "Checks", "Check1 = True"), Date - 30) <= Date -
30 Then
MsgBox ("Check1 needs to be done") ' and/or
Me.Check1_Label.BackColor = 255 ' the back style for the label has
to be Normal
End If

Another option for the form is to use

=DMax("CheckDate", "Checks", "Check1 = True") in a textbox control source to
show the latest test date

 

[John Spencer]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

After rereading the Steve's post in light of your comment

For any one of the checkbox fields you could use a query that looked like

SELECT Monthly_Date
FROM SomeTable
WHERE NOT EXISTS
(SELECT *
FROM SomeTable
WHERE Monthly_Date <= Date()-30
AND Field1=True)
AND Field1 = False
AND Monthly_Date <=Date()-30

Or you could use something like

If DCount("*","Sometable","Monthly_Date<=Date()-30 AND CheckField1=True")=0 THEN
...
which is a variation on your suggestion.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Larry Linson]

If I understand correctly what you wrote, you have only one date, but
multiple check boxes. That, unfortunately, is insufficient data to determine
when each check box was checked. Clarify what dates you have in your
underlying data that are related to each of the check boxes, and perhaps
someone can help.

Larry Linson
Microsoft Office Access MVP

 

[steve goodrich]

Thanks for all the advice - I now have a query set up that will give me what
I want - I would also like to amend the code below, mainly as an exercise as
I am trying to learn a little VBA.
The code below always prompts me with the message although my last entry is
less than 30 days old - It's obviously checking all the dates and all but
the last entry is older than 30 days.

How do I amend the code so that all dates must be 30 days old before getting
the message
Many thanks
Steve

If Me.Monthly_Date <= Date - 30 Then
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End If

 

[John Spencer]

First, WHERE are you using the code. Presumably on a form, but in what event
are you using it (a control's after update event, the form's Current event, ...)?

And what do you want to test. Do you want to test if there is no field with a
check, a specific field without a check, etc.

Right now, you are checking whether or not the value of the control on the
form is more than 30 days ago. Does the form have the date and all five
checkboxes on it?


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[steve goodrich]

John,

I'm ignoring the check boxes for this exercise.
There is one date field on my form that is bound to a table.
I want the message to appear when I open my form and every date is more than
30 days old (i.e. if the last date entered is less than 30 days old then the
message does not appear).
I have the code set in the forms 'on open' event

Steve

I

 

[John Spencer]

You need to test the table or at least all the records in the record source of
the form.

You can use DCount function if the forms record source is a table or a query.
Use the DCount to count the number of records with a date within the last 30
days.

IF DCOUNT("*","SomeTableName","Monthly_Date Between Date()-30) and Date()") =
0 THEN
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End IF

I used the range in case you should accidentally get a record with a date in
the future. If you do enter records for future dates then you can change the
criteria clause to
"Monthly_Date > Date()-30)"
if you want to avoid the message if the dates are in the future.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[steve goodrich]

John,
I copied & pasted the text and placed my table name in the code, but got the
following error
Run time error 3075, extra ) in qery expression 'Monthly_date between
date()-30)and date()'.

Steve

If DCount("*", "tblMonthly", "Monthly_Date Between Date()-30) and Date()") =
0 Then
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End If

 

[John W. Vinson]

John,
I copied & pasted the text and placed my table name in the code, but got the
following error
Run time error 3075, extra ) in qery expression 'Monthly_date between
date()-30)and date()'.

Steve

If DCount("*", "tblMonthly", "Monthly_Date Between Date()-30) and Date()") =
0 Then
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End If

Just a typo. Remove the ) after 30.

 

[steve goodrich]

John,
I removed the offending )

I am still getting the message displayed if any one of the dates is more
than 30 days old!
I have deleted all but 2 records and carried out this test:

I entered 1 June 09 for both records and the message pops up because they
are both more than 30 days old.
I entered 20 July 09 for both records and the message doesn't pop up because
they are both less than 30 days old.
I changed one of the dates to 20 July 09 and the other date to 1 June 09 and
the message still pops up.

If any date is less than 30 days old I don't want the message to pop up
What am I doing wrong?

Steve

 

[John Spencer]

IF DCOUNT("*","SomeTableName", _
"Monthly_Date Between Date()-30 and Date()") = 0 THEN

MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End IF

That should work if you have any record in the database that has a date
that is less than 30 days ago. So I am puzzled on why you got the
results you did.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[steve goodrich]

I don't think I have explained it very well
It does work if there dates over 30 days old.
If there are many dates that are 30 days old and only one date that is not
30 days old then I don't want the message to appear
Steve

 

[John Spencer]

Somehow I am missing something you are doing. The message should only
be triggered if there are NO records in the database with a date later
than 30 days ago. So for today if there is a record between July 3 and
August 1, 2009 the message would not be displayed.

Try entering the expression in the immediate window AND see what it returns.
?DCOUNT("*","SomeTableName","Monthly_Date Between Date()-30 and Date()")

The try this expression and see what it returns.
?DCOUNT("*","SomeTableName","Monthly_Date < Date()-30")

This one should return the number of records in the table
?DCount("*","SomeTableName")

This one should return the number of records in the database where
Monthly_Date is not null
?DCount("Monthly_Date","SomeTableName")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

Got that wrong.

The message will be triggered as long as there is no record between a
date 30 days ago and today.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[steve goodrich]

Sorry John,

I am just starting to learn VBA , what is the immediate window? and what
should I do once I enter the text
Steve

 

[John Spencer]

In database design view, Type control+g.

That should open up Access VBA and the immediate window. If for some reason
you don't see the VBA window, select it from the view menu.

Then type in one of the lines in the Immediate window including the question
mark and press return
The computer should return a number on the next line that corresponds to the
number of records that met the criteria

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County