2nd time around Filter / recordsource don't work

FuzMic

FuzMic

DataBase Tinker
Local time
Today, 20:02
Joined
Sep 13, 2006
Messages
744
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!!
 
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.
 
your are following with filter assignment

TargetSubForm.FilterOn=True
 
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 & "';"
 
field is a reserved word, using it can cause issues - this may be one of them
 
I did not use Field, it is for this thread only i use the reserved word field.
 
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:
 
it is for this thread only i use the reserved word field.
Click to expand...
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?
 
Point about reserved word taken.

CJ_London said:
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?
Click to expand...

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.
 
Are you saying that this is a way from filter option.
Click to expand...
yes

if the recordsource to your subform is say

SELECT * FROM myTable

change it to

SELECT Left([Num],2) as LinkFld, * FROM myTable
 
Got you, Thanks. Will try how it goes.
 
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.
 
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:
 
Hmm, well why not link them via code within the filter? And why aren't they linked? Why are they there?
 
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:
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?
 
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:
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.
 
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.
 
You must log in or register to reply here.

Similar threads

M
Solved Form Filter and RecordSource Don't Query New Record
Replies
13
Views
853
Pat Hartman
P
C
Recordsource Doesn't work after adding data
Replies
2
Views
451
chuckcoleman
C
G
Filtering on Date fields problem
Replies
8
Views
722
azhar2006
A
V
Code that worked suddenly started throwing errors?
Replies
10
Views
606
Pat Hartman
P
P
Solved current record PDF issues
2
Replies
23
Views
671
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom