Application defined or object defined error

[Opal]

I need a second pair of eyes to look at the following.

I am trying to clear the data input into a bound subform
using VBA. I have the following module, but get the above
error:

Dim dbobject As DAO.Database
Dim ClearUnsRS As DAO.Recordset
Dim strquery As String
Dim HoldUnsafeTally As Integer
HoldUnsafeTally = Forms!frmDriveAudit.UnsafeTally.Value
Set dbobject = CurrentDb
strquery = "SELECT * FROM Unsafe Where UnsafeTally = " &
HoldUnsafeTally
Set ClearUnsRS = dbobject.OpenRecordset(strquery)
With ClearUnsRS
.MoveFirst
Do While Not .EOF
.Edit
.Fields("UnsafeTally").Value = Null
.Update
.MoveNext
Loop
End With
ClearUnsRS.Close

Now the main form is called frmDriveAudit and the subform
is called subfrmUnsafe. If I change the name of the form in the
code to the subform, I get an error telling me Access cannot find
the form. I'm not sure how to proceed and I hope that another
pair of eyes can see what I'm missing. Thanks.

 

[JimBurke via AccessMonster.com]

You didn't specify which line is giving you the error. Is UnsafeTally a
control on frmDriveAudit? If so, you need an '!' before the control name
instead of a '.':

HoldUnsafeTally = Forms!frmDriveAudit!UnsafeTally.Value

You don't need to open a recordset to do this - you can just exceute an
update query instead:

strquery = "UPDATE Unsafe " & _
"SET UnsafeTally = Null WHERE UnsafeTally = " & Forms!frmDriveAudit!
UnsafeTally
currentDB.Execute strquery

 

[Douglas J. Steele]

To refer to a control on a subform, you need to use

Forms![NameOfParentForm]![NameOfSubformControl].Form![NameOfControlOnSubform]

Note that NameOfSubformControl may be different than the name of the form
being used as a subform.

That being said, why are you opening a recordset? Far better would be

Dim dbObject As DAO.Database
Dim strQuery As String

HoldUnsafeTally = Forms!frmDriveAudit.UnsafeTally.Value
Set dbObject = CurrentDb
strQuery = "UPDATE Unsafe " & _
"SET UnsafeTally = Null " & _
"WHERE UnsafeTally = " & Forms!frmDriveAudit.UnsafeTally

dbObject.Execute strQuery, dbFailOnError

 

[Opal]

Hi Doug,

Thank you for your help. I set it up as follows:

Dim dbObject As DAO.Database
Dim strQuery As String
Dim HoldUnsafeTally As Integer

HoldUnsafeTally = Forms!frmDriveAudit!subfrmUnsafe.Form!
UnsafeTally.Value
Set dbObject = CurrentDb
strQuery = "UPDATE Unsafe " & _
"SET UnsafeTally = Null " & _
"WHERE UnsafeTally = " & Forms!frmDriveAudit!subfrmUnsafe.Form!
UnsafeTally


dbObject.Execute strQuery, dbFailOnError

.... and I got an invalid use of Null error....

 

[Opal]

So, I changed:

Dim HoldUnsafeTally As Integer
to
Dim HoldUnsafeTally As Variant

and I get:

Syntax error (missing operator) in query expression 'UnsafeTally ='.

and the debugger points to:

dbObject.Execute strQuery, dbFailOnError

in the code....


hmmmmm.........