Adding Editing and Deleting Records in a Recordset

source or the new record will be lost. This is called working with a disconnected recordset and is explained in detail in an upcoming example.

Updating an Existing Record with the Update Method

The Update method of the Recordset object updates the current record. If the Update method is used in conjunction with the AddNew method, the information from the empty record is moved to the end of the recordset.

rsRecordset.Update

If the recordset is connected to a data source, the changes are saved in the underlying data source. If the recordset is disconnected from the data source, the changes are saved in the local recordset and must be updated in Batch or other mode with the underlying data source or the changes will be lost.

Deleting an Existing Record with the Delete Method

The Delete method of the Recordset object deletes the current record.

rsRecordset.Delete

Again, if the recordset is connected to a data source, then the deletion occurs on the underlying data source. If the recordset is disconnected from the data source, the record is deleted from the local recordset and must later be removed from the underlying data source.

Try It Out Building an Unbound Contacts Form Using a Disconnected Recordset

Now, it's time to build a contacts form that is based on a disconnected recordset.

1. Select the frmContactsBound form you created previously, right-click, and select Copy from pop-up menu. Then, click Paste and name the new form frmContactsUnbound.

2. Open frmContactsUnbound. Delete the current code in the On Load event of the Form from the Event tab of the Properties dialog box or from the Visual Basic Editor.

3. Set the Record Selectors, Navigation Buttons, and Dividing Lines properties of the form to No, as shown in Figure 5.9.

4. Add seven command buttons to the form. Set the Name properties of each command button to cmdMoveFirst, cmdMovePrevious, cmdMoveNext, cmdMoveLast, cmdAddNew, cmdDelete, and cmdSaveAll, respectively. Change the Caption property of each command button to Move First, Move Previous, Move Next, Move Last, Add New, Delete, and Save Changes To Database, respectively. Change the Picture property of the four navigation buttons to existing pictures called Go To First 1, Go To Previous 1, Go To Next 1, and Go To Last 1, respectively. Instead of the picture buttons, you can just use text as the caption if desired. The form should look similar to the form illustrated in Figure 5.10.

5. Add the following code to the General Declarations section of the form.

Dim rsContacts As ADODB.Recordset Dim cnCh5 As ADODB.Connection Dim strConnection As String

6. Add the following code to the Form_Load event of the form.

Figure 5.9

31 frmContactsUnbound : Form QiSlQ

I

1 2 1 1 1 i ■ 1 ■ ■ ■ 1 - ■ ■ 6 ■

a

i i

1

J

Unbound

: |f rst Warn

Unbound

: : ¡Middle Na

rig ; : Unbound

: i-:-:-::::-:-::::-:-:-: ::::::

: tifc:: : :::

Unbound

: : A<jc!ress : : : :

Unbound

: : iadnress 2:: : :

Unbound

: |qty:: : :

Jnbound

: Staie::i:;Unbo i

|Zip: | Unbound

"i-:-:-::::-:-::"-:-Jnbound

: VirprkPhone?:

:: Home Philips ;

Jnbound

: : : : : : : : : : : : : : : : 1 : : : : : : : : : : : : : : ■

: loyptw;: :

Jnbound

I <

► H

Add New

Delete

Save All Changes To Database

< mm >

Private Sub Form_Load()

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & CurrentProject.Path & "\Ch5CodeExamples.mdb;"

'create a new connection instance and open it using the connection string Set cnCh5 = New ADODB.Connection cnCh5.Open strConnection

'create a new instance of a recordset Set rsContacts = New ADODB.Recordset

'set various properties of the recordset With rsContacts

'specify a cursortype and lock type that will allow updates .CursorType = adOpenKeyset .CursorLocation = adUseClient .LockType = adLockBatchOptimistic

'open the recordset based on tblContacts table using the existing connection .Open "tblContacts", cnCh5 'disconnect the recordset .ActiveConnection = Nothing End With

'if the recordset is empty

If rsContacts.BOF And rsContacts.EOF Then Exit Sub

Else

'move to the first record rsContacts.MoveFirst 'populate the controls on the form Call PopulateControlsOnForm End If

'close the database connection and release it from memory cnCh5.Close

Set cnCh5 = Nothing

End Sub

7. Add the following event procedures to the class module for the Form.

Private Sub cmdAddNew_Click()

'add a new record to the local disconnected recordset Call AddRecord

End Sub

Private Sub cmdDelete_Click()

'delete the current record from the local disconnected recordset Call DeleteRecord

End Sub

Private Sub cmdMoveFirst_Click()

'move to the first record in the local disconnected recordset Call MoveToFirstRecord

End Sub

Private Sub cmdMoveLast_Click()

'move to the last record in the local disconnected recordset Call MoveToLastRecord

End Sub

Private Sub cmdMoveNext_Click()

'move to the next record in the local disconnected recordset Call MoveToNextRecord

End Sub

Private Sub cmdMovePrevious_Click()

'move to the previous record in the local disconnected recordset Call MoveToPreviousRecord

End Sub

Private Sub cmdSaveAll_Click()

'save all changes made to the local disconnected recordset 'back to the database Call SaveAllRecords

End Sub

8. Add the following procedures to the class module for the form.

Sub AddRecord()

'add a new record to the local disconnected recordset rsContacts.AddNew

'commit the new empty record to the local disconnected recordset rsContacts.Update

'clear the current controls on the form so the 'user can fill in values for the new record Call ClearControlsOnForm

End Sub

Sub DeleteRecord()

'delete the record from the local disconnected recordset rsContacts.Delete

'commit the deletion to the local disconnected recordset rsContacts.Update

'move to the first record since the current one has been deleted rsContacts.MoveFirst

'populate the controls on the form Call PopulateControlsOnForm

End Sub

9. Add the following navigation procedures to the class module for the form.

Sub MoveToFirstRecord()

'before performing move operation, save the current record Call SaveCurrentRecord

'move to the first record in the local disconnected recordset If Not rsContacts.BOF And Not rsContacts.EOF Then rsContacts.MoveFirst

'populate the controls on the form with the current record Call PopulateControlsOnForm

End If

End Sub

Sub MoveToLastRecord()

'before performing move operation, save the current record Call SaveCurrentRecord

'move to the last record in the local disconnected recordset If Not rsContacts.BOF And Not rsContacts.EOF Then rsContacts.MoveLast

'populate the controls on the form with the current record Call PopulateControlsOnForm

End If

End Sub

Sub MoveToPreviousRecord()

'before performing move operation, save the current record Call SaveCurrentRecord

'move to the previous record in the local disconnected recordset 'if not already at the beginning If Not rsContacts.BOF Then rsContacts.MovePrevious

'populate the controls on the form with the current record Call PopulateControlsOnForm End If

End Sub

Sub MoveToNextRecord()

'before performing move operation, save the current record Call SaveCurrentRecord

'move to the next record in the local disconnected recordset 'if not already at the end If Not rsContacts.EOF Then rsContacts.MoveNext

'populate the controls on the form with the current record Call PopulateControlsOnForm End If

End Sub

10. Add the following procedures to the class module for the form.

Sub PopulateControlsOnForm()

'Populate the controls on the form with the values

of the

'current record in the local disconnected recordset

'Use the same field names as the tblContacts table

from

'which

it was generated.

If Not

rsContacts.BOF And Not rsContacts.EOF Then

Me.

txtLastName = rsContacts!txtLastName

Me.

txtFirstName = rsContacts!txtFirstName

Me.

.txtMiddleName = rsContacts!txtMiddleName

Me.

txtTitle = rsContacts!txtTitle

Me.

.txtAddress1 = rsContacts!txtAddress1

Me.

.txtAddress2 = rsContacts!txtAddress2

Me.

.txtCity = rsContacts!txtCity

Me.

.txtState = rsContacts!txtState

Me.

.txtZip = rsContacts!txtZip

Me.

.txtWorkPhone = rsContacts!txtWorkPhone

Me.

.txtHomePhone = rsContacts!txtHomePhone

Me.

.txtCellPhone = rsContacts!txtCellPhone

ElseIf

rsContacts.BOF Then

'past beginning of recordset so move to next record

rsContacts.MoveNext

ElseIf

rsContacts.EOF Then

'past end of recordset so move back to previous

record

rsContacts.MovePrevious

Sub ClearControlsOnForm()

'clear the values in the controls on the form Me.txtLastName = "" Me.txtFirstName = "" Me.txtMiddleName = "" Me.txtTitle = "" Me.txtAddress1 = "" Me.txtAddress2 = "" Me.txtCity = "" Me.txtState = "" Me.txtZip = "" Me.txtWorkPhone = "" Me.txtHomePhone = "" Me.txtCellPhone = ""

End Sub

Sub SaveCurrentRecord()

'save the values in the controls on the form to the current record 'in the local disconnected recordset. If Not rsContacts.BOF And Not rsContacts.EOF Then rsContacts!txtLastName = Me.txtLastName rsContacts!txtFirstName = Me.txtFirstName rsContacts!txtMiddleName = Me.txtMiddleName rsContacts!txtTitle = Me.txtTitle rsContacts!txtAddress1 = Me.txtAddress1 rsContacts!txtAddress2 = Me.txtAddress2 rsContacts!txtCity = Me.txtCity rsContacts!txtState = Me.txtState rsContacts!txtZip = Me.txtZip rsContacts!txtWorkPhone = Me.txtWorkPhone rsContacts!txtHomePhone = Me.txtHomePhone rsContacts!txtCellPhone = Me.txtCellPhone End If

End Sub

Sub SaveAllRecords()

'Save current record to local disconnected recordset Call SaveCurrentRecord

'create a new connection instance and open it using the connection string Set cnCh5 = New ADODB.Connection cnCh5.Open strConnection

'set the disconnected recordset to the reopened connection Set rsContacts.ActiveConnection = cnCh5

'save all changes in the local disconnected recordset back 'to the database rsContacts.UpdateBatch

'disconnect the recordset again Set rsContacts.ActiveConnection = Nothing

'close the database connection and release it from memory cnCh5.Close

Set cnCh5 = Nothing

End Sub

11. Add the following event procedure to the form.

Private Sub Form_Unload(Cancel As Integer)

Dim intResponse As Integer

'prompt the user to save changes

intResponse = MsgBox("Save All Changes To Database?",

vbYesNo)

If intResponse = vbYes Then

'save all local records in disconnected recordset

back

'to the database in a batch update

Call SaveAllRecords

ElseIf intResponse = vbNo Then

MsgBox "Unsaved changes were discarded."

End If

'release the recordset from memory

Set rsContacts = Nothing

End Sub

12. Save all changes in the Visual Basic Editor by selecting Save from the Toolbar.

13. Save all changes in the form from design view by selecting Save from the Toolbar.

14. Open the form in View mode so that a screen such as Figure 5.11 appears and displays some data.

15. Make changes to the values on the screen and then navigate to another record. When you return to the record, note whether the value is still changed.

16. Close the form without saving changes. See if the changes you previously made are present.

17. Make changes to the values on the screen and then select the Save All Changes To Database option.

Was this article helpful?

0 0

Responses

  • Miikka
    How cai i delete a current record in a form by using RecordSet in access?
    9 years ago

Post a comment