Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10954 > unrolled thread

Update form SQL based on client's Field filtering?

Back to article view | Back to comp.databases.ms-access

Contents

  Update form SQL based on client's Field filtering? Barry Wright <barry.edmund.wright@witstoronto.ca> - 2015-07-23 08:48 -0700
    Re: Update form SQL based on client's Field filtering? Mike P <mikewpayne@tiscali.co.uk> - 2015-07-23 11:01 -0700
    Re: Update form SQL based on client's Field filtering? Ulrich Möller <knobbi38@arcor.de> - 2015-07-23 22:30 +0200
      Re: Update form SQL based on client's Field filtering? Barry Wright <barry.edmund.wright@witstoronto.ca> - 2015-07-25 20:56 -0700
    Re: Update form SQL based on client's Field filtering? Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-07-24 06:26 -0700

#10954 — Update form SQL based on client's Field filtering?

I have a sub form on a form. The sub form displays all sales transactions for a store location. What the client wants to do is use the sub form's field filters to further narrow the records selected. Unfortunately using the field filters doesn't remove the unselected records from the sub form it just hids them. So how can I update the SQL statement of the sub form so that only the filtered records will be displayed? I've tried CurrentDb.QueryDefs("qryTemp3").SQL = Form_subfrmCustomer.RecordSource but it puts all the filtered and unfiltered records into qryTemp3.

Would really appreciate any insight into this issue, thanks.
Barry

[toc] | [next] | [standalone]

#10956

Hello Barry,

Please explain the difference between hiding the unselected records and displaying the filtered records. Aren't they the same set of records?

Mike P

23/7/15

[toc] | [prev] | [next] | [standalone]

#10958

Am 23.07.2015 um 17:48 schrieb Barry Wright:
> I have a sub form on a form. The sub form displays all sales transactions for a store location. What the client wants to do is use the sub form's field filters to further narrow the records selected. Unfortunately using the field filters doesn't remove the unselected records from the sub form it just hids them. So how can I update the SQL statement of the sub form so that only the filtered records will be displayed? I've tried CurrentDb.QueryDefs("qryTemp3").SQL = Form_subfrmCustomer.RecordSource but it puts all the filtered and unfiltered records into qryTemp3.
>
> Would really appreciate any insight into this issue, thanks.
> Barry
Hi Barry,

don't know, what you mean by hiding records, but you can use the forms 
applyfilter event. In the event handler read the me.filter property,  
use that value for modifying your forms recordsource or querydef and 
afterwards cancel the event. The applytype ist used for determining 
whether the filter should be set or unset.

Ulrich

[toc] | [prev] | [next] | [standalone]

#10963

Thanks Ulrich, Using the me.Filter property I was able to put that into a string and include it in the "Where" clause of the form.recordsource sql statement.

On Thursday, July 23, 2015 at 2:30:07 PM UTC-6, Ulrich Möller wrote:
> Am 23.07.2015 um 17:48 schrieb Barry Wright:
> > I have a sub form on a form. The sub form displays all sales transactions for a store location. What the client wants to do is use the sub form's field filters to further narrow the records selected. Unfortunately using the field filters doesn't remove the unselected records from the sub form it just hids them. So how can I update the SQL statement of the sub form so that only the filtered records will be displayed? I've tried CurrentDb.QueryDefs("qryTemp3").SQL = Form_subfrmCustomer.RecordSource but it puts all the filtered and unfiltered records into qryTemp3.
> >
> > Would really appreciate any insight into this issue, thanks.
> > Barry
> Hi Barry,
> 
> don't know, what you mean by hiding records, but you can use the forms 
> applyfilter event. In the event handler read the me.filter property,  
> use that value for modifying your forms recordsource or querydef and 
> afterwards cancel the event. The applytype ist used for determining 
> whether the filter should be set or unset.
> 
> Ulrich

[toc] | [prev] | [next] | [standalone]

#10960

On Thursday, July 23, 2015 at 11:48:27 AM UTC-4, Barry Wright wrote:
> I have a sub form on a form. The sub form displays all sales transactions for a store location. What the client wants to do is use the sub form's field filters to further narrow the records selected. Unfortunately using the field filters doesn't remove the unselected records from the sub form it just hids them. So how can I update the SQL statement of the sub form so that only the filtered records will be displayed? I've tried CurrentDb.QueryDefs("qryTemp3").SQL = Form_subfrmCustomer.RecordSource but it puts all the filtered and unfiltered records into qryTemp3.
> 
> Would really appreciate any insight into this issue, thanks.
> Barry

I filter subforms all the time.  In the main form's header, I have fields I filter on and a command button to clear the filter.  Example:  Active/Inactive records, a specific customer from a combobox, a date range.  In all the fields AfterUpdate event I call SetFilter (ClearFilter for the command button to clear)

I then build my filter.
Private Sub SetFilter
  Dim strF As String
  If Me.Active then
    strF = "Active = True And "
  Endif
  If Me.ComboCustomer > "" then
    strF = strF & "CustomerID = " & Me.ComboCustomer & " And "
  Endif
  If not isnull(Me.FromDate) then
    strF = strF & "DateAdded > #" & ME.FromDate & "# And "
  Endif
  if strF > "" then
     strF = Left(strF, len(strF) - 5)  'removes trailing And
     Me.SubformName.Form.Filter = strF
     Me.SubformName.Form.FilterOn = True
  Endif
End Sub

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web