3 tables - RS.MoveFirst Do While Not RS.EOF PROBLEM

[Jimzen]

Hi all,
I have 3 tables, table1 is connected 1:n to table2 and table3 with referential integrity.
Putting all together in a form and generating an email via Outlook is working fine so far.

My problem:


if table2 has 4 data sets and table3 only 1 data set, the output of table3 in the email is
doublicated to the number of data sets of table2, this means in this example, the output of
table3 in the email shows 4 entries.

Can anyone help me to solve this problem please.

#email vba
# output table2
RS.MoveFirst
Do While Not RS.EOF
strBody = strBody & "<tr><td>" & RS!field1table2 & "</td><td>" & RS!field2table2 & "</td></tr>"
RS.MoveNext
Loop
strBody = strBody & "<br />"

# output table3
RS.MoveFirst
Do While Not RS.EOF
strBody = strBody & "<tr><td>" & RS!field1table3 & "</td><td>" & RS!field2table3 & "</td></tr>"
RS.MoveNext
Loop
strBody = strBody & "<br />"

[John_G]

It is happening because (I think) you have all three tables in the same recordset. Because there are 4 records (which is what you should call them, not data sets) in table2 corresponding to a table1 record, the corresponding record from table3 is repeated 4 times.

Because you are dealing with them separately in your code for the E-mail, create two recordsets - one for table1 + table2, and another for table1 + table3, and loop through each one of them separately.

[Jimzen]

Hi John,

thank you for your help.
Working with 2 recordsets, sounds interesting and complicated as well.
Don't know how to deal with them, does this mean to create 2 queries, one with table1+table2 (query 1) and one additional with table1+ table3 (query2 )
and putting this 2 queries together in query3 (query1 + query2 = query3)

With kind regards

[John_G]

It's really not complicated at all. You would create the 2 queries as you have them, but you would not combine them into a third query. In your code, you would create 2 recordsets, one based on query1, the other based on query2:

Dim RS1 as recordset, RS2 as recordset
Set RS1 = Currentdb.OpenRecordset("query1")
Set RS2 = Currentdb.OpenRecordset("query2")

Then in your code above, the "output table2" part would loop through RS1, and the "output table3" part would loop through RS2. That should accomplish what you are wanting to do.

[Jimzen]

Hi John,

great idea, thank you very much.

With kind regards