Continuing when an Error occurs

[Jmbostock]

I've a problem.

The following is part of a procedure that takes information from a
access database.

If rst.Fields("One_Time_Fee") <> "" Then holdamount = amount
rst.Fields("One_Time_Fee")

If rst.Fields("Adjustment") <> "" Then holdamount = holdamount
rst.Fields("Adjustment")

The first one works fine. However, sometimes there are no adjustments
so the table doesn't create that particular field, and an error occur
saying correctly that there is not field by that name.

I'm trying to write something that will skip that line of code if ther
is an error like that.

I've tried using the "On Error Resume Next", but that doesn't seem t
work.

Anyone have any ideas??

Jame

 

[Bob Phillips]

James,

That should work. remember to reset after (On Error Goto 0).

If not try something like

On Error Resume Next
Set fld = rst.Fields("Adjustment")
On Error Goto 0
If Not fld Is Nothing Then
holdamount = holdamount + rst.Fields("Adjustment")
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jmbostock]

That was it. It was the "On Error Goto 0" not being in there that thre
a spanner in the works.

Thanks

Jame

 

[onedaywhen]

Do you want to solve the problem rather than simple deal with the
symptoms? If so you will have to redesign your table schema. When you
have a schema where the 'table doesn't create that particular field'
you will end up with crazy situations like, well, just like you
described. Re-design so that the Adjustment column is always present
in the results set, even if it returns Null. And do you *really* need
to create new tables on the fly?

Aside, you are relying on default properties which could lead to
unexpected results. For example:

rst.Fields("Adjustment")

normally returns a Field object, however when doing a text comparison
e.g.

rst.Fields("Adjustment") <> ""

VBA has to look in a data dictionary for the the Field object's
default property, being the Value property, fetch it and coerce it to
text if necessary, before doing the comparison. OK so explicitly using
the Value property is more verbose, hence fractionally less efficient
i.e.

If rst.Fields("Adjustment").Value <> "" Then

but if you want efficiency, try this:

If Len(rst!Adjustment) > 0 Then