[dba-SQLServer] Continuous Forms and Filters

Thanks Gary.

Regards

David

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gary Miller
Sent: Friday, 25 April 2014 1:23 p.m.
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Continuous Forms and Filters

David,

Here is the code in the OnOpen event of the subform. The subform resides on
a tab page that cals the subform when the tab page is clicked -- not
specified from the parent or populated until then. Even though the SQL
references a 'qry' it is a mult-table view on the server that is just
retaining the original 'qry' prefix of the name of the original query from
the old Access backend.

Private Sub Form_Open(Cancel As Integer) Dim SQL As String Dim lngID As Long

' Open the forms recordset based on the parent form ID lngID =
Nz(Forms!frmPeople!PersonID) SQL = "SELECT * FROM qry_Quilts_andLocation" &
_
    " WHERE PersonID = " & lngID & " AND EventID = " & fnActiveShowID() & _
    " ORDER BY EventYear DESC, quilt_name;"
'Debug.Print SQL
Me.RecordSource = SQL

End Sub


Gary

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
Emerson
Sent: Thursday, April 24, 2014 4:38 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Continuous Forms and Filters

Gary,

Looks promising.  I am using a stored procedure.  Can you please give me an
example of how you make the recordset call to a SQL view - when I try to
replace my stored procedure name with a view name in my call it rejects it
and tells me it is because it is a view!!

David

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gary Miller
Sent: Friday, 25 April 2014 11:21 a.m.
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Continuous Forms and Filters

David,

I have a 2010 FE linked to a 2008 SQL BE living on the internet using ODBC.
The subforms are fed by dynamic recordsets that I populate on the selection
of the parent record and I seem to have the 'Text Filters' available on
right-click of the fields in it. I just tried a 'Contains' filter and it
worked fine. Caveat is I am not using a stored procedure as such -- just a
recordset call to a SQL view that I am filtering by Parent ID in the SQL
statement for the subform RecordSource.

Have you double-checked your AllowFilters property of the subform?

Gary Miller



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com