How To Copy Current Database Record into a Record Variable (97413)

SUMMARY

Although Visual Basic version 3.0 for Windows does not provide a direct way to assign the current database record to a record variable, this article gives you a generic routine. Using this generic routine, you can assign the current record, containing any number of fields, to a record variable that represents the structure of the current database record.

This generic routine is useful if you have existing database code that uses record variables to represent database records. For example, using this routine, you can use the Visual Basic data access features without making major changes to how you read and handle records. After you assign the contents of the current record to a record variable of the appropriate type, your code can manipulate the record as before, independent of the underlying database.

The routine demonstrated below requires Windows version 3.1 or later because it uses the Windows API function hmemcpy(), which was introduced in Windows version 3.1. An error will result on the call to hmemcpy() if you attempt to run the sample using Windows version 3.0.

MORE INFORMATION

Follow these general steps to assign the current database record to a record variable:

Define a Type ... End Type structure that represents the record structure of the database table that you are going to use. This requires that the number and data types of the fields in the table be known in advance.

To determine the structure of the table quickly, run the Data Manager tool provided with Visual Basic. From the Data Manager File menu, choose Open to open the database. Select a Table from the list displayed in the Database window, and choose the Design button to see the table's field names, data types, and field lengths.
Dimension a variable of the user-defined type structure created in step 1.
Create a generic routine using the Windows API hmemcpy() function to copy each field of the current database record into a string. To do this, step through all of the fields in the Fields collection and accumulate the fields together into a single string.
Use the hmemcpy() function to copy the contents of the string created in step 3 to the record variable created in step 2.

Perform the following steps to create an example application that demonstrates how to copy the current database record into a user-defined structure. This example shows you how to use the Data control to copy a record from the BIBLIO.MDB sample database provided with Visual Basic.

Start Visual Basic or from the File menu, choose New Project (ALT, F, N) if Visual Basic is already running. Form1 is created by default.
Add two text boxes (Text1 and Text2) to Form1
Add a data control (Data1) to Form1
Add a command button (Command1) to Form1.

Using the following table as a guide, set the properties of the controls you added in steps 2, 3 and 4:

   Control Name   Property       New Value    Comment
   ------------------------------------------------------------------
   Command1       Caption        "Copy"
   Data1          DatabaseName   BIBLIO.MDB   You will also need to
                                              provide the full path to
                                              this file, which should
                                              be in your Visual Basic
                                              directory C:\VB
   Data1          RecordSource   Authors
   Text1          DataSource     Data1
   Text1          DataField      AU_ID
   Text2          DataSource     Data1
   Text2          DataField      Author

From the File menu, choose New Module (ALT, F, M). Module1 is created.

Add the following code to the general declarations section of Module1:

   Type typeAuthor
      AU_ID As Long
      Author As String * 255
   End Type
   ' Enter the following Declare on a single line:
   Declare Sub hmemcpy Lib "KERNEL" (dest As Any, src As Any, ByVal
                                     Size As Long)

Add the following code to Module1:

   Function GetCurrRec (ds As Dynaset) As String

      Dim i As Integer
      Static FieldStr As String
      Static recStr As String

      recStr = ""

      'Step through each field in the current record and accumulate
      'the contents of each field into a string
      For i = 0 To ds.Fields.Count - 1

         'Pad out to the right size
         FieldStr = Space(ds.Fields(i).Size)

         Select Case ds.Fields(i).Type

            'Copy the binary representation of the field to a
            'string (FieldStr)

            Case 1, 2       'Bytes
               hmemcpy ByVal FieldStr, CInt(ds.Fields(i).Value),
                                                     ds.Fields(i).Size

            Case 3          'Integers
               hmemcpy ByVal FieldStr, CInt(ds.Fields(i).Value),
                                                     ds.Fields(i).Size

            Case 4          'Long integers
               hmemcpy ByVal FieldStr, CLng(ds.Fields(i).Value),
                                                     ds.Fields(i).Size

            Case 5          'Currency
               hmemcpy ByVal FieldStr, CCur(ds.Fields(i).Value),
                                                     ds.Fields(i).Size

            Case 6          'Singles
               hmemcpy ByVal FieldStr, CSng(ds.Fields(i).Value),
                                                     ds.Fields(i).Size

            Case 7, 8       'Doubles
               hmemcpy ByVal FieldStr, CDbl(ds.Fields(i).Value),
                                                     ds.Fields(i).Size

            Case 9, 10      'String types
               hmemcpy ByVal FieldStr, ByVal CStr(ds.Fields(i).Value),
                                                Len(ds.Fields(i).Value)

            Case 11, 12     'Memo and long binary
              FieldStr = ds.Fields(i).GetChunk(0, ds.Fields(i).FieldSize())

         End Select

         'Accumulate the field string into a record string
         recStr = recStr & FieldStr

      Next

      'Return the accumulated string containing the contents of all
      'fields in the current record
      GetCurrRec = recStr

   End Function

Add the following code to the Command1_Click event in Form1:

   Sub Command1_Click ()

      Dim recAuthor As typeAuthor
      Dim strCurrRec As String
      Dim strVerify As String

      'Copy the current record in the Authors table to a string
      strCurrRec = GetCurrRec(Data1.RecordSet)

      'Copy the string to the record variable that has a structure
      'matching the struture of the current record in the Authors table
      hmemcpy recAuthor, ByVal strCurrRec, Len(recAuthor)

      'Verify that the correct results were returned by displaying
      'the contents of the record variable
      strVerify = "AU_ID: " & Format$(recAuthor.AU_ID) & Chr$(13)
      strVerify = strVerify & "Author: " & Trim(recAuthor.Author)
      MsgBox strVerify

   End Sub

From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program. Click the scroll bar of the Data control to select an author. The Text1 box displays the author ID, and the Text2 box displays the author's name. Click the "Copy" button to copy the current author's information to the record variable and see contents of the record variable displayed in a MsgBox.