Array of Letters (A,B,C ...Z) use to FindFirst

[Gina]

Hi all

I have a form with customers and the user can select customer id/name via a
cboSearch combobox

now some buttons with the letters of the alphabet should make it easier.

I've done some code for 6 buttons each with the corresponding Caption ...
all works so far

BUT
the alphabet has 26 letters and i wonder whether I could use an array insted
of calling my modulecode for every single button as follows

Private Sub btnA_Click()
Alphabet.SelectByLetter ("A")
End Sub

Thanks for your input and help

Gina

 

[Ron Weiner]

Gina

Access Does Not Support control arrays, but you really don't need one in
this case. Assuming that you have already written the code for a FUNCTION
called AlphabetSelectByLetter (strFirstLetter) then all you need to do is to
put:
=Alphabet.SelectByLetter ("A")
on the property sheet into the Click event of btnA instead of [Event
Procedure] that is there now. Similarly you will want to change the btnB
click event to:
=Alphabet.SelectByLetter ("B")
and so on for all 26 buttons.

NOTE: the routine you call MUST BE A FUNCTION even if it does not return a
value. A sub will NOT work.

Ron W
www.WorksRite.com

 

[Gina]

Thanks Ron.

well ... yes the function is already written

as the buttons have the corresponding letter I 've changed the click event
for every button
Alphabet.SelectByLetter (Screen.ActiveControl.Caption)

so I can just cut n paste ....

was there something like VB does handle the control array thing ??

Gina

Gina

Access Does Not Support control arrays, but you really don't need one in
this case. Assuming that you have already written the code for a FUNCTION
called AlphabetSelectByLetter (strFirstLetter) then all you need to do is to
put:
=Alphabet.SelectByLetter ("A")
on the property sheet into the Click event of btnA instead of [Event
Procedure] that is there now. Similarly you will want to change the btnB
click event to:
=Alphabet.SelectByLetter ("B")
and so on for all 26 buttons.

NOTE: the routine you call MUST BE A FUNCTION even if it does not return a
value. A sub will NOT work.

Ron W
www.WorksRite.com

Hi all

I have a form with customers and the user can select customer id/name

via

a
cboSearch combobox

now some buttons with the letters of the alphabet should make it easier.

I've done some code for 6 buttons each with the corresponding Caption ....
all works so far

BUT
the alphabet has 26 letters and i wonder whether I could use an array insted
of calling my modulecode for every single button as follows

Private Sub btnA_Click()
Alphabet.SelectByLetter ("A")
End Sub

Thanks for your input and help

Gina

 

[Marshall Barton]

I have a form with customers and the user can select customer id/name via a
cboSearch combobox

now some buttons with the letters of the alphabet should make it easier.

I've done some code for 6 buttons each with the corresponding Caption ...
all works so far

BUT
the alphabet has 26 letters and i wonder whether I could use an array insted
of calling my modulecode for every single button as follows

Take a look at http://www.lebans.com/selectalpha.htm for a
way to do this kind of thing using a single label control.

 

[Gina]

Hi Marshall.

this is great and works very well for all the 'normal letters'

Alphabet.SelectByLetter (Chr(64 - Int(-X / Me.lblAlpha.Width * 26)))

I am working in a german version and unfortunately we have three funny
letters A,O,U with double dots on top

The asc code is

for A double dot = 196
for O double dot = 214
for U double dot = 220

I don't know how to implement this obstacle ... :-(

Gina

 

[Guest]

Gina,

This could be written as a 'select case':

Me.Filter = "fldLastName LIKE " & Chr(34)
Select Case
Case 196:

& Chr(64 - Int(-X /
Me.lblAlpha.Width *

26)) & "*" & Chr(34)
Me.FilterOn = True

--
Chaim

Hi Marshall.

this is great and works very well for all the 'normal letters'

Alphabet.SelectByLetter (Chr(64 - Int(-X / Me.lblAlpha.Width * 26)))

I am working in a german version and unfortunately we have three funny
letters A,O,U with double dots on top

The asc code is

for A double dot = 196
for O double dot = 214
for U double dot = 220

I don't know how to implement this obstacle ... :-(

Gina

 

[Gina]

Chaim,

thanks for your answer...
that was exactly what I thought as well ....
problem is that, when these three extra letters are appended (or added in
between) lblAlpha the rest of the lebans label code doesn't even work for
the 'normal' letters anymore ....

now I decided to use two different labels and I am thinking around HOW on
earth I could use lebans generic formula for the three letter label
containing the extra leters
(Chr(64 - Int(-X / Me.lblAlpha.Width * 26)))
I love that generic formula and the label solution !!!

the lblShit now has only 3 letters in it instead of lblAlpha having 26
unfortunately I am not a maths wizard ....

as I understand the X is the position where the mouse clicks down onto the
label
as the user can click on e.g. the left part of the letter, the right part or
the middle part .... in any case the letter should be the result of the
whole lbl-code
so I had to give it a range from-to in order to get it to work

but how can I use that special formula together with the lblShit ???
maybe there's a maths fan out there, please ?!?!! who wants to help me

Thanks in advance,
Gina

******
Private Sub lblShit_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
Select Case X
Case 10 To 210
Alphabet.SelectByLetter Chr(196)
Case 211 To 360
Alphabet.SelectByLetter Chr(214)
Case 361 To 560
Alphabet.SelectByLetter Chr(220)
End Select
End Sub
******
Public Function SelectByLetter(letter As String)
Dim rs As Object
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Cust.custID, Cust.Name FROM Cust WHERE Cust.Name LIKE
'" & letter & "*'"

Set rs = Form__frmCust.Recordset.Clone

Set rst = CurrentDb.OpenRecordset(strSQL)
If rs.AbsolutePosition > -1 Then
rs.FindFirst "[custID] = " & rst("custID")
Form__frmCust.Bookmark = rs.Bookmark
' following code for subforms' unbound cboBoxes reflecting the
corresponding values for rs.Bookmark
Form__frmCust.cboSearch = Form__frmCust.custID
Form__frmCar.cboRegNum = Form__frmCar.RegNum
Form__frmWork.Refresh
Form__frmWork.cboWork = Form__frmWork.cboWork.ItemData(0)
End If
End Function
******

Gina,

This could be written as a 'select case':

Me.Filter = "fldLastName LIKE " & Chr(34)
Select Case
Case 196:

& Chr(64 - Int(-X /
Me.lblAlpha.Width *

 

[Marshall Barton]

this is great and works very well for all the 'normal letters'

Alphabet.SelectByLetter (Chr(64 - Int(-X / Me.lblAlpha.Width * 26)))

I am working in a german version and unfortunately we have three funny
letters A,O,U with double dots on top

The asc code is

for A double dot = 196
for O double dot = 214
for U double dot = 220

I don't know how to implement this obstacle ... :-(

If you want to any set of characters and arrange them in any
order, then you can use:

strCh = Mid(lblAlpha.Caption, Int(X / Me.lblAlpha.Width *
Len(lblAlpha.Caption)) + 1, 1)
Me.Filter = "fldLastName LIKE """ & strCh & "*"""

Just make sure that you use the Format - SizeToFit menu item
after editing the label's caption.

 

[Gina]

Wouw, Marshall ... That's it .....
Alphabet.SelectByLetter (Mid(lblAlpha.Caption, Int(X / Me.lblAlpha.Width *
Len(lblAlpha.Caption)) + 1, 1))

)

Very BIG Thanks!!!

Gina