MIC 110

Methods

[AddNew] [AddItem] [Delete] [Find] [Load/Unload] [Print] [PrintForm] [Refresh] [Update]

A link to built- in functions list.

AddNew

Creates a new record for an updatable Recordset object.

Syntax

recordset.AddNew

The recordset placeholder is an object variable that represents an updatable Recordset object to which you want to add a new record.

Remarks

After you call the AddNew method, the new record becomes the current record and remains current after you call the Update method. The new record may be added to the end of the Recordset (typically if the select query required extra sorting or filtering) or will appear "in-place" in the Recordset (with the exact location depending on what index is being used). It will remain visible.

If you call AddNew while editing the current record or while adding a new record, ADO calls the Update method to save any changes and then creates the new record.

In immediate update mode (the provider writes changes to the underlying data source once you call the Update method), calling the AddNew method without arguments sets the EditMode property to adEditAdd. ADO caches any field value changes locally. Calling the Update method posts the new record to the database and resets the EditMode property to adEditNone. If you pass the Fields and Values arguments, ADO immediately posts the new record to the database (no Update call is necessary); the EditMode property value does not change (adEditNone).

ADO always updates in immediate mode. Batch mode is not supported.

Example

This example uses the AddNew method to create a new record with the specified name.

Public Sub AddNewX()

   Dim cnn1 As ADODB.Connection
   Dim rstEmployees As ADODB.Recordset
   Dim strCnn As String
   Dim strID As String
   Dim strFirstName As String
   Dim strLastName As String
   Dim booRecordAdded As Boolean

   ' Open a connection.
   Set cnn1 = New ADODB.Connection
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;"
   cnn1.Open strCnn
      
   ' Open Employee table.
   Set rstEmployees = New ADODB.Recordset
   rstEmployees.CursorType = adOpenKeyset
   rstEmployees.LockType = adLockOptimistic
   rstEmployees.Open "employee", cnn1, , , adCmdTable

   ' Get data from the user. The employee ID must be formatted as 
   ' first, middle, and last initial, five numbers, then M or F to 
   ' signify the gender. For example, the employee id for Bill Sornsin
   ' would be "B-S55555M".
   strID = Trim(InputBox("Enter employee ID:"))
   strFirstName = Trim(InputBox("Enter first name:"))
   strLastName = Trim(InputBox("Enter last name:"))

   ' Proceed only if the user actually entered something
   ' for both the first and last names.
   If (strID <> "") And (strFirstName <> "") _
      And (strLastName <> "") Then

      rstEmployees.AddNew
      rstEmployees!emp_id = strID
      rstEmployees!fname = strFirstName
      rstEmployees!lname = strLastName
      rstEmployees.Update
      booRecordAdded = True

      ' Show the newly added data.
      MsgBox "New record: " & rstEmployees!emp_id & " " & _
         rstEmployees!fname & " " & rstEmployees!lname

   Else
      MsgBox "Please enter an employee ID, " & _
         "first name, and last name."
   End If
      
   ' Delete the new record because this is a demonstration.
   cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strID & "'"
      
   rstEmployees.Close
   cnn1.Close

End Sub

ActiveX Data Objects (ADO)

ActiveX Data Objects (ADO) enables you to write a client application to access and manipulate data in a data source through a provider. ADO is ideally suited to consume data exposed by OLE DB providers, such as those written with the Microsoft OLE DB Simple Provider Toolkit. ADO’s primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint.

By using the Toolkit with ADO, you build a foundation for implementing flexible data access strategies at a higher level. For example, you can combine ADO's ease of application programmability with the Simple Provider Toolkit's ease of developing providers to quickly build end-to-end, single, or multi-tiered applications that address your corporate, intranet, Internet, or enterprise-wide data access needs.

For more information about ADO, including a detailed description of the object model, ADO properties and methods, and complete code samples, refer to the ADO help file supplied with the DA SDK.

 

 

AddItem: AddItem for ListBox and AddItem for ComboBox

AddItem for ListBox:

The ListBox control allows users to view a list of items. To add one or more items to the ListBox control, use the AddItem method.

Format: theNameOfListBox.AddItem string(s)

For example, lstSummaryBox.AddItem "The current balance is : " & curCurBal & "."

Here, & sign concatenates the The current balance is : string and the content of curCurBal variable and . (the period).

The GUI:

The codes:

Option Explicit

Private Sub cmdAdd_Click()

Dim curCurBal As Currency

curCurBal = 100

lstSummaryBox.AddItem "The current balance is : " & curCurBal & "."

lstSummaryBox.AddItem "The current balance is : " & (curCurBal + 10) & "."

lstSummaryBox.AddItem "The current balance is : " & (curCurBal + 20) & "."

End Sub

The outcome:

Sample Form and Project





AddItem for ComboBox

ComboBox control is a combination of TextBox features and a drop-down list. To add one or more items to the ComboBox control, use the AddItem method.

Format: theNameComboBox.AddItem string(s)

For example, theNameOfListBox.AddItem string(s)

The GUI:

The codes:

Option Explicit

Private Sub cmdAdd_Click()

cboCurrency.Text = "Click on the drop-down arrow"

cboCurrency.AddItem "US Dollar"

cboCurrency.AddItem "Japanese Yen"

cboCurrency.AddItem "EU Euro"

' Once the user selects one of the items,

' the Text property of the ComboBox (cboCurrency) changes.

' So, you can use that information as:

' If cboCurrency.Text = "US Dollar" Then

' instruction(s)

' End If

End Sub

The outcome:

Sample Form and Project

 

Delete Method (ADO Recordset)

Deletes the current record or a group of records.

Syntax

recordset.Delete AffectRecords

Parameters

AffectRecords   An AffectEnum value that determines how many records the Delete method will affect. Can be one of the following constants.

Constant Description
adAffectCurrent Default. Delete only the current record.
adAffectGroup Delete the records that satisfy the current Filter property setting. You must set the Filter property to one of the valid predefined constants in order to use this option.

Remarks

Using the Delete method marks the current record or a group of records in a Recordset object for deletion. If the Recordset object doesn't allow record deletion, an error occurs. If you are in immediate update mode, deletions occur in the database immediately. Otherwise, the records are marked for deletion from the cache and the actual deletion happens when you call the UpdateBatch method. (Use the Filter property to view the deleted records.)

Retrieving field values from the deleted record generates an error. After deleting the current record, the deleted record remains current until you move to a different record. Once you move away from the deleted record, it is no longer accessible.

If you nest deletions in a transaction, you can recover deleted records with the RollbackTrans method. If you are in batch update mode, you can cancel a pending deletion or group of pending deletions with the CancelBatch method.

If the attempt to delete records fails because of a conflict with the underlying data (for example, a record has already been deleted by another user), the provider returns warnings to the Errors collection but does not halt program execution. A run-time error occurs only if there are conflicts on all the requested records.

Delete Method Example

This example uses the Delete method to remove a specified record from a Recordset.

Public Sub DeleteX()

   Dim rstRoySched As ADODB.Recordset
   Dim strCnn As String
   Dim strMsg As String
   Dim strTitleID As String
   Dim intLoRange As Integer
   Dim intHiRange As Integer
   Dim intRoyalty As Integer

   ' Open RoySched table.
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set rstRoySched = New ADODB.Recordset
   rstRoySched.CursorLocation = adUseClient
   rstRoySched.CursorType = adOpenStatic
   rstRoySched.LockType = adLockBatchOptimistic
   rstRoySched.Open "SELECT * FROM roysched " & _
      "WHERE royalty = 20", strCnn, , , adCmdText

   ' Prompt for a record to delete.
   strMsg = "Before delete there are " & _
      rstRoySched.RecordCount & _
      " titles with 20 percent royalty:" & vbCr & vbCr
   Do While Not rstRoySched.EOF
      strMsg = strMsg & rstRoySched!title_id & vbCr
      rstRoySched.MoveNext
   Loop
   strMsg = strMsg & vbCr & vbCr & _
      "Enter the ID of a record to delete:"
   strTitleID = UCase(InputBox(strMsg))

   ' Move to the record and save data so it can be restored.
   rstRoySched.Filter = "title_id = '" & strTitleID & "'"
   intLoRange = rstRoySched!lorange
   intHiRange = rstRoySched!hirange
   intRoyalty = rstRoySched!royalty

   ' Delete the record.
   rstRoySched.Delete
   rstRoySched.UpdateBatch

   ' Show the results.
   rstRoySched.Filter = adFilterNone
   rstRoySched.Requery
   strMsg = ""
   strMsg = "After delete there are " & _
      rstRoySched.RecordCount & _
      " titles with 20 percent royalty:" & vbCr & vbCr
   Do While Not rstRoySched.EOF
      strMsg = strMsg & rstRoySched!title_id & vbCr
      rstRoySched.MoveNext
   Loop
   MsgBox strMsg

   ' Restore the data because this is a demonstration.
   rstRoySched.AddNew
   rstRoySched!title_id = strTitleID
   rstRoySched!lorange = intLoRange
   rstRoySched!hirange = intHiRange
   rstRoySched!royalty = intRoyalty
   rstRoySched.UpdateBatch

   rstRoySched.Close

End Sub
Find Method (ADO)

Searches a Recordset for the record that satisfies the specified criteria. If the criteria is met, the recordset position is set on the found record; otherwise, the position is set on the end of the recordset.

Syntax

Find (criteria, SkipRows, searchDirection, start)

Parameters

criteria   A String containing a statement that specifies the column name, comparison operator, and value to use in the search.

SkipRows   An optional Long value, whose default value is zero, that specifies the offset from the current row or start bookmark to begin the search.

searchDirection   An optional SearchDirectionEnum value that specifies whether the search should begin on the current row or the next available row in the direction of the search. Its value can be adSearchForward or adSearchBackward. The search stops at the start or end of the recordset, depending on the value of searchDirection.

start   An optional Variant bookmark to use as the starting position for the search.

Remarks

The comparison operator in criteria may be ">" (greater than), "<" (less than), "=" (equal), or "like" (pattern matching).

The value in criteria may be a string, floating point number, or date. String values are delimited with single quotes (for example, "state = 'WA'"). Date values are delimited with "#" (number sign) marks (for example, "start_date > #7/22/97#").

If the comparison operator is "like", the string value may contain "*" (one or more occurrences of any character) or "_" (one occurrence of any character). (For example, "state like M_*" matches Maine and Massachusetts.)

Load/Unload

The Load method loads a form to the window during the execution of the program.

The Unload method unloads a form from the window during the execution of the progam.

The Show method is used to display the form on the window. Without the Show method, no window will be shown even though that form is active.

Here is an example:

Private Sub cmdLoadForm2_Click()

Unload Form3

Unload Form4

Load Form2

Form2.Show

End Sub

This procedure will unload other forms (Form 3 and 4) from the window, and load and display a new form (Form2).

 

Print

Prints text in the Immediate window. With the Printer object, prints text to the printer.

Syntax

object.Print [outputlist]           

e.g.1: Form1.Print "Hello"         ' prints Hello to the form window.

e.g.1: Printer.Print "Hello"         ' prints Hello to the printer.

The Print method syntax has the following object qualifier and part:

Part Description
object Optional. An object expression that evaluates to an object in the Applies To list.
outputlist Optional. Expression or list of expressions to print. If omitted, a blank line is printed.

The outputlist argument has the following syntax and parts:

{Spc(n) | Tab(n)} expression charpos

Part Description
Spc(n) Optional. Used to insert space characters in the output, where n is the number of space characters to insert.
Tab(n) Optional. Used to position the insertion point at an absolute column number where n is the column number. Use Tab with no argument to position the insertion point at the beginning of the next print zone.
expression Optional. Numeric expression or string expression to print.
charpos Optional. Specifies the insertion point for the next character. Use a semicolon (;) to position the insertion point immediately following the last character displayed. Use Tab(n) to position the insertion point at an absolute column number. Use Tab with no argument to position the insertion point at the beginning of the next print zone. If charpos is omitted, the next character is printed on the next line.

Remarks

Multiple expressions can be separated with either a space or a semicolon.

All data printed to the Immediate window is properly formatted using the decimal separator for the locale settings specified for your system. The keywords are output in the appropriate language for the host application.

For Boolean data, either True or False is printed. The True and False keywords are translated according to the locale setting for the host application.

Date data is written using the standard short date format recognized by your system. When either the date or the time component is missing or zero, only the data provided is written.

Nothing is written if outputlist data is Empty. However, if outputlist data is Null, Null is output. The Null keyword is appropriately translated when it is output.

For error data, the output is written as Error errorcode. The Error keyword is appropriately translated when it is output.

The object is required if the method is used outside a module having a default display space. For example an error occurs if the method is called in a standard module without specifying an object, but if called in a form module, outputlist is displayed on the form.

Note   Because the Print method typically prints with proportionally-spaced characters, there is no correlation between the number of characters printed and the number of fixed-width columns those characters occupy. For example, a wide letter, such as a "W", occupies more than one fixed-width column, and a narrow letter, such as an "i", occupies less. To allow for cases where wider than average characters are used, your tabular columns must be positioned far enough apart. Alternatively, you can print using a fixed-pitch font (such as Courier) to ensure that each character uses only one column.

Print Method Example

Using the Print method, this example displays the value of the variable MyVar in the Immediate window. Note that the Print method only applies to objects that can display text.

Dim MyVar
MyVar = "Come see me in the Immediate pane."
Debug.Print MyVar

Printer.Print

See also PrintForm method

PrintForm

Sends a bit-by-bit image of a Form object to the printer.

Syntax: object.PrintForm              e.g.: Form1.PrintForm

The object placeholder represents an object expression that evaluates to an object in the Applies To list. If object is omitted, the Form with the focus is assumed to be object.

Remarks

PrintForm prints all visible objects and bitmaps of the Form object. PrintForm also prints graphics added to a Form object or PictureBox control at run time if the AutoRedraw property is True when the graphics are drawn.

The printer used by PrintForm is determined by the operating system's Control Panel settings.

PrintForm Method Example

This example uses the PrintForm method to print the current form.  To try this example, paste the code into the Declarations section of a form. Place on the form any controls you want to see on the printed form, and then press F5 and click the form.

Private Sub Form_Click ()
   Dim Msg   ' Declare variable.
   On Error GoTo ErrorHandler   ' Set up error handler.
   PrintForm   ' Print form.
   Exit Sub
ErrorHandler:
   Msg = "The form can't be printed."
   MsgBox Msg   ' Display message.
   Resume Next
End Sub

See also Print method.

Refresh Method (ADO)

Updates the objects in a collection to reflect objects available from and specific to the provider.

Syntax

collection.Refresh

Remarks

The Refresh method accomplishes different tasks depending on the collection from which you call it.

Parameters

Using the Refresh method on a Command object's Parameters collection retrieves provider-side parameter information for the stored procedure or parameterized query specified in the Command object. The collection will be empty for providers that do not support stored procedure calls or parameterized queries.

You should set the ActiveConnection property of the Command object to a valid Connection object, the CommandText property to a valid command, and the CommandType property to adCmdStoredProc before calling the Refresh method.

If you access the Parameters collection before calling the Refresh method, ADO will automatically call the method and populate the collection for you.

Note   If you use the Refresh method to obtain parameter information from the provider and it returns one or more variable-length data type Parameter objects, ADO may allocate memory for the parameters based on their maximum potential size, which will cause an error during execution. You should explicitly set the Size property for these parameters before calling the Execute method to prevent errors.

Fields

Using the Refresh method on the Fields collection has no visible effect. To retrieve changes from the underlying database structure, you must use either the Requery method or, if the Recordset object does not support bookmarks, the MoveFirst method.

Properties

Using the Refresh method on a Properties collection of some objects populates the collection with the dynamic properties the provider exposes. These properties provide information about functionality specific to the provider beyond the built-in properties ADO supports.

Example

This example demonstrates using the Refresh method to refresh the Parameters collection for a stored procedure Command object.

Public Sub RefreshX()

   Dim cnn1 As ADODB.Connection
   Dim cmdByRoyalty As ADODB.Command
   Dim rstByRoyalty As ADODB.Recordset
   Dim rstAuthors As ADODB.Recordset
   Dim intRoyalty As Integer
   Dim strAuthorID As String
   Dim strCnn As String

   ' Open connection.
   Set cnn1 = New ADODB.Connection
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   cnn1.Open strCnn
      
   ' Open a command object for a stored procedure
   ' with one parameter.
   Set cmdByRoyalty = New ADODB.Command
   Set cmdByRoyalty.ActiveConnection = cnn1
   cmdByRoyalty.CommandText = "byroyalty"
   cmdByRoyalty.CommandType = adCmdStoredProc
   cmdByRoyalty.Parameters.Refresh
   
   ' Get paramater value and execute the command, 
   ' storing the results in a recordset.
   intRoyalty = Trim(InputBox("Enter royalty:"))
   cmdByRoyalty.Parameters(1) = intRoyalty
   Set rstByRoyalty = cmdByRoyalty.Execute()
      
   ' Open the Authors table to get author names for display.
   Set rstAuthors = New ADODB.Recordset
   rstAuthors.Open "authors", cnn1, , , adCmdTable
   
   ' Print current data in the recordset, adding
   ' author names from Authors table.
   Debug.Print "Authors with " & intRoyalty & " percent royalty"
   Do While Not rstByRoyalty.EOF
      strAuthorID = rstByRoyalty!au_id
      Debug.Print "   " & rstByRoyalty!au_id & ", ";
      rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
      Debug.Print rstAuthors!au_fname & " " & _
         rstAuthors!au_lname
      rstByRoyalty.MoveNext
   Loop

   rstByRoyalty.Close
   rstAuthors.Close
   cnn1.Close
   
End Sub
Update Method (ADO)

Saves any changes you make to the current record of a Recordset object.

Syntax

recordset.Update Fields, Values

Parameters

Fields   Optional. A Variant representing a single name or a Variant array representing names or ordinal positions of the field or fields you wish to modify.

Values   Optional. A Variant representing a single value or a Variant array representing values for the field or fields in the new record.

Remarks

Use the Update method to save any changes you make to the current record of a Recordset object since calling the AddNew method or since changing any field values in an existing record. The Recordset object must support updates.

To set field values, do one of the following:

  • Assign values to a Field object's Value property and call the Update method.

  • Pass a field name and a value as arguments with the Update call.

  • Pass an array of field names and an array of values with the Update call.

When you use arrays of fields and values, there must be an equal number of elements in both arrays. Also, the order of field names must match the order of field values. If the number and order of fields and values do not match, an error occurs.

If the Recordset object supports batch updating, then you can cache multiple changes to one or more records locally until you call the UpdateBatch method. If you are editing the current record or adding a new record when you call the UpdateBatch method, ADO will automatically call the Update method to save any pending changes to the current record before transmitting the batched changes to the provider.

If you move from the record you are adding or editing before calling the Update method, ADO will automatically call Update to save the changes. You must call the CancelUpdate method if you want to cancel any changes made to the current record or to discard a newly added record.

The current record remains current after you call the Update method.

 

Update and CancelUpdate Methods Example

This example demonstrates the Update method in conjunction with the CancelUpdate method.

Public Sub UpdateX()

   Dim rstEmployees As ADODB.Recordset
   Dim strOldFirst As String
   Dim strOldLast As String
   Dim strMessage As String

   ' Open recordset with names from Employee table.
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set rstEmployees = New ADODB.Recordset
   rstEmployees.CursorType = adOpenKeyset
   rstEmployees.LockType = adLockOptimistic
   rstEmployees.Open "SELECT fname, lname " & _
      "FROM Employee ORDER BY lname", strCnn, , , adCmdText

   ' Store original data.
   strOldFirst = rstEmployees!fname
   strOldLast = rstEmployees!lname
   ' Change data in edit buffer.
   rstEmployees!fname = "Linda"
   rstEmployees!lname = "Kobara"

   ' Show contents of buffer and get user input.
   strMessage = "Edit in progress:" & vbCr & _
      "  Original data = " & strOldFirst & " " & _
      strOldLast & vbCr & "  Data in buffer = " & _
      rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
      "Use Update to replace the original data with " & _
      "the buffered data in the Recordset?"

   If MsgBox(strMessage, vbYesNo) = vbYes Then
      rstEmployees.Update
   Else
      rstEmployees.CancelUpdate
   End If

   ' Show the resulting data.
   MsgBox "Data in recordset = " & rstEmployees!fname & " " & _
      rstEmployees!lname

   ' Restore original data because this is a demonstration.
   If Not (strOldFirst = rstEmployees!fname And _
         strOldLast = rstEmployees!lname) Then
      rstEmployees!fname = strOldFirst
      rstEmployees!lname = strOldLast
      rstEmployees.Update
   End If

   rstEmployees.Close

End Sub

This example demonstrates the Update method in conjunction with the AddNew method.

Public Sub UpdateX2()

   Dim cnn1 As ADODB.Connection
   Dim rstEmployees As ADODB.Recordset
   Dim strEmpID As String
   Dim strOldFirst As String
   Dim strOldLast As String
   Dim strMessage As String

   ' Open a connection.
   Set cnn1 = New ADODB.Connection
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   cnn1.Open strCnn

   ' Open recordset with data from Employee table.
   Set rstEmployees = New ADODB.Recordset
   rstEmployees.CursorType = adOpenKeyset
   rstEmployees.LockType = adLockOptimistic
   rstEmployees.Open "employee", cnn1, , , adCmdTable

   rstEmployees.AddNew
   strEmpID = "B-S55555M"
   rstEmployees!emp_id = strEmpID
   rstEmployees!fname = "Bill"
   rstEmployees!lname = "Sornsin"

   ' Show contents of buffer and get user input.
   strMessage = "AddNew in progress:" & vbCr & _
      "Data in buffer = " & rstEmployees!emp_id & ", " & _
      rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
      "Use Update to save buffer to recordset?"

   If MsgBox(strMessage, vbYesNoCancel) = vbYes Then
      rstEmployees.Update
      ' Go to the new record and show the resulting data.
      MsgBox "Data in recordset = " & rstEmployees!emp_id & ", " & _
          rstEmployees!fname & " " & rstEmployees!lname
   Else
      rstEmployees.CancelUpdate
      MsgBox "No new record added."
   End If

   ' Delete new data because this is a demonstration.
   cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strEmpID & "'"
   
   rstEmployees.Close

End Sub