Question Access Reminder msgBox reference to cutsomer (1 Viewer)

kionke

New member
Local time
Today, 03:47
Joined
Jan 24, 2020
Messages
1
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
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

  • relations.jpg
    relations.jpg
    83.8 KB · Views: 85
Last edited by a moderator:

Ranman256

Well-known member
Local time
Today, 06:47
Joined
Apr 9, 2015
Messages
4,337
the subform I would think has the CustID in the record,
grab it then dLookup the customer name

vCustID = rst.fields("CustID").value
vName = Dlookup("CustName","tCustomer","[CustID]=" & vCustID)
 

Users who are viewing this thread

Top Bottom