calling subroutine from VBA routine

[klnlsu]

I am trying to call a subroutine from a VBA routine to append a record to an existing table. Below is the code for opening my recordsets for reading followed by the code creating my table to append to:

Public Sub UpdateLotsizes()
Dim curDatabase As DAO.Database
Dim ProdPlanDB As DAO.Database
Dim rstQryLot As DAO.Recordset
Dim rstInvLoc As DAO.Recordset
Set curDatabase = CurrentDb
Set ProdPlanDB = DAO.OpenDatabase("O:\Production\ProdPlan.mdb", False, False)

Set rstQryLot = curDatabase.OpenRecordset("tblLotsizeQuery")
Set rstInvLoc = ProdPlanDB.OpenRecordset("iminvloc_sql")

On Error GoTo CreateTableError
DoCmd.RunSQL "CREATE TABLE tblExceptionReport([Item #] text, " & _
"[Previous Stock Code] text, " & _
"[Current Stock Code] text, " & _
"[Previous Lotsize] number, " & _
"[Current Lotsize] number);"
...

To call my subroutine, I simply put in the code:

ExceptionReportAddRecord

And this is the code of the subroutine:

Public Sub ExceptionReportAddRecord()
Dim rstExcRpt As Recordset
Set rstExcRpt = curDatabase.OpenRecordset("tblExceptionReport")

With rstExcRpt
.AddNew
.Fields("item_no").Value = txtItemNumber
.Fields("prevStkSt").Value = txtPrevStkCode
.Fields("curStkSt").Value = txtStkCode
.Fields("prevLS").Value = dblPrevLotsize#
.Fields("currLS").Value = dblLotsize#
.Update ' add new record
End With

rstExcRpt.Close
Set rstExcRpt = Nothing
End Sub

I am receiving "Run-time error 434: Object required" at the line I marked with

Can someone tell what I am doing wrong?

 

[SOS]

Change this:

Dim rstExcRpt As Recordset

To this

Dim rstExcRpt As DAO.Recordset

 

[klnlsu]

I did but I still get the same error.

 

[SOS]

oops, I missed that you had

curDatabase

Make sure it is declared in the proper location so both can use it. And the proper location would be in the GENERAL DECLARATIONS section like

Public curDatabase As DAO.Database

 

[klnlsu]

That fixed it!