Subform Recordsource problem

  • Thread starter Thread starter Chris James
  • Start date Start date
C

Chris James

My subform's recordsource is a set of records with a date field .
I want buttons on the main form to ' filter' the records , by date ,
displayed on the subform .
Simple ! .... I thought .
Just use code in the button click to change the SQL_WHERE to the subform's
recordsource ....

Can't get this to work .
And , why do subforms deny they have a recordsource ( in hte object
browser ) ?
But when I RClick for the design properties of my subform ... there it is !
.... Record Source ..

Confused .

Please help .

Chris
 
My subform's recordsource is a set of records with a date field .
I want buttons on the main form to ' filter' the records , by date ,
displayed on the subform .
Simple ! .... I thought .
Just use code in the button click to change the SQL_WHERE to the subform's
recordsource ....

Can't get this to work .
Click to expand...

Set the subform's filter, not that of the subform's recordsource:

'***
Me.SubformControlName.Form.Filter = "MyDate=" & Cdate(Me.txtFilter.Value)
Me.SubformControlName.Form.FilterOn = True
'***

Where "SubformControlName" is the name of the subform *control* on the main form
(not the name of the subform, although they may both share the same name),
"MyDate" is the name of the date field and "Me.txtFilter" is a control
containing the criteria you have entered as the filter.
And , why do subforms deny they have a recordsource ( in hte object
browser ) ?
But when I RClick for the design properties of my subform ... there it is !
... Record Source ..
Click to expand...

Because you need to reference the "Form" property of the "subform control" on
the main form.

Incidentally, if you meant to say that you wanted to "sort" the subform, rather
than "filter" it, the syntax to do so is:

'***
Me.SubformControlName.Form.OrderBy = "MyDate"
Me.SubformControlName.Form.OrderByOn = True
'***
 
Thanks Bruce .
Now all I need to know is why am I getting an " Error 2001 : you cancelled
the previous operation " at the line .FilterOn=True ?

Chris .


----- Original Message -----
From: "Bruce M. Thompson" <bthmpson@big_NOSPAM_foot.com>
Newsgroups: microsoft.public.access.forms
Sent: Tuesday, October 21, 2003 2:09 AM
Subject: Re: Subform Recordsource problem


|
| > My subform's recordsource is a set of records with a date field .
| > I want buttons on the main form to ' filter' the records , by date ,
| > displayed on the subform .
| > Simple ! .... I thought .
| > Just use code in the button click to change the SQL_WHERE to the
subform's
| > recordsource ....
| >
| > Can't get this to work .
|
| Set the subform's filter, not that of the subform's recordsource:
|
| '***
| Me.SubformControlName.Form.Filter = "MyDate=" & Cdate(Me.txtFilter.Value)
| Me.SubformControlName.Form.FilterOn = True
| '***
|
| Where "SubformControlName" is the name of the subform *control* on the
main form
| (not the name of the subform, although they may both share the same name),
| "MyDate" is the name of the date field and "Me.txtFilter" is a control
| containing the criteria you have entered as the filter.
|
| > And , why do subforms deny they have a recordsource ( in hte object
| > browser ) ?
| > But when I RClick for the design properties of my subform ... there it
is !
| > ... Record Source ..
|
| Because you need to reference the "Form" property of the "subform control"
on
| the main form.
|
| Incidentally, if you meant to say that you wanted to "sort" the subform,
rather
| than "filter" it, the syntax to do so is:
|
| '***
| Me.SubformControlName.Form.OrderBy = "MyDate"
| Me.SubformControlName.Form.OrderByOn = True
| '***
|
| --
| Bruce M. Thompson
| (e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)
| >> NO Email Please. Keep all communications
| within the newsgroups so that all might benefit.<<
|
|
 
Thanks Bruce .
Now all I need to know is why am I getting an " Error 2001 : you cancelled
the previous operation " at the line .FilterOn=True ?
Click to expand...

I assume that you mean Error 2501. I don't know what other code you might have
in place, so I couldn't tell you why you are getting that error and sometimes
the error messages generated by Access are misleading. The best thing might be
to just try trapping for that error in your errorhandling:

'****EXAMPLE START
Private Sub MyProcedure()
On Error Goto MyProcedure_Err

<Your code here>

MyProcedure_Exit:
Exit Sub

MyProcedure_Err:
If Err.Number = 2501 Then 'Operation cancelled
Resume Next
Else
MsgBox "Error: " & Err.Number & " - " & Err.Description
Resume MyProcedure_Exit

End Sub
'****EXAMPLE END
 
Thanks again ,
Actually it's error 2001 .
I tried substituting that into the handler .
No good , the form becomes useless afterwards : wont respond to filtering
etc .

There's barely any code in this simple 2 form application .
I'm stumped .

Chris .
 
Actually it's error 2001 .

Ah said:
I tried substituting that into the handler .
No good , the form becomes useless afterwards : wont respond to filtering
etc .

There's barely any code in this simple 2 form application .
I'm stumped .
Click to expand...

Try backing up and then compacting the file. If that doesn't work, you might
have some corruption in the VBA code. There is a "decompile" switch (available
in Access 97, 2000, 2002 and, probably, 2003) that can be used to help correct
this situation, but this is more of a "last resort" in that there is some risk
to the file as described in http://www.trigeminal.com/usenet/usenet004.asp?1033
(so ... work with a copy of the file). Further information is available at
http://www.databasecreations.com/DatabasePerformanceTips.pdf. The procedure I
use when implementing this is as follows:

1) BACK UP YOUR MDB FILE!
1) BACK UP YOUR MDB FILE! (I meant it the first time <g>)
2) Compact the MDB.
3) Implement the "/decompile" as described in the articles I referenced.
(Access 2000, and later, don't provide the confirmation dialog that
existed in Access 97, but the decompile will still take place.)
4) Open Access normally and compact the MDB again to clean up.
5) Compile and save.
6) Compact again before testing/using.
 
Aha !
Mistake was simple , and mine !
The first two examples below didn't work and I was getting the strange error
message , on the first one .
The third example works fine .
The error message threw me .


sf.Filter = "fldDate = '25/10/'03'"
sf.FilterOn = True

sf.Filter = "fldDate = #25/10/03#"
sf.FilterOn = True


sf.Filter = "fldDate = #25/10/2003#"
sf.FilterOn = True

Thanks Bruce .

Chris
 
Back
Top