Microsoft KB Archive/97413

= How To Copy Current Database Record into a Record Variable =

Article ID: 97413

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q97413



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.
 * 1) Dimension a variable of the user-defined type structure created in step 1.
 * 2) 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.
 * 3) 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        &quot;Copy&quot; 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.</li>  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 &quot;KERNEL&quot; (dest As Any, src As Any, ByVal                                    Size As Long)

</li>  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 = &quot;&quot;

'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

</li>  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 = &quot;AU_ID: &quot; & Format$(recAuthor.AU_ID) & Chr$(13) strVerify = strVerify & &quot;Author: &quot; & Trim(recAuthor.Author) MsgBox strVerify

End Sub

</li> 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 &quot;Copy&quot; button to copy the current author's information to the record variable and see contents of the record variable displayed in a MsgBox.</li></ol>

Keywords: kbhowto KB97413

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.