Embedded Visual Basic Windows Ce And Pocket Pc Mobile Applications [Electronic resources]

Implementing the CD Form

Now, build the CD form that you added at the beginning of the project. The CD form allows the user to edit an existing CD record or add a new CD record.

Designing the CD Form

The CD form (frmCD) has the following controls and nondefault property values:

Visually design the CD form to look similar to Figure 7.5.

Setting Up frmCD

The CD form (frmCD) uses two module-level variables:

Dim mlngCDId As Long      'stores the current CD primary key
Dim mlngArtistId As Long  'stores the current Artist primary key

Implementing CDOpen: The Entry Point to frmCD

Because the only function called from the main form (frmMain) is CDOpen, implement it first (see Listing 7.21).

Listing 7.21 Opening the CD Form

Public Function CDOpen( _
ByVal lngCDId As Long, _
ByVal lngArtistId As Long, _
lngFormMode As Long) As Boolean
On Error Resume Next
' set the default error return
CDOpen = False
' check to see if a CDId was received
If IsNumeric(lngCDId) Then
' set the module variable
mlngCDId = lngCDId
End If
' check to see if an artist was received
If IsNumeric(lngArtistId) Then
' set the module variable
mlngArtistId = lngArtistId
End If
' which mode are we in?
Select Case lngFormMode
Case CDFORM_MODE_EDIT
' Edit Mode
' Close this up so we can get an
' updatable version of just this record
If gobjCD.State = adStateOpen Then
gobjCD.Close
End If
' get the record
gobjCD.Open _
"SELECT * FROM cd WHERE CDId = " & mlngCDId, _
gobjConnection, _
adOpenForwardOnly, _
adLockOptimistic
' did we find it?
If gobjCD.EOF Then
Exit Function
End If
' let's refresh the form
If Not RefreshFormCD() Then
' something went wrong
MsgBox "Error refreshing form"
Exit Function
End If
Case CDFORM_MODE_NEW
' New CD
If Not CdNew() Then
MsgBox "Can not create a new CD."
Exit Function
End If
End Select
' show the form
Me.Show
End Function

CDOpen does the usual setup, and then checks the value of the CDId parameter (lngCDId). If a numeric value was passed, it sets the module variable to it. This value is used to retrieve and save the record.

' check to see if a CDId was received
If IsNumeric(lngCDId) Then
' set the module variable
mlngCDId = lngCDId
End If

A similar action is taken with the contents of the ArtistId parameter (lngArtistId). If the New button on the Menu is tapped, the new CD record will default to this artist.

' check to see if an artist was received
If IsNumeric(lngArtistId) Then
' set the module variable
mlngArtistId = lngArtistId
End If

A decision is then made via a Select Case statement as to whether the form should behave in "edit" or "add new" mode. Remember, the Const CDFORM_MODE_EDIT and CDFORM_MODE_NEW are defined in modGlobal.

' which mode are we in?
Select Case lngFormMode
Case CDFORM_MODE_EDIT
' Edit Mode
' Close this up so we can get an
' updatable version of just this record
If gobjCD.State = adStateOpen Then
gobjCD.Close
End If
' get the record
gobjCD.Open _
"SELECT * FROM cd WHERE CDId = " & mlngCDId, _
gobjConnection, _
adOpenForwardOnly, _
adLockReadOnly
' did we find it?
If gobjCD.EOF Then
Exit Function
End If
' let's refresh the form
If Not RefreshFormCD() Then
' something went wrong
MsgBox "Error refreshing form"
Exit Function
End If
Case CDFORM_MODE_NEW
' New CD
If Not CdNew() Then
MsgBox "Can not create a new CD."
Exit Function
End If
End Select

In edit mode, the CD Recordset (gobjCD) is immediately closed. Then, the particular record being edited is retrieved into the CD recordset.

The recordset LockType is still read-only (via adLockReadOnly enumeration value for the LockType parameter of the Open method). This would seem like an obvious place to open the recordset as an updatable LockType, but, in the SaveCD function, the record is retrieved again before updating.

I have found in my experience with eVB, it's best to count on maintaining the state of as few items as I can. In other words, there is currently no design reason here to not open the record set as updatable (adLockOptimistic). But, knowing eVB's encapsulation limitations, I find that it leads to more robust, maintainable codebase if the application never counts on that recordset to be in the same state when attempting to update the recordset. The recordset objects are constantly being reused. The application design might change in the sense that the CD recordset is manipulated in some manner that's not harmonious to the goals of opening the recordset updatable in the CDOpen function. This is one limitation to the encapsulation model of eVB and using global objects.

' Close this up so we can get an
' updatable version of just this record
If gobjCD.State = adStateOpen Then
gobjCD.Close
End If
' get the record
gobjCD.Open _
"SELECT * FROM cd WHERE CDId = " & mlngCDId, _
gobjConnection, _
adOpenForwardOnly, _
adLockReadOnly

CDOpen verifies the record was retrieved. If not, it exits the function returning an error value. If all is well, the RefreshFormCD function is called.

' did we find it?
If gobjCD.EOF Then
MsgBox "Error finding CD record."
Exit Function
End If
' let's refresh the form
If Not RefreshFormCD () Then
' something went wrong
MsgBox "Error refreshing form"
Exit Function
End If

Implementing the RefreshFormCD Function

RefreshFormCD is generic to handle both an edit of a record and new record (see Listing 7.22). It populates both the Artist and Genre comboboxes. If the artist or genre is known, it sets the combobox to the appropriate record. Then, it populates all the textboxes with the known values.

Listing 7.22 Setting the Values on the CD Form

Private Function RefreshFormCD() As Boolean
Dim lngIndex As Long
On Error Resume Next
RefreshFormCD = False
' add all artist records to
' the artist combobox
PopulateArtistCombo
' add all genre records to
' genre combobox
PopulateGenreCombo
' set the artist combo to no selected item
Me.cboArtist.ListIndex = -1
' check for valid value
If Not IsNull(gobjCD.Fields("ArtistId").Value) Then
If gobjCD.Fields("ArtistId").Value > 0 Then
' set the artist combobox
For lngIndex = 0 To Me.cboArtist.ListCount - 1
' is this the artist?
If Me.cboArtist.ItemData(lngIndex) = _
gobjCD.Fields("ArtistId").Value Then
' set the selected item
Me.cboArtist.ListIndex = lngIndex
' get out of here
Exit For
End If
Next
End If
End If
' set the genre combo to no selected item
Me.cboGenre.ListIndex = -1
' check for valid value
If Not IsNull(gobjCD.Fields("GenreId").Value) Then
If gobjCD.Fields("GenreId").Value > 0 Then
' set the genre combobox
For lngIndex = 0 To Me.cboGenre.ListCount - 1
If Me.cboGenre.ItemData(lngIndex) = _
gobjCD.Fields("GenreId").Value Then
Me.cboGenre.ListIndex = lngIndex
Exit For
End If
Next
End If
End If
' clear the textboxes
Me.txtCDName.Text = "
Me.txtYear.Text = "
Me.txtPrice.Text = "
' do we have a CD name?
If Not IsNull(gobjCD.Fields("CDName").Value) Then
' set the name
Me.txtCDName.Text = gobjCD.Fields("CDName").Value
End If
' do we have a valid year?
If Not IsNull(gobjCD.Fields("Year").Value) Then
If gobjCD.Fields("Year").Value <> 0 Then
' set the year
Me.txtYear.Text = gobjCD.Fields("Year").Value
End If
End If
' valid price
If Not IsNull(gobjCD.Fields("Price").Value <> 0) Then
If gobjCD.Fields("Price").Value <> 0 Then
' set the price
Me.txtPrice.Text = gobjCD.Fields("Price").Value
End If
End If
If Err.Number = 0 Then
RefreshFormCD = True
End If
End Function

Implementing the PopulateArtistCombo Function

The PopulateArtistCombo function closes, then opens the artist table with the artist recordset object (gobjArtist). After clearing the artist combobox (cboArtist), it repopulates it (see Listing 7.23). The artist recordset is scanned, adding an entry to the combobox for each artist record. After adding the record, it sets the appropriate item in the ItemData collection to the primary key value of the artist record.

Listing 7.23 Adding the Choices to the Artist Combobox

Private Function PopulateArtistCombo() As Boolean
On Error Resume Next
' set the error state for early exit
PopulateArtistCombo = False
' close the artist recordset
If gobjArtist.State = adStateOpen Then
gobjArtist.Close
End If
' open artist
gobjArtist.Open "artist", _
gobjConnection, _
adOpenForwardOnly, _
adLockReadOnly, _
adCmdTableDirect
' clear the current contents
' of the combobox
Me.cboArtist.Clear
' scan the recordset
Do While Not gobjArtist.EOF
' add the artist
Me.cboArtist.AddItem gobjArtist.Fields("ArtistName").Value
' set the ItemData to the primary key of the artist record
Me.cboArtist.ItemData(Me.cboArtist.NewIndex) = _
gobjArtist.Fields("ArtistId").Value
' next record
gobjArtist.MoveNext
Loop
' check for errors
If Err.Number = 0 Then
PopulateArtistCombo = True
End If
End Function

Implementing the PopulateGenreCombo Function

The PopulateGenreCombo function works exactly like the PopulateArtistCombo function, except it uses the genre table (see Listing 7.24).

Listing 7.24 Adding the Choices to the Genre Combo Box

Private Function PopulateGenreCombo() As Boolean
On Error Resume Next
' set the error state for early exit
PopulateGenreCombo = False
' close the genre recordset
If gobjGenre.State = adStateOpen Then
gobjGenre.Close
End If
' open genre
gobjGenre.Open "genre", _
gobjConnection, _
adOpenForwardOnly, _
adLockReadOnly, _
adCmdTableDirect
' clear the current contents
' of the combobox
Me.cboGenre.Clear
' scan the recordset
Do While Not gobjGenre.EOF
' add the artist
Me.cboGenre.AddItem gobjGenre.Fields("genreName").Value
' set the ItemData to the primary key of the artist record
Me.cboGenre.ItemData(Me.cboGenre.NewIndex) = _
gobjGenre.Fields("genreId").Value
' next record
gobjGenre.MoveNext
Loop
If Err.Number = 0 Then
PopulateGenreCombo = True
End If
End Function

Viewing the CD

At this point, your CD form should operate minimally. Start the application up and tap a CD record in the grid of the main form (frmMain). Does it bring up the CD form? Are the comboboxes populated?

Implementing the New Button on the Main Form

At this point, let's take a step back and implement the New button of the menu bar from the main form (frmMain):

Private Sub MenuBar1_NewClick()
' open the cd form with the current artist in new mode
frmCD.CDOpen Null, glngArtistId, CDFORM_MODE_NEW
End Sub

Implementing a New CD in the CD Form

In the CDOpen function (refer to Listing 7.21), there was a case of the Select Case block we didn't examine. Part of the functionality of creating a new CD from the main form is implemented in CDOpen within that Case statement:

Case CDFORM_MODE_NEW
' New CD
If Not CdNew() Then
MsgBox "Can not create a new CD."
Exit Function
End If

In the Case CDFORM_MODE_NEW statement, a call is made to the CdNew function, whose duty it is to temporarily append a record to the CD recordset object (see Listing 7.25),. A peculiar looking approach might be to append a new record, canceling it only a few lines later. This is just a "trick" to give RefreshFormCD the ability to generically handle new and edit records.

Listing 7.25 frmCD: Creating a New CD Record

Private Function CdNew() As Boolean
On Error Resume Next
' a bit different, since we have
' cleanup to do, we'll assume no
' error return value and set it at
' the end if there is an error
CdNew = True
' since it's a new record, it has no primary key
mlngCDId = Null
' if it currently does not adding
' new records, let's close it, so
' we can reopen it with AddNew support
If gobjCD.State = adStateOpen Then
If Not gobjCD.Supports(adAddNew) Then
gobjCD.Close
End If
End If
adOpenForwardOnly, adLockOptimistic, adCmdTableDirect
End If
' add a new record
gobjCD.AddNew
' set the artist id foreign key
gobjCD.Fields("ArtistId") = mlngArtistId
' we're going to refresh the form
' while we have this blank record
' on hand, this is a good way to
' keep that RefreshFormCD generic
If Not RefreshFormCD() Then
' error
CdNew = False
End If
' now that we refreshed the form, we
' need to cancel the update
gobjCD.CancelUpdate
' everything go ok?
If Err.Number <> 0 Then
' something did not go ok
CdNew = False
End If
End Function

Go Ahead, Make My CD

Your application should be able to not only display a particular record from the CD table, but be able to mimic creating a new one. Start your application and tap the New button on the main form. It should display a blank record, with the exception of setting the artist combobox (cboArtist) to the "current artist."

Implementing the SaveCD Function

The CD Library application saves CD records when users tap the OK button on the top-right of the form. By default, there is an OK button on every eVB form. Typically, on first edit of a form's code, eVB will put template code in the Form.OKClick event to stop execution of the application:

Private Sub Form_OKClick()
App.End
End Sub

You need to override this behavior in the CD form. The code in Listing 7.26 enables the CD form to call SaveCD on an OKClick event of the form.

Listing 7.26 Saving the CD Record

Private Sub Form_OKClick()
On Error Resume Next
' save the cd
If SaveCD() Then
' it's saved, let's get back
frmMain.Show
Else
' error - do not close form
MsgBox "Error saving record."
End If
End Sub

The OKClick event in Listing 7.26 calls the SaveCD function in Listing 7.27.

Listing 7.27 Committing the Record

Private Function SaveCD() As Boolean
Dim lngArtistId As Long
Dim lngGenreId As Long
On Error Resume Next
' because we may have some cleanup
' work to do, let's set the
' default return value to true
SaveCD = True
' do we have an existing or new genre
If Me.cboGenre.ListIndex >= 0 Then
' existing genre
' get the pk value
lngGenreId = Me.cboGenre.ItemData(Me.cboGenre.ListIndex)
Else
' new genre
' ask user to add new genre
If MsgBox("Would you like to add the genre: " _
& Me.cboGenre.Text, vbYesNo) = vbYes Then
' get a new genre pk value
lngGenreId = AddGenre(Me.cboGenre.Text)
' is it valid?
If lngGenreId < 0 Then
' outta here
MsgBox "Error adding genre."
SaveCD = False
Exit Function
End If
Else
' can't save without a genre
SaveCD = False
MsgBox "You can not save a CD without a genre."
Exit Function
End If
End If
' do we have an existing or new artist
If Me.cboArtist.ListIndex >= 0 Then
' existing genre
' get the pk value
lngArtistId = Me.cboArtist.ItemData(Me.cboArtist.ListIndex)
Else
' new genre
' ask user to add new genre
If MsgBox("Would you like to add the artist: " _
& Me.cboArtist.Text, vbYesNo) = vbYes Then
' get a new artist pk value
lngArtistId = AddArtist(Me.cboArtist.Text, lngGenreId)
' is it valid?
If lngArtistId < 0 Then
' outta here
SaveCD = False
MsgBox "Error adding artist."
Exit Function
End If
Else
' can't save without a artist
SaveCD = False
MsgBox "You can not save a CD without a artist."
Exit Function
End If
End If
' close the CD recordset, we're about to update
If gobjCD.State = adStateOpen Then
gobjCD.Close
End If
' check mode
If Not IsNull(mlngCDId) Then
'edit mode - let's get the record
gobjCD.Open "SELECT * FROM cd WHERE CDId = " _
& mlngCDId, gobjConnection, _
adOpenForwardOnly, adLockOptimistic
Else
' new mode, no primary key value
gobjCD.Open "cd", gobjConnection, adOpenForwardOnly, _
adLockOptimistic, adCmdTableDirect
' get a new pk value
mlngCDId = GetNewPK("CD")
' add the record
gobjCD.AddNew
' set the pk value
gobjCD.Fields("CDId").Value = mlngCDId
End If
' are we at the end of the
' recordset (no record added
' or no record retrieved)
If gobjCD.EOF Then
Exit Function
End If
' assign the values
gobjCD.Fields("ArtistID").Value = lngArtistId
gobjCD.Fields("CDName").Value = Me.txtCDName.Text
gobjCD.Fields("Year").Value = CLng(Me.txtYear.Text)
gobjCD.Fields("Price").Value = CCur(Me.txtPrice.Text)
gobjCD.Fields("GenreID").Value = lngGenreId
' update the recordset
gobjCD.Update
' check for errors
If Err.Number <> 0 Then
Exit Function
End If
' everything's cool, return true
SaveCD = True
End Function

Now examine SaveCD. This function determines whether the genre combobox is an existing or new genre. If the current genre combobox displays an existing genre, the ListIndex property of the combobox will be => 0. If ListIndex is < 0, it must be a genre not in the genre table.

If the genre already exists, the primary key value of that genre record is retained from the ItemData collection of the combobox:

' do we have an existing or new genre
If Me.cboGenre.ListIndex >= 0 Then
' existing genre
' get the pk value
lngGenreId = Me.cboGenre.ItemData(Me.cboGenre.ListIndex)
Else
' new genre
' ask user to add new genre
If MsgBox("Would you like to add the genre: " _
& Me.cboGenre.Text, vbYesNo) = vbYes Then
' get a new genre pk value
lngGenreId = AddGenre(Me.cboGenre.Text)
' is it valid?
If lngGenreId < 0 Then
' outta here
MsgBox "Error adding genre."
SaveCD = False
Exit Function
End If
Else
' can't save without a genre
SaveCD = False
MsgBox "You can not save a CD without a genre."
Exit Function
End If
End If

If the genre doesn't exist, the user is prompted by a MsgBox to add it. If the user agrees to add the genre, AddGenre is called with the value of the combobox's Text property. The return value of AddGenre (the new primary key value) is retained and checked for validity.

' do we have an existing or new genre
If Me.cboGenre.ListIndex >= 0 Then
' existing genre
' get the pk value
lngGenreId = Me.cboGenre.ItemData(Me.cboGenre.ListIndex)
Else
' new genre
' ask user to add new genre
If MsgBox("Would you like to add the genre: " _
& Me.cboGenre.Text, vbYesNo) = vbYes Then
' get a new genre pk value
lngGenreId = AddGenre(Me.cboGenre.Text)
' is it valid?
If lngGenreId < 0 Then
' outta here
MsgBox "Error adding genre."
SaveCD = False
Exit Function
End If
Else
' can't save without a genre
SaveCD = False
MsgBox "You can not save a CD without a genre."
Exit Function
End If
End If

If the genre doesn't exist and the user doesn't choose to add the genre, the return value for the SaveCD function is set to False and the function exits.

' do we have an existing or new genre
If Me.cboGenre.ListIndex >= 0 Then
' existing genre
' get the pk value
lngGenreId = Me.cboGenre.ItemData(Me.cboGenre.ListIndex)
Else
' new genre
' ask user to add new genre
If MsgBox("Would you like to add the genre: " _
& Me.cboGenre.Text, vbYesNo) = vbYes Then
' get a new genre pk value
lngGenreId = AddGenre(Me.cboGenre.Text)
' is it valid?
If lngGenreId < 0 Then
' outta here
MsgBox "Error adding genre."
SaveCD = False
Exit Function
End If
Else
' can't save without a genre
SaveCD = False
MsgBox "You can not save a CD without a genre."
Exit Function
End If
End If

The same structure of checking the combobox values and prompting the user takes place for the artist combobox:

' do we have an existing or new artist
If Me.cboArtist.ListIndex >= 0 Then
' existing genre
' get the pk value
lngArtistId = Me.cboArtist.ItemData(Me.cboArtist.ListIndex)
Else
' new genre
' ask user to add new genre
If MsgBox("Would you like to add the artist: " _
& Me.cboArtist.Text, vbYesNo) = vbYes Then
' get a new artist pk value
lngArtistId = AddArtist(Me.cboArtist.Text, lngGenreId)
' is it valid?
If lngArtistId < 0 Then
' outta here
SaveCD = False
MsgBox "Error adding artist."
Exit Function
End If
Else
' can't save without a artist
SaveCD = False
MsgBox "You can not save a CD without a artist."
Exit Function
End If
End If

Now, SaveCD should have all the values needed to save the CD record. The CD recordset object is closed.

' close the CD recordset, we're about to update
If gobjCD.State = adStateOpen Then
gobjCD.Close
End If

The check of mlngCDId for Null determines whether the record to save is a new CD record or a CD record edit. If the value of mlngCDId is Not Null, the record being saved is an edit, otherwise it's a new record.

' check mode
If Not IsNull(mlngCDId) Then
'edit mode - let's get the record
gobjCD.Open "SELECT * FROM cd WHERE CDId = " _
& mlngCDId, gobjConnection, _
adOpenForwardOnly, adLockOptimistic
Else
' new mode, no primary key value
gobjCD.Open "cd", gobjConnection, adOpenForwardOnly, _
adLockOptimistic, adCmdTableDirect
' get a new pk value
mlngCDId = GetNewPK("CD")
' add the record
gobjCD.AddNew
' set the pk value
gobjCD.Fields("CDId").Value = mlngCDId
End If

If the record is an edit, the CD recordset object is opened via a SQL statement. The table is filtered down to one record using the WHERE clause and the value of mlngCDId. The LockType is adLockOptimistic.

'edit mode - let's get the record
gobjCD.Open "SELECT * FROM cd WHERE CDId = " _
& mlngCDId, gobjConnection, _
adOpenForwardOnly, adLockOptimistic

If the record is a new record, the CD table is opened directly, again using LockType of adLockOptimistic. Also, notice the optional parameter of adCmdTableDirect. This is why the source is only the table name.

' new mode, no primary key value
gobjCD.Open "cd", gobjConnection, adOpenForwardOnly, _
adLockOptimistic, adCmdTableDirect

Because this is new CD record, it needs a primary key value. GetNewPK provides this new primary key value:

' get a new pk value
mlngCDId = GetNewPK("CD")

A new record is added:

' add the record
gobjCD.AddNew

Set the primary key value on this new record:

' set the pk value
gobjCD.Fields("CDId").Value = mlngCDId

Now that the different actions have been taken for a new or edited CD record, the rest of the function to save the record is the same, for both Add and Edit.

A check is made to ensure you have a record, whether that record should be an appended record (for new) or a single record (for an edit):

' are we at the end of the
' recordset (no record added
' or no record retrieved)
If gobjCD.EOF Then
Exit Function
End If

The values are assigned to the Field objects of the Fields collection:

' assign the values
gobjCD.Fields("ArtistID").Value = lngArtistId
gobjCD.Fields("CDName").Value = Me.txtCDName.Text
gobjCD.Fields("Year").Value = CLng(Me.txtYear.Text)
gobjCD.Fields("Price").Value = CCur(Me.txtPrice.Text)
gobjCD.Fields("GenreID").Value = lngGenreId

The recordset object is updated, sending the data back to the data store. A check for errors and SaveCD is done:

' update the recordset
gobjCD.Update
' check for errors
If Err.Number <> 0 Then
Exit Function
End If
' everything's cool, return true
SaveCD = True