?
Solved

filtering a subform

Posted on 2012-08-13
23
Medium Priority
?
244 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:Shawn
  • 10
  • 7
  • 4
  • +1
23 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38290028
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 & "'"
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38290029
.. even better, add NULL handling ...

str1 = "TargetLanguage  = '" & Nz(Me!langue_cible, '') & "' And SourceLanguage = '" & Nz(Me!langue_source, '') & "'"
0
 
LVL 75
ID: 38290030
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:Shawn
ID: 38290036
>Assuming text fields:

no, they are number fields (language IDs)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38290041
Str1 = "targetlanguage = '" & me.langue_cible & "' and sourcelanguage = '"  & me.langue_source & "'"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38290043
Ignore my post ...
0
 
LVL 1

Author Comment

by:Shawn
ID: 38290044
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"
0
 
LVL 1

Author Comment

by:Shawn
ID: 38290063
not sure if this makes a difference but this code is in another subform on the onCurrent event.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38290064
<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
0
 
LVL 75
ID: 38290072
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
0
 
LVL 1

Author Comment

by:Shawn
ID: 38290075
>> 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?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38290087
>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.
0
 
LVL 75
ID: 38290094
"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
0
 
LVL 1

Author Comment

by:Shawn
ID: 38290100
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.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 38290102
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 ...
0
 
LVL 1

Author Comment

by:Shawn
ID: 38290104
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. ???
0
 
LVL 1

Author Comment

by:Shawn
ID: 38290113
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
0
 
LVL 1

Author Comment

by:Shawn
ID: 38290125
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?
0
 
LVL 75
ID: 38290126
cool
0
 
LVL 75
ID: 38290135
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
0
 
LVL 1

Author Comment

by:Shawn
ID: 38290145
that did the trick! wow thx mx. It was driving me crazy.
0
 
LVL 75
ID: 38290153
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.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38290158
yep, agreed. thax again.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question