2nd time around Filter / recordsource don't work (1 Viewer)

FuzMic

DataBase Tinker
Local time
Tomorrow, 00:12
Joined
Sep 13, 2006
Messages
719
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

DataBase Tinker
Local time
Tomorrow, 00:12
Joined
Sep 13, 2006
Messages
719
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:12
Joined
Feb 19, 2013
Messages
16,553
your are following with filter assignment

TargetSubForm.FilterOn=True
 

FuzMic

DataBase Tinker
Local time
Tomorrow, 00:12
Joined
Sep 13, 2006
Messages
719
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 & "';"
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:12
Joined
Feb 19, 2013
Messages
16,553
field is a reserved word, using it can cause issues - this may be one of them
 

FuzMic

DataBase Tinker
Local time
Tomorrow, 00:12
Joined
Sep 13, 2006
Messages
719
I did not use Field, it is for this thread only i use the reserved word field.
 

FuzMic

DataBase Tinker
Local time
Tomorrow, 00:12
Joined
Sep 13, 2006
Messages
719
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? :banghead:
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:12
Joined
Feb 19, 2013
Messages
16,553
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/forums/showthread.php?t=301681&highlight=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?
 

FuzMic

DataBase Tinker
Local time
Tomorrow, 00:12
Joined
Sep 13, 2006
Messages
719
Point about reserved word taken.

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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:12
Joined
Feb 19, 2013
Messages
16,553
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
 

FuzMic

DataBase Tinker
Local time
Tomorrow, 00:12
Joined
Sep 13, 2006
Messages
719
Got you, Thanks. Will try how it goes.
 

GinaWhipp

AWF VIP
Local time
Today, 12:12
Joined
Jun 21, 2011
Messages
5,901
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.
 

FuzMic

DataBase Tinker
Local time
Tomorrow, 00:12
Joined
Sep 13, 2006
Messages
719
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. :banghead::banghead::banghead:
 

GinaWhipp

AWF VIP
Local time
Today, 12:12
Joined
Jun 21, 2011
Messages
5,901
Hmm, well why not link them via code within the filter? And why aren't they linked? Why are they there?
 

FuzMic

DataBase Tinker
Local time
Tomorrow, 00:12
Joined
Sep 13, 2006
Messages
719
G because i am not sure how or if it is really necessary. A little direction may help. :D

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 :banghead: :banghead: :banghead: :banghead:
 
Last edited:

GinaWhipp

AWF VIP
Local time
Today, 12:12
Joined
Jun 21, 2011
Messages
5,901
I guess I'm not getting a clear picture because I'm thinking there has got to be an easier way to to do this. Just nothing coming to me as I just can't figure what you are doing and why. Screenshot?
 

FuzMic

DataBase Tinker
Local time
Tomorrow, 00:12
Joined
Sep 13, 2006
Messages
719
3rd time around i will try another way just simple buttons on a single form to filter and come back to this problem when i settle down my current urgent needs. Thanks G.
 
Last edited:

FuzMic

DataBase Tinker
Local time
Tomorrow, 00:12
Joined
Sep 13, 2006
Messages
719
To those who was following this, i think i know what is the cause of this phenomena.

Essentially if 2 subforms are unrelated, if we try to make changes to 2nd subform from an event in first subform it will not work. I experimented by setting focus on the 2nd form and then apply the filter from 1st form, it will work but then there is a 2nd instance of the 2nd subform as it is not the same as its first instance as a subform.

No best solution yet but will try on a completely different way.
 

GinaWhipp

AWF VIP
Local time
Today, 12:12
Joined
Jun 21, 2011
Messages
5,901
It should work if you put a hidden field on the main Form and use that to requery either subform. I have done that before though I do not recommend it because if the focus is lost the requery goes haywire.
 

Users who are viewing this thread

Top Bottom