Creating an Access Database - Step 4: The Forms

Creating an Access Database - Step 4: The Forms - The Search Form

One of the requirements for this database is that I can search for a specific track, and find which records this track appears on. To do this, I will create a new form, which will have a text box to enter part of the name of the track, and a list box to display the resulting matches.

Start by creating a new form, and adding a text box to this form towards the top of the detail section. Call this text box txtTrack. Add a list box to the form, towards the bottom, and set the width of the form to almost the same as the screen, resizing the form as well. Call this list box lstResult. Set the number of columns to 3, with the widths 0cm;5cm;9cm, and don't set a row source. Finally, we need to add 2 command buttons beneath the list box, one to close the form, and one to clear the search results, called cmdClose and cmdClear respectively, with captions set to "&Close" and "&New Search", so that we can use keyboard shortcuts of Alt-C and Alt-N as an alternative to the mouse.

Now, we need to add some code behind the form, to get it to do what we want.

Option Compare Database
Option Explicit

'   Constant declarations

Dim strSQL As String
Const strSQL1 = "SELECT DISTINCT tblRecord.RecordID, tblBand.BandName AS Band, tblRecord.RecordName AS Record FROM" _
    & "(tblBand INNER JOIN tblRecord ON tblBand.BandID=tblRecord.BandID) ORDER BY tblBand.BandName, tblRecord.RecordName;"
Const strSQL2 = "SELECT DISTINCT tblRecord.RecordID, tblBand.BandName AS Band, tblRecord.RecordName AS Record," _
    & "tblTrack.TrackNumber AS Track, tlkpFormat.FormatName AS Format"
Const strSQL3 = " FROM tlkpFormat INNER JOIN ((tblBand INNER JOIN tblRecord ON tblBand.BandID = tblRecord.BandID)" _
    & " INNER JOIN tblTrack ON tblRecord.RecordID = tblTrack.RecordID) ON tlkpFormat.FormatID = tblRecord.FormatID"
Const strSQL4 = " WHERE tblTrack.TrackName LIKE "
Const strSQL5 = " ORDER BY tblBand.BandName, tblRecord.RecordName, tblTrack.TrackNumber, tlkpFormat.FormatName;"

Private Sub cmdClear_Click()
'   Code to reset the data displayed in the listbox and change the number of columns displayed.
    Me!lstResults.ColumnCount = 3
    Me!lstResults.ColumnWidths = "0cm;5cm;9cm"
    Me!lstResults.RowSource = strSQL1
End Sub

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Private Sub Form_Load()
'   Code to set the RowSource of the listbox when the form initially loads.
    Me!lstResults.RowSource = strSQL1
End Sub

Private Sub lstResults_DblClick(Cancel As Integer)
'   Code to open the Record form to the selected record.
    DoCmd.OpenForm "frmRecord", , , "[RecordID]=" & Forms!frmSearch!lstResults
End Sub

Private Sub txtTrack_AfterUpdate()
'   Code to call the Sub that builds the new RowSource for the list box.
    sBuildSQL
End Sub

Sub sBuildSQL()
'   Code that builds the new RowSource for the list box.
    If Not IsNull(Me!txtTrack) Then
        strSQL = strSQL2 & strSQL3 & strSQL4 & Chr(34) & "*" & Me!txtTrack & "*" & Chr(34) & strSQL5
        Me!lstResults.ColumnCount = 5
        Me!lstResults.ColumnWidths = "0cm;5cm;9cm;1cm;1cm"
        Me!lstResults.RowSource = strSQL
    End If
End Sub

A few notes on what the code does, and why I have done it this way:

I've created a Sub procedure, sBuildSQL() to update the RowSource of the list box, rather than leaving it in the AfterUpdate event for the text box, so that if I decide that I want other ways of searching for a record, I can just add the necessary code to the Sub, and call it from anywhere. An example might be if I only wanted to view a certain format in the search results.

I've put a double click event on the list box, so that we can easily view all the details for the selected record, which opens frmRecord where the RecordID field equals the RecordID value from the first (hidden) column from the list box.

Save this form as "frmSearch", and then close it.

Top

Last modified at 06/06/2006 15:01:41