VBA: Contact Rolodex - Fun add to customer management database

This idea came from VBA for Dummies book. Contact rolodex that can be created inside any database or as a stand alone database application. This one specifically captures all business, customer, supplier, and company information that is used by a small business. These are the steps I took

1. Created a Multiple Items Form from a tblContacts that I have created (See Design View pic)

2. Customized the form with the fields I wanted to list and buttons on top that link to other database functions. I then added a button with the text Show Details that will take the user to more specific customer information and order history.

3. Added a Option Group box with 27! toggle buttons.

4. I then wrote the VBA code to be fired from a AfterUpdate Event, here it is:

Private Sub Rolodex_AfterUpdate()

'Variable to hold filtered SQL string

Dim strFilterSQL As String

'Set default records source of form

Const strSQL = "SELECT ContactID, ContactTypeID,Company,LastName,FirstName,Phone,Email FROM tblContacts "


Select Case Me.Rolodex


'Filter record source on selected option

Case 1

strFilterSQL = strSQL & "WHERE [Company] Like 'A*' OR [LastName] Like 'A*' ;"

Case 2

strFilterSQL = strSQL & " Where [Company] Like 'B*' OR [LastName] Like 'B*';"

Case 3

strFilterSQL = strSQL & " Where [Company] Like 'C*' OR [LastName] Like 'C*';"

Case 4

strFilterSQL = strSQL & " Where [Company] Like 'D*' OR [LastName] Like 'D*';"

Case 5

strFilterSQL = strSQL & " Where [Company] Like 'E*' OR [LastName] Like 'E*';"

Case 6

strFilterSQL = strSQL & " Where [Company] Like 'F*' OR [LastName] Like 'F*';"

Case 7

strFilterSQL = strSQL & "WHERE [Company] Like 'G*' OR [LastName] Like 'G*' ;"

Case 8

strFilterSQL = strSQL & " Where [Company] Like 'H*' OR [LastName] Like 'H*';"

Case 9

strFilterSQL = strSQL & " Where [Company] Like 'I*' OR [LastName] Like 'I*';"

Case 10

strFilterSQL = strSQL & " Where [Company] Like 'J*' OR [LastName] Like 'J*';"

Case 11

strFilterSQL = strSQL & " Where [Company] Like 'K*' OR [LastName] Like 'K*';"

Case 12

strFilterSQL = strSQL & " Where [Company] Like 'L*' OR [LastName] Like 'L*';"

Case 13

strFilterSQL = strSQL & "WHERE [Company] Like 'M*' OR [LastName] Like 'M*' ;"

Case 14

strFilterSQL = strSQL & " Where [Company] Like 'N*' OR [LastName] Like 'N*';"

Case 15

strFilterSQL = strSQL & " Where [Company] Like 'O*' OR [LastName] Like 'O*';"

Case 16

strFilterSQL = strSQL & " Where [Company] Like 'P*' OR [LastName] Like 'P*';"

Case 17

strFilterSQL = strSQL & " Where [Company] Like 'Q*' OR [LastName] Like 'Q*';"

Case 18

strFilterSQL = strSQL & " Where [Company] Like 'R*' OR [LastName] Like 'R*';"

Case 19

strFilterSQL = strSQL & "WHERE [Company] Like 'S*' OR [LastName] Like 'S*' ;"

Case 20

strFilterSQL = strSQL & " Where [Company] Like 'T*' OR [LastName] Like 'T*';"

Case 21

strFilterSQL = strSQL & " Where [Company] Like 'U*' OR [LastName] Like 'U*';"

Case 22

strFilterSQL = strSQL & " Where [Company] Like 'V*' OR [LastName] Like 'V*';"

Case 23

strFilterSQL = strSQL & " Where [Company] Like 'W*' OR [LastName] Like 'W*';"

Case 24

strFilterSQL = strSQL & " Where [Company] Like 'X*' OR [LastName] Like 'X*';"

Case 25

strFilterSQL = strSQL & " Where [Company] Like 'Y*' OR [LastName] Like 'Y*';"

Case 26

strFilterSQL = strSQL & " Where [Company] Like 'Z*' OR [LastName] Like 'Z*';"

Case 27

strFilterSQL = strSQL & ";"



'If filter applied with no option selected use default record source

Case Else

strFilterSQL = strSQL & ";"

End Select


' Set record source with filtered SQL

Me.RecordSource = strFilterSQL

Me.Requery
End Sub
 
 
Here is the completed form.. with names boxed out, lol... Easy to use, pretty fun little add to an application.