Access VBA 2003 Script Code Question

[choppaz]

I've inherited an Access 2003 VBA project which is misbehaving (sometimes locks up, right now it is complaining on:

DoCmd.RunCommand acCmdShowTable

It's complaint is "The command or action 'ShowTable' isn't available now", Run-time error 2046.
I'm not sure which object it is trying to perform this command on -- Me? Me's active control?



Here is a shortened version of the code:

Private Sub MatchLocation()

Dim rstRup as Recordset
strSQL = "SELECT DISTINCTROW Id, Name, Location FROM CustTable WHERE Location = 3;"

Set rstRup = dbs.OpenRecordset(strSQL)

With Me
lngRecCount = conZero
If Not rstRup.BOF Then
rstRup.MoveLast
rstRup.MoveFirst

With rstRup

Do Until .EOF
.
.
.
GoSub RollUpNextRec
.
.
.
rstRup.MoveNext
Loop

End With ' end with rstRup

End With 'end with Me

RollUpNextRec
Dim MyLoc as Integer
rstRup.MoveNext

DoCmd.RunCommand acCmdShowTable

With rstRup
myLoc = .Location
End With
Return

End Sub

So, what I'd really like to be able to do is find out what object the DoCmd.RunCommand acCmdShowTable is acting upon.
Since the sub RollUpNextRec is outside the with block for rstRup, and it's outside the with block for Me, I'm guessing
the command is acting upon Me's active control.

Thanks!

[GremlinSA]

My guess is that the command is been run after the loops are finished, and the object is no longer set..

Using GOSUB is also a very bad method to code..

rather put that code insode the loops and take the gosub out.. or make a new function/sub for the code, that you can call directly...


Private Sub MatchLocation()

Dim rstRup as Recordset
strSQL = "SELECT DISTINCTROW Id, Name, Location FROM CustTable WHERE Location = 3;"

Set rstRup = dbs.OpenRecordset(strSQL)

With Me
lngRecCount = conZero
If Not rstRup.BOF Then
rstRup.MoveLast
rstRup.MoveFirst

With rstRup

Do Until .EOF
.
.
.
RollUpNextRec
Dim MyLoc as Integer
rstRup.MoveNext

DoCmd.RunCommand acCmdShowTable

With rstRup
myLoc = .Location
End With
.
.
.
rstRup.MoveNext
Loop

End With ' end with rstRup

End With 'end with Me

End Sub