Display query results in a textbox
I have a working Access query that returns a single value.
It will use the values selected from 3 comboboxes for its criteria
I need to have the value displayed in a textbox on a form.
I want the the query to run and return a result after I make a selection from the third combobox.
I tried using the following code under the After Update event of the third combobox:
DoCmd.Requery "MyTextBox"
I tried putting the following code as the Control Source of MyTextBox:
=DLookUp("QueryResult","My
QueryResult is the field that displays the result of the query named MyQuery. What am I doing wrong? Also, is there a method other then DLookUp? Because in the past, whenever I get a DLookUp to work, it seems to process slowly.
It will use the values selected from 3 comboboxes for its criteria
I need to have the value displayed in a textbox on a form.
I want the the query to run and return a result after I make a selection from the third combobox.
I tried using the following code under the After Update event of the third combobox:
DoCmd.Requery "MyTextBox"
I tried putting the following code as the Control Source of MyTextBox:
=DLookUp("QueryResult","My
QueryResult is the field that displays the result of the query named MyQuery. What am I doing wrong? Also, is there a method other then DLookUp? Because in the past, whenever I get a DLookUp to work, it seems to process slowly.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It looks like harfang has already covered the "Criteria" part of my post...
harfang, is obviously (as always) correct.
Dlookup is "Slower" than a query, but it is a simple way to get what you want.
Dlookup (for lack of a simpler explanation) actual creates a virtual query with the arguments you provide.
So:
Dlookup("YourField", "YourTable","YourID=" & YourID)
Translates roughly into:
SELECT YourField
FROM YourTable
WHERE YourID=YourID
Thus, in the same way that Driving is "Slower" than taking a train...
...Yes, Dlookup is slower than a query.
But to say that it is slow in general is one sided, because there really is no other alternative in some cases...
For example, if your processor was faster and you had more memory, ...etc, it might be "faster"
;-)
JeffCoachman
Dlookup is "Slower" than a query, but it is a simple way to get what you want.
Dlookup (for lack of a simpler explanation) actual creates a virtual query with the arguments you provide.
So:
Dlookup("YourField", "YourTable","YourID=" & YourID)
Translates roughly into:
SELECT YourField
FROM YourTable
WHERE YourID=YourID
Thus, in the same way that Driving is "Slower" than taking a train...
...Yes, Dlookup is slower than a query.
But to say that it is slow in general is one sided, because there really is no other alternative in some cases...
For example, if your processor was faster and you had more memory, ...etc, it might be "faster"
;-)
JeffCoachman
ASKER
boag2000,
To answer your question, nothing happens when I try to get the query running through the form. When I click on the query directly from the Objects window, it prompts me for the 3 criteria that would otherwise be grabbed from the comboboxes on the form. After I enter the criteria, the query runs and returns the expected value. However, when I'm in the form and I select a value from Combobox1, Combobox2 and Combobox 3, nothing happens. The way it should be working is after I select a value from Combobox3 (presuming I already selected values from Combobox1 and Combobox2 so that they are already displaying the selected values on the screen), the query should run and display its result in MyTextBox on the form. I'll post the query in a few minutes.
To answer your question, nothing happens when I try to get the query running through the form. When I click on the query directly from the Objects window, it prompts me for the 3 criteria that would otherwise be grabbed from the comboboxes on the form. After I enter the criteria, the query runs and returns the expected value. However, when I'm in the form and I select a value from Combobox1, Combobox2 and Combobox 3, nothing happens. The way it should be working is after I select a value from Combobox3 (presuming I already selected values from Combobox1 and Combobox2 so that they are already displaying the selected values on the screen), the query should run and display its result in MyTextBox on the form. I'll post the query in a few minutes.
OK, then try the Expert's suggestions in order, and report back on each
mbizip: https://www.experts-exchange.com/questions/27712424/Display-query-results-in-a-textbox.html?anchorAnswerId=37954178#a37954178
LSMConsulting: https://www.experts-exchange.com/questions/27712424/Display-query-results-in-a-textbox.html?anchorAnswerId=37954279#a37954279
harfang: https://www.experts-exchange.com/questions/27712424/Display-query-results-in-a-textbox.html?anchorAnswerId=37954326#a37954326
boag2000: https://www.experts-exchange.com/questions/27712424/Display-query-results-in-a-textbox.html?anchorAnswerId=37954361#a37954361
mbizip: https://www.experts-exchange.com/questions/27712424/Display-query-results-in-a-textbox.html?anchorAnswerId=37954178#a37954178
LSMConsulting: https://www.experts-exchange.com/questions/27712424/Display-query-results-in-a-textbox.html?anchorAnswerId=37954279#a37954279
harfang: https://www.experts-exchange.com/questions/27712424/Display-query-results-in-a-textbox.html?anchorAnswerId=37954326#a37954326
boag2000: https://www.experts-exchange.com/questions/27712424/Display-query-results-in-a-textbox.html?anchorAnswerId=37954361#a37954361
ASKER
I attempted mbizup's approach. I'm getting: Run-time error '3601' . Too few parameters. Expected 3. The following line is getting highlighted in yellow:
Set rs = CurrentDb.OpenRecordset(st
This is what my query looks like in a module I created:
Function GetResult()
Dim strSQL As String
Dim rs As Dao.Recordset
strSQL = "SELECT sum(AmountField) AS MyBalance FROM MyTable " & _
"WHERE (MyTable.Field1=Forms!MyFo
"And (mid(MyTable.Field2,1,1)=m
"And (MyTable.Field3=Forms!MyFo
Set rs = CurrentDb.OpenRecordset(st
If rs.RecordCount > 0 Then GetResult = rs!SubOrgBalance
rs.Close
Set rs = Nothing
End Function
Set rs = CurrentDb.OpenRecordset(st
This is what my query looks like in a module I created:
Function GetResult()
Dim strSQL As String
Dim rs As Dao.Recordset
strSQL = "SELECT sum(AmountField) AS MyBalance FROM MyTable " & _
"WHERE (MyTable.Field1=Forms!MyFo
"And (mid(MyTable.Field2,1,1)=m
"And (MyTable.Field3=Forms!MyFo
Set rs = CurrentDb.OpenRecordset(st
If rs.RecordCount > 0 Then GetResult = rs!SubOrgBalance
rs.Close
Set rs = Nothing
End Function
"Too Few Parameters" means you have something misspelled. If that is the actual code you're using, then unless you have a Table named "MyTable" which has fields named "Field1", "Field2" and so forth, that SQL won't work.
In this case, the missing parameters are the expressions like “Forms!MyForm.combo1”. The Forms collection is an Access object, so only queries run from the interface can use them. When opening a recordset from code, you cannot use that syntax. Instead, try:
This will not be faster than the corresponding DSum() expression, however.
(°v°)
strSQL _
= " SELECT Sum(AmountField) AS MyBalance FROM MyTable" _
& " WHERE Field1='" & Forms!MyForm.Combo1 & "'" _
& " AND Field2 Like '" & Left(Forms!MyForm.Combo2, 1) & "*'" _
& " AND Field3='" & Forms!MyForm.Combo3 & "'"This will not be faster than the corresponding DSum() expression, however.
(°v°)
Under some circumstances, it is possible to achieve a faster performance with a custom function using the Seek method on an existing index of the table being queried. But it's rather technical. But, all things considered, DLookup is as fast as a generic versatile query building function gets.
Believe me, I tried!
(^v°)