Customer Phone List

[Dr_Worm]

One of my work mates has created a customer list which is like the "Customer Phone List" in the "Northwind" Micro$oft sample database. It work fine as a stand alone form. But he has tried to insert it as a subform on a tab control, which doesn't work, and I understand why it doesn't work. I just need to work out how to get the customer list onto a sheet of the tab control.

So far I have told my work mate to have the customer list as a seperate form the is opened from a comand button.

Any help would be greatly appreciated.

 

[bat17]

It does not seem to like the macro, try the following code on the afterUpdate event of the CompanyNameFilters button group in the form footer. only had a quick check but should work

Private Sub CompanyNameFilters_AfterUpdate()
Select Case Me![CompanyNameFilters]
Case 1
            ' Filter for company names that start with A, À, Á, Â, Ã, or Ä.
            Me.Filter = "[CompanyName] Like ""[AÀÁÂÃÄ]*"""
Case 2
            ' B
            Me.Filter = "[CompanyName] Like ""B*"""
Case 3
            ' C or Ç
            Me.Filter = "[CompanyName] Like ""[CÇ]*"""
Case 4
            ' D
            Me.Filter = "[CompanyName] Like ""D*"""
Case 5
            ' E, È, É, Ê, or Ë
            Me.Filter = "[CompanyName] Like ""[EÈÉÊË]*"""
Case 6
            ' F
            Me.Filter = "[CompanyName] Like ""F*"""
Case 7
            ' G
            Me.Filter = "[CompanyName] Like ""G*"""
Case 8
            ' H
            Me.Filter = "[CompanyName] Like ""H*"""
Case 9
            ' I, Ì, Í, Î, or Ï
            Me.Filter = "[CompanyName] Like ""[IÌÍÎÏ]*"""
Case 10
            ' J
            Me.Filter = "[CompanyName] Like ""J*"""
Case 11
            ' K
            Me.Filter = "[CompanyName] Like ""K*"""
Case 12
            ' L
            Me.Filter = "[CompanyName] Like ""L*"""
Case 13
            ' M
            Me.Filter = "[CompanyName] Like ""M*"""
Case 14
            ' N, or Ñ
            Me.Filter = "[CompanyName] Like ""[NÑ]*"""
Case 15
            ' O, Ò, Ó, Ô, Õ, or Ö
            Me.Filter = "[CompanyName] Like ""[OÒÓÔÕÖ]*"""
Case 16
            ' P
            Me.Filter = "[CompanyName] Like ""P*"""
Case 17
            ' Q
            Me.Filter = "[CompanyName] Like ""Q*"""
Case 18
            ' R
            Me.Filter = "[CompanyName] Like ""R*"""
Case 19
            ' S or Š (S hacek)
            Me.Filter = "[CompanyName] Like ""[SŠ]*"""
Case 20
            ' T
            Me.Filter = "[CompanyName] Like ""T*"""
Case 21
            ' U, Ù, Ú, Û, or Ü
            Me.Filter = "[CompanyName] Like ""[UÙÚÛÜ]*"""
Case 22
            ' V
            Me.Filter = "[CompanyName] Like ""V*"""
Case 23
             ' W
            Me.Filter = "[CompanyName] Like ""W*"""
Case 24
            ' X
            Me.Filter = "[CompanyName] Like ""X*"""
Case 25
            ' Y, Ý, or ÿ
            Me.Filter = "[CompanyName] Like ""[YÝÿ]*"""
Case 26
            ' Z, Æ, Ø, or  Å
            Me.Filter = "[CompanyName] Like ""[ZÆØÅ]*"""
Case 27
            ' Show all records.
            Me.Filter = "[CompanyName] Like ""*"""
End Select
Me.FilterOn = True
        
        If (Me.RecordsetClone.RecordCount > 0) Then
            ' If records are returned for the selected letter, go to the CompanyName control.
            DoCmd.GoToControl "CompanyName"
            ' Stop the macro.
            Exit Sub
        End If
        If (Me.RecordsetClone.RecordCount = 0) Then
            ' If no records are returned for the selected letter, display a message.
            Beep
            MsgBox "There are no records for that letter.", vbInformation, "No Records Returned"
            ' Show all records.
            DoCmd.ShowAllRecords
            ' Press in the All button.
            Me.CompanyNameFilters = 27
        End If
End Sub

HTH

Peter[/code]

 

[Norie]

Why not replace all those select case statements with something like this:

Dim FilterArray
Dim CmpyFilter As String

FilterArray = Array("[AÀÁÂÃÄ]*", "B*", "[CÇ]*", "D*", "[EÈÉÊË]*", "F*", "G*", "H*", "[IÌÍÎÏ]*", _
            "J*", "K*", "L*", "M*", "[NÑ]*", "[OÒÓÔÕÖ]*", "P*", "Q*", "R*", "[SŠ]*", _
            "T*", "[UÙÚÛÜ]*", "V*", "W*", "X*", "[YÝÿ]*", "[ZÆØÅ]*", "*")

CmpyFilter = "[CompanyName] Like"""

CmpyFilter = CmpyFilter & FilterArray(Me![CompanyNameFilters] - 1) & ""

'Me![CompanyNameFilters] - 1 if Option Base 1 not set
'Me![CompanyNameFilters] if Option Base 1 set