DLookup with TRUE

[shutzy]

i would like to know what i am missing in this

sendSMS "118-324-1616", DLookup("[MobileTelephoneNumber]", "tmptblTextMarketing", "SendText" = True , " " & Me.txtTextMessage & ". Thanks Chrysalis."

i have never used a true aspect in dlookup before. the results will also be many records. will that make a difference?

 

[pr2-eugin]

This should do it.. Since the value is not a variable, you do not have to concatenate it.. Just apply it straight off..

[COLOR=Green]'you did not have a closing parentheses.. [/COLOR]
sendSMS "118-324-1616", DLookup("[MobileTelephoneNumber]", "tmptblTextMarketing", [COLOR=Red][B]"SendText = True")[/B][/COLOR] , " " & Me.txtTextMessage & ". Thanks Chrysalis."

What exactly do you mean...

....the results will also be many records.

DLookUp is a simplified SELECT statement that will return only the First record even if there are several records returned based on the condition..

 

[shutzy]

thanks for helping me out so much recently. what i am wanting to do is send a message to every number that has the SendMessage = true. i was hoping that it would loop itself through all the records.

how would i do this if the DLookup will only return the first record it finds?

 

[pr2-eugin]

I think you need a Recordset Object to do that.. Look over the link, it should be fairly simple, if you do not understand post back..

thanks for helping me out so much recently.

That's alright.. Happy to Help !!

 

[shutzy]

i have had a whirl. if this is correct then it is easy. im guessing that nothing is this easy but i might be suprised. hopefully!!

Sub DoSomething()
On Error Goto Err_Proc
 
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tmptblTextMessages")
If rs.Recordcount <> 0 Then
With rs
.FindFirst "SendText" = True
If Not .NoMatch Then
[COLOR=lime][COLOR=blue](either)[/COLOR][/COLOR][COLOR=seagreen]sendSMS "118-324-1616", "[MobileTelephoneNumber]", " " & Me.txtTextMessage & ". Thanks Chrysalis."[/COLOR]
[COLOR=red][COLOR=blue](or)[/COLOR]MsgBox "No Clients Selected!", vbOKOnly, "No Messages Sent!."[/COLOR]
End If
End With
End If
 
Exit_Proc:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub
Err_Proc:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Proc
End Sub

 

[pr2-eugin]

Almost there.. When you start using Access on a regular basis, some stuff will seem really simple.. IMO, Recordsets are one such thing..

Well I have just removed the FindFirst method because, I belive the Query tmptblTextMessages is the Query that is already filtered dataset, if it is not a Query, then I would just use a RAW SQL on that table.. So,

Sub DoSomething()
On Error Goto Err_Proc
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT MobileTelephoneNumber FROM tmptblTextMessages WHERE sendSMSFieldName = True;")
    If rs.Recordcount <> 0 Then
        [COLOR=Red][B]Do While Not rs.EOF[/B][/COLOR]
            sendSMS "118-324-1616", rs.Fields("MobileTelephoneNumber"), " " & Me.txtTextMessage & ". Thanks Chrysalis."
            [COLOR=Red][B]rs.MoveNext[/B][/COLOR]
        [COLOR=Red][B]Loop[/B][/COLOR]
    Else
        MsgBox "No Clients Selected!", vbOKOnly, "No Messages Sent!."
    End If
Exit_Proc:
    rs.Close
    Set rs = Nothing
    Exit Sub
Err_Proc:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Proc
End Sub

 

[shutzy]

can you tell me which part of this record set code tells it to loop through the records please.

 

[pr2-eugin]

OOPS !! Sorry my bad, I have now edited the code.. the .MoveNext is the method that causes the Recordset object to loop through..

 

[shutzy]

hi again. i am now trying to do this for another application. i am wanting to automate it. ie on open of a form it runs.

im just struggling to refer to the record set. i have tried to follow what you did yesterday and what you have done today. ie rs.Field("etc") and " & Me.MyField & " but it seem to not work for me

sendSMS "118-324-1616", rs.Fields("MobileTelephoneNumber"), "Appointment Reminder: rs.Fields("Format(StartDate, "dddd d mmm")") at rs.Fields("Format(Me.OrderTime, "hh:nn")"). If you would like to cancel or change this, please ring Lytham on 01253 739432. Thanks Chrysalis."

any help

 

[pr2-eugin]

shutzy, you should concatenate the values you obtain from the recordset.. Also the Format function should be applied after getting the value from the recordset field..

sendSMS "118-324-1616", _
        rs.Fields("MobileTelephoneNumber"), _
        "Appointment Reminder: [COLOR=Red][B]" & Format([/B][/COLOR][COLOR=Blue][B]rs.Fields("StartDate")[/B][/COLOR][COLOR=Red][B], "dddd d mmm") &[/B][/COLOR] _
        " at [COLOR=Red][B]" & Format([/B][/COLOR][COLOR=Blue][B]rs.Fields("OrderTime")[/B][/COLOR][COLOR=Red][B], "hh:nn") &[/B][/COLOR] _
        ". If you would like to cancel or change this, please ring Lytham on 01253 739432. Thanks Chrysalis."

 

[shutzy]

thanks. ive just been playing around with it, i got

"Appointment Reminder: " & rs.Fields(Format(StartDate, "dddd d mmm")) & " at  " & rs.Fields(Format(StartTime, "hh:nn")) & ". If you would like to cancel or change this, please ring Lytham on 01253 739432. Thanks Chrysalis."

this time i didnt get any errors but looking at yours the formatting was in the wrong place anyway.

im getting there

thanks

 

[shutzy]

in all honesty, i dont think i would ever have got that layout.

thanks again

 

[pr2-eugin]

you will get there soon enough shutzy..

 

[shutzy]

could you please help me out again. i have tried to follow your lead but it aint working. i am trying to do another record set to send another bunch of texts.

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 beofre this period ends. Thank You, Chrysalis Lytham."
        rs.MoveNext
        Loop
    Else
       'if no text need be sent then show this message'
        MsgBox "No Clients Selected!", vbOKOnly, "No Messages Sent!."
    End If
End Sub

the query is correct but i am getting errors on the rs.fields(***)

thanks for the other record set. it works flawlessly.

 

[pr2-eugin]

What is the exact error you are getting?

 

[shutzy]

compile error. expected end of statement on the rs.Field("FirstName")

 

[pr2-eugin]

You need to concatenate the values properly..

"Hi [COLOR=Red][B]" &[/B][/COLOR] rs.Fields("FirstName") & _
", it has been 10 Months since your last visit to Chrysalis Lytham. You have [COLOR=Red][B]" &[/B][/COLOR] 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."

 

[shutzy]

sorry i didnt know that that applied here. i thought that the & _ was to indicate that the next line was included.

thanks again.

 

[shutzy]

i now have an unexpected end of statement on the last row

"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 beofre this period ends. Thank You Chrysalis Lytham."

i dont get this

 

[pr2-eugin]

That's alright.. Just remember, every time you want to include values from variables you concatenate them.. The error you are getting is because of missing double quotes after the word 1 year... Try..

"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.[COLOR=Red][B] " &[/B][/COLOR] _
"If you would like to keep your accrued points please visit the salon before this period ends. Thank You, Chrysalis Lytham."

Not trying to patronize, but you do not have to use line breaks if it confuses you.. When I started I used to type them all in one full line.. It is not a big deal, once you get the hang of things you can change them.. Sorry if this offends you..