recordsetclone question

[raven39]

I have a form whose recordsource is based on a query, which has 2 sets of criteria ( date range and person). In the footer of this continous form i have added a total text box with a running sum and a textbox of the recordcount (txtcount). i want to get a percentage in another textbox (txtpercent) based on txtcount and only the records with the word "booked" in qactive field of form.


Here is the current code i have:


Private Sub Form_GotFocus()

Dim db As DAO.Database
Dim rcdcnt As Integer
Dim rst As DAO.Recordset


Set db = CurrentDb
Set rst = Me.RecordsetClone


rcdcnt = 0

While Not rst.EOF

If Me.qactive = "booked" Then

rcdcnt = (rcdcnt + 1)

End If

rst.MoveNext

Wend

Me.txtpercent.Value = ([txtcount] / [rcdcnt])


End Sub


It compiles but doesnt give me a value in txtpercent.

What am i doing wrong here? any suggests are most welcome.

Raven

 

[PHV]

Perhaps this ?
rcdcnt = 0
txtcount = 0
rst.MoveFirst
While Not rst.EOF
txtcount = txtcount + 1
If rst!qactive = "booked" Then
rcdcnt = (rcdcnt + 1)
End If
rst.MoveNext
Wend
Me!txtpercent.Value = txtcount / rcdcnt

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[raven39]

Phv,

Thanks for your reply. I tried this and it didnt work either. I am wondering if i have the code in the correct place? or maybe a sql statement instead of a while statement?

Thanks.
Raven

 

[raven39]

phv,

I also have both textboxes ( txtcount and txtpercent ) formatted as general number could that be the problem?

thanks
raven

 

[PHV]

I'd try this:
rcdcnt = 0
Me!txtcount = 0
rst.MoveFirst
While Not rst.EOF
Me!txtcount = Me!txtcount + 1
If rst!qactive = "booked" Then
rcdcnt = rcdcnt + 1
End If
rst.MoveNext
Wend
Me!txtpercent = Me!txtcount / rcdcnt

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

Raven, why not have txtPercent, as a calculated control?

ControlSource = Val([txtCount] / DCount("pkID","tblName","qactive = 'booked'"))

 

[raven39]

zion7

Thank you for your reply. I get a #Name? error in the textbox.

this is the code i put in:

= Val([txtCount] / DCount("pkID","form!frmsalesman","qactive = 'booked'"))

what is pkid? i am assuming it is the primary keyID. I am not familar with dcount, so any information would be most welcome.

phv,

Thanks for your reply.

That gave me an error, then i changed the code without txtcount in it and received a 0.00%.

TThanks
Raven

 

[Zion7]

Just for the record, pkID is your primary key, but in this case, it can be any field, in your table.
BUT, always safer to use a field that has a value.
put in the ACTUAL name of YOUR primary Key, belonging to the table, the form is based on.
secondly, the Second argument, must be the name of the TABLE, not the form....

= Val([txtCount] / DCount("YOURpkID","tblSalesman??","qactive = 'booked'"))

 

[raven39]

Alright after messing with this for hours, i am finally able to get this far. One Access does NOT like division, and two i am math challenged.

i have this code:

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rcdcnt As Integer
Dim rst As DAO.Recordset


Set db = CurrentDb()
Set rst = Me.RecordsetClone


rcdcnt = 0

rst.MoveFirst
While Not rst.EOF

If rst![qactive] = "booked" Then
rcdcnt = rcdcnt + 1
End If
rst.MoveNext
Wend


Me!txtpercent = rcdcnt / txtcount
end sub

and it gives me a runtime error that says division by zero. and when i run the debugger it shows that it has correctly divided the numbers and the answer is correct ( .199288....) I have the txtpercent textbox formatted to percent, 4 decimals. I am at a loss as to how to fix this

Thanks
Raven

 

[Zion7]

I know you're not going to like this suggestion but, no need for a recordset...


Private Sub Form_Open(Cancel As Integer)


Dim rcdcnt As Integer

rcdcnt = DCount("YOURpkID","tblSalesman??","qactive = 'booked'")


Me!txtpercent = rcdcnt / txtcount

end sub

...I always get confused with percentages also, is it...
Me!txtpercent = rcdcnt \ txtcount Or

Me!txtpercent = txtcount / rcdcnt

Maybe try Nz(txtCount,1)???



Maybe try the Form_Current Event?

 

[TheAceMan1]

How are ya raven39 . . . . .

Going thru this thread I notice your code resides in either the forms [blue]OnOpen[/blue] or [blue]GotFocus[/blue] event. For all the code prescribed ([purple]that should've worked[/purple]) I suspect you may have a [purple]loading problem[/purple]. Thats is, [blue]data isn't fully loaded[/blue] even though your parsing thru the recordset. You get the right [blue] rcdcnt[/blue] when you break in the code (you allow loading to complete), but not in realtime (your phantom zero! . . . indicative of rst not fully loaded). This loading problem increases with increasing recordcount (loading time).

To be sure try the following:
[ol][li]Delete/remove the code where ever you have it.[/li]
[li]In the [blue]OnLoad[/blue] event of the form, copy/paste the following code:

[blue]    Dim db As DAO.Database, rst As DAO.Recordset, RcdCnt As Integer
    
    Set db = CurrentDb
    Set rst = Me.RecordsetClone
    
    [purple]rst.MoveLast
    DoEvents[/purple] [green]'Allow rst to fully load[/green]
    rst.MoveFirst
    
    While Not rst.EOF
      If Me!qActive = "booked" Then RcdCnt = (RcdCnt + 1)
      rst.MoveNext
    Wend
        
   If RcdCnt Then
      Me!txtPercent = Me!txtCount / RcdCnt
   Else
      MsgBox "RcdCnt = 0 . . . No Can Do!"
   End If[/blue]

[/li][/ol]
[purple]Thats it . . . give it a whirl & let me know . . .[/purple]

See Ya! . . . . . .