Need help with Recordsets and moving through them

[platypus71]

Okay, a while back I was able to use code to send automated emails. This code works wonderfully for what we are doing. Unfortunately, we've expanded our needs and now I need a more complex email.

What I am trying to do: sqlstr2 generates a list of each person that we need to send an email to. sqlstr creates a list of the Metrics to send to said person (ShortName).
I want to then send my canned message that gives a list of the Metrics in it to each ShortName. What I came up with doesn't quite work.... Any help would be appreciated, I know I'm missing something simple.

Function Reminder()
    Dim X As Boolean
    Dim mydb As Database
    Dim rst As Recordset
    Dim sqlstr As String
    Dim sbj As String
    Dim bod As String
    Dim list As String
    
    
    'Define the SQL Strings for each email below
    sqlstr2 = "SELECT MetricsContactList.ShortName, MetricsContactList.[Metric Owner] FROM MetricsContactList WHERE (((MetricsContactList.Frequency) Like 'MO') AND ((MetricsContactList.Automated)<>True)) GROUP BY MetricsContactList.ShortName, MetricsContactList.[Metric Owner];"
    sqlstr = "SELECT MetricsContactList.Code, MetricsContactList.Metric, MetricsContactList.ShortName FROM MetricsContactList WHERE (((MetricsContactList.Frequency) Like 'MO') AND ((MetricsContactList.Automated)<>True) AND ((MetricsContactList.ShortName) Like rst1!ShortName));"
    
    Set mydb = CurrentDb
    
    'This sends reminders for metrics
    Set rst1 = mydb.OpenRecordset(sqlstr2)
    Do While Not rst1.EOF
    Set rst = mydb.OpenRecordset(sqlstr)
    Do While Not rst.EOF
    list = rst!Metric & list
    rst.MoveNext
    Loop
    
    sbj = "REMINDER:  Enter your monthly metrics"
    bod = "Dear " & rst1![Metric Owner] & "," & vbCrLf & vbCrLf & "Please take a few minutes to complete your metrics entry for the Scorecard." & vbCrLf & "Your metrics are:" & rst!Metric & vbCrLf & "Thank you!"
    
    X = SendEmail(rst1!ShortName, bod, "", sbj, "")
    rst1.MoveNext
    Loop
    
End Function

 

[CaptainD]

You did not say what was not working. But, a quick look

Function Reminder()
    Dim X As Boolean
    Dim mydb As Database
    Dim rst As Recordset
[COLOR=blue]    Dim rst1 as Recordset[/color][COLOR=red]<--- Missing[/color]
    Dim sqlstr As String
    Dim sbj As String
    Dim bod As String
    Dim list As String
    
    
    'Define the SQL Strings for each email below
    sqlstr2 = "SELECT MetricsContactList.ShortName, MetricsContactList.[Metric Owner] FROM MetricsContactList WHERE (((MetricsContactList.Frequency) Like 'MO') AND ((MetricsContactList.Automated)<>True)) GROUP BY MetricsContactList.ShortName, MetricsContactList.[Metric Owner];"
    sqlstr = "SELECT MetricsContactList.Code, MetricsContactList.Metric, MetricsContactList.ShortName FROM MetricsContactList WHERE (((MetricsContactList.Frequency) Like 'MO') AND ((MetricsContactList.Automated)<>True) AND [COLOR=red]((MetricsContactList.ShortName) Like rst1!ShortName));"[/color]

Should be something like [COLOR=blue]((MetricsContactList.ShortName) Like '*" & rst1!ShortName & "*'));"[/color]

[COLOR=green]I do not recall what if Access uses the "*" or the "%" for a wildcard when you use "Like"[/color]

 

[dhookom]

In addition, you should be explicit with your declarations:

    Dim X As Boolean
    Dim mydb As [red]DAO.[/red]Database
    Dim rst As [red]DAO.[/red]Recordset
    Dim rst1 as [red]DAO.[/red]Recordset
    Dim sqlstr As String
    Dim sbj As String
    Dim bod As String
    Dim list As String

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

And finally, sqlstr should be initialized inside the outer loop:

...
Do While Not rst1.EOF
    sqlstr = "SELECT Code, Metric, ShortName FROM MetricsContactList WHERE Frequency='MO' AND Automated=False AND ShortName='" & rst1!ShortName & "'"
    Set rst = mydb.OpenRecordset(sqlstr)
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[platypus71]

Thanks for the help thusfar. I'm now getting an error at a different point. Here is what I have:

Function Reminder()
    Dim X As Boolean
    Dim mydb As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst1 As DAO.Recordset
    Dim sqlstr As String
    Dim sqlstr2 As String
    Dim writelog As String
    Dim sbj As String
    Dim bod As String
    Dim list As String
    
    
    'Define the SQL Strings for each email below
    sqlstr2 = "SELECT MetricsContactList.ShortName, MetricsContactList.[Metric Owner] FROM MetricsContactList WHERE (((MetricsContactList.Frequency) Like 'MO') AND ((MetricsContactList.Automated)<>True)) GROUP BY MetricsContactList.ShortName, MetricsContactList.[Metric Owner];"
    
    Set mydb = CurrentDb
    
    'This sends reminders for metrics
    Set rst1 = mydb.OpenRecordset(sqlstr2)
    Do While Not rst1.EOF
        sqlstr = "SELECT MetricsContactList.Code, MetricsContactList.Metric, MetricsContactList.ShortName FROM MetricsContactList WHERE (((MetricsContactList.Frequency) Like 'MO') AND ((MetricsContactList.Automated)<>True) AND ((MetricsContactList.ShortName) Like rst1!ShortName));"
---->    Set rst = mydb.OpenRecordset(sqlstr)
    Do While Not rst.EOF
    list = rst!Metric & list
    rst.MoveNext
    Loop
    
    sbj = "REMINDER:  Enter your monthly scorecard metrics"
    bod = "Dear " & rst1![Metric Owner] & "," & vbCrLf & vbCrLf & "Please take a few minutes to complete your metrics entry for the Scorecard." & vbCrLf & "Your metrics are:" & rst!Metric & vbCrLf & "Thank you!" & vbCrLf & "IT Learning & Development Operations Team (ONEITLD)"
    
    X = SendEmail(rst1!ShortName, bod, "", sbj, "")
    writelog = "INSERT INTO tblMailing( ShortName, Code, Metric ) IN 'J:\Databases\Scorecard\ScoreCard.mdb' VALUES('" & rst1!ShortName & "','" & rst1!Code & "', '" & list & "')"
    
    DoCmd.RunSQL writelog
    rst1.MoveNext
    Loop
    
End Function

The arrow indicates where the debugger says the problem is.
The error is Run-time error 3061, Too few parameters. Expected 1.

Additionally, if I put in what CaptainD says with the wildcard in the sqlstr, I get "No current Record" in the variable assignment line of bod.

 

[huggyboy]

Just a thought - you havent got null values coming from rst1 have you? stop the code just before opening rst and make sure no nulls in strsql built from rst1

H

 

[PHV]

Please, reread carefully my previous post.

 

[huggyboy]

my mistake missed the Like rst1!ShortName));" instead of ='" & rst1!ShortName & "'"
that would be it then

 

[platypus71]

Function Reminder()
    Dim X As Boolean
    Dim mydb As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst1 As DAO.Recordset
    Dim sqlstr As String
    Dim sqlstr2 As String
    Dim writelog As String
    Dim sbj As String
    Dim bod As String
    Dim list As String
    
    
    'Define the SQL Strings for each email below
    sqlstr2 = "SELECT MetricsContactList.ShortName, MetricsContactList.[Metric Owner] FROM MetricsContactList WHERE (((MetricsContactList.Frequency) Like 'MO') AND ((MetricsContactList.Automated)<>True)) GROUP BY MetricsContactList.ShortName, MetricsContactList.[Metric Owner];"
    
    Set mydb = CurrentDb
    
    'This sends reminders for metrics
    Set rst1 = mydb.OpenRecordset(sqlstr2)
    Do While Not rst1.EOF
        sqlstr = "SELECT MetricsContactList.Code, MetricsContactList.Metric, MetricsContactList.ShortName FROM MetricsContactList INNER JOIN rst1 ON MetricsContactList.ShortName = rst1.ShortName WHERE (((MetricsContactList.Frequency) Like 'MO') AND ((MetricsContactList.Automated)<>True) AND ((MetricsContactList.ShortName) Like '*" & rst1!ShortName & "*'));"
        Set rst = mydb.OpenRecordset(sqlstr)
        Do While Not rst.EOF
        list = rst!Metric & ", " & list
        rst.MoveNext
        Loop
    
    sbj = "REMINDER:  Enter your monthly scorecard metrics"
    bod = "Dear " & rst1![Metric Owner] & "," & vbCrLf & vbCrLf & "Please take a few minutes to complete your metrics entry for the Scorecard." & vbCrLf & "Your metrics are:  " & list & vbCrLf & "Thank you!" & vbCrLf & "IT Learning & Development Operations Team (ONEITLD)"
    
    X = SendEmail(rst1!ShortName, bod, "", sbj, "")
    writelog = "INSERT INTO tblMailing( ShortName, Metric ) IN 'J:\Databases\Scorecard\ScoreCard.mdb' VALUES('" & rst1!ShortName & "', '" & list & "')"
    
    DoCmd.RunSQL writelog
    rst1.MoveNext
    Loop
    
End Function

This works, though I had to create a query called rst1 to make it work. This doesn't make sense to me and wasn't what I was hoping for, but working is better than not.

Any insight on how to eliminate the query named rst1 and have it built in would be appreciated. rst1 (the query)only shows two fields, ShortName and [Metric Owner].

As to PHV...I read your post the first time, and though I may be mistaken, I thought the code posted in both this post and my previous post was what you had suggested.

 

[PHV]

I thought the code posted in both this post and my previous post was what you had suggested
Still NO.
Your sqlstr has nothing to do with mine.

 

[platypus71]

Ok, I see what I missed. Thanks, that did the trick in getting rid of the query and works.

My last posted code with the sqlstr from PHV's first post is what did the trick.