melodymurray
asked on
How do I set a text box control source to a query result?
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
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
ASKER
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.
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
Use an expression like this for the Control Source property of a text box:
=DLookup("[YourFieldName]"
Include the equals sign ...
mx
ASKER
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?
Am I doing something wrong?
"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
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
ASKER
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.
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]"
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.
"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
Ok ... then lets use DCount
=DCount ("*", "qryNumAmendments")
That will return just the Count. You can also add optional Criteria is necessary.
mx
ASKER
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.
Sorry ... then how about this:
=DCount ("[NumAMendments]", "qryNumAmendments")
mx
=DCount ("[NumAMendments]", "qryNumAmendments")
mx
ASKER
Same thing. Sum of all the the amendments.
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
Tell me again exactly which count you are after ?
And can you post the SQL for the query?
mx
ASKER
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.Amendme nts) 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.
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.Amendme
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.
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
Is your Contacts form in Single Form view or Continuous or Datasheet View ?
mx
ASKER
Single Form view
ASKER
Yes, that's exactly what I want
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks soooo much! I was starting to go a little crazy.
Sorry for the confusion.
you are welcome ...
mx
you are welcome ...
mx
mx
Capture1.gif