Showing the result of a query in a field on a form

  • Thread starter Thread starter Nozza
  • Start date Start date
N

Nozza

I want to add an text box or label which will display the number of
sales an agent has made on the agent form

I have named the form frmAgent as it is based on the table tblAgent
which is used to maintain agent details.

I have written a query named qryAgentSalesCount which does a count of
the sales based on the AgentID.

The query works and returns the right results when I run it. If I view
a different agent on frmAgent, and re-run the query, the correct sales
count is displayed in the re-run query.

All I want to do is display this info in a text box on the form - and
that's where I am stuck.

I have tried adding an unbound text box but I don't know where to tell
it to read the value from a query! I have tried setting a default
value to be the text box, but no luck there.

Am I trying to do it the wrong way?

Many thanks

Noz
 
You might look into using DCount().

=DCount("[SaleID]", "Sales", "[AgentID = " & Forms!frmAgent!txtAgentID)
 
You might look into using DCount().

=DCount("[SaleID]", "Sales", "[AgentID = " & Forms!frmAgent!txtAgentID)
Click to expand...

I like the idea of this.

but I get

#Name?

And I have checked the field names and form name.

Where do I put this DCOUNT - in the Default Value, or where?

Thanks

Noz
 
You ought to make this the control source for your unbound control.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com


Nozza said:
You might look into using DCount().

=DCount("[SaleID]", "Sales", "[AgentID = " & Forms!frmAgent!txtAgentID)
Click to expand...

I like the idea of this.

but I get

#Name?

And I have checked the field names and form name.

Where do I put this DCOUNT - in the Default Value, or where?

Thanks

Noz
Click to expand...
 
You ought to make this the control source for your unbound control.
Click to expand...

:(

Still returns

#Name?

I've tried the original query, and that runs and returns the correct
result when an agent is displayed on the form. But I simply cant get
it to display the result in the text box.

And I have tried setting the control source to the query as well.

Hmmmmm

Thanks for your help Lynn

Noz
 
1. It is usually faster to use * in the count rather than a field name.

Interesting, Klatuu, I would expect the primary key field to query faster.
2. A Closing bracket is missing.
Click to expand...

Thanks for catching that.
3. If AgentID is a numeric field, the syntax is correct. If it is a text
field, it needs to be delimited as in the first example.
Click to expand...

Nozza, you can use mine or Klattu's depending on whether or not AgentID is a
numeric or text field. I assumed numeric. Just be sure to fix the missing
square bracket.
4. Although it violates good naming rules, I have found that using only
the
control name when the formula is in a Control Source is more reliable.
Click to expand...

Never tried that, but I suppose you may be right.
 
=DCount("*", "Sales", "[AgentID] = '" & txtAgentID & "'")
Click to expand...

Am still not getting what I want

Have tried various approaches using square brackets, putting in
missing brackets, etc. and either get

#Name?

or

Error

So, do I put it in the control source?

And how do I return the result of a query to a text box?

Many thanks

Noz
 
I can build a new form using the wizard.

I base the form on the query which returns the correct results.

The new form displays the correct number assuming the agent form is
open.

I copy the control from the new form into the agent form.

I get

#Name?

This is driving me nuts! Hours to solve this tiny problem

So, I'll rephrase the question.

I have a query which returns one result.

I have a form in which I edit agent records.

How can I display the result of the query on the agent form?

Thanks for your persistence here - I am appreciating the input :)

Noz
 
And how do I return the result of a query to a text box?

Many thanks
Click to expand...

The "Result" of a query could, in principle, consist of 255 fields and
millions upon millions of records. In short - that's the wrong
question!

What's the Query? What does it return?

=DCount("*", "Sales", "[AgentID] = '" & txtAgentID & "'")

will show the count of the number of records in the table named Sales
where the Text datatype field AgentID is equal to the value in the
form control named txtID. If AgentID is a Number field leave off the '
and the & "'" bits.

If you have some other query result that you want to display, please
explain.

John W. Vinson[MVP]
 
You might look into using DCount().

=DCount("[SaleID]", "Sales", "[AgentID = " & Forms!frmAgent!txtAgentID)
Click to expand...

Got there in the end.

=DCount("[SaleID]", "Sales", "AgentID =
[Forms]![frmAgent]![txtAgentID]")

I had to put square bracket around the names - even though there
aren't spaces etc in the name.

Never sure when you *need* the square brackets and bangs

Thanks

Noz
 
I'm glad you finally figured it out.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com


Nozza said:
You might look into using DCount().

=DCount("[SaleID]", "Sales", "[AgentID = " & Forms!frmAgent!txtAgentID)
Click to expand...

Got there in the end.

=DCount("[SaleID]", "Sales", "AgentID =
[Forms]![frmAgent]![txtAgentID]")

I had to put square bracket around the names - even though there
aren't spaces etc in the name.

Never sure when you *need* the square brackets and bangs

Thanks

Noz
Click to expand...
 
Back
Top