[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2004-11-23
19
Medium Priority
?
1,708 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:Hal9000Returns
  • 8
  • 7
  • 3
  • +1
19 Comments
 
LVL 26

Accepted Solution

by:
Alan Warren earned 1000 total points
ID: 12660016
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
 

Author Comment

by:Hal9000Returns
ID: 12660539
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12660576
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Hal9000Returns
ID: 12660929
...hmmmm

now I get a Run-Time error 91, 'Object Variable or With block variable not set'
0
 
LVL 3

Expert Comment

by:paix120
ID: 12662356
Just a wild guess... is there supposed to be a . between Forms(0) and ("fieldname")?

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

-Paix120
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12663008
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
 

Author Comment

by:Hal9000Returns
ID: 12666504
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12666831
Did you set a reference to Microsoft Internet Controls?
  Tools > References > Microsoft Internet Controls


Alan


0
 

Author Comment

by:Hal9000Returns
ID: 12667112
Yep, it's in there... does the "frmLogin" refer to the name of the form within Access?
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12667203
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
 

Author Comment

by:Hal9000Returns
ID: 12668487
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
 

Author Comment

by:Hal9000Returns
ID: 12710869
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12712070
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12712179
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
 
LVL 34

Expert Comment

by:flavo
ID: 12712302
I had the same problem Alan.. I found a way to do it every time.. Ill need to find my answer...
0
 
LVL 34

Expert Comment

by:flavo
ID: 12712312
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12712363
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
 
LVL 34

Expert Comment

by:flavo
ID: 12712514
I was listening, thought i may butt in.

Strange indeed isnt it...
0
 

Author Comment

by:Hal9000Returns
ID: 12716953
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question