Simplify my code

[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

 

[Albert D.Kallal]

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

 

[Van T. Dinh]

Stephen Lebans' SelectAlpha is probably very useful to you:

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