User Function To Reduce VBA Bloat

I have 25 command buttons with a simple "DoCmd.SearchForRecord" single line of code on each.

I would also like add to following code to each of the 25 buttons
Dim MySQL As String
MySQL = "SELECT fCONT_MOST.cID, fCONT_MOST.SORTby, fCONT_MOST.cNAME, fCONT_MOST.ck_45 FROM fCONT_MOST " & _
        "WHERE (((fCONT_MOST.ck_45)=False)); "
   
  ComboFIND1.RowSource = MySQL
  ComboFIND1.Requery
   
   Me.RecordSource = "fCONT_MOST"
   Me.Requery
   LabelFILTERname.Caption = "MOST RECORDS"

Open in new window

Pasting it in 25 times would work fine but would bloat the code for the form.

Could the extra code be reduced to a single line or two using some type of user function?
LVL 24
EirmanChief Operations ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
How does the code for each button differ?
0
Gustav BrockCIOCommented:
That's a job for WithEvents.

The article Create Windows Phone Colour Palette and Selector using WithEvents demonstrates how to use it for a bunch of textboxes.
But it can applied to any control, also buttons.
0
Juan OcasioApplication DeveloperCommented:
You can do something like this:

Public sub OnClickEvent()
Dim MySQL As String
MySQL = "SELECT fCONT_MOST.cID, fCONT_MOST.SORTby, fCONT_MOST.cNAME, fCONT_MOST.ck_45 FROM fCONT_MOST " & _
        "WHERE (((fCONT_MOST.ck_45)=False)); "
   
  ComboFIND1.RowSource = MySQL
  ComboFIND1.Requery
   
   Me.RecordSource = "fCONT_MOST"
   Me.Requery
   LabelFILTERname.Caption = "MOST RECORDS"
End Sub

Open in new window



    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CommandButton" Then
            ctrl.OnClick = "=OnClickEvent()"
        End If
    Next

Open in new window

0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Juan OcasioApplication DeveloperCommented:
Sorry: Public sub OnClickEvent() should be Public Function OnClickEvent()
0
EirmanChief Operations ManagerAuthor Commented:
That's not working for me Juan

I created the Public Function

This is the code in one the buttons
Private Sub rec3MONEY_Click()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CommandButton" Then
            ctrl.OnClick = "=OnClickEvent()"
        End If
    Next
    DoCmd.SearchForRecord , , , " [cID] = 71 "
End Sub

Open in new window

"MOST" records are selected but it does not go to the record.

In addition ....
All other buttons cease to function... e.g.
Private Sub recAIB_Click()
    DoCmd.SearchForRecord , , , " [cID] = 130 "
End Sub

Open in new window

Am I applying the code incorrectly?
0
Gustav BrockCIOCommented:
Well, that's what WithEvents  does - "writes" all this code for you.
0
NorieAnalyst Assistant Commented:
How does the single line of code for each button differ for each one?

Can you post an example of that single line of code?
0
EirmanChief Operations ManagerAuthor Commented:
@Norrie ....
  DoCmd.SearchForRecord , , , " [cID] = 130 "

   DoCmd.SearchForRecord , , , " [cID] = 120 "

   DoCmd.SearchForRecord , , , " [cID] = 211 "

@Gustav .... I will explore your solution - thanks
0
NorieAnalyst Assistant Commented:
Eirman

Is the value in the criteria, e.g. 130, 120, 211 etc. , the only thing that changes?

Do you really need 25 buttons?

Couldn't you have a textbox/combobox where the user enters/selects the value and then a single button to execute DoCmd.SearchForRecord using the value they entered/selected?
0
EirmanChief Operations ManagerAuthor Commented:
Do you really need 25 buttons?
Absolutely - It gives me instant access to my most commonly accessed records. Of course I have a combo for all ( and multiple sub-categories)
PERSONAL-DATABASE.jpg
0
Juan OcasioApplication DeveloperCommented:
You could use the tag property of the controls you want to use and then reference it like this:

If TypeName(ctrl) = "CommandButton" Then
            SELECT CASE ctrl.Tag
                     Case 131 : "=OnClickEvent(131)"
           End Select

        End If

Open in new window


Then used the passed in number to reference your DoCmd

Private function OnClickEvent(num as Integer)
    DoCmd.SearchForRecord , , , " [cID] = " & num
End Sub

Open in new window

1
EirmanChief Operations ManagerAuthor Commented:
I have upped the points for this question

I am merely an advanced beginner Juan and I have no idea of how to apply the above code and how it adds my chunk of code.
0
Gustav BrockCIOCommented:
You should sit back with a cup of coffee and browse my article.

Go to the subheader Now is the time to call for WithEvents and see how little code is needed.
That is how to solve this task.
0
EirmanChief Operations ManagerAuthor Commented:
You should sit back with a cup of coffee and browse my article
Weird coincidence - I just had a coffee with my brunch! Will digest your article for 'afters'
0
Juan OcasioApplication DeveloperCommented:
So you would modify the Tag property of each button you want to check. So in your example, button rec3MONEY would have a tag of 71.

In your form load event you would have:
  Dim ctrl As Control    
  For Each ctrl In Me.Controls
      If TypeName(ctrl) = "CommandButton" Then
            Select Case ctrl.Tag
                Case "71": ctrl.OnClick = "=OnClickEvent(71)"
                Case "130": ctrl.OnClick = "=OnClickEvent(130)"
                Case "120": ctrl.OnClick = "=OnClickEvent(120)"
            End Select
        End If
    Next

Open in new window


Then you would have the following function:
Private function OnClickEvent(num as Integer)
    DoCmd.SearchForRecord , , , " [cID] = " & num
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EirmanChief Operations ManagerAuthor Commented:
Okay Juan, I think I'm getting there in comprehension ..... However ....
Suppose My RecordSource is fCONT_MOST_23 (with the corresponding SQL for the FIND1)
How is the Forms RecordSource & FIND1 reset to fCONT_MOST
& LabelFILTERname.Caption = "MOST RECORDS"?
0
PatHartmanCommented:
Please do not implement this with buttons.  Think about the trouble this is causing.  What are you going to have to change if you have to add additional buttons.  This is spreadsheet thinking.  Why would you want to write code for all those buttons?

The combo suggested earlier is the cleanest solution.  An alternative that shows you a list is the listbox, which in this case will work just like a combo.  The RowSource for the combo should be a querydef with criteria.  No need to build it in VBA:

Select ... From YourTable Where somefield = Forms!yourform!yourcombo


The code will be reduced to one line:

Me.MyCombo.Requery
0
Juan OcasioApplication DeveloperCommented:
How does

DoCmd.SearchForRecord , , , " [cID] = 71 "

Correlate to

Dim MySQL As String
MySQL = "SELECT fCONT_MOST.cID, fCONT_MOST.SORTby, fCONT_MOST.cNAME, fCONT_MOST.ck_45 FROM fCONT_MOST " & _
        "WHERE (((fCONT_MOST.ck_45)=False)); "
   
  ComboFIND1.RowSource = MySQL
  ComboFIND1.Requery
   
   Me.RecordSource = "fCONT_MOST"
   Me.Requery
   LabelFILTERname.Caption = "MOST RECORDS"

Open in new window

0
EirmanChief Operations ManagerAuthor Commented:
Hi Pat, I know that one button for a single record is unconvertional/non-standard practice.
However this one click QAT is extremely convenient. (I think the eliptical buttons look well).
It's my personal DB so mods are no problems.

I have 35 category filters which set a new recordsource for the form & the find combo & a Label.

The RowSource for the combo should be a querydef with criteria.  No need to build it in VBA:
That really useful information - Thanks
0
EirmanChief Operations ManagerAuthor Commented:
To answer your question Juan, if I have my Form's recordsource set to records 10 - 20 only,
I cannot jump to record 91 without setting my Form's recordsource to MOSTrecords
0
PatHartmanCommented:
You would get one click if you used a listbox that was expanded to show all 25 items.

Doing it the hard way still only requires 2 lines of code for each button as long as you use the querydef with a form field reference:

Me.HiddenIDField = 131
Me.Requery

So 50 lines of code in 25 buttons or 1 line of code in 1 control.


In this case, the query would reference HiddenIDField which should be hidden since no one needs to see it.
1
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Side note regarding:

  Me.RecordSource = "fCONT_MOST"
   Me.Requery

Setting the RecordSource automatically does a Requery. So, no need for an additional Requery .. which is a unnecessary performance hit.
1
EirmanChief Operations ManagerAuthor Commented:
The TAG Property works really well. Thanks for your patience Juan.

Gustav - I'll study your solution when I have the time a full pot of coffee to hand.

EDIT:

This is the function I used

Private Function OnClickEvent(num As Integer)
Dim MySQL As String
MySQL = "SELECT fCONT_MOST.cID, fCONT_MOST.SORTby, fCONT_MOST.cNAME, fCONT_MOST.ck_45 FROM fCONT_MOST " & _
        "WHERE (((fCONT_MOST.ck_45)=False)); "
   
  ComboFIND1.RowSource = MySQL
  ComboFIND1.Requery
   
   Me.RecordSource = "fCONT_MOST"
   LabelFILTERname.Caption = "MOST RECORDS"

    DoCmd.SearchForRecord , , , " [cID] = " & num
End Function

Open in new window


VBA Bloating  now reduced!
0
Juan OcasioApplication DeveloperCommented:
Excellent!  Glad it's working for you!
0
PatHartmanCommented:
@Eirman,
I hate to sound ungrateful but since you didn't take my advice, it wasn't necessary to award me "participation" points just because I offered a suggestion.  Not everyone requires a trophy and giving trophies to every one diminishes their value in addition to cheating the person or persons who actually did provide the solution out of points.  Granted, the points are meaningless but people do like to keep score so some people care about them.
0
EirmanChief Operations ManagerAuthor Commented:
Point taken Pat, but   ....
even though I didn't actually use your suggestions in resolving my particular problem,
I will, in the future, be referring to the techniques and suggestions you made.
They were most definitely not participation points. (Not all participants received points)
Ditto for Gustav.

I upped the priority so there would be more points to  distribute.
I think JUAN received 2100 points (maybe more).
The sidenote from JOE was not relevant to the question but was genuinely useful - ergo 150 points
0
Juan OcasioApplication DeveloperCommented:
Always glad to assist irrespective of the points distributed.  For me, knowledge transfer is more important than points.  Always glad to help and be helped.  As an aside, Pat is invaluable in this forum, and I have learned a ton.
0
PatHartmanCommented:
Thank you for the explanation.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.