Pop up reminder in Access 2000.

[scottblue3]

I am working with Access 2000 and would like to create a pop up reminder on form Open to state the number of payments that will become due in 30, 60, and 90 days.

For e.g. ..."You have 25 payments due in 30 days, would you like to see them now?

If yes, then display the name, address and customer number of each customer.

If no open form.

Data base table info:

Table Payments
cID
Name
Address
Customer Number

Thanks,

Scottblue3.

[TheSmileyCoder]

Here is some example code that could work for your issue.
First it will make a count of those records in your payment table that shall occur between today and 30 days from today. (I made an assumption that that is what you needed/wanted)
I also assumed there is a duedate in your payments table, even though you don't mention one.

If it finds any records, it will then create a msgbox asking if you want to view those records.

Now you will have to prepare a report yourself with the customer table and payment table joined togehter as the datasource, and then add the where clause in the docmd.openrepor t statement (as illustrated below).

Hope it works for you, otherwise feel free to ask more questions.

Private Sub Form_Open(Cancel As Integer)

    'Lets see if there are any payments due within the next 30 days days
    Dim intCount30 As Integer
    
    'Payments between today, and the next 30 days.
    intCount30 = DCount("*", "tbl_Payments", "DueDate<date()+30 and DueDate>Date()")
    
    If intCount30 > 0 Then
        If vbYes = MsgBox("There are " & intCount30 & " payments due in the next 30 days. " & vbNewLine & _
                    "Would you like to view them now?", vbYesNo, "Payments due") Then
            
            'Open a report we have prepared in advance, and add the Where clause specified
            DoCmd.OpenReport "Prepared Report", acViewPreview, , "DueDate<date()+30 and DueDate>Date()", acWindowNormal
            
            'Dont proceed with form opening.
            Cancel = True
        End If
    End If
                    


End Sub