Use alphabetical buttons to jump to records starting with that letter 11

[pmo]

I would like to have 27 buttons on a form. (26 letters of the alphabet and one that selects all letters)
I can do this with an option group. I need to relate the allocted number from the option box to the query that the list of records is based.
How do I do that. Do I have to use code? (Which I know nothing about)

Wayne

 

[evalesthy]

See BillPower's response in thread181-548003 - it shows how to filter a form to show select records on a form - similar to what you probably want to do.

 

[FancyPrairie]

There are a couple ways you can do this. One way would be to use command buttons and name your command buttons something like this:
cmdButtonA
cmdButtonB
...
In the OnClick event of each of the command buttons, call a function (i.e. DetermineQueryName). Assume your query names are:
qryA
qryB
...

Function DetermineQueryName()

Dim strQueryName as string

strQueryName = "qry" & Right$(Screen.ActiveControl.Name,1)

End Function

I don't know what you are trying to do once you know the query name. Are you trying to filter your form? Or What?

 

[Carol57]

Here's my code for a tab control form, allowing for 12 buttons for the alphabet and 1 button that shows all. Modify it to your field names:

Private Sub EmployeeNameFilters_TabChange(ByVal nNewTab As Integer, ByVal nOldTab As Integer)

nCount = nNewTab

Select Case (nNewTab)
Case 1
' Filter for Employee names that start with A, B
lbl_cap.Caption = "A-B"
DoCmd.ApplyFilter "", "[NameA] Like ""[AB]*"""

Case 2
' C, D
lbl_cap.Caption = "C-D"
DoCmd.ApplyFilter "", "[NameA] Like ""[CD]*"""

Case 3
' E, F
lbl_cap.Caption = "E-F"
DoCmd.ApplyFilter "", "[NameA] Like ""[EF]*"""

Case 4
' G, H
lbl_cap.Caption = "G-H"
DoCmd.ApplyFilter "", "[NameA] Like ""[GH]*"""

Case 5
' I, J, K
lbl_cap.Caption = "I-J-K"
DoCmd.ApplyFilter "", "[NameA] Like ""[IJK]*"""

Case 6
' L, M
lbl_cap.Caption = "L-M"
DoCmd.ApplyFilter "", "[NameA] Like ""[LM]*"""

Case 7
' N, O
lbl_cap.Caption = "N-O"
DoCmd.ApplyFilter "", "[NameA] Like ""[NO]*"""

Case 8
' P, Q, R
lbl_cap.Caption = "P-Q-R"
DoCmd.ApplyFilter "", "[NameA] Like ""[PQR]*"""

Case 9
' S, T
lbl_cap.Caption = "S-T"
DoCmd.ApplyFilter "", "[NameA] Like ""[ST]*"""

Case 10
' U, V
lbl_cap.Caption = "U-V"
DoCmd.ApplyFilter "", "[NameA] Like ""[UV]*"""

Case 11
' W, X
lbl_cap.Caption = "W-X"
DoCmd.ApplyFilter "", "[NameA] Like ""[WX]*"""

Case 12
' Y, Z
lbl_cap.Caption = "Y-Z"
DoCmd.ApplyFilter "", "[NameA] Like ""[YZ]*"""

Case 13
' Show all records.
lbl_cap.Caption = "All Active Employees"
DoCmd.ShowAllRecords

If (RecordsetClone.RecordCount = 0) Then
' If no records are returned for the selected letter, display a message.
Beep
MsgBox "There are no Active Employees for that letter selection.", vbInformation, "Personnel Manager"
' Show all records.
DoCmd.ShowAllRecords
' Press in the All button.
EmployeeNameFilters = 13
End If


EmployeeNameFilters_TabChange_Exit:
Exit Sub

EmployeeNameFilters_TabChange_Err:
MsgBox Error$
Resume EmployeeNameFilters_TabChange_Exit

End Select

End Sub

 

[BillPower]

evalesthy:

Thanks very much for the recommendation, it's very much appreciated. You cheered me up so much that I thought I'd take the time to demonstrate how with a few lines of code Wayne can get his Form up and running combining both FancyPrairie's and Carol57's Methods.

Wayne:

On your Form, create 28 Buttons, name 26 of them "A-Z" respectively, one of them "0-9" and the last one "All".

Paste this code into the Form's Module:

Private Function FilterMyForm()
Dim strFilter
strFilter = Screen.ActiveControl.Name
Me.Filter = "CompanyName Like '[" & strFilter & "]*'"
Me.FilterOn = True
Me!txtCustomFilter = vbNullString
End Function

Private Sub All_GotFocus()
DoCmd.ShowAllRecords
End Sub

Change CompanyName to the Field that you want to use for your Filter.

Compile and Save the Module. Close the Visual Basic Editor.

In the Form's Design View, aelect all your Buttons other than "All".

In the Properties Window scroll down until you see "On Got Focus", Type In:

=FilterMyForm()

This will save you having to do it 27 times.

Click on the Button "All", "[Event Procedure]" should appear in the "On Got Focus" Text Box, if not select it from the list. That's it.

An additional feature I like to give people is a Custom Filter, which will display all instances of a String typed into a Text Box, to do this:

Add a Text Box to your Form, call it "txtCustomFilter". Add this to the Form's Module:

Private Sub txtCustomFilter_AfterUpdate()
Me.Filter = "CompanyName Like '*" & Me!txtCustomFilter & "*'"
Me.FilterOn = True
Me!CompanyName.SetFocus
End Sub

Once again change CompanyName to your Field Name.

Compile and Save the Module. Close the Visual Basic Editor.

Check the AfterUpdate of the Text Box, should say "[Event Procedure]", if not select it from the list.

Finally, I don't want to get too Technical, naming Controls A, B, C etc doesn't Conform to Usual Naming Conventions, i.e. Button A should really be comA or similar to identify it as a Command Button if used in your Code. I don't want to give you bad habits. The alternatives here are to use either the "Caption" or "Tag" Properties of the Buttons. As long as they say A, B, C etc the Code above will work just the same. If you used the Tag Property you would change the line(s) in the above Code containing "Screen.ActiveControl.Name":

From:
Screen.ActiveControl.Name
To:
Screen.ActiveControl.Tag

Finally, I haven't included any Error Procedures, I'll leave that to your own discretion.

I've posted the DB I wrote this in at

http://www.wpower.fsbusiness.co.uk/

you are welcome to Download it to see how it's done. The File is called "FilterForm.zip"

Have a Good Weekend All

Bill

 

[pmo]

Thanks for the help. I will get to and make it work
Wayne

 

[FancyPrairie]

Here's yet another way: Set the OnClick property of each button to call a function and pass the function the letter you want to use:

OnClick...=Function EmployeeNameFilters_TabChange("[AB]&quot

Function EmployeeNameFilters_TabChange(strFilter as string)

Me.Filter = "NameA Like" & strFilter & "*"
Me.FilterOn = True

End Function

 

[fneily]

Create a form named Long List 02. Create a Command Button on the form that will call a form Letter Buttons. The following will be created on this form.

1. Create an Option Group, 2” by 2”, by using the Option Group button on the Toolbox toolbar. Name it ButtonFrame.
2. Add a Toggle Button to the group. The size of the Button will be .33” wide and .29” high. Keep note of the name Access assigns to the Button, eg. Toggle2, etc. This is important because we will use these names.
3. Use the Copy and Paste commands to make four copies of the button, and then arrange the buttons in a horizontal row. Make sure their names are in numeric order.
4. Copy all five buttons and then paste four more groups of five: move them into rows.
5. Copy one button and paste it so it is the 26th button on the bottom.
The buttons should be in numeric order – Toggle2, Toggle3, etc.

Create a List Box next to the Option Group. Again, make it about 2” by 2”. Name it Names.

Bring up the Forms property sheet and click on the Event tab. Click on the On Load box, click on the Build (…) button. If need be, select Code Builder.
After Private Sub_Form_Load(), type the following:

Private Sub Form_Load()
Dim Button
Dim R As DAO.Recordset, SQLText
SQLText = "SELECT Distinct Left([FieldName],1) " _
& "From [TableName];"
Set R = CurrentDb.OpenRecordset(SQLText)
For Button = 1 To 26
With Me("Toggle" & Button + 1) 'Subtract 1 From the First Toggle #
.Caption = Chr(Button + 64)
.OptionValue = Button + 64
End With
R.FindFirst R(0).Name & "= '" _
& Chr(Button + 64) & "'"
If R.NoMatch Then
'Me("Toggle" & Button + 1).Enabled = False
End If
Next
Me![ButtonFrame] = 65
SetListContents
End Sub


After typing the above, type this :

Sub SetListContents()
Dim SQLText
SQLText = "SELECT [FieldName] FROM [TableName] " _
& "WHERE [FieldName] LIKE '" & Chr(Me![ButtonFrame]) _
& "*';"
Me![Names].RowSource = SQLText
Me![Names].Requery
End Sub

While in VISUAL BASIC, click the dropdown arrow next the the General Box. Select Buttonframe.
In the next box, select AFTERUPDATE.
Then type:
Private Sub ButtonFrame_AfterUpdate()
SetListContents
End Sub

Bring up the property sheet for the List Box. Click the Event tab and click on On Dbl Click. Click on the Build(…) button and select Code Builder. Type the following:

Private Sub Names_DblClick(Cancel As Integer)
Dim R As DAO.Recordset
Set R = Forms![FormName].RecordsetClone
R.FindFirst "[FieldName] = '" & Me![Names] & _
"'"
Forms![customer info form].Bookmark = R.Bookmark
DoCmd.Close acForm, "Form with Buttons Name"
End Sub

Neil

 

[BillPower]

There's an additional Access97 version at

http://www.wpower.fsbusiness.co.uk/

you are welcome to Download it to see how it's done. The File is called "FilterForm.zip"

Bill

 

[redapples]

Thought I would throw my hat in the ring on this one too.
I prefer the tab control to do this as I find one control easier to manage than 27 differet buttons.

My control has 0.725cm as the tab width which lets it fit on a screen 800X600, but I have also used a larger tab and set the control multi row.

the code for it is as follows

Private Sub tbctlAlpha_Change()
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim iAlpha As Integer
    Dim strAlpha As String
    
On Error GoTo HandleErr
    iAlpha = tbctlAlpha + 1
    Echo False
    stDocName = "Form1"
    If iAlpha < 27 Then
        strAlpha = Choose(iAlpha, &quot;a&quot;, &quot;b&quot;, &quot;c&quot;, &quot;d&quot;, &quot;e&quot;, &quot;f&quot;, &quot;g&quot;, &quot;h&quot;, &quot;i&quot;, &quot;j&quot;, &quot;k&quot;, &quot;l&quot;, _
        &quot;m&quot;, &quot;n&quot;, &quot;o&quot;, &quot;p&quot;, &quot;q&quot;, &quot;r&quot;, &quot;s&quot;, &quot;t&quot;, &quot;u&quot;, &quot;v&quot;, &quot;w&quot;, &quot;x&quot;, &quot;y&quot;, &quot;z&quot;)
        stLinkCriteria = &quot;left([SECONDNAME],1) = &quot;&quot;&quot; & strAlpha & &quot;&quot;&quot;&quot;
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    ElseIf iAlpha = 27 Then
    DoCmd.OpenForm stDocName
    Else
        stLinkCriteria = &quot;[IndId]&quot;
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
    
    
    Echo True
ExitHere:
    Exit Sub

' Error handling block added by Error Handler Add-In. DO NOT EDIT this block of code.
' Automatic error handler last updated at 06-10-2003 14:02:12   'ErrorHandler:$$D=06-10-2003    'ErrorHandler:$$T=14:02:12
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox &quot;Error &quot; & Err.Number & &quot;: &quot; & Err.Description, vbCritical, &quot;Form_Form1.tbctlAlpha_Change&quot;   'ErrorHandler:$$N=Form_Form1.tbctlAlpha_Change
    End Select
' End Error handling block.
End Sub

I use the Choose statement as i find it requires less coding than the select case version earlier in the threas.

I have also used this with an '*' tab for contacts without a second name but could also be used for companies with a number at the start of there name.

All good suggestions above though.

 

[MichaelRed]

While the various suggestions are direct answers to the question, they will -in general- require a great deal of 'real estate' on the form. For the more simplistic apps, this is perhaps not an issue. In other situations, the space required can be significantly reduced by using a combobox with a simple list for the source. This would also generally simplify the code, as the would be no need for the Case (or Choose) statements.

Another 'enhancement' to the overll process would be to reduce hte number of "selections" (a-la Carol57's approach, however I would go the one step further and set up hte segments according to a distribuion schema (How many names actually start with ("Q", "X", "Y", "Z", ...) as opposed to the more popular ("R", "S", "T", "L", ...). You can find various 'distributions', which do depend to some degree on the locale (or language), or -for relatively static data- generate one from your own list.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[pmo]

Thanks. I am on holidays for a few weeks so I will give it a try on my return. Michael I like the idea of reducing the selections. Thanks

Wayne

 

[redapples]

Not sure if I agree about the real estate issue, on a form I built I have used a tab control that is 20cm wide and 1cm high inside the Form Header. This contained A-Z, '*' for anomalies and ALL. I don’t think that is too much space to use particularly as this presents the user with a convenient way to filter the records in the form that mirrors other environments (e.g. a card file address book). Similarly when users might not be especially computer literate combo boxes might not be the best visual representation of a filter. When designing the aforementioned form one user expressly asked for something to mimic their address book, I don’t think 20 square cm is too much real estate to meet that request.

I appreciate it is horses for courses on this issue though and concede that giving up too much room to "Q", "X", "Y", "Z", could be over kill, still does "P Q R" as one tab reduce real estate over 3 sensibly proportioned tabs?

 

[jdegeorge]

Billpower...enjoy the star, this is a great idea.

MichaelRed...How would you replace the 28 buttons with a CBO?

Jim DeGeorge

 

[MichaelRed]

Set the source type to list and enter the item(s) in the source. Either the "single" character genreally proposed, or as a group (a-la- the telephine "dials&quot aa I mentioned. Wheather the source is a 'button', 'tab' or ComboBox selected item makes little or no differenct to the remainder of the process.

Of course, with the ComboBox -and a bit MORE programming- you could make the source a table/query, with the users preferences in a table referenced by their UserName, and have as many different 'distributions' as their are user nomes, but that is -perhaps- a bit beyond the scope of what most will even attempt.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[jdegeorge]

Michael

I get it. Thanks, and enjoy the star!

Jim DeGeorge