Reminder Popup

[coolcatkelso]

Hiya

I have a popup reminder on my form splash screen that should only display if there are any work orders with "Awaiting Start" in the related tables, here is the code

Private Sub Form_Load()
 Dim intStore As Integer
 intStore = DCount("[WorkorderID]", "[Workorders]", "[StartDate] <=Now()+4 AND [StartDate]")
 If intStore = 0 Then
            Exit Sub
                Else
                    If MsgBox("There is new jobs booked to start" & _
                    vbCrLf & vbCrLf & "Would you like to see these now?", _
                    vbYesNo, "You have new jobs ready to start...") = vbYes Then
                  
                    
                    DoCmd.Minimize
                    DoCmd.RunMacro "StartJob"
                 
                Else
            Exit Sub
        End If
    End If
End Sub

But, I did a test run and found that if I create a new work order and its category is "Awaiting Start" the reminder picks it up.. If I go in and change the status to "Job Complete", save and refresh everything, then the reminder still pops up with no records.. All I get is the message "There are new jobs ready to start, View jobs? Yes / No" If I click yes, i just get a blank form with (NEW) for the ID field, but I shouldn't actually get any popup

Am I missing something?
________
MOTOR ENGINEERING & MANUFACTURING NORTH AMERICA

 

[Steve R.]

You are running the program at the "Load Event", depending on how your program works, you may not be re-triggering your Dcount statement.

Also,as an aside, you should avoid having your "exit sub" in the current location. Change "If intStore = 0 Then" to "If intStore > 0 Then". Also insert "intStore = 0" before the Dcount statement. Technically, you don't need to initialize intStore.

I have a form that is similar to what you are doing. The status of the project is identified by an integer. If you are using actual text, such as "Awaiting Start" , you may want to convert to an integer for identifying the status. Its a lot easier and avoids spelling issues.

 

[boblarson]

What is this supposed to be:

"[StartDate] <=Now()+4 AND [StartDate]")

That doesn't make sense. Are you trying to capture something between two dates? And if StartDate doesn't use time then don't use NOW use DATE.

So, post back what that is supposed to be looking for and we'll see what we can do.

 

[coolcatkelso]

Hiya guys, cheers for the reply

The reminder should pick up a date 4 days from the date in StartDate

So - "[StartDate] <=Now()+4

the other bit I didn't see was there, that part should pickup a part of the Workorder form - WorkProgress which is a Combo box with the following

Awaiting Start
Work in Progress
Job Complete

So the whole reminder should pick up the startdate 4 days before its due to start and also pick "Awaiting Start"
________
Oxygen Vaporizer

 

[coolcatkelso]

Will the vba not need changed to -

"[StartDate] <=Date()+4 AND [WorkProgress]")
________
Chrysler Royal Specifications

 

[boblarson]

How about:

"[StartDate] <= DateAdd("d", 4, Date()) AND [WorkProgress] = 'Awaiting Start'")

 

[coolcatkelso]

Hiya Bob

I copied your new code like this

intStore = DCount("[WorkorderID]", "[Workorders]", "[StartDate] <= DateAdd("d", 4, Date()) AND [WorkProgress] = 'Awaiting Start'")

and its all highlighted in red in vba, If I try and run it, I get Syntax Error, When I added it in at first it gave an error on the ("d", part
________
Dorino Serafini

 

[boblarson]

Sorry, forgot about the double quotes:

intStore = DCount("[WorkorderID]", "[Workorders]", "[StartDate] <= DateAdd('d', 4, Date()) AND [WorkProgress] = 'Awaiting Start'")

 

[coolcatkelso]

Hi again Bob

Just to say I managed to get it working, Ended up using it like this

intStore = DCount("[WorkorderID]", "[Workorders]", "[StartDate] <=DATE()+4 AND [WorkProgress] = 'Awaiting Start'")

Did a test and seems to be working perfect now, only picks up records within 4 days and with Awaiting Start

Cheers
________
Navajo

 

[coolcatkelso]

Ok m8, put your full code in and its works as expected

Is there any difference in the two codes, no?

Your way -
intStore = DCount("[WorkorderID]", "[Workorders]", "[StartDate] <= DateAdd('d', 4, Date()) AND [WorkProgress] = 'Awaiting Start'")

Mine
intStore = DCount("[WorkorderID]", "[Workorders]", "[StartDate] <=DATE()+4 AND [WorkProgress] = 'Awaiting Start'")
________
THE HILLS ADVICE

 

[boblarson]

Not really, I just like that the DateAdd function is more explicit and self documenting than the Date() + 4 if someone doesn't know that the +4 adds 4 days. But in reality, it really doesn't matter, as long as it works and works consistently.