troubleshooting Question

VBA - MS Access: Sub providing the repetitive/same results despite changing variables

Avatar of Brandon Garnett
Brandon Garnett asked on
Microsoft AccessMicrosoft ApplicationsProgrammingVisual Basic ClassicVisual Basic.NET
7 Comments1 Solution102 ViewsLast Modified:
So I'm working with a PHP API you see, and when a button is clicked I want it to send an HTTP request and get the result (which is only a few words at max)

I load the onclick method up with this code:
Private Sub cmdButton_Click()
    Dim Response As String
    Dim Protocol As String
    Dim Message As String
    Response = http_Resp("http://blahblahblah.blah&API&Variable=" & Me.Variable)
    Message = Response
    MsgBox Message
    Response = ""
    Protocol = ""
    Message = ""
End Sub

Public Function http_Resp(ByVal sReq As String) As String

    Dim byteData() As Byte
    Dim XMLHTTP As Object

    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")

    XMLHTTP.Open "GET", sReq, False
    byteData = XMLHTTP.responseBody

    Set XMLHTTP = Nothing

    http_Resp = StrConv(byteData, vbUnicode)

End Function

The first time I press said button, there is a slight (expected) delay until the message box pops up. When it does pop up, it shows the appropriate response. However, when I click the button again, there is virtually no wait time and it pops up with the exact same response.

This is troublesome because in my case: 2 consecutive calls to the API DOES result in 2 different responses.
I am certain it is not the API's fault because if I do this manually (ie, go into the browser and go to "blahblahblah.blah") I get the expected differing responses per every refresh.

The fact that the first click makes a delay and the second (or anything after) does not have a delay makes me wonder if the VBA/MS Access is just caching the result for the Sub/Function.

I'm frustrated because this is the first language I have used that flat out skips lines code. The only way I can get the next/different result is if I completely close out of MS Access and open it back up again then click said button.
What am I doing wrong?

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros