i make simple system to make calculation in the end of each month bout point that car salesman from customer bout our their service
i make cmd button to calculate where criteria (by salesman name and month) based on combo box then the result (calculation) return to box..
i make 2 form:-
1. to edit record
2. to calculate
i've no problem in form 1 but in form 2 it say my calculation overflow and when debug. i c my calculation value = null
i let u c my coding and i attack my db to u guy.. plz help me.. i need help ASAP
Private Sub cmd_calculate_Click()
Dim rsSurvey As DAO.Recordset
Dim strSurvey As String
Dim Counter, Aaa1, Aaa2, Aaa3, Aaa4, Aaa5, Aaa6, Aaa7, Bbb1, Bbb2, Bbb3, Bbb4, Bbb5, Bbb6, Bbb7, Bbb8, Bbb9, Ccc1, Ccc2, Ccc3, Ccc4, Ccc5, Ccc6, Ccc7, Ccc8, Ccc9, Ddd1 As Integer
Set rsSurvey = CurrentDb.OpenRecordset("q_Survey")
''' strSurvey = Forms!f_Survey.RecordsetClone.RecordCount
strSurvey = rsSurvey.RecordCount
Me!TextTest = strSurvey ' try to see record(i mean row) count
' but it only show 1 instead of 4
' i think i make wrong code
Do While Not rsSurvey.EOF
If rsSurvey.Fields("SalesmanName") = Me.SalesmanName2 Then
If rsSurvey.Fields("MonthRegister") = Me.Month Then
If rsSurvey.Fields("YearRegister") = Me.Year Then
This doesnt work the way you think.... All variables now are variant except Ddd1, which is an integer... Creative naming by the way
Instead you sould dim on a seperate line or add as integer before each comma.
have you ever though about using an array??
One and a half:
If you were using an array instad of type in all that 1,2,3, etc number you could do something with a loop instead... Reducing the number of lines needed drasticaly
Second:
try using Me. instead of Me!
Third:
Assuming Forms!f_Survey is your form and also using the save data as the query...
Me.recordsetclone.movelast
me.recordsetclone.recordcout
If not...
Add a rsSurvey.Movelast before the recordcount and a rsSurvey.movefirst after to fetch the right number of records and return to the start of the query. YES it is a pain...
Four:
If rsSurvey.Fields("SalesmanName") = Me.SalesmanName2 Then
If rsSurvey.Fields("MonthRegister") = Me.Month Then
If rsSurvey.Fields("YearRegister") = Me.Year Then
why not 'simply' use AND?
If rsSurvey.Fields("SalesmanName") = Me.SalesmanName2 and If rsSurvey.Fields("MonthRegister") = Me.Month and rsSurvey.Fields("YearRegister") = Me.Year Then
Five:
rsSurvey.MoveNext
Else
rsSurvey.MoveNext
End If
Else
rsSurvey.MoveNext
End If
Else
rsSurvey.MoveNext
End If
Loop
Note that on each Else you are doing a movenext... Why have the else and those seperate movenext???
End If
End If
End If
rsSurvey.MoveNext
Loop
Also I hop you indent your own code.... If not you should... And when posting that indended code on the forum use [ code ] and [/ code ] (without the spaces) to mark the start and stop of the code...
Code:
End If
End If
End If
rsSurvey.MoveNext
Loop
Indeted its much easier to read....
Six:
Why do the If to search for the sales man at all and not using the query to limit the search right there?
so instead of Set rsSurvey = CurrentDb.OpenRecordset("q_Survey")
try using something like
Set rsSurvey = CurrentDb.OpenRecordset("Select * from q_Survey where SalesmanName = '" & Me.SalesmanName2 & "'")
Expand this to include the others...
Six and a half:
in general you should not search on names ever... Sooner or later it will cause problems where people end up with the same name....
Seven, your 'real' question:
When deviding by Null or Zero you have a problem... cause you simply cannot
Since counter is a variant and not an integer... this no good either (see 1)
AND you should allways initialize your variables...
ccc1 = 0
counter = 0 etc...
sorry if it all is to much.... and to harsch.... just trying to help
