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?