Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-12-2018, 01:25 AM   #1
FuzMic
DataBase Tinker
 
FuzMic's Avatar
 
Join Date: Sep 2006
Location: Far East
Posts: 675
Thanks: 2
Thanked 3 Times in 3 Posts
FuzMic is on a distinguished road
2nd time around Filter / recordsource don't work

Hi guys

This is really puzzling for my little brain.

I have a FormM with than one sub forms. I use one of this subform eg SwForm to filter or change the recordsource on TargetSubForm eg TargetSubForm.Filter = "[fid] = 'x'" whenever SwForm goes currentEvent on a record ie arriving on another record X values changes to another.

The other way i tried was on currentEvt on records in SwForm will cause the TargetSubForm.recordsource to be assigned to something else"

I works ok the first time i use this FormM; filter works or recordsource changes as planned.

But if i leave this FormM and reenter 2nd time, both filter and recordsource don't work.

Really puzzling and spend hours still no clue WHY? Help!!

FuzMic is offline   Reply With Quote
Old 10-12-2018, 01:51 AM   #2
FuzMic
DataBase Tinker
 
FuzMic's Avatar
 
Join Date: Sep 2006
Location: Far East
Posts: 675
Thanks: 2
Thanked 3 Times in 3 Posts
FuzMic is on a distinguished road
Re: 2nd time around Filter / recordsource don't work

In Access 02, 03 & 07 it happens. If i get out of the Access and return then no problem, filter works first time ... but on reentering form, same problem as stated above.

Compact and repair mdb still problems persist.

I am going to duplicate this with a much simpler form to see what really bugs it.
FuzMic is offline   Reply With Quote
Old 10-12-2018, 02:08 AM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,334
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: 2nd time around Filter / recordsource don't work

your are following with filter assignment

TargetSubForm.FilterOn=True

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-12-2018, 02:16 AM   #4
FuzMic
DataBase Tinker
 
FuzMic's Avatar
 
Join Date: Sep 2006
Location: Far East
Posts: 675
Thanks: 2
Thanked 3 Times in 3 Posts
FuzMic is on a distinguished road
Re: 2nd time around Filter / recordsource don't work

Yes Sir

With Form_Target
.FilterOn = False
.Filter = "Left([Num],2) = '" & me.Field & "'"
.FilterOn = True
End With

If i use recordsource then "select * From Tbl where Left([Num],2) = '" & me.Field & "';"
FuzMic is offline   Reply With Quote
Old 10-12-2018, 02:36 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,334
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: 2nd time around Filter / recordsource don't work

field is a reserved word, using it can cause issues - this may be one of them
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-12-2018, 02:37 AM   #6
FuzMic
DataBase Tinker
 
FuzMic's Avatar
 
Join Date: Sep 2006
Location: Far East
Posts: 675
Thanks: 2
Thanked 3 Times in 3 Posts
FuzMic is on a distinguished road
Re: 2nd time around Filter / recordsource don't work

I did not use Field, it is for this thread only i use the reserved word field.
FuzMic is offline   Reply With Quote
Old 10-12-2018, 02:46 AM   #7
FuzMic
DataBase Tinker
 
FuzMic's Avatar
 
Join Date: Sep 2006
Location: Far East
Posts: 675
Thanks: 2
Thanked 3 Times in 3 Posts
FuzMic is on a distinguished road
Re: 2nd time around Filter / recordsource don't work

I remember that filter has the following observations
  • It will fire the current event
  • if AllowEdit is False, filter will not work
  • IF current Record is already outside the filter, it will not work.

Could the last observation be the culprit?

FuzMic is offline   Reply With Quote
Old 10-12-2018, 02:54 AM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,334
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: 2nd time around Filter / recordsource don't work

Quote:
it is for this thread only i use the reserved word field.
please don't. Either use the real name or at least avoid using reserved words - or if you really have to then also include a sentence saying this is what you have done.

As it is you've wasted time - reserved words cause all sorts of issues and is one of the first things that will be commented on and needs to be eliminated as a possible reason - see this link https://www.access-programmers.co.uk...light=reserved

Have you tried including a field in your subform recordsource e.g.
Left([Num],2) AS LinkFld

then using the subform link child/master properties, LinkFld as the child and 'Field' as the master?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-12-2018, 03:08 AM   #9
FuzMic
DataBase Tinker
 
FuzMic's Avatar
 
Join Date: Sep 2006
Location: Far East
Posts: 675
Thanks: 2
Thanked 3 Times in 3 Posts
FuzMic is on a distinguished road
Re: 2nd time around Filter / recordsource don't work

Point about reserved word taken.

Quote:
Originally Posted by CJ_London View Post
Have you tried including a field in your subform recordsource e.g.
Left([Num],2) AS LinkFld

then using the subform link child/master properties, LinkFld as the child and 'Field' as the master?
I have used linked tables but what you just said is new to me in relations to recordsource. Are you saying that this is a way from filter option.

I imagine it is a iink to a recordsource via a linkFld, hope you can elaborate a bit. Thanks in advance.
FuzMic is offline   Reply With Quote
Old 10-12-2018, 03:47 AM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,334
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: 2nd time around Filter / recordsource don't work

Quote:
Are you saying that this is a way from filter option.
yes

if the recordsource to your subform is say

SELECT * FROM myTable

change it to

SELECT Left([Num],2) as LinkFld, * FROM myTable
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-12-2018, 11:49 AM   #11
FuzMic
DataBase Tinker
 
FuzMic's Avatar
 
Join Date: Sep 2006
Location: Far East
Posts: 675
Thanks: 2
Thanked 3 Times in 3 Posts
FuzMic is on a distinguished road
Re: 2nd time around Filter / recordsource don't work

Got you, Thanks. Will try how it goes.
FuzMic is offline   Reply With Quote
Old 10-12-2018, 01:23 PM   #12
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,383
Thanks: 21
Thanked 898 Times in 882 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: 2nd time around Filter / recordsource don't work

Hmm, just passing by but I would try...

Code:
With Form_Target
     .Filter = ""
     .FilterOn = False
     .Filter = "Left([Num],2) = '" & me.Field & "'"
     .FilterOn = True
End With
Only because I'm wondering if it's holding on the filter value because you haven't told it to let it go.
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Old 10-12-2018, 03:08 PM   #13
FuzMic
DataBase Tinker
 
FuzMic's Avatar
 
Join Date: Sep 2006
Location: Far East
Posts: 675
Thanks: 2
Thanked 3 Times in 3 Posts
FuzMic is on a distinguished road
Re: 2nd time around Filter / recordsource don't work

Sorry Gina you whizzing by with blank string did not do the trick.

I was just wondering if this is the cause.

All subforms at in Main form and there is NO relationship among them eg master / child relationship. The event in the switching subform is OnCurrent wherein the filter codes are sent to the a neighbouring target subform.

Any comments.
FuzMic is offline   Reply With Quote
Old 10-12-2018, 03:33 PM   #14
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,383
Thanks: 21
Thanked 898 Times in 882 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: 2nd time around Filter / recordsource don't work

Hmm, well why not link them via code within the filter? And why aren't they linked? Why are they there?
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Old 10-12-2018, 04:50 PM   #15
FuzMic
DataBase Tinker
 
FuzMic's Avatar
 
Join Date: Sep 2006
Location: Far East
Posts: 675
Thanks: 2
Thanked 3 Times in 3 Posts
FuzMic is on a distinguished road
Re: 2nd time around Filter / recordsource don't work

G because i am not sure how or if it is really necessary. A little direction may help.

Two subforms are in the same mainform; my little evil plan is when user switch records in Switchingsubform, on arrival in a new Record, filter codes is sent using TargetForm..blah blah from SwithingSubForm.

Do remember it works only the first time unless user leaves Access mdb and come back again. That is the part i do this


Last edited by FuzMic; 10-12-2018 at 04:56 PM.
FuzMic is offline   Reply With Quote
Reply

Tags
filter , recordsource

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
filter recordsource for main form based on subform slimjen1 Forms 3 05-13-2012 06:39 PM
Filter or setting the RecordSource JKpeus Forms 5 08-23-2009 10:53 PM
Open form filter vs. recordsource IqbalS Forms 2 07-15-2008 11:45 AM
RecordSource= does not seem to work in Acess-2007 Shreenivas Potnis Visual Basic 1 05-18-2008 08:45 AM
Filter Vs. Recordsource rob_levy Forms 2 11-12-2007 08:48 AM




All times are GMT -8. The time now is 08:40 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World