Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Simplify my code

Discussion in 'Information Technology' started by Ross, Nov 14, 2005.

Tags:
  1. Ross

    Ross
    Expand Collapse
    Guest

    Hi there
    I have produced an alpha list which is updated by pressing 26 "A" - "Z"
    Alpha buttons, works a treat, but there must be a simpler way than having to
    produce 26 snippets of code, any ideas?

    Code Example 1
    Private Sub A_Click()
    'Updates txtAlpha, which inturn updates the Driver Details Query.
    Me.txtAlpha = "A"
    Me.Refresh
    End Sub

    Code Example 2
    Private Sub txtAlpha_AfterUpdate()
    On Error GoTo Err_txtAlpha_AfterUpdate
    'Updates the driver details query.

    Dim stDocName As String

    stDocName = "DriverDetailsQuery"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    Exit_txtAlpha_AfterUpdate:
    Exit Sub

    Err_txtAlpha_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_txtAlpha_AfterUpdate
    End Sub

    Cheers Ross
     
  2. Loading...


  3. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    Just use a option group for the 26 buttons (use the wizard, as it will let
    you enter a-z).

    Then, in the after update event of the option group, you go:

    dim strSql as string
    dim strLetter as string

    strLetter = ASC("A") + me.OptionGroup - 1
    me.txtAlpha = strLetter

    stDocName = "DriverDetailsQuery"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    So, you only need the above code, and not 26 of them...

    However, I would dump the use of a parameter in the query, and simply use
    in-line sql in code

    The above however is all the code you need. You don't really mention what
    you are doing with this query, but likely you are doing some type of
    filter, and could use

    strSql = "select * from tblcustomers where lastname like '" & strLetter &
    "*'"
    me.Recordsource = strSql

    And, you would be even better to use the "where" clause of a form, and
    display the results in a nice grid. I got some screen shots that do the
    above here:

    http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  4. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Stephen Lebans' SelectAlpha is probably very useful to you:

    http://www.lebans.com/selectalpha.htm

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Ross" <Ross@discussions.microsoft.com> wrote in message
    news:E41AFB17-2ED5-4A71-9E50-6A2159CC15FD@microsoft.com...
    > Hi there
    > I have produced an alpha list which is updated by pressing 26 "A" - "Z"
    > Alpha buttons, works a treat, but there must be a simpler way than having
    > to
    > produce 26 snippets of code, any ideas?
    >
    > Code Example 1
    > Private Sub A_Click()
    > 'Updates txtAlpha, which inturn updates the Driver Details Query.
    > Me.txtAlpha = "A"
    > Me.Refresh
    > End Sub
    >
    > Code Example 2
    > Private Sub txtAlpha_AfterUpdate()
    > On Error GoTo Err_txtAlpha_AfterUpdate
    > 'Updates the driver details query.
    >
    > Dim stDocName As String
    >
    > stDocName = "DriverDetailsQuery"
    > DoCmd.OpenQuery stDocName, acNormal, acEdit
    >
    > Exit_txtAlpha_AfterUpdate:
    > Exit Sub
    >
    > Err_txtAlpha_AfterUpdate:
    > MsgBox Err.Description
    > Resume Exit_txtAlpha_AfterUpdate
    > End Sub
    >
    > Cheers Ross
    >
     

Share This Page