Using a Command Button to bring up specified criteria

[hazeleyre23]

Hi,

I have created a command button called 'Search'.

I want it to bring up the information based criteria in the Start Date and End Date text box, however I want the results to show in a sub form.

My start date is called txtstartdate and my end date is called txtenddate. My sub form is called Roster_Subform.



I am very new to Access so appreciate any help/tips.

Thank you

[ranman256]

the click event of the button will open a query:
docmd.openquery "qsMyDateQuery"

The query uses the date boxes.
select * from table where [date] between forms!frmRpt!txtStartDate and forms!frmRpt!txtEndDate

[hazeleyre23]

How do I then get this to populate in a sub form?
For now my sub form is called frmTest.

thanks

[davegri]

The way to alter the results in the subform is to change its recordsource. So, you need to create a query containing all the data you want to show in the subform and then assign that query as the recordsource for the subform, and you need to do it via VBA code. The below code will create a dynamic query to use as the recordsource. The query name is qtempQuery.
Here's how.
To create the query:
put this somewhere appropriate in the main form's code:
sub somesub
Dim strSQL as string
strSQL = "Select 'whatever you need'"
CALL fcnCustomizeSQL("qtempQuery", strSQL)
Me.YOURsubformcontrolname.requery
end sub
'Place below sub in a module
Function fcnCustomizeSQL(qName As String, strPassedSQL As String) As Boolean
Dim qthisQuery As DAO.QueryDef
'if the query has been deleted, create it
If DCount("Name", "MSysObjects", "[Name] = " & Chr$(39) & qName & Chr$(39)) = 0 Then
Set qthisQuery = CurrentDb.CreateQueryDef(qName, strPassedSQL)
Set qthisQuery = Nothing
Exit Function
End If
'else modify it
Set qthisQuery = CurrentDb.QueryDefs(qName)
qthisQuery.SQL = strPassedSQL
fcnCustomizeSQL_Exit:
Set qthisQuery = Nothing
End Function

Make recordsource for the subform 'qtempQuery'.

That should automate the entire process.