Creating an Alert Message for a Form

[J1mmy]

Hello,

Ive made a database in Access 2007 for work that holds everyones contacts inside of it. Then on a form where you can view all these contacts I have placed a "prompt date" option. This is there so that the user knows they have to contact this customer within a certain amount of days, say 5.
This is were I'm falling down, as I can't for the life of me find a way to make a code to show a message when you go onto the "Edit Contacts" form that says you need to contact a certain person.

Originally searching the Internet I found this code,

If (DateDiff("d", Now, thedate) < 7) Then
MsgBox ("Less than 7 days until event")
End If

But it doesn't do what I want because it tells you no matter what, even if there are no Prompt Dates set, plus it doesn't tell you which Customer needs to be contacted.

The database is shared by everyone over a network and I don't really mind if say, Dave can see Barry's alert message when he goes on as long as Barry can also see it.

I know it seems like a lot but I would be greatful of any help. I'm not what you'd call overly skilled in Access but I can get by.
Thanks a lot

 

[Kafrin]

What I suggest is that you start by building a query. You base it on your Contacts table, include the name of the contact (+ any other info you want to show on the message) and set it with the criteria PromptDate <= Date(). This will then show you everyone whose prompt date is today or in the past (and it ignores nulls, so only works for contacts that have a prompt date).

[edit]Just realised, I'm not sure if your PromptDate is a date or a number of days. The above is for a date. If it's a number of days you want PromptDays <= Date() - TheDate[/edit]

Then when you open your form you need some code in the OnOpen event that looks something like this:

Dim rsQ As Recordset, S As String
 
Set rsQ = CurrentDb.OpenRecordset("QueryName")
If rsQ.RecordCount <> 0 Then
    S = "The following people need to be contacted:"
    rsQ.MoveFirst
    Do Until rsQ.EOF
        S = S & Chr(13) & Chr(10) & rsQ!ContactName
        rsQ.MoveNext
    Loop
    MsgBox S
End If

This checks your query to see if any people need contacting. If there are any, it lists them in a message box.

This will show you everyone that needs contacting. You mention different users. Do your users have to log in to the database? Is there any way to tell who's opening the screen? If so it should be possible to include this so people only see their own prompts.

 

[J1mmy]

Hello,

Thanks for that, it really did help, and so far so good. It says I have one error though and it's to do with the line of code
Set rsQ = CurrentDb.OpenRecordset("QueryName")

It says there are too few parameters. Not quite sure what that means.
Not sure if it helps any, but I changed the recordset wording the line to Contacts. As thats what my contacts table is called and this still didn't help, not sure what it was going to do but I thought it was worth a try as I don't know anything else

Thanks in advance

 

[Kafrin]

When you open up a query as a recordset in code, you need to feed in any parameters that are used in the query and that refer to controls on forms. I'm surprised you have any of these, so probably the most helpful thing is if you post up the SQL for the query.

Please note that where I've used the words PromptDate, TheDate, QueryName and ContactName, you need to replace them with the field/query names in your actual database. If you've used PromptDate in your query but don't have a field called PromptDate, this will give you the error you described.

In the meantime, here's how you assign parameters in code:

Dim qd As QueryDef, P As Parameter, rsQ As Recordset, S As String
 
Set qd = CurrentDb.QueryDefs("QueryName")
For Each P In qd.Parameters
    Select Case P.Name 'add a case for each parameter in your query
        Case "Param1Name"
            P = Param1Value
        Case "Param2Name"
            P = Param2Value
    End Select
Next
Set rsQ = qd.OpenRecordset 'opens the Recordset based on the QueryDef
 
'...continue with code