?
Solved

How do I set a text box control source to a query result?

Posted on 2011-03-14
18
Medium Priority
?
335 Views
Last Modified: 2012-05-11
Hi. I made a database of consultants, their contracts and amendments to their contracts, to be brief.

To determine the number of amendments for each contract, i created a query. The query runs successfully, but I don't know how to put the data into my contracts form
0
Comment
Question by:melodymurray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
18 Comments
 
LVL 75
ID: 35131637
Well ... you can make the query the RecordSource of your Form ... then add text boxes to get the query fields you need ....

mx
Capture1.gif
0
 

Author Comment

by:melodymurray
ID: 35131688
Well, I have a bunch of other bound controls in the form that are using a table as the Record Source. Will they change if I change the record source to the query. (They're not in the query. I thought about creating one for this, but I had so many joins that I couldn't run it.
0
 
LVL 75
ID: 35131784
I see ... instead then, you can use the DLookup() function to get a single value from the query - assuming the query only returns one record (other wise you will need to add a criteria clause):

Use an expression like this for the Control Source property of a text box:

=DLookup("[YourFieldName]", "YourQueryName", "<Optional Criteria if necessary>")

Include the equals sign ...

mx
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:melodymurray
ID: 35131856
Whenever I try that, (with no optional criteria: =DLookup("[YourFieldName]", "YourQueryName",), I just get the first value in each record.

Am I doing something wrong?
0
 
LVL 75
ID: 35131908
"in each record.'
If your query returns more than on record, then you would need the WHERE clause in the DLookup()

What exactly does this query return ?

mx
0
 

Author Comment

by:melodymurray
ID: 35131951
Sorry. So the query counts how many amendments are assigned to each contractID.

I have a form that displays the individual contractID record with a text box for Number of Amendments. I'm trying to get the "Number of Amendments" from the query I ran.

When I set the Amendments control source to DLookUp...:
=DLookup("[NumAMendments]", "qryNumAmendments")

I get "1" in each record. The form only shows one record at a time, but when I move between them, they all have the same value.
0
 
LVL 75
ID: 35132001
"the "Number of Amendments""

Ok ... then lets use DCount

=DCount ("*", "qryNumAmendments")

That will return just the Count.  You can also add optional Criteria is necessary.

mx
0
 

Author Comment

by:melodymurray
ID: 35132068
That just gives a sum of all the amendments that exist in the database.  I've been looking for this answer for 2 days now. I didn't think it'd be this difficult.
0
 
LVL 75
ID: 35132143
Sorry ... then how about this:

=DCount ("[NumAMendments]", "qryNumAmendments")

mx
0
 

Author Comment

by:melodymurray
ID: 35132188
Same thing. Sum of all the the amendments.
0
 
LVL 75
ID: 35132236
ok ... Clearly I'm not understanding which count you are after ... sorry.

Tell me again exactly which count you are after ?

And can you post the SQL for the query?

mx
0
 

Author Comment

by:melodymurray
ID: 35132374
sure.

I'm pretty much a beginner, so I've probably done something wrong. I  just have no idea what.

the SQL for the query:
SELECT tblContracts.ContractID, Count(tblContracts.Amendments) AS NumAmendments
FROM tblContracts LEFT JOIN tblAmendments ON tblContracts.ContractID = tblAmendments.ContractID
GROUP BY tblContracts.ContractID;

So there are contracts with unique IDs and then the amendments also have unique ID's but a corresponding ContractID so that I can tell which amendments belong to which contract.

Hopefully, the query above pulls all contract IDs, checks the contract ID against the amendments and counts the number of amendments with the same contractID.

That's what I meant it to do, anyway.  Might it have something to do with the fact that most of the records are incomplete? I'm trying to set up all the fields and relationships, so I have some data in the database but there are a lot of empty related fields.

Thanks.
0
 
LVL 75
ID: 35132461
ok.  So, you want to get NumAmendments ... but for only the Contact currently showing on the Form at any given moment ?

Is your Contacts form in Single Form view or Continuous or Datasheet View ?

mx
0
 

Author Comment

by:melodymurray
ID: 35132477
Single Form view
0
 

Author Comment

by:melodymurray
ID: 35132481
Yes, that's exactly what I want
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 35132554
OK ... assuming that ContractID is a field in the table that is connected to your form AND is Numeric, then is seems this should work:

=DLookup("[NumAMendments]", "qryNumAmendments", "[ContractID] = " & [ContractID])

or if your Form has a text box showing ContractID ... and lets say the Name of the text box is
txtContractID ... then this:

=DLookup("[NumAMendments]", "qryNumAmendments", "[ContractID] = " & [txtContractID])

mx
0
 

Author Closing Comment

by:melodymurray
ID: 35132652
Thanks soooo much! I was starting to go a little crazy.
0
 
LVL 75
ID: 35132678
Sorry for the confusion.

you are welcome ...

mx
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question