Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-30-2019, 02:38 AM   #1
amir0914
Newly Registered User
 
Join Date: May 2018
Posts: 25
Thanks: 19
Thanked 0 Times in 0 Posts
amir0914 is on a distinguished road
how to reduce the code lines

Hi all, I write the code to filter subform by two criteria on after update of textbox, but i think that's long and should be a way to brief that, Can someone help me or give a correct way to do this??

Code:
Private Sub Text5_AfterUpdate()

If IsNull(Me.Text5) And IsNull(Me.Text25) Then

Me.child85.Form.RecordSource = "SELECT * FROM qry_Customers2 "
 
ElseIf Not IsNull(Me.Text25) And Not IsNull(Me.Text5) Then

Me.child85.Form.Filter = "[S_Active] = '" & Me.Text5 & "'  And [fgk]=" & Me.Text25
Me.child85.Form.FilterOn = True
 
ElseIf IsNull(Me.Text25) And Not IsNull(Me.Text5) Then

Me.child85.Form.Filter = "[S_Active] = '" & Me.Text5 & "'"
Me.child85.Form.FilterOn = True
 
ElseIf Not IsNull(Me.Text25) And IsNull(Me.Text5) Then

Me.child85.Form.Filter = "[fgk]=" & Me.Text25
Me.child85.Form.FilterOn = True

End If

amir0914 is offline   Reply With Quote
Old 06-30-2019, 03:01 AM   #2
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,850
Thanks: 411
Thanked 691 Times in 670 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: how to reduce the code lines

Not sure why the record source is going to change, but....
Code:
Private Sub Text5_AfterUpdate()
Dim strFilter As String

Me.child85.Form.RecordSource = "SELECT * FROM qry_Customers2 "

If Not IsNull(Me.Text25) Then
    strFilter = "[fgk]=" & Me.Text25
End If
If Not IsNull(Me.Text5) Then
    strFilter = strFilter & " AND [S_Active] = '" & Me.Text5 & "'"
End If

If Left(strFilter, 4) = " AND" Then
    strFilter = Mid(strFilter, 5)
End If

If strFilter <> "" Then
    Me.child85.Form.Filter = strFilter
    Me.child85.Form.FilterOn = True
End If
End Sub
Not really shorter, but easier to understand perhaps?
HTH
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-30-2019, 03:13 AM   #3
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 2,034
Thanks: 0
Thanked 488 Times in 484 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: how to reduce the code lines

What you had wasn't actually wrong (I am sure it worked) and the revision isn't really shorter but it does eliminate repetition of filter commands by use of a variable.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
Old 06-30-2019, 03:28 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,029
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: how to reduce the code lines

not sure it makes for good sql

but for shortest code

Code:
Private Sub Text5_AfterUpdate()

'why is this line required
Me.child85.Form.RecordSource = "SELECT * FROM qry_Customers2 "

Me.child85.Form.Filter = "[fgk]=" & nz(Me.Text25,"[fgk]") & " AND [S_Active] = '" & nz(Me.Text5,"[S_Active]") & "'"
Me.child85.Form.FilterOn = True
End Sub
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Gasman (06-30-2019)
Old 06-30-2019, 03:38 AM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,038
Thanks: 78
Thanked 1,996 Times in 1,944 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: how to reduce the code lines

Amir0914,
For your own code readability and for anyone who may maintain the code, better to assign meaningful names to your controls. Child85 and text5, text25 don't convey any application meaning or usage. It doen't affect your question nor the response, just an observation on style for consideration.
Good luck.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
sonic8 (06-30-2019)
Old 06-30-2019, 05:29 AM   #6
sonic8
Newly Registered User
 
Join Date: Oct 2015
Posts: 225
Thanks: 35
Thanked 63 Times in 60 Posts
sonic8 is on a distinguished road
Re: how to reduce the code lines

Quote:
Originally Posted by CJ_London View Post
Code:
Me.child85.Form.Filter = "[fgk]=" & nz(Me.Text25,"[fgk]") & " AND [S_Active] = '" & nz(Me.Text5,"[S_Active]") & "'"
I think using the column names as fallback for no filter is not very intuitive.

However, the real problem is that it's not going to work this way. The text delimiters must not be used around the [S_Active] column name. Sure, you can put that right but I guess, I would dislike the resulting code even more.

Unless setting the Filter property is required for whatever purpose, I would go probably for something like the following.

Code:
Dim WhereCondition as String
    
WhereCondition = WhereCondition & IIf(IsNull(Me.Text25.Value),"", " AND [fgk]=" & Me.Text25.Value)
WhereCondition = WhereCondition & IIf(IsNull(Me.Text5.Value),"", " AND [S_Active] = '" & Me.Text5.Value & "'")
        
Me.child85.Form.RecordSource = "SELECT * FROM qry_Customers2 WHERE 1 = 1 " & WhereCondition
__________________
New article:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by sonic8; 06-30-2019 at 05:57 AM.
sonic8 is offline   Reply With Quote
The Following User Says Thank You to sonic8 For This Useful Post:
Gasman (06-30-2019)
Old 06-30-2019, 05:38 AM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,101
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: how to reduce the code lines

First, brevity is often the least of your worries. You should always shoot for clarity because you will at some point probably need to revisit code, or someone else will. If your code is clear and straight-forward, no biggie. But if it looks like a plate of spaghetti, that IS a big deal.

Next... convert everything to meaningful names.

Finally, if that much typing gets to you, consider a WITH ... END WITH block so that you could do something like this:

Code:
WITH Me.Child85.Form

...

    .Filter=...
    .FilterOn=...

...

END WITH
That way, you shorten what you type and make the logic stand out more. You would still need the references to "Me.Textnn" inside the with-block since they have a different prefix, but all that diddling with the properties of the indicated subform could be shortened by three elements for each property you touch.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-30-2019, 01:18 PM   #8
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,125
Thanks: 3
Thanked 462 Times in 455 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: how to reduce the code lines

I'll throw another option into the mix, embedded If statements rather than a series of ElseIfs


Code:
if IsNull(Me.Text5) then
  if IsNull(Me.Text25) Then
     ...
  else
    ...
  endif
else
  if IsNull(Me.Text25) Then
     ...
  else
    ...
  endif
endif
Cronk is offline   Reply With Quote
Old 06-30-2019, 01:26 PM   #9
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 2,034
Thanks: 0
Thanked 488 Times in 484 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: how to reduce the code lines

Another 2 cents worth:
Code:
With Me
.child85.Form.Filter = Nz("[fgk]=" + .Text25 & IIf(Not IsNull(.Text25), " AND ", "") + "[S_Active]='" + .Text5 + "'", "")
.child85.Form.FilterOn = True
End With
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 06-30-2019 at 01:40 PM.
June7 is online now   Reply With Quote
Old 06-30-2019, 02:55 PM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,029
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: how to reduce the code lines

can be taken further

Code:
With Me.child85.Form
    .Filter = Nz("[fgk]=" + .Text25 & IIf(Not IsNull(.Text25), " AND ", "") + "[S_Active]='" + .Text5 + "'", "")
    .FilterOn = True
End With
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-30-2019, 02:58 PM   #11
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 2,034
Thanks: 0
Thanked 488 Times in 484 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: how to reduce the code lines

No, I think Text5 and Text25 are on main form.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
Old 06-30-2019, 03:04 PM   #12
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,029
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: how to reduce the code lines

oh yes - just requires removing the . for those two controls
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-30-2019, 03:26 PM   #13
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 2,034
Thanks: 0
Thanked 488 Times in 484 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: how to reduce the code lines

Sure, just won't get benefit of intellisense popup tips.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
Old 06-30-2019, 11:18 PM   #14
sonic8
Newly Registered User
 
Join Date: Oct 2015
Posts: 225
Thanks: 35
Thanked 63 Times in 60 Posts
sonic8 is on a distinguished road
Re: how to reduce the code lines

Quote:
Originally Posted by June7 View Post
Sure, just won't get benefit of intellisense popup tips.
...and the expliciticity (is this a word?) of using a a explicit reference.

After looking through the suggestions made in this thread, including mine, I think most of them are driven too much by the intent to reduce the lines to a feasible minimum. - This is not really a sensible goal!

Sure, we should remove duplication from the code, but beyond that I would rather focus on the readability and how well the code conveys its intent. - I feel, several of the very brief alternatives of the code don't do very well in that regard.
__________________
New article:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by sonic8; 06-30-2019 at 11:19 PM. Reason: grammar/formatting
sonic8 is offline   Reply With Quote
Old 06-30-2019, 11:25 PM   #15
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 2,034
Thanks: 0
Thanked 488 Times in 484 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: how to reduce the code lines

Which was the intent of providing examples. I didn't say shortest was best, but OP asked so we showed. Now they are educated and can make a choice. Would I use my expression? Yes, I would.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 07-01-2019 at 09:22 AM.
June7 is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Reduce repetive code on multiple forms TimTDP Modules & VBA 18 04-07-2019 12:30 PM
Reduce or eliminate event code from forms.... amc Forms 12 01-05-2016 11:49 AM
reduce code bodylojohn Modules & VBA 1 11-28-2007 05:56 AM
Reduce the code ? illusion Modules & VBA 11 04-09-2005 06:18 AM
Reduce code George Too Modules & VBA 4 02-05-2003 12:30 PM




All times are GMT -8. The time now is 03:05 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World