Hello everyone,
currently I'm working on a pop-up reminder which is supposed to appear 30 and 14 days before a due date of a sub-form. So far I managed to include the due date into the msgBox but I am struggling to also include the customer the due date belongs to. The customers details are placed in the main form.
So all I want is to have a pop-up that includes the due date and the name of the customer.
I hope you guys can help me with my problem.
Thank you!
UG Added Code Tags
The subform I'm referring to is tblStatus (with the due date called 'Fälligkeit'), tblKontakt is the table that contains all information for the main form such as the customer name.
currently I'm working on a pop-up reminder which is supposed to appear 30 and 14 days before a due date of a sub-form. So far I managed to include the due date into the msgBox but I am struggling to also include the customer the due date belongs to. The customers details are placed in the main form.
So all I want is to have a pop-up that includes the due date and the name of the customer.
I hope you guys can help me with my problem.
Thank you!
UG Added Code Tags
Code:
Option Compare Database
Option Explicit
Public Function RunMe()
On Error GoTo err
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblStatus")
'If there are no records in the table, just exit
If rst.EOF Then Exit Function
'Loop over all the records in the table until the end of file
Do Until rst.EOF
'If the date is not entered, do not evaluate
If Len(Nz(rst!Fälligkeit)) > 0 Then
'14 days
If DateDiff("d", Now(), rst!Fälligkeit) < 14 Then
MsgBox "Die" & " " & DLookup("Kre_ID_F", "tblKontakt") & " hat eine Fälligkeit am" & " " & rst!Fälligkeit & ", was in weniger als 14 Tagen ist"
'30 days
ElseIf DateDiff("d", Now(), rst!Fälligkeit) < 30 Then
MsgBox "Die" & " " & DLookup("Kre_ID_F", "tblKontakt") & " hat eine Fälligkeit am" & " " & rst!Fälligkeit & ", was in weniger als 30 Tagen ist"
End If
End If
rst.MoveNext
'Go back to the DO statement above and start over
Loop
On Error Resume Next
Set db = Nothing
rst.Close
Set rst = Nothing
Exit Function
'Should an error occur, this function will alert you to the error, and then stop running
err:
MsgBox err.Number & ": " & err.Description
End Function
The subform I'm referring to is tblStatus (with the due date called 'Fälligkeit'), tblKontakt is the table that contains all information for the main form such as the customer name.
Attachments
Last edited by a moderator: