UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> 3 Tables - Rs.movefirst Do While Not Rs.eof Problem, Access 2013    
 
   
jimbob24
post Jan 29 2018, 11:16 AM
Post#1



Posts: 15
Joined: 2-January 16



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 />"
Go to the top of the page
 
DanielPineault
post Jan 29 2018, 12:21 PM
Post#2


UtterAccess VIP
Posts: 6,273
Joined: 30-June 11



What is RS set as? What is the record source? I'm assuming this is from a query, in which case you need to adjust the query. The other thing, depending on how things are setup, you could possibly test to see if the field has a value and only output it if it does. The issue here though is that you are generating an HTML table and the column count need to match row to row.

Can you post a sample of your db with any sensitive information removed?
Go to the top of the page
 
jimbob24
post Jan 29 2018, 04:15 PM
Post#3



Posts: 15
Joined: 2-January 16



Hi Daniel,

thank you very much for your help.
Please be patient to me, because I'm a semi professional novice in Access.

1.) What is RS set as?
Don't know what you mean exactly. I'm posting my complete VBA code at the end.

2. What is the record source?
you are right, it is a query containing 6 tables (apartments, customer, offer (for the unique offer-no), offer details (table1), offer-period (table2) and offer-equipment (table3).
Is it OK for you to post a screenshot with the relationships of the tables, or do you need a copy of the db with all tables, queries, forms an reports?

with kind regards
Jimbob

##################

Private Sub Befehl136_Click()
On Error GoTo Fehler


Dim strBody As String
Dim olItem As Outlook.MailItem, olApp As New Outlook.Application
Set olItem = olApp.CreateItem(olMailItem)


Set DB = CurrentDb

If Not IsNull(Me!offerno) Then
Set RS = DB.OpenRecordset("select * from offerapartment where offerno in (" & Me!offerno & ")")
Else
MsgBox "Suchfeld darf nicht leer sein!"
Exit Sub
End If

If RS.BOF = True Then
MsgBox "Es wurde kein Datensatz gefunden!"
Exit Sub
End If

strBody = strBody & "<!DOCTYPE HTML><html>"
strBody = strBody & "<body style=""font-family: Arial;font-size:13px;"">"
strBody = strBody & "<table><tr><td>" & Me!field1table1 & "</td></tr>"
strBody = strBody & "<tr><td>" & Me!field2table1 & "</td></tr></table>"
strBody = strBody & "___________________________________________________________________________
_____<br /><br />"
strBody = strBody & "<table>"
RS.MoveFirst
Do While Not RS.EOF
strBody = strBody & "<tr><td>" & RS!field1table2 & "</td><td>" & RS!field2table2 & "</td></tr>"
RS.MoveNext
Loop
strBody = strBody & "</table><br /><br />"

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

strBody = strBody & "" & Me!field3table1 & "<br><br>
With olItem

.To = "" & Me![EMAIL_CUSTOMER] & ""
.Subject = "" & Me!offerperiod & ""
.HTMLBody = strBody
.BodyFormat = olFormatHTML
.Display
End With


DB.Close
Set RS = Nothing

Aus:
Exit Sub

Fehler:
MsgBox Err.Description
Resume Aus
End Sub
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2018 - 12:44 AM