running query using command button

[jerry28ph]

Im confusing on how to get my query to be done and show the value on my subreport using command button. I need help about the procedure and the correct syntax on how to put my query inside the command button, when you click the button on your main form, it will run the query and result will come up on my sub report.

Please i need some help.
Thanks and more power to all.

 

[pbaldy]

You don't have to run the query. Base the report or subreport on it, and your button opens the report. Access will run the query itself to populate the report.

 

[jerry28ph]

hi pbaldy,

What should I need to do to show the value of my query into my main form? I created a query with =count(*) and the criteria needs to be enter in my main form. How can I get the value of my query and show it into my form? After I enter all the criteria in my main form, and using command button, i want to see the value of my query in my form. Is it possible?

Thanks for you reply.
Regards

You don't have to run the query. Base the report or subreport on it, and your button opens the report. Access will run the query itself to populate the report.

 

[pbaldy]

I don't know your situation. If you just want one value from this query in a report that is based on something else, try a textbox with a DLookup function that gets the value from the query.

 

[jerry28ph]

hi, to give you an idea and maybe you could give me more information.
I had several queries in which the criteria is not constant, I mean the user need to enter the criteria on the main form. I used subform/subreport to get the value of my query but i need to open the query first and data grid always pop up and you need to close that window in order to go back in the main form before you see the value. I'm looking for help on how to open my query without showing the data grid, I mean i can get the value of my query and show it in my main form.

Here's my code:

On Error GoTo Err_RnRfrshQry_Click
Dim stDocName As String

stDocName = "Query_Total_Talk_Time_Outgoing"
DoCmd.SetWarnings False

DoCmd.OpenQuery stDocName, acNormal, acReadOnly
[Query_Total_Talk_Time_Outgoing subform].Form.Requery

DoCmd.Close

DoCmd.SetWarnings True
Exit_RnRfrshQry_Click:
Exit Sub
Err_RnRfrshQry_Click:
MsgBox Err.Description
Resume Exit_RnRfrshQry_Click

So, when you run the main form and you will see the data grid of query showing up. I will try to use this dLookup if gonna work for me. I haven't try this before, and now i have to search for the correct syntax and procedure how to do it.

I hope you're not confusing.

Thanks,
Jer

I don't know your situation. If you just want one value from this query in a report that is based on something else, try a textbox with a DLookup function that gets the value from the query.

 

[pbaldy]

Well, if the report is based on the query, you don't have to open the query. Just open the report. Can you post the db?

 

[jerry28ph]

hi,

i attached a copy of my db: callLog and my main form: FrmLog
I also put some notes on my main form how to get started.

Thanks.

Well, if the report is based on the query, you don't have to open the query. Just open the report. Can you post the db?

 

[pbaldy]

What are you trying to accomplish by running the queries? What report are you trying to open?

 

[jerry28ph]

i want to run my query using command button and after i ran it, i want to put the value of my query in one of the text box on my main form. I want to get rid the command buttons on my main form (such as "All calls", "talk CCEC", "TotalCCEC" etc.) If possible to put SQL as String in VB editor and run it thru command button, and value will show on my main form using test box. To elaborate this, I want to put my query script in a command button and run it like a script. Is it possible?

What are you trying to accomplish by running the queries? What report are you trying to open?

 

[pbaldy]

I'm not sure how to put this any differently. You do NOT have to run the queries to get values from them. The only thing that's doing is creating the flicker you don't like. To get a single value from a query, you can use DLookup. If the query only returns one line, you can simply use:

=DLookup("FieldName", "QueryName")

If the query returns multiple lines, you may need a criteria:

http://www.mvps.org/access/general/gen0018.htm

 

[pbaldy]

I got a phone call so just posted that. I should have added that if you change the dates on the form and that changes the values returned by the query, you'll need to either requery the form or the specific control.

 

[jerry28ph]

All of my queries in my main form need criteria before i get the value of my query, shall I put the the Dlookup in my ControlSource inside "Expression Builder or do i need to create a button that will call the dlookup function after entering the criteria?

I got a phone call so just posted that. I should have added that if you change the dates on the form and that changes the values returned by the query, you'll need to either requery the form or the specific control.

 

[jerry28ph]

Ok here's the result: I put a textbox on my form and inside the CONTROLSOURCE in "Expression Builder" I typed

=DLookUp("TotalCCEC","Smdr Qry_Total_CCEC (PC)")

In order to get the count of TotalCCEC my criteria is this:

SQL:

SELECT Count(*) AS TotalCCEC
FROM TabLogs
WHERE (((TabLogs.Party1Name)=[forms]![frmLog]![combo17]) AND ((TabLogs.Dialled_number)="598") AND ((TabLogs.[Call duration])<>#12/30/1899#) AND ((TabLogs.[Call start]) Between [forms]![frmLog]![text295] And [forms]![frmLog]![text297]));

Party1Name is Combo17
Call Start is From Date and To Date

Both value of this criteria has to be enter in the mainform.

So, when I ran the form a small screen is asking to enter Party1Name before the mainform shows. When you enter the 2 criteria, a data grid that shows the value of my query will shows and the value of my Textbox w/c is equal to DLookup is 0.

Please advise.
Thanks

I'm not sure how to put this any differently. You do NOT have to run the queries to get values from them. The only thing that's doing is creating the flicker you don't like. To get a single value from a query, you can use DLookup. If the query only returns one line, you can simply use:

=DLookup("FieldName", "QueryName")

If the query returns multiple lines, you may need a criteria:

http://www.mvps.org/access/general/gen0018.htm