Loop though records and display pop up message if a date Criteria is met

[yashuaking]

I have a form called InvestmentF based on a table InvestmentF with the following fields

Investor Name
InvestorID
InvestmentDate
MaturityDate

What I want to is that on load, Access should look through the recordsets and check for Investments which would mature on the MaturityDate and display a continuous pop Message for all matured investments

I guess Do loop would help but cannot really figure out how to go about it.
This is the code I have so far but it just open the form and does nothing!

I would appreciate if there is another way to do this.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim StrMsgBox As String
DoCmd.OpenForm "investmentF"
DoCmd.SetWarnings True
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("InvestmentF", dbOpenTable)
rst.MoveFirst
 
Do While Not rst.EOF
If Not MaturityDate = Date Then Exit Sub
StrMsgBox = MsgBox("An Investment would matured today! Do you want to go to client's Record? ClientName is" & FullName, vbInformation + vbYesNoCancel, "Matured Investment")
If StrMsgBox <> vbYes Then Exit Sub
DoCmd.OpenForm "InvestmentF"
DoCmd.GoToControl "FullName"
rs.MoveNext
Loop
 
MsgBox "Finished searching for Matured investments"
 
rs.Close
Set rs = Nothing
End Sub

[zmbd]

Before we go down this route...
What you suggest doing is not considered to be the most user friendly for more than one or two such messages.

As an end user, if the programmer stuffed a dozen pop-up prompts in my face, I'd be highly irrate by about the fourth such message. I'd be leading the pitch-fork and tourch mob by the time anything more than that popped up.

I would highly recommend that you reconsider the design of your form:

Have a form that has a combobox that allows you to select matured, unmatured, and all. The form's filter and control default could be set to show the matured records on load

(These insight articles should provide some guidence here:
- Microsoft Access / VBA Insights Sitemap
.Cascaded Form Filtering
.Multiple Combobox Filter with Clear Option
.Example Filtering on a Form.


OR

- perhaps to use conditional formating
Change the appearance of a control by using conditional formatting (ACC2007/2010) using the maturity of the record for the comparision values.

OR

Perhaps an unbound parent form that has two subforms. One subform based on a query that pulls the records that have not yet matured and the second subform based on a query where the records that have matured.

IF you truely insist on the pop-ups, then what I would do is either use stored query or open a recordset in the vba that pulls these records. Check that the record set has records in it. Move to the first record. Then use a do..while..not EOF loop and pop-up the message with the information from the record that you want... just don't come to me when the Mob is outside your office!

[yashuaking]

Ok I got your point. But it was a request made by those am developing the database for. They want Pop ups. Can you please elaborate on how to use the do while loop for me to generate the pop up?

[ADezii]

If you insist on this approach, why not display a single Pop Up Dialog displaying all Invesments, via their IDs, that will mature on the Current Date?

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strMsg As String
Dim strBuild As String
 
strMsg = "The following Investments, identified by their IDs, will Mature today: " & _
          vbCrLf & vbCrLf
 
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("InvestmentF", dbOpenTable)
 
With rst
  Do While Not .EOF
    If ![MaturityDate] = Date Then
      strBuild = strBuild & ![InvestmentID] & ", "
    End If
      rst.MoveNext
  Loop
End With
 
'Remove Trailing ", "
strBuild = Left$(strBuild, Len(strBuild) - 2)
 
MsgBox strMsg & strBuild, vbExclamation, "Matured Investments"
 
MsgBox "Finished searching for Matured investments"
 
rst.Close
Set rst = Nothing

[zmbd]

@ADezii - I missed one - opps... (^-^)

Yes, even that single pop-up would be better!

[ADezii]

The only disadvantage, should you decide to disply more than the Investment IDs, is that you may go over the Maximum Allowable Characters in the Message Box, and the Build String may be concatenated.

[zmbd]

Yashuaking:
Even though the customer asked for this... you really should go back to them with the alternatives... point out to them that as they grow that there is a high potential for dozens if not hundreds of records, and the corresponding pop-ups! The end user will simply press and hold down the [Enter]-key; thus, bypassing the intent of the pop-up

[yashuaking]

Wonderful contribution I would try it as soon as Possible. Thank you very much ADzeli

[yashuaking]

OK zmbd. It is a good advice.

[yashuaking]

Adzeli's code works perfectly for matured investments but there was a little problem. When no investment is maturing today, it gives a vb error message that run-time error 5, invalid procedure call or argument. Please can you help me sort this out?

[yashuaking]

I figured a way out. I added an error handler to trap this error.

[yashuaking]

But I must say thank you all for your contribution headache solved!

[ADezii]

You are quite welcome.

[NeoPa]

A faster way to arrive at your list of 'qualifying' records is to include the criteria in the dataset that you open. The code wouldn't change too much, but the logic would be that it is no longer necessary to check the status of each record as that has already been done for you (and much faster generally) by the SQL.

Just a thought. The existing solutions posted are not invalidated in any way by incorporating this.

[yashuaking]

Ok NeoPa can you show me the way the to do this?

[zmbd]

yashuaking
Instead of just opening the recordset on the raw data table as done here:
Set rst = dbs.OpenRecordset("InvestmentF", dbOpenTable)

Build the query first to return only the records of interest. Then use this query as the source
Set rst = dbs.OpenRecordset("qry_withwhatyouwant", dbOpenDynaset)

You can even do this in code by building the SQL string (If I do this in the VBA code, quite often, I cheat and use the query builder and switch to SQL view and copy and paste the SQL into the code, with minimal modification it's usable) and assigning that instead of the table or query name
strSQL = "SELECT [field] FROM tbl_example WHERE( conditions )
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

The rest should be straight forward.

[yashuaking]

I modified the code and am using list box instead of a message box to populate more entries this is the new code I wrote. I EVEN ADDED a sound alert to it-Thanks to NeoPa's tutorial on sound!

Private Sub Form_Load()
 
    On Error GoTo Err_Handler
 
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strmsg As String
    Dim strBuild As String
    Dim nmaturedinv As Integer
    Dim iRetValue As Long
 
    Done = False
    Label2.Visible = False
    Label5.Visible = False
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("InvestmentCertificateQ")
    strBuild = ""
    nmaturedinv = 0
    With rst
        Do While Not .EOF
            If ![MaturityDate] = Date Then
                nmaturedinv = nmaturedinv + 1
                List0.AddItem (![FullName]) & ";" & (![AccountNumber]) & ";" & (![CustomerNumber]) & ";" & (![InvestmentCycle]), 0
            End If
            rst.MoveNext
        Loop
    End With
    If nmaturedinv = 0 Then
        Label5.Visible = True
        List0.AddItem "No investment Is Maturing Today"
        DoCmd.Close
    Else
        Label2.Visible = True
        iRetValue = sndPlaySound("C:\Program Files (x86)\Falcon\Sounds\Reminder.WAV", SND_ASYNC)
    End If
 
Err_Handler:
    rst.Close
    Set rst = Nothing
End Sub]

[NeoPa]

I don't believe that was one of mine, but I'm nevertheless pleased you found it helpful, and pleased you're happy with your results :-)