| 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: | Control | Property Setting(s) | | Label | Caption = "Artist" | | ComboBox | Name = "cboArtist" | | Label | Caption = " Genre " | | ComboBox | Name = "cboGenre" | | Label | Caption = "CD Name" | | Textbox | Name = "txtCDName", MaxLength = 64 | | Label | Caption = "Year Released" | | Textbox | Name = "txtYear", MaxLength = 4 | | Label | Caption = "Price" | | Textbox | Name = "txtPrice" | | Menu | Name = "MenuBar1", NewButton = True (default value, but ensure) | Visually design the CD form to look similar to Figure 7.5. Figure 7.5. The CD form (frmCD) has a control for each user-editable column in the CD table. The menu control is added to allow the creation of a new CD record.  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 | 
