Can't Get Message Box to Work Properly

[JamesJoey]

I'm checking a date field to see if the date is past due (less than today.)
In the On Load I have:

If Me!ReminderDate > Date Then
            MsgBox "Past Do Events."
                 Else
            End If

But, in the code's current state the message box displays even if there is a record with a date less than today.
When I switch the 'greater than less than' the message box doesn't display at all even if all the record's dates are greated then today.

Any ideas?
James

 

[Minty]

You probably need to move your code to the forms open event or current event.
using the On_Load event it won't have populated the detail on the form. Also the Else is unnecessary - you can make your code one line;

If Me!ReminderDate > Date Then MsgBox "Past Do Events."

 

[Ranman256]

be sure the values are what you think....
put a break point on the IF line.
when the code stops, hoover the cursor over REMINDERDATE and see if the value is what you think.

in the immediate window, ctl-G, type:
?REMINDERDATE >Date

see if the calc is correct. IF not ,(i have seen 1<>1 =true) this is due to conversion error.
you may need
cDate(REMINDERDATE) >Date

so you are indeed comparing 1 date to 1 date.

 

[JamesJoey]

Hovering over the RemindersDate:

Me!ReminderDate = 5/2/2018
That date is a date I have for a record in the future.

 

[arnelgp]

you are only checking for 1 record.
possible on the Form's Load event the
first record that has focus does not match your
criteria. better to use Count() function
again your recordsource:

Private Sub Form Load()
Dim sSource As String
Dim rs As Dao.Recordset
Dim bolMissed As Boolean
sSource=Replace(Me.RecordSource, ";", "")
Set rs=CurrentDB.OpenRecordSet("SELECT Count(*) From (" & sSource & ") Where ReminderDate < Date()")
bolMissed = Not (rs.BOF And rs.EOF)
rs.Close
set rs=Nothing
If bolMissed Then
'There is missed reminder
End If
End Sub

 

[isladogs]

Try explicitly formatting both parts of the expression as mm/dd/yyyy or try converting both to long using CLng

 

[missinglinq]

you are only checking for 1 record. possible on the Form's Load event the first record that has focus does not match your criteria.

arnelgp has hit on your problem. Having the event in the Form_Load event only checks the first Record that is displayed, not each Record!

If you want this message to pop up, when appropriate, for each Record, as you move from one Record to another, it needs to be in the Form_OnCurrent event, as Minty suggested.

If you want the message to pop up, on opening the Form, if any Record meets your criteria, once again you'll need to do something as arnelgp suggested.

Linq ;0)>

 

[JamesJoey]

The message box still pops up even though no date in the table is earlier than 8/31/2017.

And it doesn't matter if I use greater than or less than in the expression.

 

[Minty]

Do you have a unique ID on your records on your form?

Assuming the answer is yes then add the following to actually see what you are comparing and which record it is looking at (if any).

Dim strMessage as string

If Me!ReminderDate > Date Then 
      strMessage = "Your date: " & Me.ReminderDate & " ." & VbCrLf
      strMessage = strMessage & "Your ID: " & Me.YourIDfeild & VbCrLf
      strMessage = strMessage & "Date Now: " & Date
      MsgBox strMessage
End If

 

[JamesJoey]

I'll do without this check.

How... can something so simple be so complicated to achieve????

Maybe it's Access. Maybe one wouldn't have this issue if I were using another Database manager.

 

[Minty]

If you won't apply the simplest of debugging aids I doubt anyone will help you any further.

It will take 1 minute to add that code and point you to your problem. Your choice.

 

[JamesJoey]

Your date: 5/2/2018
Your ID: 5/2.2018
Date Now: 8/31/2017

This form was actually an unbound form, sort of a main menu.
So, in order to find out if a Reminder date is past due I simply set the Control Source of the form to tblReminders which has a unique ID field and ReminderDate.

None of the fields are displayed on the form. Not sure if that matters.

 

[Minty]

If the form is unbound then there won't be a underlying record to check against.

Now that you have set it to the table it will probably be sitting on the first record - hence your ID = 5/2.2018 (is that really your table primary key ??)

So now you know what you were comparing - what do you really what to do?

 

[JamesJoey]

The Primary Key is ReminderID which is an autonumber.

 

[JamesJoey]

I found it works ok with another form with simply:

If Me!ReminderDate < Date Then
DoCmd.SelectObject acForm, Me.Name
MsgBox "Past Due Events"
Else
End If

I still must put the SelectObject in the code so the message opens after the form opens.

But it's a different form. The form were it works is based on the same table but it's a continuous form and has all pertinent fields displayed.

The form that it doesn't work is my bad impression of a week view that has 7 subforms for today + 6

Not sure why it would matter.

Just checking to see if any records have a past due date.

 

[JamesJoey]

Finally got it to work.

I changed the record source form tblReminder to a select statement pulling all fields and sorting on ReminderDate.

It was the only difference between the 2 forms.

 

[moke123]

Glad you got it working although I tend to agree with the method suggested by Arnel.

Since your going to the trouble of throwing up a generic reminder message I would consider making the message more meaningful, assuming you have something in that table describing what your being reminded about. if so, you could include it in the msgbox.

Public Sub sRemindMe()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strMessage As String

    strSql = "select * from  tblReminders where ReminderDate < #" & Date & "#"

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

    If rs.BOF And rs.EOF Then
        GoTo MyExit
    End If

    Do Until rs.EOF
        strMessage = strMessage & rs!txtreminder & "   Due : " & rs!ReminderDate & vbNewLine
        rs.MoveNext
    Loop

    MsgBox "Just reminding you about:" & vbNewLine & strMessage

MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub