Creating a message alert on a record

[Phonik]

Hi

I am trying to create an alert function so that when a user open up a form and displays a record, it will also display any alerts that have been created for that record. e.g. missed payments etc.
I have created a table to store the alert data along with the job number to which the alert relates so in essence I can have more that one alert per job record. I amtrying get one of the alerts to display ok using the following code

alerts = DLookup("[Job _Number]", "Alerts", "[Job _Number]='" & Me.Job_Number & "'" And "[Start_Date]" >= Date)

You will see that I am trying to only display alerts that are within the valid date range (I have not yet included [End_Date] as I cannot get the code above to work.

Can anyone help me correct this code please?

The other thing is that because I am using a dlookup, I am assuming it will only display the first alert it finds in the table and that's it? If so, how do it get it to display all 'active' alerts with a matching job number. By active I mean where the Start date is >= Date() and End date is <= Date()

I would really appreciate any help on this..

Thank you all for reading.

 

[RuralGuy]

Try:
alerts = DLookup("[Job _Number]", "Alerts", "[Job _Number]='" & Me.Job_Number & "' And [Start_Date] >= " & Date)
...if JobNumber is text.

 

[Phonik]

Hi

Thanks for your reply. The job number is not text it's an autonumber

 

[DCrake]

Simple Software Solutions

Have you thought of creating an alerts form that contains a large text area?

When you visit a record yoou could code it so that it does the following

Dim Rs As DAO.RecordSet
Dim StrAlerts As String

Set Rs = CurrentDB.OpenRecordSet("Select * From Alerts Where [Job_Number] =" & JobID & " And Start_Date Between #" & DateLower & "# And #" & DateUpper & "#")

If Not Rs.EOF And Not Rs.BOF Then

Do Until Rs.EOF

StrAlerts = StrAlerts & "Date:" & vbTab & Rs("Start_Date") & VbCrLf _
"Alert:" & vbTab & Rs("AlertText"( & VbCrLf & VbCrLf

Rs.MoveNext

Loop

Rs.Close
Endif

Set Rs = Nothing

'////////////////
The above will create a vertical string of each alert with a blank line between each alert

Then pass this string to the text box on the form when you open it

If no alerts are found then do not open form or you could send a message box to the user to state that there are no alerts active for this job.

CodeMaster:http://www.icraftlimited.co.uk

 

[RuralGuy]

Hi

Thanks for your reply. The job number is not text it's an autonumber

In that case eliminate the single quotes around the value:
alerts = DLookup("[Job _Number]", "Alerts", "[Job _Number]=" & Me.Job_Number & " And [Start_Date] >= " & Date)