How to Copy Current Database Record into a Record Variable

- Standard and Professional Editions of Microsoft Visual Basic for

  Windows, version 3.0

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:

1. 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.

2. Dimension a variable of the user-defined type structure created in step 1.

3. 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.

4. Use the hmemcpy() function to copy the contents of the string created in step 3 to the record variable created in step 2.

1. 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.

2. Add two text boxes (Text1 and Text2) to Form1

3. Add a data control (Data1) to Form1

4. Add a command button (Command1) to Form1.

5. 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
   
   

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

7. 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)
   
   

8. 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
   
   

9. 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
   
   

10. From the Run menu, choose Start (ALT, R, S), or press the F5 key

        to run the program.