Use A Form To Run A Query?

[jesusoneez]

I'm trying to create a form for users to grab data from a sales table. The form will show text boxes for a user to enter a start and end date. They'll then click a "search" button which will show the results in a subform. Apart from the fact I have no idea how to do it. I have created the form with a couple of text boxes, and I have created a subform which shows all the sales data, but I don't know how to filter the data using the data in the text boxes by way of the "Search" button.

Hopefully someone can help! Eventually, as well as entering a start and end date, I want them to be able to enter an account number, and possibly some other details as well, but once I'm on the right road with the dates, I should be OK to do the rest myself.

Thanks,

Steve

 

[Brianwarnock]

The search button runs a query which has criteria which reference the textboxes on the form
eg
Between Forms!formname!textbox1name And Forms!formname!textbox2name if the names are correct Access will put the [] in for you.

Brian

 

[dopedealer]

Something i made in a hurry for you to show you the concept:
Acount numbers: 10001 - 10004
StartDate
12/12/2004
11/12/2004
10/12/2004
07/12/2004
EndDate
13/12/2004
15/12/2004
17/12/2004
19/12/2004
Check Attachment and use the above data to Search. Modify according to your needs.
Chris

 

[jesusoneez]

Great Stuff

Thanks guys...that's got me on the right foot. I now have a silly problem. The form itself works...I put in a start and end date, click my query button and the results appear in the subform below.

Unfortunately, the results also appear in the usual query results window...here's the code behind the button...

Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim stDocName As String

stDocName = "qrySystem_SalesQuerySubForm"
DoCmd.OpenQuery stDocName, , acReadOnly



Me!frmSystem_SalesQuerySubForm.Form.Requery
Me!frmSystem_SalesQuerySubForm.Form.Refresh

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
MsgBox Err.Description
Resume Exit_cmdRunQuery_Click

End Sub

Now obviously the DoCmd.OpenQuery shows the query results in the seperate window...I suppose my question is how do I run that query without it showing the query window, and just refreshing the subform?

Thanks,

Steve

 

[dopedealer]

Why are you opening the Query in the first place? Just set the criteria in the query for the fields Startdate and Enddate and on the button put this code:
Me.Refresh
Subform1.requery
Subfor2.requery

 

[jesusoneez]

I thought it had to open the query to run the query to then refresh the subform.

Obviously not

Thanks,

Steve

 

[dopedealer]

well it really depends what your wanting done, theres different ways of doing the same thing, if you get stuck just post your Database here.
Chris

 

[Scott Heslop]

I have a database with alot of data regarding maintenance jobs. I can create reports no bother from queries... but I want a more flexible way of doing the reports. I have a dropdown list with all the different buildings the maintenance team go to, so when I select that building from the drop down i want the SQL to run a query and produce the report? I was thinking i would have to be doing Cases, or IF statements depending on what JobID is selected. I have been trying for a while!

 

[Brianwarnock]

This thread is 7 years old, I have been retired 6 !

I would suggest creating a new thread rather than resurrecting an old one, however if I understand you correctly then all you need to do is reference the combo in the query criteria

Brian