filtering a subform from a popup form

[tania20]

Hi, I have a main form which has a button that brings up a small pop up form to enter dates from and to. I then want the subform on the main to be filtered according to these dates. So far I have, where frmSummary is the sub.

Forms!frmMain!frmSummary.Form.Filter = "Date_game" & " Between " & Format(Me.txtDateFrom, conDateFormat) _
& " And " & Format(Me.txtDateTo, conDateFormat)
Forms!frmMain!frmSummary.Form.FilterOn = True

but am getting an error on frmSummary.Can anyone spot where I am going wrong? Thanks!

 

[Remou]

You need date delimiters:

Forms!frmMain!frmSummary.Form.Filter = "Date_game" & " Between #" & Format(Me.txtDateFrom, conDateFormat) _
               & "# And #" & Format(Me.txtDateTo, conDateFormat) & "#"

 

[tania20]

im getting an error saying i cant assign a value to this object

 

[Remou]

Can you use Filter By Form? What is the value of conDateFormat?

 

[tania20]

the value is

Const conDateFormat = "\#mm\/dd\/yyyy\#"...thats why my previous expr. doesnt include date delimiters.Cant seem to find any useful info on how to use filter by form for this application - any ideas to a good site?

 

[MajP]

I find working with form filters in code somewhat cumbersome since the whole thing needs to be set as a string with quotes. I find it a lot cleaner to work with queries. I would set the recordsource of the subform based on a query using values of the pop up form.
Several ways you could construct this. One way would be to use global variable. Example:
public dtmFromDate
public dtmToDate

Your pop up form could set these dates.
I would then have public functions to return the dates. Example

public function getFromDate() as date
getFromDate = dtmFromDate
end function

Now you can use these functions in the query for your sub form. When you close the pop up form set the recordsource or if it is already set then just requery the recordset.

Another way to construct it would be to hide the pop up form, but not close it. Save the to and from date on the pop up form in hidden text boxes. The query can then reference the field values in the criteria.

 

[MajP]

You may want to have functions to return the global date as a string. Makes it easier to use in certain sql statements

public function getSqlFromDate () as string
getSqlFromDate = "#" & format(dtmFromDate,"dd/mmm/yyyy") & "#"
end function

Now you should be able to pop this in a query.

 

[Remou]

Filter By Form, Filter By Selection and Apply Filter are available as buttons on the Form View menu or from the Records menu when you are viewing a form. The reason for my suggestion was applying a filter manually would allow you to check if the form allows filters. In addition, if you look at the design of the form after filtering, the Filter property will show the filter you have just applied.

Delimiters don't really work that way. This should be sufficient:
conDateFormat = "mm/dd/yyyy"
It would be a little better to use:
conDateFormat = "yyyy/mm/dd"

 

[tania20]

Hey all, thanks for everyones input! After stepping back i realised the problem was that the control had already been set to short date format whichI hadnt realised (I used a form from another prgram I wrote awhile ago)..so I just removed the conDateFormat to use this...

Me.Requery 'this is important on the popup form!
Forms!frmMain!sfCurrent.Form.Filter = "Date_game" & " Between #" & Me.txtDateFrom _
& "# And #" & Me.txtDateTo & "#"
Forms!frmMain!sfCurrent.Form.FilterOn = True

and it works perfectly.