Filtering subform records (1 Viewer)

T

TobyMace

Registered User.

Local time

Today, 00:09

Joined

Apr 13, 2018

Messages

65

• Oct 15, 2018

• #1

Hi All,

I am looking to filter my subform records. Maybe.

I have two fields:
RER Number
Serial Number

My subform shows the RER Number of all the records in which that Serial Number has been entered before. However it shows the current parent RER Number also. I tried applying a filter to the subform:

Code:Copy to clipboard

Me.Filter = Me!RER_Number <> Me.Parent!RER

But that then prevents any records being shown in the subform.

Any help is appreciated!

 

RuralGuy

AWF VIP

Local time

Yesterday, 17:09

Joined

Jul 2, 2005

Messages

13,826

• Oct 15, 2018

• #2

Why should the current parent RER Number NOT be shown?

 

T

TobyMace

Registered User.

Local time

Today, 00:09

Joined

Apr 13, 2018

Messages

65

• Oct 15, 2018

• #3

Well that information is already available if you see what I mean?
Preferably I wouldn't like to duplicate information being shown.
If a user sees the same RER number on the parent from and sub form they may think there is a difference between the records. I'm trying to make it less confusing and more user friendly is all.

 

RuralGuy

AWF VIP

Local time

Yesterday, 17:09

Joined

Jul 2, 2005

Messages

13,826

• Oct 15, 2018

• #4

There has to be an answer but I need more coffee right now to get the brain to wake up.

 

T

TobyMace

Registered User.

Local time

Today, 00:09

Joined

Apr 13, 2018

Messages

65

• Oct 15, 2018

• #5

I know the feeling well! I look forward to your reply!

 

RuralGuy

AWF VIP

Local time

Yesterday, 17:09

Joined

Jul 2, 2005

Messages

13,826

• Oct 15, 2018

• #6

What is the record source of the SubForm?

 

T

TobyMace

Registered User.

Local time

Today, 00:09

Joined

Apr 13, 2018

Messages

65

• Oct 15, 2018

• #7

The subform record source is Query1.

Query1 pulls values from TableA.

Parent form record source is TableA.

 

RuralGuy

AWF VIP

Local time

Yesterday, 17:09

Joined

Jul 2, 2005

Messages

13,826

• Oct 15, 2018

• #8

Are you using the LinkFields to filter?

 

T

TobyMace

Registered User.

Local time

Today, 00:09

Joined

Apr 13, 2018

Messages

65

• Oct 15, 2018

• #9

On Serial Number yes.

 

RuralGuy

AWF VIP

Local time

Yesterday, 17:09

Joined

Jul 2, 2005

Messages

13,826

• Oct 15, 2018

• #10

How about if you modify the SubForm query and use the Forms collection so the recordset does not pull records with RER equal to Forms.YourMainForm.RERcontrol?

 

T

TobyMace

Registered User.

Local time

Today, 00:09

Joined

Apr 13, 2018

Messages

65

• Oct 15, 2018

• #11

Hi RuralGuy,

I'll have to try this tomorrow. 1000 things just hit my desk!

Many thanks!

 

T

TobyMace

Registered User.

Local time

Today, 00:09

Joined

Apr 13, 2018

Messages

65

• Oct 24, 2018

• #12

Hi again,

Apologies for the delay!

I used:

Code:Copy to clipboard

Dim rervalue As String
rervalue = [RER]

Me.PRQ.Form.Filter = "[Previous_Returns_Q].[RER]<>'" & rervalue & "'"
Me.PRQ.Form.FilterOn = True

And it seems to be working now!
I want the form to be in datasheet view and the subform results are shown as a subdatasheet. (Done that!)
But the value in the subdatasheet will only update when I click in there. Is there anyway to have in constantly refreshing the subdatasheet value?

Thanks.

 

RuralGuy

AWF VIP

Local time

Yesterday, 17:09

Joined

Jul 2, 2005

Messages

13,826

• Oct 28, 2018

• #13

Not really sure. Sorry.