SQL update in VBA public sub Error 91 (1 Viewer)

[Jallan!]

Runtime Error 91, is the error I get.

This is the base code I have written to open a recordset:

**Begin base code**
Strsql = "SELECT bomts.*, Rotors.PartNumber, Rotors.Price, Rotors.Price2 FROM BOMTS LEFT JOIN Rotors ON BOMTS.Component = Rotors.PartNumber WHERE(isnull(BOMTS.[Level])) OR (((BOMTS.[Comp Proc Type])='F')) order by bomts.rcdcount"

Set rstsrc = db.OpenRecordset(Strsql)

rstsrc.MoveLast 'used to pull in full recordset
rstsrc.MoveFirst 'used to position to first record in recordset

Call UpdateHTS()

**End relevant base code**

I want to update records with a null field, so I created a public sub, and am calling this sub in my main routine above:

Public Sub UpdateHTS()

Strsql = "UPDATE BOMTS SET BOMTS.[Parent HTS] = '" & 999999999 & "' WHERE isnull(BOMTS.[Parent HTS]);" 'remove null HTS codes

Debug.Print Strsql

DoCmd.SetWarnings False 'Turn off warnings so records write without msg box
rstsrc.AddNew 'Prep for writing record
DoCmd.RunSQL Strsql 'SQL to append to table
DoCmd.SetWarnings True 'Turn warnings back on

I get the runtime 91 error when rstsrc.AddNew executes.

FYI, all of the code worked until I wanted to remove the null condition in the field BOMTS.[Parent HTS]

Do I need to declare a different SQL string and not use Strsql? Maybe Strsql1?

I need help on this please and thanks in advance.

 

[MajP]

Is rstSrc a global variable? Do away with the global variable and maybe you can debug this code. This is a perfect example of why not to use globals. Clearly by error 91 rstSrc is not instantiated, but because of this design I cannot tell why. Matter of fact it makes no sense to even share a global since both routines are really two different recordsets. Just make rstSrc local in each routine.

 

[The_Doc_Man]

Error 91 is a runtime error, "Object not set" (and has other related meanings).

Inside your UpdateHTS() subroutine, you use rstsrc, which is not declared or instantiated inside the sub. You also didn't show us whether it was defined or set outside of that code. If you don't have Option Explicit defined, rstsrc will be a Variant, so you will get away with using the name at compile time, but at run time it has to be explicitly opened in context OR it would have to be declared PUBLIC in your outer (calling) module.

EDIT: I see MajP finished typing before I did.

 

[arnelgp]

you run updateHTS() sub first on your main code:

'call updateHTS first
'before opening the recordset
'
Call UpdateHTS

'**Begin base code**
Strsql = "SELECT bomts.*, Rotors.PartNumber, Rotors.Price, Rotors.Price2 FROM BOMTS LEFT JOIN Rotors ON BOMTS.Component = Rotors.PartNumber WHERE(isnull(BOMTS.[Level])) OR (((BOMTS.[Comp Proc Type])='F')) order by bomts.rcdcount"

Set rstsrc = db.OpenRecordset(Strsql)

rstsrc.MoveLast 'used to pull in full recordset
rstsrc.MoveFirst 'used to position to first record in recordset

now on updateHTS() sub, you do not need to Addnew since you are running
a Query:

Public Sub UpdateHTS()
Dim Strsql As String
Strsql = "UPDATE BOMTS SET BOMTS.[Parent HTS] = '" & 999999999 & "' WHERE isnull(BOMTS.[Parent HTS]);" 'remove null HTS codes

Debug.Print Strsql

DoCmd.SetWarnings False 'Turn off warnings so records write without msg box
'You don't need the Addnew since you are running
'a Query
'rstsrc.AddNew 'Prep for writing record
DoCmd.RunSQL Strsql 'SQL to append to table
DoCmd.SetWarnings True 'Turn warnings back on
End Sub

 

[The_Doc_Man]

Arnel's point is ALSO valid, plus the fact that using an UPDATE will change any records, but the .AddNew isn't an update. If you were adding records, that SQL would have been more like an INSERT INTO, which is what a recordset.AddNew really does.