asked on

Run-time error 424 (calling a procedure)

Hello programmers. i am calling the below procedure from my first procedure. when i get
to the "SET" line I get the error "Run-time error '424' Object Required. I use the same recordset variable in the first procedure but i close and set it to nothing before calling this procedure. I added the dims in the called procedure hoping that would make it see the object but no go. Any suggestions. thank you again.
Public Sub Update3009rpt()

Dim rsQryCAvg As DAO.Recordset
Dim rsTblRpt As DAO.Recordset
Dim rsQryCAvgcnt As Integer

DoCmd.RunSQL "DELETE tbl_ChkAvgRpt_3009.* FROM tbl_ChkAvgRpt_3009;"

Set rsTblRpt = db.OpenRecordset("tbl_ChkAvgRpt_3009")

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

Or this:

    Dim rsQryCAvg As DAO.Recordset
    Dim rsTblRpt As DAO.Recordset
    Dim rsQryCAvgcnt As Integer
    Dim db as database
    
    set db = currentDB
    DoCmd.RunSQL "DELETE tbl_ChkAvgRpt_3009.* FROM tbl_ChkAvgRpt_3009;"
    
    Set rsTblRpt = db.OpenRecordset("tbl_ChkAvgRpt_3009")

Open in new window

Jim Dettman (EE MVE)

Or better yet, use the function in here in place of CurrentDB():

https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2072-CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

Jim.

Jim Dettman (EE MVE)

BTW, you're better off to do this:

DoCmd.RunSQL "DELETE tbl_ChkAvgRpt_3009.* FROM tbl_ChkAvgRpt_3009;"

like this:

Dim strSQL

strSQL = "DELETE tbl_ChkAvgRpt_3009.* FROM tbl_ChkAvgRpt_3009;"
CurrentDB().Execute strSQL, dbfailonerror

In this case the SQL is not all that complicated, but by stuffing it into a variable, you can breakpoint on the Execute line and look at the SQL statement. Can save a lot of troublshooting time.

Second important thing there is the .Execute method rather then RunSQL, because you can error trap by using dbFailonerror.

Jim.

ilfocorp

ASKER

Perfect! Thanks again.