We help IT Professionals succeed at work.

filtering a subform

Shawn
Shawn asked
on
I am trying to set a filter and cna't seem to get it right. Any ideas?

Dim str1 As String

str1 = "TargetLanguage  = " & Me!langue_cible & " And SourceLanguage = " & Me!langue_source
Forms![frmtrad_commandes2]![sbfrmTranslationPreselectTranslators].Form.Filter = str1
Forms![frmtrad_commandes2]![sbfrmTranslationPreselectTranslators].Form.FilterOn = True

Open in new window

Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
str1 = "TargetLanguage  = " & Me!langue_cible & " And SourceLanguage = " & Me!langue_source

Assuming both of these are text columns, you'll need single quotes around the passed values, like this:

str1 = "TargetLanguage  = '" & Me!langue_cible & "' And SourceLanguage = '" & Me!langue_source & "'"
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
.. even better, add NULL handling ...

str1 = "TargetLanguage  = '" & Nz(Me!langue_cible, '') & "' And SourceLanguage = '" & Nz(Me!langue_source, '') & "'"
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Assuming text fields:

Dim str1 As String

str1 = "TargetLanguage  = " & Chr(34) & Me!langue_cible & Chr(34)  & " And SourceLanguage = " & Chr(34) & Me!langue_source & Chr(34)
Forms![frmtrad_commandes2]![sbfrmTranslationPreselectTranslators].Form.Filter = str1
Forms![frmtrad_commandes2]![sbfrmTranslationPreselectTranslators].Form.FilterOn = True

Author

Commented:
>Assuming text fields:

no, they are number fields (language IDs)
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Str1 = "targetlanguage = '" & me.langue_cible & "' and sourcelanguage = '"  & me.langue_source & "'"
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Ignore my post ...

Author

Commented:
I'm getting the error "you entered an expression that has an invalid reference to the property Form/report".

I even tried hard coding this:
Forms![frmtrad_commandes2]![sbfrmTranslationPreselectTranslators].Form.Filter = "TargetLanguage = 1"

Author

Commented:
not sure if this makes a difference but this code is in another subform on the onCurrent event.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
<wild guesses>
Make sure you spelled everything correctly
Make sure the subform reference is the subform control name, NOT the actual form's name
Verify that the code is being executed from your main form, not your subform
Run it in Debug mode, and copy-paste the contents of Str1 into this question
Check for gremlins
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Be sure for the part in BOLD you are using the Name property


Forms![frmtrad_commandes2]![sbfrmTranslationPreselectTranslators].Form.Filter = str1
Forms![frmtrad_commandes2]![sbfrmTranslationPreselectTranslators].Form.FilterOn = True

1

Author

Commented:
>> Verify that the code is being executed from your main form, not your subform

I have 2 subforms. This code is on the onCurrent of a subform and the code refers to the other subform.

Is there something wrrong with that?
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>not sure if this makes a difference but this code is in another subform on the onCurrent event.
Yes, it makes a difference.  That makes it (I forget which one)
(1)   Me.Parent![sbfrmTranslationPreselectTranslators].Form.Filter = str1
or
(2)  Create a PUBLIC (i.e. not private) sub on your main form named something like refresh_everything, write your refreshing code in that.  Then call it from the subform.

My only live example I didn't retain, so I can't help you beyond that.  Good luck.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"This code is on the onCurrent of a subform and the code refers to the other subform."
Your syntax s/b ok because you are using the long form version ...  but see my image above.

mx

Author

Commented:
mx, I noticed you image above and am sure it is fine...they are both named the same. I know maybe not the best practice but I don't think it is causing the error.
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Is frmtrad_commandes2 the name of the Main Form ?

"This code is on the onCurrent of a subform"

Is this error occurring when you first open the Main form?
Because subforms load before the Main form ...

Author

Commented:
ok, I put exactly the same code behind a button on the main form and it worked fine. It seems it is something with referencing from one subform to another. ???

Author

Commented:
mx, you got it..."Is this error occurring when you first open the Main form"

just added a requery and it works great!

Forms![frmtrad_commandes2]![sbfrmTranslationPreselectTranslators].Form.Requery
Forms![frmtrad_commandes2]![sbfrmTranslationPreselectTranslators].Form.Filter = str1
Forms![frmtrad_commandes2]![sbfrmTranslationPreselectTranslators].Form.FilterOn = True

Author

Commented:
I spoke to soon....when I move in and out of the subform the filter works. but then I close the form and reopened and am getting the same error.

I guess it isn't loaded in time. other than the requery I did above could you suggest anything?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
cool
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
One possible way - 'depending' ...
See if you can use this ...

On Error Resume Next ' covers Form opening
Forms![frmtrad_commandes2]![sbfrmTranslationPreselectTranslators].Form.Filter = str1
Forms![frmtrad_commandes2]![sbfrmTranslationPreselectTranslators].Form.FilterOn = True
Err.Clear

Author

Commented:
that did the trick! wow thx mx. It was driving me crazy.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
It's a bit of a hack, but ... It's a bit of a issue to reference a main form as such when opening a form with subforms.

Author

Commented:
yep, agreed. thax again.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.