Loop Code

[Wissam]

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

 

[Douglas J. Steele]

I believe you'd realize extreme benefit if you normalized your table. Having
field names like DateN, CholesterolN and GlucoseN is known as a repeating
group, and it's definitely to be avoided. See some of the links Jeff Conrad
has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
for how to design your tables correctly.

Secondly, it's almost always significantly more efficient to use a SQL
statement rather than looping through a recordset.

That having been said, in the meantime you can use

For N =1 To 40
If rsSource.Fields("Result" & N) <> "" Then
rsTarget.Edit
rsTarget.Fields("Date" & N) = rsSource.Fields("Date" & N)
rsTarget.Fields("Result" & N) = rsSource/Fields("Result" & N)
rsTarget.Update
End IF
Next N

 

[Wissam]

Thank you very much for the very valuable suggestions, and for the code (it
worked well).

 

[zhaorong]

ʲô