Nation wide text message from access??

[JHB]

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:

        SendMessage FROMPHONE, DriverJobQ![DriverPhone1], msg

That is part of this 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?

Now you're back to the old code which is a mess!

 

[alvarogueva]

Now you're back to the old code which is a mess!

After I change it to rs! like you told me it gives me a Variable not defined.

Damn! So what do I need to change to make it work better? cause I am lost!

 

[JHB]

Read post #40

 

[alvarogueva]

Read post #40

The numbers that are there are the right numbers, where one is the Twilio number and the other one is my cell phone.

I changed it to rs! and gave me the error i mention earlier.

I did add the XML library

 

[pbaldy]

I don't know if it's relevant, but my numbers don't have the plus sign in them. You also don't want the brackets here:

Const FROMPHONE As String = "[+16694004739]"

 

[JHB]

The numbers that are there are the right numbers, where one is the Twilio number and the other one is my cell phone.
I changed it to rs! and gave me the error i mention earlier.
I did add the XML library

Let us try a new thing, create a new button on the form, and place the one code line below, (nothing else), behind the buttons click event, what error do you get now?

SendSMS "+16694004739", "+17862037388", "Hello"

 

[alvarogueva]

I don't know if it's relevant, but my numbers don't have the plus sign in them. You also don't want the brackets here:

Const FROMPHONE As String = "[+16694004739]"

It gives me FAILED WITH ERROR#404 NOT FOUND

 

[alvarogueva]

No more errors Have been found aside of that ERROR#404 so something must of been fixed.... I think.

 

[alvarogueva]

Let us try a new thing, create a new button on the form, and place the one code line below, (nothing else), behind the buttons click event, what error do you get now?

SendSMS "+16694004739", "+17862037388", "Hello"

I did that. it gives me sub or function not defined.
HM? :S

 

[pbaldy]

Why don't you attach a sample db with your code in it we can play with.

 

[The_Doc_Man]

Here is some debugging advice. If you already knew how to do this, then consider my comments as merely a reminder.

You say your code doesn't work because if you run it, you know Access is going to throw an error at you. Let it. It will offer a pop-up that includes the option to DEBUG your VBA code. Click DEBUG. That gets you into a code window AND in the context of the failing module. Put a breakpoint on the line that fails. Close out the code window, close the form, and try again (knowing that the only change you have made is to add a breakpoint, you didn't actually fix the code.)

The code will run again but this time it will stop (breakpoint) BEFORE executing the line that was failing. At this point, you are in the context of the failing routine and the instruction that would be executed next is going to fail. But at that breakpoint, you are in local context in order to be able to see values for everything that your code can see. Now open the Immediate sub-window on the code window.

When you are inside the Immediate window, the command "Debug.Print object" will print out the value of the object (or tell you that it is null.) On the line that is failing, ask Access to print every non-quoted value on the line (except the subroutine entry point that begins the line). That way you can see which variable seems to be the problem.

I am not familiar with the package that you are using, and remote-diagnosis can only go so far. I have to agree with Paul that at this juncture, the only way any of the members more experienced with that package can help is to be able to take a look at the offending piece.

Error 404 is telling you "File or directory not found" - which means that you have somehow worked a file reference into something in a way that is not valid. The problem is that Access, Windows, and HTTP drivers can all return this error, so it is not clear as to which of those layers is trying to tell you something.

 

[JHB]

I did that. it gives me sub or function not defined.
HM? :S

Difficult to help you when you certainly change the Sub/Functions name, from "SendSMS" to "SendMessage".

 

[alvarogueva]

Here is some debugging advice. If you already knew how to do this, then consider my comments as merely a reminder.

You say your code doesn't work because if you run it, you know Access is going to throw an error at you. Let it. It will offer a pop-up that includes the option to DEBUG your VBA code. Click DEBUG. That gets you into a code window AND in the context of the failing module. Put a breakpoint on the line that fails. Close out the code window, close the form, and try again (knowing that the only change you have made is to add a breakpoint, you didn't actually fix the code.)

The code will run again but this time it will stop (breakpoint) BEFORE executing the line that was failing. At this point, you are in the context of the failing routine and the instruction that would be executed next is going to fail. But at that breakpoint, you are in local context in order to be able to see values for everything that your code can see. Now open the Immediate sub-window on the code window.

When you are inside the Immediate window, the command "Debug.Print object" will print out the value of the object (or tell you that it is null.) On the line that is failing, ask Access to print every non-quoted value on the line (except the subroutine entry point that begins the line). That way you can see which variable seems to be the problem.

I am not familiar with the package that you are using, and remote-diagnosis can only go so far. I have to agree with Paul that at this juncture, the only way any of the members more experienced with that package can help is to be able to take a look at the offending piece.

Error 404 is telling you "File or directory not found" - which means that you have somehow worked a file reference into something in a way that is not valid. The problem is that Access, Windows, and HTTP drivers can all return this error, so it is not clear as to which of those layers is trying to tell you something.

Hey thanks for the instruction!
JHB Help out to fix it, Now I am able to send text messages, and drivers are able to receive it; We still have a problem.

The driver did received the message, but it did bring an error; it brings me a null on the Line:

SendMessage FROMPHONE, rs![DriverPhone1], msg

Do you know why?

Again; thanks to JHB now is working, but I am getting this null. How do you do a breakpoint? ZERO experience in VBA.... sorry.

Thanks for swinging by!!!

 

[alvarogueva]

Difficult to help you when you certainly change the Sub/Functions name, from "SendSMS" to "SendMessage".

Did I change it? or you mean in the post???

 

[alvarogueva]

Why don't you attach a sample db with your code in it we can play with.

Check my last update!!

 

[alvarogueva]

MAJOR PROBLEM.

I send a couple of text messages with the 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("DriverJobQ")
  If Not rs.EOF Then
    msg = Me.txtMessage.Value
    rs.MoveFirst
    Do While Not rs.EOF
       SendMessage FROMPHONE, rs![DriverPhone1], msg
      rs.MoveNext
    Loop
  End If
End Sub

I noticed that even tho I send only 2 test, my messages send with Twilio went from 18 to 30!! Is this code mean to send to the single phone number listed in the selected customer? or to ALL CUSTOMERS? Also, If I just send it with the regular code :

Private Sub btnOK_Click()
  SendMessage "+16694004739", "+17862037388", "Hello"
End Sub

It send with no problem, no error or anything. This is a MAJOR PROBLEM for me right now.

 

[The_Doc_Man]

Is this code mean to send to the single phone number listed in the selected customer? or to ALL CUSTOMERS?

As the code appears in post #56, it will send the message to everyone the recordset returned from query "DriverJobQ" or at least that is how I would read it.

If that is wrong, then either modify DriverJobQ to have a WHERE clause or sub-clause to eliminate the drivers you don't want, OR put another IF statement in place, as e.g.

    Do While Not rs.EOF
       IF rs![someotherfield]="right value"  (or not quoted, if the value is numeric) THEN
           SendMessage FROMPHONE, rs![DriverPhone1], msg
           rs.MoveNext
       End If
    Loop

In other words, either the query or your VBA code must choose to whom you should send the message. The code you posted does not filter out drivers, so if DriverJobQ doesn't filter any drivers either, you have a wide-scope broadcast.

 

[alvarogueva]

As the code appears in post #56, it will send the message to everyone the recordset returned from query "DriverJobQ" or at least that is how I would read it.

If that is wrong, then either modify DriverJobQ to have a WHERE clause or sub-clause to eliminate the drivers you don't want, OR put another IF statement in place, as e.g.

    Do While Not rs.EOF
       IF rs![someotherfield]="right value"  (or not quoted, if the value is numeric) THEN
           SendMessage FROMPHONE, rs![DriverPhone1], msg
           rs.MoveNext
       End If
    Loop

In other words, either the query or your VBA code must choose to whom you should send the message. The code you posted does not filter out drivers, so if DriverJobQ doesn't filter any drivers either, you have a wide-scope broadcast.

GOD DAMN! Thats the only thing I did not wanted!!!!!!

okok so... the code would look like this?

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("DriverJobQ")
       IF rs![DriverJobQ]="DriverPhone1"
  If Not rs.EOF Then
    msg = Me.txtMessage.Value
    rs.MoveFirst
    Do While Not rs.EOF
       SendMessage FROMPHONE, rs![DriverPhone1], msg
      rs.MoveNext
    Loop
  End If
End Sub

 

[The_Doc_Man]

Separate question:

How do you do a breakpoint?

When you are in a code window (which you WILL be if the code offers you the chance to DEBUG the error) then you just click in the left margin of the code window opposite the line you wanted to set as a breakpoint. Alternatively if you highlight the line, then the function key F9 toggles the breakpoint on or off. You can tell it is on when there is a round reddish-brown dot in the margin of the line. When that breakpoint is set and you start running the code, Access takes a break at that line BEFORE it executes the code. At that moment, you can see every public and private variable in the class module that would be visible to the code, plus any Public variables derived from general modules that are active at the moment. There is also an option in the debug window to "Clear All Breakpoints" - which you would use when done with debugging.

It might not hurt to look up "Access VBA debugging" to understand the controls and windows available in this rather technical context. From your words, this is an alien environment to you. To debug this kind of problem, you need to understand it better.

A comment about that null... it is POSSIBLE (but by no means guaranteed) that it originates because you didn't define everything you needed ahead of time. The calls to set up the transmission probably have some parameters that must be supplied, but some of them are used before the transmission and some after.

It is also possible that one of the nulls is being returned from the loop because at least one of the records returned by rs![DriverPhone1] is partly null. You might wish to examine that table by opening it in datasheet mode and sorting by DriverPhone1. If there is a null, it will be at the top of the datasheet list. (Don't worry, doing this won't save the new sort order unless you SAVE the query afterwards; if you just close the query and Access prompts you to save it, answer NO.)

 

[alvarogueva]

Separate question:



When you are in a code window (which you WILL be if the code offers you the chance to DEBUG the error) then you just click in the left margin of the code window opposite the line you wanted to set as a breakpoint. Alternatively if you highlight the line, then the function key F9 toggles the breakpoint on or off. You can tell it is on when there is a round reddish-brown dot in the margin of the line. When that breakpoint is set and you start running the code, Access takes a break at that line BEFORE it executes the code. At that moment, you can see every public and private variable in the class module that would be visible to the code, plus any Public variables derived from general modules that are active at the moment. There is also an option in the debug window to "Clear All Breakpoints" - which you would use when done with debugging.

It might not hurt to look up "Access VBA debugging" to understand the controls and windows available in this rather technical context. From your words, this is an alien environment to you. To debug this kind of problem, you need to understand it better.

A comment about that null... it is POSSIBLE (but by no means guaranteed) that it originates because you didn't define everything you needed ahead of time. The calls to set up the transmission probably have some parameters that must be supplied, but some of them are used before the transmission and some after.

It is also possible that one of the nulls is being returned from the loop because at least one of the records returned by rs![DriverPhone1] is partly null. You might wish to examine that table by opening it in datasheet mode and sorting by DriverPhone1. If there is a null, it will be at the top of the datasheet list. (Don't worry, doing this won't save the new sort order unless you SAVE the query afterwards; if you just close the query and Access prompts you to save it, answer NO.)

WOW! you were completely right!! it did give me that Null because it tried to send the info to a a null PhoneNumber1.

Great , that null won't come out anymore, since I actually set it up as a If not Null criteria, so it will only send to a driver that the phone number is set.

Now, how can I filter to the actual phone number, of the driver I have selected?? If I can decide to which number, on dependence of the company and the driver I have in the form at the moment, then it will work just fine as I need it I believe!