Nation wide text message from access??

JHB said:
I'm not your boss, so please do not call me that!

You didn't show in which codeline you got the error!
I've look at the code in Sub btnOK_Click, and see it is a lot of junk in it, declaring variables which are not used and other stupid things, so I've cleaned it up a little.
Code: 
Private Sub btnOK_Click()
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim msg As String
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("RosterQ")
  If Not rs.EOF Then
    msg = Me.txtMessage.Value
    rs.MoveFirst
    Do While Not rs.EOF
      SendSMS FROMPHONE, rs![ContactNumber], msg
      rs.MoveNext
    Loop
  End If
End Sub
I've noticed you are using a variable/constant "FROMPHONE", but I can't see where you've declared it and given it a value, I think that could cause the error you get.
So I suggest you put it in the top of the module, (remember to put in the correct value):
Code: 
Const FROMPHONE As String = "FromPhoneNumber"
Also here I notice you are using variables/constants, ("ACCOUNTSID", "BASEURL" and "AUTHTOKEN", maybe more but check it your self), but I can't see where you've declared them and given them a value, so I suggest you put them in the top of the module, (remember to put in the correct value):
Code: 
Const ACCOUNTSID As String = "YourAccountsID"
Const AUTHTOKEN As String = "YourAuthToken"
Const BASEURL As String = "TheBaseURL" 'I think it is "https://api.twilio.com"
So have I been your boss, you've been in big trouble now! :D:D:D
Click to expand...

I sure would be in trouble haha.

I made all the changes, First error came here:

Run error 3265 as Item not found in this collection.

Code: 
SendSMS FROMPHONE, rs![+16694004739], msg

I tried everything to try to fix it but it keeps giving me the same error.

Here is the entire code:

Code: 
Private Sub btnOK_Click()
  Const FROMPHONE As String = "+16694004739"
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim msg As String
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("RosterQ")
  If Not rs.EOF Then
    msg = Me.txtMessage.Value
    rs.MoveFirst
    Do While Not rs.EOF
      SendSMS FROMPHONE, rs![+17868591639], msg
      rs.MoveNext
    Loop
  End If

End Sub
 
alvarogueva said:
Code: 
SendSMS FROMPHONE, rs![+16694004739], msg
I tried everything to try to fix it but it keeps giving me the same error.
Click to expand...
Is "+16694004739" a fieldname in your table, I don't believe that!
 
SendSMS FROMPHONE, rs![+17868591639], msg
Click to expand...

I bolded a part of this to make it unequivocal as to what JHB means here. This syntax involving a recordset object, a bang (!), and a bracketed item EXPRESSLY says that the thing in brackets is the NAME (and NOT the value) of some field in the recordset. Since the "+" character is not legal as part of a field name, it is not surprising that you get a syntax error. This CAN NEVER be correct syntax given the current rules of VBA, Access, and recordsets.

If the value in question represents a dial-out number (egad, haven't played with modem dial-outs in YEARS) then the needed syntax might need to be

Code: 
SendSMS FROMPHONE, "+17868591639", msg
 
The_Doc_Man said:
I bolded a part of this to make it unequivocal as to what JHB means here. This syntax involving a recordset object, a bang (!), and a bracketed item EXPRESSLY says that the thing in brackets is the NAME (and NOT the value) of some field in the recordset. Since the "+" character is not legal as part of a field name, it is not surprising that you get a syntax error. This CAN NEVER be correct syntax given the current rules of VBA, Access, and recordsets.

If the value in question represents a dial-out number (egad, haven't played with modem dial-outs in YEARS) then the needed syntax might need to be

Code: 
SendSMS FROMPHONE, "+17868591639", msg
Click to expand...

Now says "Invalid use of Null"

Code: 
Private Sub btnOK_Click()
  Const FROMPHONE As String = "+16694004739"
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim msg As String
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("RosterQ")
  If Not rs.EOF Then
    msg = Me.txtMessage.Value
    rs.MoveFirst
    Do While Not rs.EOF
      SendSMS FROMPHONE, "+17862037388", msg
      rs.MoveNext
    Loop
  End If

End Sub
 
Yes, and that code worked for me. Did you use the Access code? What's all your code? What line throws the error?
 
alvarogueva said:
Now says "Invalid use of Null"

Code: 
Private Sub btnOK_Click()
  Const FROMPHONE As String = "+16694004739"
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim msg As String
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("RosterQ")
  If Not rs.EOF Then
    msg = Me.txtMessage.Value
    rs.MoveFirst
    Do While Not rs.EOF
      SendSMS FROMPHONE, "+17862037388", msg
      rs.MoveNext
    Loop
  End If

End Sub
Click to expand...
If you only want to test it then, then you don't need the above code, then the code was in it's original form intended to send a message to all numbers returned by a query, (or in a table).
The only line for testing is, (check if the phone numbers are correct):
Code: 
Private Sub btnOK_Click()
  SendSMS "+16694004739", "+17862037388", "Hello"
End Sub
But as pbaldy wrote, what line throws the error?
 
JHB said:
If you only want to test it then, then you don't need the above code, then the code was in it's original form intended to send a message to all numbers returned by a query, (or in a table).
The only line for testing is, (check if the phone numbers are correct):
Code: 
Private Sub btnOK_Click()
  SendSMS "+16694004739", "+17862037388", "Hello"
End Sub
But as pbaldy wrote, what line throws the error?
Click to expand...

Ok I must be doing an error. I proceed to do exacly like I was told.

I need some help here... This are all my code lines:

textMessage Box on Change Event:
Code: 
Private Sub txtMessage_Change()

    LimitChange Me.txtMessage, 160

    Dim msg As String
    msg = Me.txtMessage.Text
    
    Me.lblTyped.Caption = Len(msg)
    Me.lblLeft.Caption = 160 - Len(msg)
End Sub

Thenk, also in textMessage Box on Key press:
Code: 
Private Sub txtMessage_KeyPress(KeyAscii As Integer)
   LimitKeyPress Me.txtMessage, 160, KeyAscii
End Sub

Function SendSMS(fromNumber As String, toNumber As String, body As String)
    
    Dim SmsUrl As String
    Dim CallUrl As String
    Dim TwimlUrl As String
    Dim postData As String
    
    On Error GoTo Error_Handler

' ******************************************************
'
' To switch to from sending SMS messages to making
' phone calls, comment out the SmsUrl and postData
' variables and uncomment CallUrl, TwimlUrl and
' postData variables below.
   
    SmsUrl = BASEURL & " https://demo.twilio.com/welcome/sms/reply/" & ACCOUNTSID & "AUTHTOKEN"
    postData = "From=" & fromNumber _
                & "&To=" & toNumber _
                & "&Body=" & body
    
'    CallUrl = BASEURL & "/2010-04-01/Accounts/" & ACCOUNTSID & "/Calls"
'    TwimlUrl = "http://twimlets.com/" _
'                & "message?Message[0]=" _
'                    & "Due%20to%20inclement%20weather,%20todays%20practice%20has%20been%20canceled."
'    postData = "From=" & fromNumber _
'               & "&To=" & toNumber _
'               & "&Url=" & url
    
' ******************************************************
    
    ' setup the request and authorization
    Dim http As MSXML2.XMLHTTP60
    Set http = New MSXML2.XMLHTTP60
    
Const ACCOUNTSID As String = "MYACCOUNTSID"
Const AUTHTOKEN As String = "MYAUTHTOKEN"
Const BASEURL As String = "https://api.twilio.com" 'I think it is "https://api.twilio.com"
    
    ' send the POST data
    http.send postData
    
    ' optionally write out the response if you need to check if it worked
    Debug.Print http.responseText

    If http.Status = 201 Then

    ElseIf http.Status = 400 Then
        MsgBox "Failed with error# " & _
            http.Status & _
            " " & http.statusText & vbCrLf & vbCrLf
    ElseIf http.Status = 401 Then
        MsgBox "Failed with error# " & http.Status & _
            " " & http.statusText & vbCrLf & vbCrLf
    Else
        MsgBox "Failed with error# " & http.Status & _
            " " & http.statusText
    End If

Exit_Procedure:

    On Error Resume Next

    ' clean up
    Set http = Nothing

Exit Function

Error_Handler:

    Select Case Err.Number

        Case NOINTERNETAVAILABLE
            MsgBox "Connection to the internet cannot be made or " & _
                "Twilio website address is wrong"

        Case Else
            MsgBox "Error: " & Err.Number & "; Description: " & Err.Description

            Resume Exit_Procedure

        Resume

    End Select
End Function


Sub LimitKeyPress(ctl As Control, iMaxLen As Integer, KeyAscii As Integer)
On Error GoTo Err_LimitKeyPress
    ' Purpose:  Limit the text in an unbound text box/combo.
    ' Usage:    In the control's KeyPress event procedure:
    '             Call LimitKeyPress(Me.MyTextBox, 12, KeyAscii)
    ' Note:     Requires LimitChange() in control's Change event also.

    If Len(ctl.Text) - ctl.SelLength >= iMaxLen Then
        If KeyAscii <> vbKeyBack Then
            KeyAscii = 0
            Beep
        End If
    End If

Exit_LimitKeyPress:
    Exit Sub

Err_LimitKeyPress:
    'Call LogError(Err.Number, Err.Description, "LimitKeyPress()")
    Resume Exit_LimitKeyPress
End Sub
Sub LimitChange(ctl As Control, iMaxLen As Integer)
On Error GoTo Err_LimitChange
    ' Purpose:  Limit the text in an unbound text box/combo.
    ' Usage:    In the control's Change event procedure:
    '               Call LimitChange(Me.MyTextBox, 12)
    ' Note:     Requires LimitKeyPress() in control's KeyPress event also.

    If Len(ctl.Text) > iMaxLen Then
        MsgBox "Truncated to " & iMaxLen & " characters.", vbExclamation, "Too long"
        ctl.Text = Left(ctl.Text, iMaxLen)
        ctl.SelStart = iMaxLen
    End If

Exit_LimitChange:
    Exit Sub

Err_LimitChange:
    'Call LogError(Err.Number, Err.Description, "LimitChange()")
    Resume Exit_LimitChange
End Sub

Then, in OK button, in On Click Event:
Code: 
Private Sub btnOK_Click()
  Const FROMPHONE As String = "+16694004739"
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim msg As String
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("RosterQ")
  If Not rs.EOF Then
    msg = Me.txtMessage.Value
    rs.MoveFirst
    Do While Not rs.EOF
      SendSMS FROMPHONE, "+17862037388", msg
      rs.MoveNext
    Loop
  End If

End Sub

That's all my Coding right now... and is not working.

Right now I am getting error "user-defined type not defined" in Code:
Code: 
    Dim http As MSXML2.XMLHTTP60  <--------- HERE
    Set http = New MSXML2.XMLHTTP60
 
pbaldy said:
Yes, and that code worked for me. Did you use the Access code? What's all your code? What line throws the error?
Click to expand...

Please check what I jsut Quote our friend here about the error I am getting
 
Could you post all the code in one block, please?
Have you add the references to "Microsoft XML 6.0 library"?
 
JHB said:
Could you post all the code in one block, please?
Have you add the references to "Microsoft XML 6.0 library"?
Click to expand...

I did posted everything In a code didn't I?

No I did not do XML 6.0 Library. how do I do that??
 
pbaldy said:
It is shown in the link, about a third of the way down.
Click to expand...

I just click on the XML v 6.0 like in the picture. Now is giving me this error:

Code: 
Const ACCOUNTSID As String =

Duplicate declaration in current scope
 
pbaldy said:
It is shown in the link, about a third of the way down.
Click to expand...

Ok soooo...
I twitch it a bit. I change some code as to this:
Code: 
Private Sub btnOK_Click()
  Const FROMPHONE As String = "+16694004739"
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim msg As String
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("DriverT")    [COLOR="Red"]<--- I actually chose the Driver       Table where I have the phone numbers of the drivers.[/COLOR]
  If Not rs.EOF Then
    msg = Me.txtMessage.Value
    rs.MoveFirst
    Do While Not rs.EOF
      SendSMS FROMPHONE, DriverT![DriverPhone1], msg   [COLOR="red"]<--- Then I declared to get the information from DriverT, and from the field DriverPhone1. That would put in there who's phone will be the receiving one...[/COLOR]
      rs.MoveNext
    Loop
  End If

End Sub


After I made the changes, it comes to tell me that ERROR 424 "object required" Here:

Code: 
      SendSMS FROMPHONE, DriverT![DriverPhone1], msg

Thoughts?
 
JHB said:
Could you post all the code in one block, please?
Have you add the references to "Microsoft XML 6.0 library"?
Click to expand...

I guess you mean like this:

Code: 
Private Sub btnOK_Click()
  Const FROMPHONE As String = "+16694004739"
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim msg As String
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("DriverT")
  If Not rs.EOF Then
    msg = Me.txtMessage.Value
    rs.MoveFirst
    Do While Not rs.EOF
      SendSMS FROMPHONE, DriverT![DriverPhone1], msg
      rs.MoveNext
    Loop
  End If

End Sub

Private Sub Combo17_Enter()
  Me.Combo17.RowSource = "SELECT TruckID, TruckPlate FROM TruckT WHERE CompanyID=" & [Parent].[CompanyF]![CompanyID]
End Sub

Private Sub Command23_Click()
Application.FollowHyperlink _
"http://www.flhsmv.gov/forms/72190.pdf"
End Sub

Private Sub txtMessage_Change()

    LimitChange Me.txtMessage, 160

    Dim msg As String
    msg = Me.txtMessage.Text
    
    Me.lblTyped.Caption = Len(msg)
    Me.lblLeft.Caption = 160 - Len(msg)
End Sub

Private Sub txtMessage_KeyPress(KeyAscii As Integer)
   LimitKeyPress Me.txtMessage, 160, KeyAscii
End Sub

Function SendSMS(fromNumber As String, toNumber As String, body As String)
    
    'Dim SmsUrl As String
    Dim CallUrl As String
    Dim TwimlUrl As String
    Dim postData As String
    
    On Error GoTo Error_Handler

' ******************************************************
'
' To switch to from sending SMS messages to making
' phone calls, comment out the SmsUrl and postData
' variables and uncomment CallUrl, TwimlUrl and
' postData variables below.
   
    SmsUrl = BASEURL & "/2010-04-01/Accounts/" & ACCOUNTSID & "/SMS/Messages"
    postData = "From=" & fromNumber _
                & "&To=" & toNumber _
                & "&Body=" & body
    
'    CallUrl = BASEURL & "/2010-04-01/Accounts/" & ACCOUNTSID & "/Calls"
'    TwimlUrl = "http://twimlets.com/" _
'                & "message?Message[0]=" _
'                    & "Due%20to%20inclement%20weather,%20todays%20practice%20has%20been%20canceled."
'    postData = "From=" & fromNumber _
'               & "&To=" & toNumber _
'               & "&Url=" & url
    
' ******************************************************
    
    ' setup the request and authorization
    Dim http As MSXML2.XMLHTTP60
    Set http = New MSXML2.XMLHTTP60
    
Const ACCOUNTSID As String = "I ERASE THIS. PRIVATE LOL"
Const AUTHTOKEN As String = "I ERASE THIS. PRIVATE LOL"
Const BASEURL As String = "https://api.twilio.com"
'I think it is "https://api.twilio.com"

    ' send the POST data
    http.send postData
    
    ' optionally write out the response if you need to check if it worked
    Debug.Print http.responseText

    If http.Status = 201 Then

    ElseIf http.Status = 400 Then
        MsgBox "Failed with error# " & _
            http.Status & _
            " " & http.statusText & vbCrLf & vbCrLf
    ElseIf http.Status = 401 Then
        MsgBox "Failed with error# " & http.Status & _
            " " & http.statusText & vbCrLf & vbCrLf
    Else
        MsgBox "Failed with error# " & http.Status & _
            " " & http.statusText
    End If

Exit_Procedure:

    On Error Resume Next

    ' clean up
    Set http = Nothing

Exit Function

Error_Handler:

    Select Case Err.Number

        Case NOINTERNETAVAILABLE
            MsgBox "Connection to the internet cannot be made or " & _
                "Twilio website address is wrong"

        Case Else
            MsgBox "Error: " & Err.Number & "; Description: " & Err.Description

            Resume Exit_Procedure

        Resume

    End Select
End Function


Sub LimitKeyPress(ctl As Control, iMaxLen As Integer, KeyAscii As Integer)
On Error GoTo Err_LimitKeyPress
    ' Purpose:  Limit the text in an unbound text box/combo.
    ' Usage:    In the control's KeyPress event procedure:
    '             Call LimitKeyPress(Me.MyTextBox, 12, KeyAscii)
    ' Note:     Requires LimitChange() in control's Change event also.

    If Len(ctl.Text) - ctl.SelLength >= iMaxLen Then
        If KeyAscii <> vbKeyBack Then
            KeyAscii = 0
            Beep
        End If
    End If

Exit_LimitKeyPress:
    Exit Sub

Err_LimitKeyPress:
    'Call LogError(Err.Number, Err.Description, "LimitKeyPress()")
    Resume Exit_LimitKeyPress
End Sub
Sub LimitChange(ctl As Control, iMaxLen As Integer)
On Error GoTo Err_LimitChange
    ' Purpose:  Limit the text in an unbound text box/combo.
    ' Usage:    In the control's Change event procedure:
    '               Call LimitChange(Me.MyTextBox, 12)
    ' Note:     Requires LimitKeyPress() in control's KeyPress event also.

    If Len(ctl.Text) > iMaxLen Then
        MsgBox "Truncated to " & iMaxLen & " characters.", vbExclamation, "Too long"
        ctl.Text = Left(ctl.Text, iMaxLen)
        ctl.SelStart = iMaxLen
    End If

Exit_LimitChange:
    Exit Sub

Err_LimitChange:
    'Call LogError(Err.Number, Err.Description, "LimitChange()")
    Resume Exit_LimitChange
End Sub
 
pbaldy said:
It is shown in the link, about a third of the way down.
Click to expand...

Ok so I have a new update;

I forgot to create a couple of modules that definetly need it to be there.
I also twitched everything a little, and Now I am getting the errors that the sample database that this guy gives you does.

I get error #404 NOT FOUND. After I get this error 4 or 5 times I get ERROR 94 INVALID USE OF NULL; and points to this piece of code:
Code: 
        SendMessage FROMPHONE, DriverJobQ![DriverPhone1], msg

That is part of this code:
Code: 
Option Explicit
'Option Compare Database

' **************************************************
' NOTE: You will need to update this constant
'  with your own From phone number
Const FROMPHONE As String = "[+16694004739]"
' **************************************************

Private Sub btnOK_Click()

    Dim msg As String
    msg = Me.txtMessage.Value

    Dim db As DAO.Database
    Dim strSQL As String
    Set db = CurrentDb

    Dim qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs("DriverJobQ")

    Dim DriverJobQ As Recordset
    Set DriverJobQ = qdf.OpenRecordset()
   
    DriverJobQ.MoveFirst

    Do While DriverJobQ.EOF = False

        SendMessage FROMPHONE, DriverJobQ![DriverPhone1], msg
        'MakeCall FROMPHONE, roster![ContactNumber], msg
    
    DriverJobQ.MoveNext

  Loop

End Sub

Any ideas on how to solve this?
 
alvarogueva said:
Ok soooo...
I twitch it a bit. I change some code as to this:
Code: 
Private Sub btnOK_Click()
  Const FROMPHONE As String = "+16694004739"
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim msg As String
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("DriverT")    [COLOR=Red]<--- I actually chose the Driver       Table where I have the phone numbers of the drivers.[/COLOR]
  If Not rs.EOF Then
    msg = Me.txtMessage.Value
    rs.MoveFirst
    Do While Not rs.EOF
      SendSMS FROMPHONE, DriverT![DriverPhone1], msg   [COLOR=red]<--- Then I declared to get the information from DriverT, and from the field DriverPhone1. That would put in there who's phone will be the receiving one...[/COLOR]
      rs.MoveNext
    Loop
  End If

End Sub
After I made the changes, it comes to tell me that ERROR 424 "object required" Here:

Code: 
      SendSMS FROMPHONE, DriverT![DriverPhone1], msg
Thoughts?
Click to expand...
You really to read up on how recordset works, because you're messing it up.
The correct code would be:
Code: 
      SendSMS FROMPHONE, [B][COLOR=Red]rs[/COLOR][/B]![DriverPhone1], msg
But before you start to send a message to all drivers in the table, I think you should get it to run first, as I mention before in a former post the only code you need for testing it is, (remember to change the number "+16694004739" to the correct phonenumber from where the message is send, also change "+17862037388" to the correct number which should receive the message) :
Code: 
Private Sub btnOK_Click()
  SendSMS "+16694004739", "+17862037388", "Hello"
End Sub
Did you get adding the references to to "Microsoft XML 6.0 library"?
 
You must log in or register to reply here.

Similar threads

Z
Notes field, quotations, apostrophes, and error messages
Replies
19
Views
1,873
Zydeceltico
Z
isladogs
What is the most secure method of distributing databases?
Replies
17
Views
3,197
isladogs
isladogs
isladogs
Using Twilio with Access to send & receive text & voice messages
2 3
Replies
48
Views
11,054
isladogs
isladogs
P
Send messages from Access to Telegram
Replies
1
Views
4,638
CJ_London
C
Z
Data entry to populate multiple tables logic and process
Replies
18
Views
2,263
Pat Hartman
P

Users who are viewing this thread

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