Filter not being applied

[andrew3254]

Hi,

I am trying to apply a filter to a form in a Access 2007 project in VBA.
The filter string is being generated correctly but it is not being applied to
the form.

Here is my code:

strFilter = "projNum = '" & myproj & "' AND period = " & mon
Me.Filter = strFilter
Me.Filter = True

Anyon ehave any ideas?

 

[strive4peace]

Hi Andrew,

set FilterON to true

Me.Filter = strFilter
Me.FilterOn = true

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[andrew3254]

No still doesnt work.

What I am trying to do is select all the records from my SQL Server back end
then use the form to display a specific record.

It just shows the first record.

 

[strive4peace]

Hi Andrew,

if you want to OPEN the form with a filter, then use this approach:

DoCmd.OpenForm "Formname", , , strFilter

"It just shows the first record"

is your form set up to display multiple records at one time? Are there
more records in the Recordset? What kind of criteria is being imposed
by the underlying Recordsource (ie: query or SQL statement)

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[andrew3254]

Sorry, i probably should have mentioned this before, it is on a subform and
has a SQL statement recordsource getting data from three different tables.

I originally did this through Access tables but I am in the process of
migrating to SQL Server 2005 and I dont think I can use the same criteria
(e.g. Forms!frmSub!period) as I did whilst using Access tables.

Due to the nature of the data I am using multiple sub forms to essentially
create a crosstab query style form so it will look like this

JAN FEB MAR.....
A 12 10 21
B 32 37 23
C 12 38 23
D 24 23 43

Hope this makes it clearer

 

[strive4peace]

Hi Andrew,

if the code is behind the main form and what you are trying to filter is
a subform, you need to do this:

'~~~~~~~~~~~~~~~~~~~~~
with me.subform_controlname.form
.Filter = strFilter
.FilterOn = true
end with
'~~~~~~~~~~~~~~~~~~~~~

WHERE
subform_controlname is the Name property of the subform control

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[andrew3254]

Sorry, Im not making this very clear am I.

The main form has a project number on it and the subform has the SQL Code to
select data for all projects over all the periods. when the form is opened I
want to see specific data according to the filter but the filter is not being
applied for some reason and shows me all records.

I am new to Access projects with SQL Server so I might be missing some
syntax or calling the wrong method.

 

[strive4peace]

Hi Andrew,

I would suggest you use Project Number in the LinkMasterFields and
LinkChildFields of the subform control.

then, it is just a matter of letting the user filter for the period (you
can use an unbound combo in the main form) -- and, if they do not choose
a period, then show all records.

The beauty of using the Link Fields is that you will not have to do
anything special when they change the main record. Read the
mainform/subform section here:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

Warm Regards,
Crystal

*
have an awesome day
*

 

[andrew3254]

That seems to have done the trick.

 

[strive4peace]

Hi Andrew,

wonderful!

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*