-----Original Message-----
Hi James
Also I have done as you have asked and I have created a
module... but you said in teh forms module where do I put
that?? I just created a seperate module and when I click
on one of the cboBoxes it come back and says it cant find
it...
The form module is the class module which is bound to the form. You get to
it by clicking View>Code or the corresponding toolbar button, from form
design view.
Is this because I put it in a seperate module?
Yes - because the function is "Private" it cannot be referenced from outside
the form.
ok and you also said that (' chop off the last " and ") in
your post...
Should I delete them? or leave them in? if I have to
delete them where do I delete them from?
No, that was just a comment to explain what the next line of code does.
Also whats the relevance of the 3 text boxes? which one do
I make visible so I know which ones not to show...
The first textbox permanently holds the total count of all responses. The
second is filled by the function I gave you and holds the count of responses
matching your criteria in the combo boxes. The third displays a simple
calculation of the second divided by the first and multiplied by 100 (IOW
the percentage).
One big advantage in doing it this way rather than using your query is that
the filter is more dynamic. For example, you could leave cboGender blank
and get the result irrespective of gender.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Many Thanks
James
-----Original Message-----
Hi James
Yes the query runs and returns a percentage... When I
use
the numeric values that the tables use...
What do you mean by this last bit? It should just run
without question when
your form is open and values are selected in your combo
boxes.
Yes the secons column in the query is called
ReturnPercentage...
Fine.
No its the secord row that the peercentage is
returned...
Well it won't be returned by DFirst then. If your query
is as you posted
back on 21 April I can't see how it can return more than
one row.
I had to dig through a whole lot of old posts to piece
together exactly what
you are trying to do here. Even then, you seem to
contradict yourself
several times. PLEASE do not start a new thread to
continue asking the same
question ... or if you feel you really need to then
repost ALL of the
relevant information from the last thread.
Assuming your post of 21 April is correct, your
tblResponses table has the
following fields:
ResponseID (autonumber)
RespondantID (number) related to the PK of tblGender
QuestionID (number) related to the PK of tblQuestion
Answer (yes/no)
I suggest you put three textboxes on your form. If you
like you can hide
the first two. They will be:
txtTotalResponses
ControlSource: =DCount("*", "tblResponses")
txtReturnCount
ControlSource: (blank) [unbound]
txtReturnPercent
ControlSource: =txtReturnCount / txtTotalResponses * 100
Then add a function to your form module:
Private Function CalcReturnCount()
Dim sFilter as string
If Not IsNull(cboGender) then
sFilter = sFilter & "(RespondantID=" & cboGender & ")
and "
End If
If Not IsNull(cboQuestion) then
sFilter = sFilter & "(QuestionID=" & cboQuestion & ")
and "
End If
If Not IsNull(cboAnswer) then
sFilter = sFilter & "(Answer=" & cboAnswer & ") and "
End If
' chop off the last " and "
If len(sFilter) then sFilter = Left(sFilter, Len (sFilter)-
5)
txtReturnCount = DCount("*", "tblResponses", sFilter)
End Function
Now, for each of your three combo boxes, set the
AfterUpdate property to:
=CalcReturnCount()
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Many THanks
James
-----Original Message-----
Hi James
Here are some things to check:
1. Try running qryPercent from the database window
while
your form is open.
Does it run and return at least one row?
2. Is one of the columns (fields) in the query
named "ReturnPercentage"?
3. Does this column in the forst row contain the
expected value?
Report back...
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
message
Hi I posted this a while ago and I got some responses
but
unfortunatly I sill have not solved this problem.
I have 3 cboboxes and when the user selects something
from
them I want the text box to run a query (which I
have)
and
put in the results of the query into this text box.
The
query calculates the percentage of the values
selected
by
the user by what is in the database matching them...
So how do I overcome my little problem?
Here is my original post:
-------------------------------------------------- ---
---
---
Hello I was assisted in this forum by someone who
give
me
thr below code:
=DFirst("[ReturnPercentage]","qryPercent")
I get #Error in the text box when I show the form
view...
The query at the back of that qryPercent...
calculates
the
percentage of all the records in the table against
the
selections made in 3 combo boxes...
How can I fix this #error thing?
Any Help greatly appreciated...
James
.
.
.
