REPOST: Problems with Text Box

[James]

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

 

[Graham Mandeno]

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...

 

[James]

Hello

Yes the query runs and returns a percentage... When I use
the numeric values that the tables use...

Yes the secons column in the query is called
ReturnPercentage...

No its the secord row that the peercentage is returned...

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

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

.

 

[Graham Mandeno]

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

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

.

 

[James]

Ok for some reason I have the cboBoxes returning text
rather than numeric values... I have no idea why... I
think this is where the problem is... If I set the bound
column to 2 and add the GenderID field into the rowSource
of the combo box that relates to Gender for example it
comes up and says its the wrong data type..... How do I
get round this?

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...

Is this because I put it in a seperate module?

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?

Also whats the relevance of the 3 text boxes? which one do
I make visible so I know which ones not to show...

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

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



.

.

 

[James]

Hi I seem to have sorted this problem now but I would like
to know how I go about limiting the results to 2 decimal
places...

I have set the input mask to 99.99 and it stil refuses to
use it where by it would come up teh result like
2.0833333333333

Really I would need it to be able to go from 000.00 -
100.00

How would I achieve this?

Many Thanks

James

-----Original Message-----
Ok for some reason I have the cboBoxes returning text
rather than numeric values... I have no idea why... I
think this is where the problem is... If I set the bound
column to 2 and add the GenderID field into the rowSource
of the combo box that relates to Gender for example it
comes up and says its the wrong data type..... How do I
get round this?

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...

Is this because I put it in a seperate module?

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?

Also whats the relevance of the 3 text boxes? which one do
I make visible so I know which ones not to show...

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



.

.

.

 

[Graham Mandeno]

Hi James

Set the Format property if the textbox to "0.00".
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi I seem to have sorted this problem now but I would like
to know how I go about limiting the results to 2 decimal
places...

I have set the input mask to 99.99 and it stil refuses to
use it where by it would come up teh result like
2.0833333333333

Really I would need it to be able to go from 000.00 -
100.00

How would I achieve this?

Many Thanks

James

-----Original Message-----
Ok for some reason I have the cboBoxes returning text
rather than numeric values... I have no idea why... I
think this is where the problem is... If I set the bound
column to 2 and add the GenderID field into the rowSource
of the combo box that relates to Gender for example it
comes up and says its the wrong data type..... How do I
get round this?

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...

Is this because I put it in a seperate module?

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?

Also whats the relevance of the 3 text boxes? which one do
I make visible so I know which ones not to show...

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



.



.

.

 

[Graham Mandeno]

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



.

.

 

[James]

Many Thanks for all your assistance....

So my query I created I now do not need so I am ok to
delete that query so I can clear up the database a bit?

James

-----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



.



.

.

 

[Graham Mandeno]

That's correct - at any rate, it's not being used by any of the code I gave
you. You might like to play it safe and just rename it for a while to be
sure.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Many Thanks for all your assistance....

So my query I created I now do not need so I am ok to
delete that query so I can clear up the database a bit?

James

-----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



.



.

.