Hi,
I have the code below that would copy data from one table (rsSource) into
another table (rsTarget). Is there a loop code that would enable me to do
something like the following:
Loop for N =1 till 40
If rsSource![ResultN] <> "" Then
rsTarget.Edit
rsTarget![DateN] = rsSource![DateN]
rsTarget![ResultN] = rsSource![ResultN]
rsTarget.Update
End IF
{This is a database on patient visits. For each visitN, I have DateN,
CholesterolN,GlucoseN,... and ~9 other fields. I am trying to see if there
is
a loop that would save me time from having to write all of this in the
code}.
Thanks in advance.
*************************************************
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim lngcount as lnteger
Set db = CurrentDb()
Set rsSource = db.OpenRecordset("SourceTable")
Set rsTarget = db.OpenRecordset("TargetTable")
rsSource.MoveFirst
lngcount = rsTarget.RecordCount
If lngcount = 0 Then
rsTarget.AddNew
rsTarget![Patient#] = rsSource![Patient#]
rsTarget.Update
End If
Do Until rsSource.EOF
'Search for matching record based on primary key field which is Patient#
rsTarget.Index = "PrimaryKey"
rsTarget.Seek "=", rsSource![Patient#]
If rsTarget.NoMatch Then ' .NoMatch is FALSE if Patient# found
rsTarget.AddNew
rsTarget![Patient#] = rsSource![Patient#]
rsTarget.Update
End If
rsTarget.Index = "PrimaryKey"
rsTarget.Seek "=", rsSource![Patient#]
If rsSource![Result1] <> "" Then
rsTarget.Edit
rsTarget![Date1] = rsSource![Date1]
rsTarget![Result1] = rsSource![Result1]
'will also need to assign values for 9 other fields called
Cholesterol1,Glucose1,...
rsTarget.Update
End If
If rsSource![Result2] <> "" Then
rsTarget.Edit
rsTarget![Date2] = rsSource![Date2]
rsTarget![Result2] = rsSource![Result2]
'will also need to assign values for 9 other fields called
Cholesterol2,Glucose2,...
rsTarget.Update
End If
‘I need to enter similar If statements for Result3, Result4,…Result40,
rsSource.MoveNext
Loop
|
