docmd running before it's supposed to... (1 Viewer)

[mikewood1980]

Hi

I have the following code:

Dim rsWallCons As Recordset
            
            sQuery = "select * from tblWallConstructions where ((tblWallConstructions.chrTestID) like '*" & Me.cboReportID & "*')"
            
            Set rsWallCons = dbcurr.OpenRecordset(sQuery)
            
            MsgBox ("matching wall constructions = " & rsWallCons.RecordCount)
            
            rsWallCons.MoveFirst <<<<<CRASHES HERE !!!!
             Do Until rsWallCons.EOF
                        'delete linings
                        DoCmd.RunSQL ("delete * from tblLining where ((tblLining.intLiningID) = " & rsWallCons!intLiningID1 & ")")
                            
                            
                        DoCmd.RunSQL ("delete * from tblLining where ((tblLining.intLiningID) = " & rsWallCons!intLiningID2 & ")")
                        MsgBox ("should have deleted linings...")
                        
                        'delete wall leaves
                        DoCmd.RunSQL ("delete * from tblWallLeaves where ((tblWallLeaves.intWallLeavesID) = " & rsWallCons!intLeafID1 & ")")
                        DoCmd.RunSQL ("delete * from tblWallLeaves where ((tblWallLeaves.intWallLeavesID) = " & rsWallCons!intLeafID2 & ")")
                                      
                'then delete that record...
                rsWallCons.Delete
                rsWallCons.MoveNext
            Loop

The code crashes at the point marked (rsWallCons.MoveFirst) - the message box above shows 0 records. So far, this makes sense, except that there should be records in rsWallCons. Even more strangely, the records that should be in there are deleted successfully by the following SQL statements. Given that the code doesnt get this far I don't know how this happens???! Does anybody know why this might be - I'm completely confused!

Just to recap - I need to now a) why there are apparently no records in rsWallCons b) why the SQL statements execute before they should and c) if the are no records in rsWallCons - how do the SQL statements delete the right records??!

Thanks in advance for your help

Mike

 

[KenHigg]

a. I think you have to traverse the entire recordset by doing a .movelast before the count will display the correct number.

 

[MarkK]

If RecordCount is zero or EOF is true when you open a recordset, then there are no records. In your case it must be that your SQL is not selecting any.
What I would do is cut and paste this ...

"select * from tblWallConstructions where chrTestID like '*YourCBOData*')"

... where 'YourCBOData' is a value from the combo you expect should yield records, cut and paste this into a Query and troubleshoot it there.

Also, when you open a DAO.Recordset, if there is a first record it is current, so there's never an immediate need to explicitly MoveFirst.

 

[gemma-the-husky]

if there might be no records then before you do a

rst.movefirst

either
a) set an error handler to detect the errror

or

b) if rst.eof then 'same thing - you are already at end of file, so move operation will fail

its similar if you try to do an indexed read

rst.findfirst "expression"

in this case you need to do a

if rst.nomatch to see if read succeeded or not

but rst.nomatch doesnt work until you do a rst operation, so it isnt available in your case.

 

[boblarson]

Also, you should explicitly declare your recordset objects so instead of:

Dim rsWallCons As Recordset

you should do

Dim rsWallCons As DAO.Recordset