DLookup with TRUE

no, patronize away. i actually wanted to ask that but did feel silly. the first text i send was all in one line, i think you acutally helped with that aswell. i do prefer it all to be in one line. thats why i like

docmd.runsql.

its all nice neat and tidy and the next line does something else. i m thinking i am going to need a mile long screen though!

thanks again
 
hello again. i have tried to repeat the record set but now it is coming up with duplicate , highlighting 'Dim rs As DAO.Recordset' as the culprit.

how do i get round it. i know i cant name it something else. is it possible to have to record set loops in the same routine?

heres what i have so far

Code: 
'run text reminders for client appointments 2 days from now'
'insert appointments to tmptblTextReminders for appointments in 2 days with a mobile number'
DoCmd.RunSQL ("INSERT INTO tmptblTextReminders ( ClientDetailsID, OrderID, StartDate, StartTime, MobileTelephoneNumber ) SELECT tblClientDetails.ClientDetailsID, tblOrders.OrderID, tblOrders.OrderDate, tblOrders.OrderTime, tblClientDetails.MobileTelephoneNumber FROM tblOrders INNER JOIN tblClientDetails ON tblOrders.ClientDetailsID = tblClientDetails.ClientDetailsID WHERE (((tblOrders.OrderDate)=DateAdd('d',2,Date())) AND ((tblClientDetails.MobileTelephoneNumber) Is Not Null) AND ((tblOrders.TextReminderSent)=No) AND ((tblOrders.Status)=1));")
'recordset for texts to be sent(loop)'
On Error GoTo Err_Proc
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT tmptblTextReminders.MobileTelephoneNumber, tmptblTextReminders.StartDate, tmptblTextReminders.StartTime FROM tmptblTextReminders;")
    If rs.RecordCount <> 0 Then
        Do While Not rs.EOF
            sendSMS "118-324-1616", _
            rs.Fields("MobileTelephoneNumber"), _
        "Appointment Reminder: " & Format(rs.Fields("StartDate"), "dddd d mmm") & _
        " at " & Format(rs.Fields("StartTime"), "hh:nn") & _
        ". If you would like to cancel or change this, please ring Lytham on 01253 739432. Thanks Chrysalis."
        rs.MoveNext
        Loop
    End If
'update tblOrders.TextReminder sent to 'yes' for messages just sent'
DoCmd.RunSQL ("UPDATE tblOrders INNER JOIN tmptblTextReminders ON tblOrders.OrderID = tmptblTextReminders.OrderID SET tblOrders.TextReminderSent = Yes WHERE (((tblOrders.OrderID)=[tmptblTextReminders].[OrderID]));")
'delete records in tmptblTextReminders'
DoCmd.RunSQL ("DELETE tmptblTextReminders.ClientDetailsID FROM tmptblTextReminders;")
Exit_Proc:
    rs.Close
    Set rs = Nothing
    Exit Sub
Err_Proc:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Proc
DoCmd.SetWarnings True
End If
'compile list of clients that have not visited the salon for 1 year and apend those records with loyalty points sum to tmptblLoyaltyPointsLastVisit'
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO tmptblLoyaltyPointsLastVisit ( ClientDetailsID, LastOfOrderDate, LoyaltyPointsSum ) SELECT subqryLoyaltyPointsLastVisit.ClientDetailsID, Max(subqryLoyaltyPointsLastVisit.LastOrderDate) AS MaxOfLastOrderDate, Sum(tblLoyaltyPoints.LoyaltyPointsAdjustments) AS SumOfLoyaltyPointsAdjustments FROM subqryLoyaltyPointsLastVisit INNER JOIN tblLoyaltyPoints ON subqryLoyaltyPointsLastVisit.ClientDetailsID = tblLoyaltyPoints.ClientDetailsID GROUP BY subqryLoyaltyPointsLastVisit.ClientDetailsID HAVING (((Max(subqryLoyaltyPointsLastVisit.LastOrderDate))<DateAdd('yyyy',-1,Date())) AND ((Sum(tblLoyaltyPoints.LoyaltyPointsAdjustments))>0));")
'apend tblLoyaltyPoints so that those that have not visited for 1 year have 0 points'
DoCmd.RunSQL ("INSERT INTO tblLoyaltyPoints ( ClientDetailsID, LoyaltyPointsAdjustments ) SELECT tmptblLoyaltyPointsLastVisit.ClientDetailsID, [LoyaltyPointsSum]-[LoyaltyPointsSum]-[LoyaltyPointsSum] AS LoyaltyPointsAdjustment FROM tmptblLoyaltyPointsLastVisit;")
'delete tmp records'
DoCmd.RunSQL ("DELETE tmptblLoyaltyPointsLastVisit.ID FROM tmptblLoyaltyPointsLastVisit;")
'send text reminders for clients that have not visited for 10 months and have >0 loyalty points'
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO tmptblLoyaltyPointsLastVisit ( ClientDetailsID, LastOfOrderDate, LoyaltyPointsSum ) SELECT subqryLoyaltyPointsLastVisit.ClientDetailsID, Max(subqryLoyaltyPointsLastVisit.LastOrderDate) AS MaxOfLastOrderDate, Sum(tblLoyaltyPoints.LoyaltyPointsAdjustments) AS SumOfLoyaltyPointsAdjustments FROM subqryLoyaltyPointsLastVisit INNER JOIN tblLoyaltyPoints ON subqryLoyaltyPointsLastVisit.ClientDetailsID = tblLoyaltyPoints.ClientDetailsID GROUP BY subqryLoyaltyPointsLastVisit.ClientDetailsID HAVING (((Max(subqryLoyaltyPointsLastVisit.LastOrderDate))=DateAdd('m',-10,Date())) AND ((Sum(tblLoyaltyPoints.LoyaltyPointsAdjustments))>0));")
'recordset for texts to be sent(loop)'
On Error GoTo Err_Proc
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT tmptblLoyaltyPointsLastVisit.ClientDetailsID, tblClientDetails.FirstName, tmptblLoyaltyPointsLastVisit.LoyaltyPointsSum, tblClientDetails.MobileTelephoneNumber FROM tblClientDetails INNER JOIN tmptblLoyaltyPointsLastVisit ON tblClientDetails.ClientDetailsID = tmptblLoyaltyPointsLastVisit.ClientDetailsID WHERE (((tblClientDetails.MobileTelephoneNumber) Is Not Null));")
    If rs.RecordCount <> 0 Then
        Do While Not rs.EOF
            sendSMS "118-324-1616", _
            rs.Fields("MobileTelephoneNumber"), _
        "Hi " & rs.Fields("FirstName") & _
        ", it has been 10 Months since your last visit to Chrysalis Lytham. You have " & rs.Fields("LoyaltyPointsSum") & _
        " and as per our Loyalty Points Policy you Loyalty Points Balance will be 'Zeroed' if this reaches 1 year. " & _
        "If you would like to keep your accrued points please visit the salon before this period ends. Thank You, Chrysalis Lytham."
        rs.MoveNext
        Loop
    End If
Exit_Proc:
    rs.Close
    Set rs = Nothing
    Exit Sub
Err_Proc:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Proc
'delete tmp records'
DoCmd.RunSQL ("DELETE tmptblLoyaltyPointsLastVisit.ID FROM tmptblLoyaltyPointsLastVisit;")
DoCmd.SetWarnings True
End Sub

i have tried this with LResponse routines before and it doesnt like it either, so im guessing that the code isnt a made up reference but a vba reference. do i have to put the 2 record sets together. without the two Dim rs As DAO.Recordset and just after the rs.close start the next one. so basically it only has one beginning with 2 middles and one end.

im thinking aloud here!

thanks
 
You have declared the recordset object twice.. Just delete the second Dim statement..
 
its now coming up with duplicate on the Exit_Proc:. do i delete the first instance of exit proc. but if i do that will the code jump to that point and miss the second rs. also im guessing that it will happen with the err_proc:. what is the rule on this?
 
Yes.. A procedure should have only unique lables, objects.. Your on Error statement will dictate the compiler to go to a specific section; no matter where the error occurs.. So delete all duplicates..
Code: 
Public Sub someNameHere()
Dim rs As DAO.Recordset
On Error GoTo Err_Proc
[COLOR=Green]    'run text reminders for client appointments 2 days from now'
    'insert appointments to tmptblTextReminders for appointments in 2 days with a mobile number'[/COLOR]
    DoCmd.RunSQL ("INSERT INTO tmptblTextReminders ( ClientDetailsID, OrderID, StartDate, StartTime, MobileTelephoneNumber ) SELECT tblClientDetails.ClientDetailsID, tblOrders.OrderID, tblOrders.OrderDate, tblOrders.OrderTime, tblClientDetails.MobileTelephoneNumber FROM tblOrders INNER JOIN tblClientDetails ON tblOrders.ClientDetailsID = tblClientDetails.ClientDetailsID WHERE (((tblOrders.OrderDate)=DateAdd('d',2,Date())) AND ((tblClientDetails.MobileTelephoneNumber) Is Not Null) AND ((tblOrders.TextReminderSent)=No) AND ((tblOrders.Status)=1));")
    
    [COLOR=Green]'recordset for texts to be sent(loop)'[/COLOR]
    Set rs = CurrentDb.OpenRecordset("SELECT tmptblTextReminders.MobileTelephoneNumber, tmptblTextReminders.StartDate, tmptblTextReminders.StartTime FROM tmptblTextReminders;")
    If rs.RecordCount <> 0 Then
        Do While Not rs.EOF
            sendSMS "118-324-1616", rs.Fields("MobileTelephoneNumber"), _
                    "Appointment Reminder: " & Format(rs.Fields("StartDate"), "dddd d mmm") & _
                    " at " & Format(rs.Fields("StartTime"), "hh:nn") & _
                    ". If you would like to cancel or change this, please ring Lytham on 01253 739432. Thanks Chrysalis."
            rs.MoveNext
        Loop
    End If
   [COLOR=Green] 'update tblOrders.TextReminder sent to 'yes' for messages just sent'[/COLOR]
    DoCmd.RunSQL ("UPDATE tblOrders INNER JOIN tmptblTextReminders ON tblOrders.OrderID = tmptblTextReminders.OrderID SET tblOrders.TextReminderSent = Yes WHERE (((tblOrders.OrderID)=[tmptblTextReminders].[OrderID]));")
   [COLOR=Green] 'delete records in tmptblTextReminders'[/COLOR]
    DoCmd.RunSQL ("DELETE tmptblTextReminders.ClientDetailsID FROM tmptblTextReminders;")
   [COLOR=Green] 'compile list of clients that have not visited the salon for 1 year and apend those records with loyalty points sum to tmptblLoyaltyPointsLastVisit'[/COLOR]
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("INSERT INTO tmptblLoyaltyPointsLastVisit ( ClientDetailsID, LastOfOrderDate, LoyaltyPointsSum ) SELECT subqryLoyaltyPointsLastVisit.ClientDetailsID, Max(subqryLoyaltyPointsLastVisit.LastOrderDate) AS MaxOfLastOrderDate, Sum(tblLoyaltyPoints.LoyaltyPointsAdjustments) AS SumOfLoyaltyPointsAdjustments FROM subqryLoyaltyPointsLastVisit INNER JOIN tblLoyaltyPoints ON subqryLoyaltyPointsLastVisit.ClientDetailsID = tblLoyaltyPoints.ClientDetailsID GROUP BY subqryLoyaltyPointsLastVisit.ClientDetailsID HAVING (((Max(subqryLoyaltyPointsLastVisit.LastOrderDate))<DateAdd('yyyy',-1,Date())) AND ((Sum(tblLoyaltyPoints.LoyaltyPointsAdjustments))>0));")
    [COLOR=Green]'apend tblLoyaltyPoints so that those that have not visited for 1 year have 0 points'[/COLOR]
    DoCmd.RunSQL ("INSERT INTO tblLoyaltyPoints ( ClientDetailsID, LoyaltyPointsAdjustments ) SELECT tmptblLoyaltyPointsLastVisit.ClientDetailsID, [LoyaltyPointsSum]-[LoyaltyPointsSum]-[LoyaltyPointsSum] AS LoyaltyPointsAdjustment FROM tmptblLoyaltyPointsLastVisit;")
   [COLOR=Green] 'delete tmp records'[/COLOR]
    DoCmd.RunSQL ("DELETE tmptblLoyaltyPointsLastVisit.ID FROM tmptblLoyaltyPointsLastVisit;")
    [COLOR=Green]'send text reminders for clients that have not visited for 10 months and have >0 loyalty points'[/COLOR]
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("INSERT INTO tmptblLoyaltyPointsLastVisit ( ClientDetailsID, LastOfOrderDate, LoyaltyPointsSum ) SELECT subqryLoyaltyPointsLastVisit.ClientDetailsID, Max(subqryLoyaltyPointsLastVisit.LastOrderDate) AS MaxOfLastOrderDate, Sum(tblLoyaltyPoints.LoyaltyPointsAdjustments) AS SumOfLoyaltyPointsAdjustments FROM subqryLoyaltyPointsLastVisit INNER JOIN tblLoyaltyPoints ON subqryLoyaltyPointsLastVisit.ClientDetailsID = tblLoyaltyPoints.ClientDetailsID GROUP BY subqryLoyaltyPointsLastVisit.ClientDetailsID HAVING (((Max(subqryLoyaltyPointsLastVisit.LastOrderDate))=DateAdd('m',-10,Date())) AND ((Sum(tblLoyaltyPoints.LoyaltyPointsAdjustments))>0));")
    [COLOR=Green]'recordset for texts to be sent(loop)'[/COLOR]
    Set rs = CurrentDb.OpenRecordset("SELECT tmptblLoyaltyPointsLastVisit.ClientDetailsID, tblClientDetails.FirstName, tmptblLoyaltyPointsLastVisit.LoyaltyPointsSum, tblClientDetails.MobileTelephoneNumber FROM tblClientDetails INNER JOIN tmptblLoyaltyPointsLastVisit ON tblClientDetails.ClientDetailsID = tmptblLoyaltyPointsLastVisit.ClientDetailsID WHERE (((tblClientDetails.MobileTelephoneNumber) Is Not Null));")
    If rs.RecordCount <> 0 Then
        Do While Not rs.EOF
            sendSMS "118-324-1616", rs.Fields("MobileTelephoneNumber"), _
                    "Hi " & rs.Fields("FirstName") & _
                    ", it has been 10 Months since your last visit to Chrysalis Lytham. You have " & rs.Fields("LoyaltyPointsSum") & _
                    " and as per our Loyalty Points Policy you Loyalty Points Balance will be 'Zeroed' if this reaches 1 year. " & _
                    "If you would like to keep your accrued points please visit the salon before this period ends. Thank You, Chrysalis Lytham."
            rs.MoveNext
        Loop
    End If
     [COLOR=Green]'delete tmp records'[/COLOR]
    DoCmd.RunSQL ("DELETE tmptblLoyaltyPointsLastVisit.ID FROM tmptblLoyaltyPointsLastVisit;")
    DoCmd.SetWarnings True
Exit_Proc:
    rs.Close
    Set rs = Nothing
    Exit Sub
Err_Proc:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Proc
End Sub
 
You must log in or register to reply here.

Similar threads

V
DLookup on a SQL statement
Replies
6
Views
412
VSolano
V
H
Solved Dlookup Or SetValue not working
Replies
17
Views
726
mike60smart
mike60smart
Y
New with a problem
Replies
4
Views
415
Pat Hartman
P
Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
598
Mike Krailo
Mike Krailo
NickCoe(UK)
Getting the true location of VBA reference files in 365 ctr.
Replies
17
Views
1,286
NickCoe(UK)
NickCoe(UK)

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom