Handler for multi Command Button, to make the code shorter

[calaxan]

Hi All...
I have 27 command buttons (it's alphabet) to get Employee Name based on First Letter and to display it on List Box.
Actually i got how to do it, but the code would be too long.
Could be somebody here can help me to make the code shorter. The code is like below:

Private Sub Command2_Click()
Dim crSQL As String
 
crSQL = "SELECT EmpName FROM tblEmployee WHERE [EmpName] LIKE 'a*' "
Me.lstEmpName.RowSource = crSQL
Me.lstEmpName.Requery
 
End Sub
 
Private Sub Command3_Click()
Dim crSQL As String
 
crSQL = "SELECT EmpName FROM tblEmployee WHERE [EmpName] LIKE 'b*' "
Me.lstEmpName.RowSource = crSQL
Me.lstEmpName.Requery
 
End Sub
 
Private Sub Command4_Click()
Dim crSQL As String
 
crSQL = "SELECT EmpName FROM tblEmployee WHERE [EmpName] LIKE 'c*' "
Me.lstEmpName.RowSource = crSQL
Me.lstEmpName.Requery
 
End Sub
 

Thanks for the help

[Luk3r]

You could turn your button code into a function and under each button click, add a global variable, set that global variable to the letter you want to be search in your SELECT statement, and replace the current letter within the SELECT statement with the global variable that you specified.

Function Code:

crSQL = "SELECT EmpName FROM tblEmployee WHERE [EmpName] LIKE '" & letterVariable & "*' "
Me.lstEmpName.RowSource = crSQL
Me.lstEmpName.Requery
 

Global Variables:

Dim letterVariable As String
Dim crSQL As String

Command Click:

letterVariable = "a"
##call the name of your function here##
 

[calaxan]

Thanks Luk3r, it's help me.

[calaxan]

upgraded a lil bit:

Option Compare Database
Dim iSQL As String
 
Private Sub Command7_Click()
    GetEmpName ("m")
End Sub
 
Function GetEmpName(myLetter As String)
    iSQL = "SELECT EmpName FROM tblEmployee WHERE [EmpName] LIKE '" & myLetter & "*' "
    Me.lstEmpName.RowSource = iSQL
    Me.lstEmpName.Requery
End Function
 

[Luk3r]

Even better than my basic suggestion. Nice job!

[NeoPa]

Let me start by directing your attention to Require Variable Declaration. It's a massive mistake to overlook this.

Then I can make a suggestion to reduce the code even further.

The .Requery isn't required as the control's data is repopulated once you change it. Also, the procedure should be a subroutine rather than a function.

Here's how you could do it to reduce the code further :

Option Compare Database
Option Explicit
 
Private Sub Command1_Click()
    Call GetEmpName("A")
End Sub
 
...
 
Private Sub Command26_Click()
    Call GetEmpName("Z")
End Sub
 
Private Sub GetEmpName(myLetter As String)
    Me.lstEmpName.RowSource = Replace("SELECT [EmpName] " _
                                    & "FROM   [tblEmployee] " _
                                    & "WHERE  ([EmpName] LIKE '%L*')" _
                                    , "%L", myLetter)
End Sub

NB. I've marked Luk3r's post as Best Answer as it pointed the way to the solution.

[calaxan]

NeoPa...your code above is not working to me. I dont know why :)

[PhilOfWalton]

Slightly different approach.
I have an Option group called "FilterGrp" (with the 26 letters + "All". The OptionValue goes from 65 to 90 for A to Z and 91 for the "All".
I also have a textBox called "Filter" on the form.
The AfterUpdate of FilterGrp runs this

Function FilterType(Frm As Form)
    On Error GoTo FilterType_Err
 
    With CodeContextObject
        If (.FilterGrp = 91) Then
            ' Set ShowFilter to "All"
            Frm!ShowFilter = "All"
            ' Set Filter to letter selected with []*
            Frm!Filter = "*"          ' Changed from "[]*"
        End If
        If (.FilterGrp <> 91) Then
            ' Set ShowFilter to letter selected
            Frm!ShowFilter = GetLetter(Frm)
            ' Set Filter to letter selected with []*
            Frm!Filter = "[" & Frm!ShowFilter & "]*"
        End If
 
        ' FormType
        On Error GoTo FilterType_Exit
 
        DoCmd.GoToControl "NamePickList"        ' Move the focus
        Call SurNameFilter(Frm)
    End With
 
FilterType_Exit:
    Exit Function
 
FilterType_Err:
    MsgBox Err.Description
    Resume FilterType_Exit
 
End Function
 

This routine copes with foreign letters

Function GetLetter(Frm As Form) As String    ' Returns the corresponding letter for the button pressed
                                  ' Accented characters mapped to the same char
                                  ' without the accent
    Dim iNum As Integer
    Dim stRetVal As String
 
    iNum% = Frm!FilterGrp
    If iNum% = 91 Then
        GetLetter = ""
    Else
        stRetVal$ = Chr$(iNum%)
 
        ' Map accented and other special chars to a button
 
        Select Case stRetVal$
            Case "a":
                stRetVal$ = stRetVal$ + "ÀÁÂÃÄ"
            Case "c":
                stRetVal$ = stRetVal$ + "Ç"
            Case "e":
                stRetVal$ = stRetVal$ + "ÈÉÊË"
            Case "i":
                stRetVal$ = stRetVal$ + "ÌÍÎÏ"
            Case "n":
                stRetVal$ = stRetVal$ + "Ñ"
            Case "o":
                stRetVal$ = stRetVal$ + "ÒÓÔÕÖ"
            Case "s":
                stRetVal$ = stRetVal$ + "Š"         ' This is S hacek
            Case "u":
                stRetVal$ = stRetVal$ + "ÙÚÛÜ"
            Case "y":
                stRetVal$ = stRetVal$ + "Ýÿ"
            Case "z":
                stRetVal$ = stRetVal$ + "ZÆØÅ"
        End Select
 
        GetLetter = stRetVal$
    End If
 
End Function
 

The Rowsource for the filtered names is

SELECT DISTINCTROW Member.MemSurName, Member.MemFirstName, Member.MemberID
WHERE Member.MemSurName) Like [screen].[activeform]![Filter])
ORDER BY Member.MemSurName, Member.MemFirstName;
 

Phil

[NeoPa]

Calaxan:
NeoPa...your code above is not working to me. I dont know why :)

Well, I can't see your system so it'll be hard for me to work out why it's not working for you.

Why don't you start by giving me some information about what happens when it doesn't work? An error message is good if you have one. Any clues are good really. Nothing at all isn't something I can work with easily.

[NeoPa]

@Phil.
Would you like to give a reason why someone may want to use your approach in preference to the others suggested which seem clearly more straightforward. Sometimes complexity is necessary, but you give nothing to suggest why this might be the case here. The original question gave no indication that special keys or values are required, so that can't be it.

[PhilOfWalton]

Main reason is that you are using an option group to establish which letter has been selected rather than having a Sub firing on each letter. Again very simple to extend if you want to filter on numbers as well as letters.
The Function GetLetter is only useful if you are dealing with foreign names though I certainly have come across the odd Norwegian and French names with odd accents on them, and if they have been entered in the database with the accent, this routine should find them.
In addition the "All" option removes the filter so that the listbox shows everyone.

Phil

[NeoPa]

PhilofWalton:
Main reason is that you are using an option group to establish which letter has been selected rather than having a Sub firing on each letter.

That's confusing. My code, and when I refer to mine I also include Luk3r's as well as it's fundamentally very similar, uses what the OP indicated they had, which was a bunch of different Command Buttons. I'm not sure where you believe the Option Groups come in.

Having said all that, your suggestion is an alternative and that's always good to see. I'm sure those on the look out for help in such an area will look at both and choose the approach that best suits their own requirements.

Having decided to read your suggestion in more detail it seems that :

1. It's air-code. This isn't too much of a problem but please be careful to remove obvious erros like where the parentheses are so they match up. I post a lot of air-code myself, but I'm always careful to check it thoroughly first.
2. It offers a clever approach to handling non-standard versions of the main letters. I expect this is the real reason for posting in the first place. It doesn't match the question of this thread, but is worth including for any that need that extra flexibility.

Overall it's worth posting. I would only say that being clearer about what it is can be particularly helpful when it's beyond the limited scope of the thread.

It seems you're new here. Already I can tell you have much to offer. Please don't let my advisory comments put you off. You're very welcome at Bytes.com and I look forward to seeing more of your contributions.

[PhilOfWalton]

Sorry, I didn't make myself clear. The 26 letters + "All" are toggle buttons on the option group (FilterFrp), and there is no code attached to the 27 toggle buttons. Depending on which letter is pressed, the value of the FilterGrp changes between 65 for an "A" to 91 for an "All". Note Toggle Buttons can only have numeric values, hence the need to use the ASCII value of the letter.
The code (not air code) runs on the AfterUpdate of the FilterGrp (ie a letter key has been pressed).
Basically that creates the appropriate letter for the "WHERE" clause for the query on which the list box of Employee names is based.
I fully accept that handling non standard letters is overkill.

[NeoPa]

Ah. That's clearer now. I took the first 'you' in your explanation to mean 'you', but essentially I should have read it as 'one'. Thus, referring to your approach rather than ours.

That's probably a better way to approach it, but that's advice to the OP about a better question as opposed to their actual one. Worth making the point, I agree.

@Calaxan.
You may want to give consideration to Phil's advice on how better to design the interface for what you want to achieve. It makes good sense to use an Option Group instead of a whole bunch of separate Command Buttons. It also makes the code tidier and easier to maintain. The AfterUpdate event handler is a single point of entry.

[calaxan]

Thanks to PhilOfWalton and NeoPa, your sharing is opening my mind to have another option of my previous code.

Best Regards,
Access Newbie :)