07-31-10, 14:02 #1Registered User
- Join Date
- Jul 2010
Unanswered: Access 2007 Query and Subform assistance..
I'm fairly new to Access and vba, the problem or logic i'm trying to solve is how to filter results in a subform based on a date selected in the main form?
At present I have one table "tblEvents" and in particular field "ProgDate" is the date field to be searched on.
I've created a query "qryEventsSimpleview" to return a selected result set..
SELECT tblEvents.ProgNo, tblEvents.ProgDetails AS [Programme Details], tblEvents.ProgDate AS [Programme Date], tblEvents.ProgStart AS [Programme Start Time], tblEvents.ProgBookedBy AS [Booked By], tblEvents.ProgComments AS [Programme Comments]
Also i've a form "frmchkbooking" with a calendar "bookingcal", ("Calendar Control 12.0" to select a date), a button "btn_chkdate" with a onclick event to paste date value into a text box.. and a subform "Child11" with source object "Query.qryEventsSimpleview"
There are no common fileds between the two forms so linking would not work (i think..)
At present the subform query is returing all the rows from the table, how would I now get the query to filter on the date selected once I click the button?
Last edited by Sarb_uk; 07-31-10 at 14:04. Reason: small edit
08-01-10, 02:15 #2Moderator
- Join Date
- Dec 2004
- Madison, WI
I might consider a popup form instead of embedding a subform into a main form which doesn't link on any fields to the main form (if you have a fairly large recordset, this would slow down the performance of the main form having it return all records in the subform.)
But you could create a 2nd subform (ie. Child12 although I'd have a better name). This subform could have a recordsource which has criteria in it where the date field is equal to the date field on the main form (ie. "Select * from myTableName where MyDateField = #" & Forms!MyMainFormName!DateTextFieldName & "#")
Then in the onClick event, after the code which populates the date field, you can change the sourceobject of the subform to the 2nd subform (ie. me.MySubFormName.SourceObject = "Child12")
Otherwise, you could also put this button (and the date text field) instead on the subform so that when the button is clicked, you can then manipulate the RecordSource of the subform itself (ie. me.RecordSource = "Select * from myTableName where MyDateField = #" & Forms!MyMainFormName!SubFormName!DateTextFieldName & "#") or ( me.RecordSource = "MyQueryWhichHasCriteriaToTheDateField")
Last edited by pkstormy; 08-01-10 at 02:21.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
08-01-10, 06:53 #3Registered User
- Join Date
- Jul 2010
PkStormy.. Thanks for coming back so quickly..
In terms of records in the data base, it's not too many, i'm only expecting there to be about 10,000 over 9 years.
I ended up adding the "progdate" field into the main form, changing it to a combo, and than amending the onclick of the button to update the progdate value with the calendar value. I made the "progdate" not visiable, so it's all hidden from the end user.
I then re-created the subform and this now filters..
I do have another issue, but i'll try fixing before i post for help..
And yes my naming convention is pants.. i need to fix that first i think...