Copying the Current Database Record Into a Record Variable
See Also
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.
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.
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),
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.
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.