Need to speed up some code

[Peter Bellamy]

I have the following in the Current Event of a form to determine some details on the form but it seem to slow down the form so much that there is a noticable delay in moving from one to the next.

records = (DCount("[goods_no]", "Goods", "[goods_returnno]=Form.[return_no]"))
Me.NoOfItems = records

test = 0
number = Nz(DSum("[goods_qnty]", "Mal Recs for Return")) ' DSum of query finding MAL records for this return
If number >= 1 Then GoTo formchoice

second:
test = 1
number1 = Nz(DSum("[goods_qnty]", "AV Recs for Return")) ' DSum of query finding AV records for this return

Can anyone suggest some code that would speed this up ?
Would writing the lines in SQL be faster? If so, I am not sure I know how to asign a variable to say a SELECT Count or SELECT Sum string.

Thanks

 

[RainLover]

This is not your full code.

Can you post a full version.

 

[Peter Bellamy]

The full code for the form On Current event is:

Private Sub Form_Current()
On Error GoTo Err_Current

Dim records As Integer
Dim fieldname As String
Dim test As String
Dim number1 As Integer
Dim number As Integer
number = 0
number1 = 0

records = (DCount("[goods_no]", "Goods", "[goods_returnno]=Form.[return_no]"))
Me.NoOfItems = records

test = 0
number = Nz(DSum("[goods_qnty]", "Mal Recs for Return")) ' DSum of query finding MAL records for this return
If number >= 1 Then GoTo formchoice

second:
test = 1
number1 = Nz(DSum("[goods_qnty]", "AV Recs for Return")) ' DSum of query finding AV records for this return

'formchoice:

If number >= 1 Then
Forms![Return record].Form![MalData].Visible = True
Me.MalData.Caption = number & " MAL PCB"
Else
If number1 >= 1 Then
Forms![Return record].Form![MalData].Visible = True
Me.MalData.Caption = number1 & " AV PCB"
Else
Forms![Return record].Form![MalData].Visible = False
Me.MalData.Caption = "MAL data"
End If
End If

If Len(Forms![Return record]![Goods Data].Form.[goods_aserialno]) > 1 Then ' skip if no serial no
Forms![Return record]![Goods Data].Form.BtnProduct.Visible = True
Else
Forms![Return record]![Goods Data].Form.BtnProduct.Visible = False
End If

getout:

Exit_Form_Current:
Exit Sub

Err_Current:
If Err = 94 And test = 0 Then
Resume Next
Else
If Err = 94 And test = 1 Then
Forms![Return record].Form![MalData].Visible = False
GoTo getout
Else
Debug.Print Err.number
MsgBox Err.Description
Resume Exit_Form_Current
End If
End If

End Sub

 

[gemma-the-husky]

the first line isnt right (not looked in detail at everything else)

records = (DCount("[goods_no]", "Goods", "[goods_returnno]=Form.[return_no]"))

the syntax should be more like

records = (DCount("[goods_no]", "Goods", "[goods_returnno] = " & Form.[return_no])

although I doubt the variable in the where bit is correct, either

------------
what exactly are you trying to do?

your code looks very convoluted.




while testing i put loads of msgbox es in my code, to see what is going on, and check my logic
I also use breakpoints, and step through code

I never got used to using varaible watch tracing/debug window - although I am sure they are both very useful

 

[Peter Bellamy]

This is a form/subform being opened.

'records' is a variable that will appear in one of the controls on the Form to show how many subform records there are.
'number' is the number of subform records of a certain type
'number1' is the number of subform records of another type. The types are mutually exclusive.
The first If loop uses number or number1 as text on a button which is then made visible.
The second If loop makes a button visible for an additional form, provided one of the subform controls has data in it.
The subform has no On Current code.

I took out all the Debug .Print and breakpoints before posting to make it easier to read !

Watching the code action it seems to spend time on the Dcount and DSum lines, not an exact way to test it I know. And, I have read, Domain expressions do not process as quickly as SQL I thought perhaps that would be a way to improve it.

 

[gemma-the-husky]

first - as I already said, I don't believe the first statement is correct - it must surely return 0 as the result

since there is no looping, i can only surmise that the dcount and dsum functions you are executing are very slow

BUT domain functions are not intrinsically slow - in fact I believe they are optimised to be as quick as possible. However, fast execution will depend on adequate indexes being avaialble to assist access run the query

i would be inclined to open the queries manually, and see which one(s) is/are opening slowly - and try and sort that/them. that is all your code appears to be doing.

 

[Peter Bellamy]

I see what you mean.
My only excuse is I wrote this code a long time ago when I knew less than I do now!
However it has been working and still does ??

Please see attachment.

 

[Peter Bellamy]

Attacment on this post

 

[RainLover]

You have

<< number = Nz(DSum("[goods_qnty]", "Mal Recs for Return")) ' DSum of query finding MAL records for this return
If number >= 1 Then GoTo formchoice >>

Then Formchoice is a Remark. Why

 

[Peter Bellamy]

It shouldn't be.
It inherited it when I deleted out some remarks before posting.

 

[Peter Bellamy]

dave.

first - as I already said, I don't believe the first statement is correct - it must surely return 0 as the result

since there is no looping, i can only surmise that the dcount and dsum functions you are executing are very slow

BUT domain functions are not intrinsically slow - in fact I believe they are optimised to be as quick as possible. However, fast execution will depend on adequate indexes being avaialble to assist access run the query

i would be inclined to open the queries manually, and see which one(s) is/are opening slowly - and try and sort that/them. that is all your code appears to be doing.

I came across this in Msofts knowledge base, I guess I must have used it when I originally wrote the code.

Specifying Numeric Criteria That Comes from a Field on a Form:
If you do not want to specify a particular value in the expression (as in the previous example), use a parameter in the criteria instead. The following examples demonstrate how to specify criteria from another field on the current form. You can try these on the Orders form in the sample database Northwind.mdb.

=DLookUp("[LastName]", "Employees", _
"[EmployeeID] = Form![EmployeeID]")


=DLookUp("[LastName]", "Employees", _
"[EmployeeID] = " & [EmployeeID])


=DLookUp("[LastName]", "Employees", _
"[EmployeeID] = " & Forms![Orders]![EmployeeID])


These three examples return the same results.

Cheers