Performing hastle free database searches using ID's.

Option Explicit
' The purpose of this example is to illustrate how to use ComboBoxes and ListBoxes with
' databases for searching capabilities. Visual BASIC applications with a Microsoft Access
' backend are prime candidates for the use of this technique. Microsoft Access has a field
' called Auto-Number. This Auto-Number field is a nice way to identify a unique row in a table
' if it's used as the Primary key. This Primary key will then relate to one or more records
' in one or more tables where this value stored would be the Foreign key.
'
' This example shows you how to use the ID to lookup the record instead of using the value in
' the ComboBox. There are several reasons for using the ID instead of the text.
'
' 1. Searching on Long Integers is much faster then searching on Text values.
'
' 2. Text values can contain non-printable characters or characters that interfere with
' Visual BASIC or the database's syntax. For example the dreaded ' in the data.
' If you've ever tried to lookup "O'Malley" in a database using SQL, you know what a
' nightmare that can be.
'
' 3. Typo's can keep the user from finding the correct record if a ComboBox or ListBox is
' not used.
'
' 4. Simplifies the code.
'
' Let's assume the relationship of Drummers --> Albums they played on. So we would have one
' drummer that played on many albums. Assume that we want a list of all albums that a drummer
' played on. Below we have a 1 to Many relationship between the Drummer table and the Album table.
' The tables are linked together on the DrummerID.
'
' Drummer Album
' --------- ------------
' DrummerID -1--\ AlbumID
' Drummer \--M- DrummerID
' Album
'
'
' So our SQL statement might look like this:
'
' SELECT Drummer.Drummer, Album.Album
' FROM Drummer INNER JOIN Album ON Drummer.DrummerID = Album.DrummerID
' WHERE (((Drummer.Drummer)="Neil Peart"));
'
' The first problem with this is that a Text field is used for the search. We could encounter
' tick marks in the data like this O'Malley or non-printable characters. The second issue is
' that because we're searching on the text, we have to join the two tables together which can
' degrade performance when many joins are involved. Now let's look at how we would use the ID
' to lookup the albums.
'
' The new SQL statement would look like this. After all, the DrummerID IS in BOTH tables.
'
' SELECT Album.Album From Album
' WHERE (((Album.DrummerID)=1));
'
' This query is much easier to work with and understand and will be more efficient since we're
' searching on Long Integer data field. The whole trick to this is being able to keep the ID
' with the Text Item. The code below will show you how to do this. While it dosn't connect to
' a database, it gives you an idea of how it would work if it were connected to a database.
'
' In order to make this demo work, add a ComboBox named cboDrummer and set it's Style property
' to '2-DropdownList' and a TextBox named txtItemData to a form.

Private Sub Form_Load()
With cboDrummer

'Add some famous drummers to the combobox.
'Add the Text to the box.
.AddItem "Neil Peart"

'Add the PK from the database to the ItemData array for Neil Peart.
'I chose random numbers 1, 5, 9, 12 and 25 to show you it doesn't matter
'what the number is you put in there, but that the number will always be
'associated with the text in the line above it. These values would be
'populated from an SQL statement like this: SELECT DrummerID, Drummer FROM Drummer.
'
'Then instead of using the hard coded numbers shown here, you would write:
'.AddItem rs!Drummer
'.ItemDate(.NewIndex) = rs!DrummerID
.ItemData(.NewIndex) = 1

.AddItem "Steve Smith"
.ItemData(.NewIndex) = 5

.AddItem "Keith Moon"
.ItemData(.NewIndex) = 9

.AddItem "John Bonhome"
.ItemData(.NewIndex) = 12

.AddItem "Dave Weckl"
.ItemData(.NewIndex) = 25
End With

cboDrummer.ListIndex = 0
End Sub

Private Sub cboDrummer_Click()
'Get the PK from the ItemData array in the ComboxBox for the item the user selected.
txtItemData.Text = cboDrummer.ItemData(cboDrummer.ListIndex)

'cboDrummer.ItemData(cboDrummer.ListIndex) is the part that returns the PK for you to
'do your search on in the database.
End Sub