Create VBA within Access that opens given url as a post with various data elements.

Use Access 2000 form (cmd button) to open a web browser, login, create session, pass variables and other data to a perl script.
I've already done this within HTML/ASP (working great)... and somewhat familiar with VB/VBA.

Current HTML Code used:

<form method="post" action="http://myserver.com/scripts/cgi/myperl.pl" target="_blank">
<input type="hidden" name="SESSIONID" value="0">
<input type="hidden" name="COMMAND" value="1">
<input type="hidden" name="NAME" value="read_only"></td>
<input type="hidden" name="PASSWORD" value=""></td>
<input type="hidden" name="ARCHIVE" value="File Cabinet1"></td>
<input type="hidden" name="DATABASEFIELD" value="<%=InvNO%>"></td>
<input type="hidden" name="CX" value='1'></td>
<input class="button" type=submit value="Open" style="<%=btnOneValue%>">
</form>

There is more to the code... but this is all that is pertinent to answer the question. InvNO will be a value from the Access database to be used along with the other values during the submission. I have been looking awhile for an answer... some are close but not exactly what I need. Please do not refer to previous posts other than copying the entire solution and pasting it into this thread. Thanks for the assistance... hopefully I've assigned enough points value!
Hal9000ReturnsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alan WarrenApplications DeveloperCommented:
Hi Hal9000Returns

From: http://www.experts-exchange.com/Databases/MS_Access/Q_21161794.html#12265636

dim myIE as new InternetExplorer
myIE.visible = true
myIE.navigate "<url>"

do while myIE.Busy
  doevents
loop

with myIE.document
    .forms(0)("field1").value "<field1 value>"
    .forms(0).submit
end with

Alan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hal9000ReturnsAuthor Commented:
I get a Run-Time error 445 Object doesn't support this action...

    Dim myIE As New InternetExplorer
   
    myIE.Visible = True
    myIE.Navigate "http://myserver.com/scripts/cgi/myperl.pl"

    Do While myIE.Busy
    DoEvents
        Loop

        With myIE.Document
            .Forms(0)("SESSIONID").Value "0"
            .Forms(0)("COMMAND").Value "1"
            .Forms(0)("NAME").Value "read_only"
            .Forms(0)("PASSWORD").Value ""
            .Forms(0)("ARCHIVE").Value "File Cabinet1"
            .Forms(0)("DATABASEFIELD").Value "12345"
            .Forms(0)("SESSIONID").Value "0"
            .Forms(0)("CX").Value "1"
            .Forms(0).submit
       
        End With

what am I missing?
0
Alan WarrenApplications DeveloperCommented:
Just about to crash out here, but I would have thought an equal sign, though the solution in the other post didn't have?

.Forms(0)("SESSIONID").Value ="0"


Alan
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Hal9000ReturnsAuthor Commented:
...hmmmm

now I get a Run-Time error 91, 'Object Variable or With block variable not set'
0
paix120Commented:
Just a wild guess... is there supposed to be a . between Forms(0) and ("fieldname")?

.Forms(0).("Name").Value = "read only"

-Paix120
0
Alan WarrenApplications DeveloperCommented:
Hi Hi Hal9000Returns,

Sorry I had to leave, needed rest.
This definately works using ASP page and  Access 2000, have tested it.
You need to set a reference to Microsoft Internet Controls

Option Explicit
Private Sub Command0_Click()
   ' Reference: Microsoft Internet Controls
 
  Dim myIE As New InternetExplorer
 
  myIE.Visible = True
  myIE.Navigate "http://somedomain/Login.asp"

  Do While myIE.Busy
    DoEvents
  Loop

  With myIE.Document
    .Forms("frmLogin")("txtUserName").Value = "UserName"
    .Forms("frmLogin")("txtPassword").Value = "Password"
    .Forms("frmLogin").Submit
  End With

End Sub


Alan
0
Hal9000ReturnsAuthor Commented:
hey paix120.. thanks but no go. And I tried the latest from alanwarren, but still get 'Object Variable or With block variable not set'

Keep in mind that the data is being submitted to a Perl Script (which hasn't even made it from the VBA app yet without error)
Latest rendition (shortened while trouble-shooting)


Option Explicit
Private Sub cmdDocs_Click()



  ' Reference: Microsoft Internet Controls
 
  Dim myIE As New InternetExplorer
 
  myIE.Visible = True
  myIE.Navigate "http://mydocs.com/scripts/cgi/dwis.pl"

  Do While myIE.Busy
    DoEvents
  Loop

  With myIE.Document
    .Forms("frmLogin")("NAME").Value = "Read_Only"
    .Forms("frmLogin")("PASSWORD").Value = ""
    .Forms("frmLogin").Submit
  End With

end sub


0
Alan WarrenApplications DeveloperCommented:
Did you set a reference to Microsoft Internet Controls?
  Tools > References > Microsoft Internet Controls


Alan


0
Hal9000ReturnsAuthor Commented:
Yep, it's in there... does the "frmLogin" refer to the name of the form within Access?
0
Alan WarrenApplications DeveloperCommented:
No it is the name of the form in the HTML

<FORM METHOD=POST ACTION="./Login.Now.asp" id=frmLogin name=frmLogin>
<TR>
  <TD WIDTH="40%">&nbsp;&nbsp;Username:</TD>
  <TD ALIGN=right>
  <INPUT TYPE=text NAME=txtUserName SIZE=9 STYLE="font-size: 8pt;"></TD>
</TR>
<TR>
  <TD WIDTH="40%">&nbsp;&nbsp;Password:</TD>
  <TD ALIGN=right>
  <INPUT TYPE=password NAME=txtPassword SIZE=9 STYLE="font-size: 8pt;"></TD>
</TR>
<TR>
  <TD>&nbsp;</TD>
  <TD ALIGN=center>
    <SPAN STYLE="margin-left: 30px;">&nbsp</SPAN>
    <INPUT TYPE=SUBMIT NAME=operation VALUE="Login"  >
  </TD>
</TR>
</form>

Alan

0
Hal9000ReturnsAuthor Commented:
Thanks Alan. You get the points as first solution. Once I created another ASP page and then used it to post to the PERL script it worked swimmingly (not intended to dig those in Tx). I was thinking of going directly to the perl script page which was obviously not the answer. I did notice a hang every once in awhile (network traffic) which then cause the object variable not found error. Any ideas on how to be more forgiving on timeout?

Thanks again for the help!
0
Hal9000ReturnsAuthor Commented:
The only problem I am now having is... if an Web Browser (IE) is open, no problem... but if there are no iexplore sessions open, I get:

Run-Time error 91, 'Object Variable or With block variable not set'

I can use...     Application.FollowHyperlink "http://myweb.com/blank.html

before the code, but then I have two IE Explorer windows open. Seems a little quirky...

Do I need to pony up some points or would someone have an easy fix?

0
Alan WarrenApplications DeveloperCommented:
Hi Hal

yes I got the same error when I dug out the test db, it seems that the .Busy value is returning false before all the form elements are completed.
The error is occuring on this line  
  With myIE.Document


I seem to be having more success with the .ReadyState property, I think that .Busy is returning false when READYSTATE_INTERACTIVE  or READYSTATE_LOADED

  Dim myIE As New InternetExplorer
  myIE.Visible = True
  myIE.Navigate "http://mydocs.com/scripts/cgi/dwis.pl"

  Do Until myIE.ReadyState = READYSTATE_COMPLETE      ' 4
    DoEvents
  Loop




' Notes

'  ReadyState
'  typedef enum tagREADYSTATE{
'      READYSTATE_UNINITIALIZED = 0,
'      READYSTATE_LOADING = 1,
'      READYSTATE_LOADED = 2,
'      READYSTATE_INTERACTIVE = 3,
'      READYSTATE_COMPLETE = 4
'  } READYSTATE;
'
'  Values
'  READYSTATE_UNINITIALIZED  The default initialization state.
'  READYSTATE_LOADING  The object is currently loading its properties.
'  READYSTATE_LOADED  The object has been initialized.
'  READYSTATE_INTERACTIVE  The object is interactive, but not all of its data is available.
'  READYSTATE_COMPLETE  The object has received all of its data.

Alan
0
Alan WarrenApplications DeveloperCommented:
hmmm...

actually:   Not .Busy happens asynchronously with READYSTATE_COMPLETE
if anything READYSTATE_COMPLETE occurs momentarily before Not .Busy

  Do While myIE.Busy And myIE.ReadyState <> READYSTATE_COMPLETE
    Debug.Print "Busy= " & myIE.Busy & " ReadyState= " & myIE.ReadyState
    DoEvents
  Loop
 
  Debug.Print "Busy= " & myIE.Busy & " ReadyState= " & myIE.ReadyState

At a bit of a loss here as to why this problem only occurs spazmodically, seems never to happen if any myIE.document is residual in memory.


Alan
0
flavoCommented:
I had the same problem Alan.. I found a way to do it every time.. Ill need to find my answer...
0
flavoCommented:
yeah.. you were right Alan


  Set docHTML = appIE.Document
'wait for it
    Do Until docHTML.readyState = "complete"
        DoEvents
    Loop
'its there now, yay!!!

<more detail here> http://www.experts-exchange.com/Databases/MS_Access/Q_21041274.html

Dave
0
Alan WarrenApplications DeveloperCommented:
Hi Dave, thanks for dropping by.

ReadyState does seem to be more resilient, I haven't failed on it yet, but in my debugs I noticed I could get a readystate = 4 and busy = true, so I concluded that .busy happens momentarilly after READYSTATE_COMPLETE

The problem only seems to occur if you haven't loaded the page for some time,.

So could probably fix with a pre-load of the page, but don't really like this approach, why do something 2 times?

  myIE.Visible = True
  myIE.Navigate "http://mydocs.com/scripts/cgi/dwis.pl"

  myIE.Quit
  Set myIE = Nothing

  myIE.Visible = True
  myIE.Navigate "http://mydocs.com/scripts/cgi/dwis.pl"



Alan



0
flavoCommented:
I was listening, thought i may butt in.

Strange indeed isnt it...
0
Hal9000ReturnsAuthor Commented:
Many thanks... works like a champ, and I now have a better understanding of the problem (which is actually more important). In fact, it reminds me of other problems we've had in the past dealing with other objects not already open (Excel) where we were posting data and manipulating using VBA. I'll need to go back and dig up those projects.  

Can I offer up some bonus points?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.