Dsum() works sometimes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there!

Nikos helped me with this earlier, and it's mostly fixed now, but when I run
the following from a button's click event I get an error when the form, in
going to its on current event runs the next block of code. bytPer is a field
on my form

DoCmd.GoToRecord , , acNewRec
Dim intAns As Integer

intAns = MsgBox("Support for the current period?", vbYesNo + vbQuestion,
"Current Period")
Select Case intAns
Case vbYes
bytPer = PubPer
Case vbNo
bytPer = PubPer + 1
End Select

This next block is in the form's current event. The thing is, when I just
cycle from record to record, the code below works great! Wazzzzup with this?

txtPerTot = DSum("SuppTot", "qrySupport", "bytPer = " & Me.bytPer)

I'm trying to find the largest number in my recordset so that I can put that
total in a text box on my form.

Thanks, John
 
John,

I think it's starting to make more sense now. When you click the button
and the first line is executed, you go to a new record and the Current
event fires *before* the rest of the code in the button's click event is
executed.

Potential Problems:

At the time the Current event fires, Me.bytPer most likely doesn't have
a value yet (it's Null, unless there's a default value) and the DSum
returns a zero value (or another value if there are records returned by
the query where bytPer is null!).

Then the Click code execution resumes from the msgbox line. Does PubPer
have a value at that time? Probably not, unless (a) it's a public
variable, or (b) it is a control on the form with a default value or
bound to a table field with a default value. If it's a local variable it
hasn't been assigned a value yet, if it's a control on the form (without
a default value) no value has been entered yet.

The bottom line is, do you want the Current event (or part of it) to be
executed on new records? If you want to not execute part of the code, then:

If Not Me.NewRecord Then
'code to execute conditionally
End If

If you don't want any of the code to execute, then simply add this as
the first line:

If Me.NewRecord Then Exit Sub

HTH,
Nikos
 
Hi Nikos!

Thanks again! Some really great ideas. I'll have a go at the tomorrow.
I'll let you know when I get it working.

Thanks again,

John
 
Back
Top