Copying the Current Database Record Into a Record Variable

See AlsoVMZ7S4


This article is reprinted from the Microsoft Knowledge Base.  To view the article, maximize your help window.  This information applies to Visual Basic for Windows, versions 2.0 and 3.0.




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.


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.


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



            Case 3          'Integers

               hmemcpy ByVal FieldStr, CInt(ds.Fields(i).Value),



            Case 4          'Long integers

               hmemcpy ByVal FieldStr, CLng(ds.Fields(i).Value),



            Case 5          'Currency

               hmemcpy ByVal FieldStr, CCur(ds.Fields(i).Value),



            Case 6          'Singles

               hmemcpy ByVal FieldStr, CSng(ds.Fields(i).Value),



            Case 7, 8       'Doubles

               hmemcpy ByVal FieldStr, CDbl(ds.Fields(i).Value),



            Case 9, 10      'String types

               hmemcpy ByVal FieldStr, ByVal CStr(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




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


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.