How to set an alert pop up window via a follow up date field?

[Fspinelli]

Good morning!

I have a follow up date field in a table/query...

Not a parameter query (I can do a that but that's for something else). This would run based on that one field with the follow up date in it. So if today is in the follow up date field then a message (preferably a report) would open up when the Switchboard (form) is opened.

Thank you!

Kind regards,
Faith

[gnawoncents]

If I understand what you're trying to do, then adding some code to the on open event of the form should do the trick. Consider the following:

Private Sub Form_Open(Cancel As Integer)
 
If [follow up date] = Date Then
    DoCmd.OpenReport "YourReportName", acViewPreview
End If
 
End Sub

[Fspinelli]

That's great, thank you gnawoncents!

Here's more that I realize I neglected to place in my query on Bytes...

There are two tables - Contacts and Calls. Calls and Contacts are related by [RecId] (if that's any help).

The calls table has the [FollowUp] date in it. Calls and Contacts are joined in a query "qrySearch".

Thank you for the assistance!

Kind regards,
Faith

[gnawoncents]

Faith,

I'm glad to help. I see you gave some more info, is it working for you now, or do you need further assistance refining the code based on your data? Let us know and we'll be glad to help.

[Fspinelli]

Good morning!

No it's not working for me. "Calls" is a subform in "Contacts" and the field [FollowUp] is in "Calls".

I have something going on upon start up now and under that code I placed the code you helped me with (I modified):

If [FollowUp] = Date Then
DoCmd.OpenReport "FollowUpReport", acViewPreview
End If

Q:
Do I need to reference the table "Calls" before [FollowUp]? And if so (or not so) how?

Thank you very much!

Kind regards,
Faith

[gnawoncents]

Faith,

Here are my assumptions, please correct me where wrong:

1) Calls displays single form view
2) You have some filter set so the Calls subform opens to the record you want when Contacts opens
3) You want the notification report to pop up when Contacts first opens, based on the record displayed in Calls

If this is correct, you should be able to use your code (as typed below) in the on open event of the Calls subform. **Make sure it is not in the Contacts on open event**

If [FollowUp] = Date Then
DoCmd.OpenReport "FollowUpReport", acViewPreview
End If 

If it still isn't working, or any of my assumptions are incorrect, please let me know.

[Fspinelli]

ok, let me try to confuse you more (i seem to be good with that)

I have a form called Switchboard. From that switchboard the end user can open up various other forms. What I want to do is when they open up the application, the Switchboard will be the first thing they see.

On the Switchboard I would like to place a subform showing which contacts the end user is supposed to call today (the day they open up the application).

Now, I have two tables called Calls and Contacts. They are related by their record ID numbers. Each contact has an ID number. The Contacts form is in three tabs, one of the tabs is Calls.

That Calls tab has a subform of the Calls table based on that contacts ID. There is a field where the end user will enter a date to follow up with this particular contact (later date). The [FollowUp] field is the date field they enter their follow up date in.

Every day when they open up the application (and get that Switchboard) they want a pop up reminder telling them they need to call whoever (some info shows) and then they can click and open the record up showing the Contacts form now open reflecting the Contacts data.

I have a query with the fields I want to show up. In the [FollowUp]I tried using in the criteria area "=date()". I have a few records with today's date in it to see if the query would work. It's not. I do have at least four records in there with 11/4/2010 in the FollowUp field but the query isn't pulling them up.

My main issue is creating a query to pull up one specific date. Yes, I'll have a parameter query in case the end user wants to print a report of calls to make for the week, but they want a pop up (or whatever) reminder for that day when they first log into the Switchboard.

I just don't know how to make the query run off of today's date. If I can at least learn how to make it do that then I can probably figure out how to then the rest should be easy (creating a report to print, etc.)

Know what I mean??

Thank you very much for your time and assistance!!!!

[gnawoncents]

The query criteria =Date() should be working. Copy it as SQL and paste it so I can see what might be happening. For the popup, it sounds like you might want to create a recordset or update a table based on the calls for follow-up that day to allow the end users to select on them individually. Anyway, please pass me the query's SQL and we'll go from there. Thanks!

[Fspinelli]

This is my query's SQL:

SELECT Calls.FollowUp, Contacts.Company, Contacts.FirstName, Contacts.LastName, Contacts.Title, Contacts.Phone, Contacts.WorkExtension, Contacts.MobilePhone, Contacts.Fax, Contacts.Email
FROM Calls INNER JOIN Contacts ON Calls.CallID = Contacts.ContactID
WHERE (((Calls.FollowUp)=Date()))
ORDER BY Contacts.Company;

BTW...I didn't realize I could do that. Thanks for a lesson learned!

[gnawoncents]

Okay, I created a Calls and Contacts table with the appropriate fields and data then created a query (qryFollowUp) using your exact SQL coding. I have good news and bad news. The good news is that it is working perfectly for me. The bad news is that doesn't really help me figure out why it isn't working for you.

When you say the query isn't working for you, what exactly do you mean? Does it show any records?

[Fspinelli]

darn!

Those are two tables, right? and in your query you put the follow up date field from Calls and a few of the Contacts fields in it and it worked?!

No records come up. Nada. I placed todays date in a few records and STILL - I get a blank.

Did you relate the two tables? I did, by contactID and callsID...yes??

I'm baffled!

And I greatly appreciate your efforts!

[Fspinelli]

Wait! Got it! It's the way I had them related. Hmmm...

But it worked!

Thank you so very much!

Have a fantastic weekend!

;)
Faith

[gnawoncents]

Glad to hear you got it! Let us know if you run into any more snags.