Error 440 "Automation Error" 2

[bkkbkk83]

Hi--

I just finished coding an Access sub. Every time I run it, it will go through the first iteration of the "For a = firstsim To nosim" loop and save all of the data correctly. But on the second iteration, it inexplicably stops and returns a 440 runtime error "Automation Error." Help just talks about there being an error in an "automation object." Does anyone have any clue as to what this might mean? If anyone has some spare time, would you mind looking taking a look at the code that follows and maybe give me some suggestions as to an error I'm missing?

Thank you so much!

Ben

----

Private Sub Command21_Click()
Dim Email
Email = InputBox("Please enter your e-mail address for 3DPSSM access.", "Enter e-mail address&quot
Set dbs = CurrentDb
Set rstprot = dbs.OpenRecordset("proteins", 2)
rstprot.MoveFirst
rstprot.MoveLast

MsgBox (rstprot.RecordCount)

Dim myvar, myvar2
myvar = "h234343*"
myvar2 = Split(myvar, "*", 2)
MsgBox (myvar2(0))

'Specify Min (firstprot) and Max (noprot) record numbers
Dim noprot
noprot = rstprot.RecordCount
Dim firstprot
rstprot.MoveFirst
firstprot = (rstprot.AbsolutePosition + 1)

'Array to store Prots to be sent through 3DPSSM
ReDim CleanedProts(noprot)
CleanedProts(0) = ""
'Loop to strip out stop codons
With rstprot
Dim protst, CleanProt, DirtyProtArray, DirtyProtArray2
Dim CurrProt
For x = firstprot To noprot
protst = !Sequence
DirtyProtArray = Split(protst, ".", 2)
CleanProt = DirtyProtArray(0)
DirtyProtArray2 = Split(CleanProt, "*", 2)
CleanProt = DirtyProtArray2(0)
CurrProt = (rstprot.AbsolutePosition + 1)
MsgBox (CurrProt)
CleanedProts(CurrProt) = CleanProt
.MoveNext
Next x
End With
MsgBox ("Before&quot
Sleep 3000
MsgBox ("3sec&quot


'Defines Array to store the URLs of the 3DPSSM jobs
ReDim JobURLs(noprot)

With rstprot
'Submit Loop
.MoveFirst
MsgBox ("submit for loop&quot
For y = firstprot To noprot
'Defines String to Submit
CurrProt = (.AbsolutePosition + 1)
submitstr = CleanedProts(CurrProt)
MsgBox (!ProteinID)
Dim IE
Dim page As String
Set IE = CreateObject("InternetExplorer.Application&quot
'Submit as URL
Dim JobURL(6), finalJobURL
JobURL(0) = "

http://www.sbg.bio.ic.ac.uk/~3dpssm/scripts/3dpssm_submit.cgi?usr-email="

JobURL(1) = Email
JobURL(2) = "&seq-desc="
JobURL(3) = !ProteinID
JobURL(4) = "&seq_format=single&global_local=yes&seg=yes&DataFormat=yes&three_iter=no&sequence="
JobURL(5) = submitstr
finalJobURL = Join(JobURL)
IE.navigate finalJobURL
Do While IE.Busy
Loop
Do While IE.ReadyState <> 4
Loop
'Takes return data
page = IE.Document.all.tags(&quot;html&quot.Item(0).outerhtml
Dim jstart, jend, jURL
jstart = InStr(1, page, &quot;<br><a href=&quot; + Chr(34))

jend = InStr(lstart + 1, page, Chr(34) + &quot; target&quot

MsgBox (page)
If jstart <> 0 And jend <> 0 Then
jURL = (Mid(page, jstart + 13, (jend - 13) - (jstart + 13)) & &quot;.summaryhits.html&quot
JobURLs(CurrProt) = jURL
MsgBox (jURL)
End If
.MoveNext
Next y
End With

MsgBox (&quot;Your computer will now wait for 3DPSSM to process the data. While the computer is waiting, Access may appear to be frozen. Disregard these warnings&quot
Sleep 2000000

'Fetch URL and Extract Protein Matches

Set rstsim = dbs.OpenRecordset(&quot;similarproteins&quot;, 2)
rstsim.MoveFirst
rstsim.MoveLast
rstsim.MoveFirst
Dim firstsim, nosim
firstsim = (rstsim.AbsolutePosition + 1)
nosim = (rstsim.RecordCount)




rstprot.MoveFirst
Dim CurrSearch, CodeStart, EValPreString, EValEndString, EValStart, EValEnd, TmpEVal
For z = firstprot To noprot
'Defines URL to retrieve
CurrProt = (rstprot.AbsolutePosition + 1)
fetchURL = JobURLs(CurrProt)
Set IE = CreateObject(&quot;InternetExplorer.Application&quot
IE.navigate fetchURL
Do While IE.Busy
Loop
Do While IE.ReadyState <> 4
Loop
'Takes data from webpage and puts it into var page
page = IE.Document.all.tags(&quot;html&quot.Item(0).outerhtml
MsgBox (page)

Dim EValPrePreString As String

'Parse page for ProteinCodes, find E-Values, Trim E-Value, and Insert into rstprot
For a = firstsim To nosim
CurrSearch = &quot;libview.cgi?id=&quot; + rstsim!proteincode
CodeStart = InStr(1, page, CurrSearch)
If CodeStart <> 0 Then
EValPreString = &quot;<td>&quot;
EValPrePreString = &quot;Chime View&quot;
EValEndString = &quot;</td>&quot;
EValPreStart = InStr(CodeStart, page, EValPrePreString)
EValStart = InStr(EValPreStart, page, EValPreString)
EValEnd = InStr(EValStart, page, EValEndString)
TmpEVal = Mid(page, (EValStart + 4), (EValEnd - (EValStart + 4)))
'MsgBox (TmpEVal)
EValue = Trim(TmpEVal)
'MsgBox (EValue)
'Recordset.Fields (SearchString)
rstprot.Edit
rstprot.Fields(rstsim!proteincode) = EValue
rstprot.Update
End If
rstsim.MoveNext
Next a

rstprot.MoveNext
Next z






MsgBox (CleanedProts(1))
MsgBox (CleanedProts(2))
MsgBox (CleanedProts(3))
MsgBox (CleanedProts(4))




'dbs.OpenRecordset
'DoCmd.OpenTable &quot;proteins&quot;


End Sub

 

[vbajock]

440 errors are returned from the application you are communicating with to VBA. I used to do a lot of DDE stuff, and that's the only place I've seen it.
A few questions:

Set rstsim = dbs.OpenRecordset(&quot;similarproteins&quot;, 2)
Is similarproteins a local table or an attached table?

Did you interogate the values of the variables firstsim and nosim when the code bombed?

 

[bkkbkk83]

Yes, similarproteins is a local table--it's part of the local Access database I'm working with.

You're right; the code crashes right after I deal with the firstsim and nosim variables.

It hangs on &quot;CurrSearch = &quot;libview.cgi?id=&quot; + rstsim!proteincode&quot;, which comes right after I use those two variables in &quot;For a = firstsim To nosim&quot;.

Thanks so much for your help!

 

[vbajock]

Are you using Option Explicit in the heading of your code module?

 

[bkkbkk83]

I just tried turning Option Explicit on and making sure that all of my variables were defined in a Dim statement...but that still doesn't seem to make any difference; I'm still getting the same 440 error.

 

[vbajock]

ok, I'm stumped. Your sure you have the line it is bombing on identified? A 440 error at this point doesn't make sense. It might help to add an error handler to your procedure. At the beginning of the proc add:

Private Sub Command21_Click()
Dim Email
===> ON error goto err_h

At the end of your proc after these lines

'dbs.OpenRecordset
'DoCmd.OpenTable &quot;proteins&quot;

add
===> Exit Sub

===> err_h:
===> msgbox str$(err)&&quot; &quot;& error$
===> Stop

 

[bkkbkk83]

You're right...there is another error hidden behind the 440: the message box traps a &quot;No Current Record.DAO.Recordset&quot; number 3021 error. Sometimes it catches an &quot;Item not Found in this Collection&quot; error.

With error trapping turned on, though, I'm not sure which line is stopping the program.

Do you see anything that could return a 3021 error? I'm thinking it may be something in the nested for loop in the second half; I'm taking a good look at that but so far I haven't been able to spot anything...

Thanks,
Ben

 

[vbajock]

to see the line:

err_h:
msgbox str$(err)&&quot; &quot;& error$
Stop
====> Resume

Code will hit the stop statement, then hit the f8 key to step through each line. Let me know what line it is

 

[bkkbkk83]

As I went through the code line-by-line, I figured it out: I wasn't returning the &quot;simprot&quot; recordset to the first record after looping through it, so when the second protein went through it reached the EOF marker in the recordset and just kept hitting that marker--which returned a 440 Automation Error and sometimes the 3021 No Current Record Error.

I added a rstsim.MoveFirst command though and everything works beautifully!

Thanks for all of your help! I really do appreciate it! I didn't know about the line-by-line debugging mode; thanks for filling me on that one...it's quite useful.

Ben