Code runs, sometimes, other times errors for .MoveFirst due to being at .EOF

[Sandman-Mondo]

Some preliminary information.
This application was written by another individual that hasn't been with the company for several years.

I have dabbled with VBA in Excel, lots of differences and function that I am not familiar with in Access.



There is an Excel application that is used by many other departments that uses 3 of the 4 Global Constants used for URLs where this data is found.

Several months ago, the locations were moved to different servers/URLs. Both the Excel application, and this Access application ceased functioning. I reached out to the owner of the Excel app and he sent me an updated copy of the sheet. After poking through the Excel code, I found the paths. I looked at the Access code and found the same information but with an additional URL.

I updated the Access to match the Excel paths and it looked like things were good. However, the Access application will not always run. I went into the code, rem’d the On Error statement to see where the code was faulting. It seems that a RecordSet is already at .EOF when it encounters a .MoveFirst command. The fact that this application will run at times and have no problem while erroring out other times makes me think it could be a timing issue with the data or network due to a plethora of “Sleep” and “Do Event” commands peppered through the code or another reason that I can't put a finger on.


With rs
.MoveFirst
Do Until .EOF

In the part of the code below referencing WebBrowser0 is where I think my issue is. I don’t think the data is being picked up.
Italic text is modified code to not show actual info but I hope I’m explaining things well enough to not be totally confusing.
This first bit of code is where I think the issue is.

Location-path is the server page url
ToolString is the string with all the separate entities concatenated together

Code:

With Forms!Main.Form!ToolStatus.Form.WebBrowser0
.navigate Location-path & ToolString
While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
Do While .ReadyState <> 4: DoEvents: Loop
End With


It then starts through 4 different With statements that relate to equipment that is grouped by what it is used for.

If [Forms]![Main].[Form].[cboArea] = "ToolsA" Then
Sleep (1000)
With Forms!Main.Form!ToolStatus.Form.WebBrowser1
.navigate ToolsA1
Do While .ReadyState <> 4: DoEvents: Loop
End With
Sleep (1000)
With Forms!Main.Form!ToolStatus.Form.WebBrowser2
.navigate ToolsA2
Do While .ReadyState <> 4: DoEvents: Loop
End With
Sleep (1000)
With Forms!Main.Form!ToolStatus.Form.WebBrowser3
.navigate ToolsA3
Do While .ReadyState <> 4: DoEvents: Loop
End With
Sleep (1000)
With Forms!Main.Form!ToolStatus.Form.WebBrowser4
.navigate ToolsA4
Do While .ReadyState <> 4: DoEvents: Loop
End With

Else ToolsE



Set HTMLDoc = Forms!Main.Form!ToolStatus.Form.WebBrowser0.Docume nt

The next line of code is where things don’t happen.

Set Tables = HTMLDoc.getElementsByTagName("TABLE")

I put” If Tables.Length = 0 Then Stop” in as a troubleshooting step and yeah, the code would stop here if this line was not there.


Thank you in advance for any input.

[Sandman-Mondo]

I should add this info
This .MoveFirst statement is in a For Next loop with the For statement being
For Column = 0 To C - 1

At this time I'm not sure how much code I can share. apologies.

[Minty]

You should always check for records in a recordset before trying to issue a movefirst or movelast.

If rs.EOF or rs.BOF then 
     MsgBox "No records to process" 
     Exit Sub
End If

The Sleep and DoEvents coding appear to all be to with interacting with a web browser, presumably to ensure it has had a chance to get the appropriate information?

[Sandman-Mondo]

Minty,

I understand that the Sleep and DoEvents are allowing things to be accomplished while data should be collected.

I also understand that checking for records would be something to do. I've essentially put a "check" in the code.

I've put the following line of code in "If Tables.Length = 0 Then Stop" after the Set Tables line of code I referrenced in my original post. Too often it comes back as 0. What I'm trying to figure out is why "Tables" does not have records or if there is another area that is preventing the data from being seen.

My issue is that I am not the original author of this code and still a novice with VBA. Up until a few months ago this code ran without fail until there was a change in the locaton of the data. Again, the Excel application has no issue with what it does and is not as complex. But this Access application isn't always pulling data.

[CJ_London]

What data type/0bject is tables? .length is not a property I recognise

Set is used for objects such as recordsets, and length implies a string although as stated it is not a property of a string

Have you got option explicit at the top of each module, disabled error checking and compiled your code?