Looping muliple related records

[FrymanTCU]

Okay I'm still learning so any help is appreciated, I have gotten pretty decent at creating data input forms and reporting from them. But I want to use my table data to send an email but want the code to look and see if there are multiple accounts and include them in the message.

There are close to 6k accounts I will be notifying and I don't want to send an email for each account. So I have attached an blank mdb with a simplified version of the data. Can someone show me how to loop through the information so I send one email per user? All I need is it to Debug.Print the account numbers I can create the email later.

But I guess I'll just ask now, I know you can turn off the Outlook Preview option but if one of the address do not work will the macro fail or just pause on that record?

Like always thanks for all your help in advance!:help
-Rich

</IMG>

[OBP]

Rich, before I look at your database, am I correct in assuming that you will have some kind of "trigger" or "flag" in the Accounts Table to identify which ones to include in your email list?

There are 2 ways to look at your data, the most common is to use a Recordset, but if you want to "see" what is happening with the records you could use a Form to step through the records, just in case there is something causing a problem. It would stop on that record and you can see the problem data.
If you are comfortable with just using the recordset then that is OK.

You can also use 2 different methods of looping through the data, you can use a Do Until loop using the End Of File (EOF) to stop the loop or you can establish the number of records and then use a For/Next loop to step through them.

[FrymanTCU]

Yes, there is an indicator but its slightly complicated so I was thinking about just using a make table query then building the logic for the email. I don't want to see the records but I'm not that familiar with the Recordset logic. It looks like it follows SQL but I havent used it before.

So I need to dim the db, create a record set with the joins OfficeNumber and UserNumber then group by Usernumber. And then I could somehow loop every related account? Would this need to be done in mulitple Recordset queries or just one?

[CreganTur]

The example here (http://www.vbaexpress.com/forum/showthread.php?p=153859#post153859) might help you. It uses a DAO connection to 2 different tables and exports to Excel all of the records from Table2 that match values in Table1. You might be able to adapt it to your purposes.

Let me know if I can provide any more help:thumb

[FrymanTCU]

Randy, you're awesome! I will look at this today and let you know where I mess it up. BTW this forum rocks, I learn something new everytime I visit.

[FrymanTCU]

Randy, I think that script will work fine but I'm recieving an error stating 'Join Expression not Supported'?

Here is my SQL statement:

strTable = "SELECT AccountInfo.*, OfficeInfo.OfficeName, OfficeInfo.OfficeEmail, UserInfo.UserName, UserInfo.UserEmail " & _
"FROM (OfficeInfo INNER JOIN AccountInfo ON OfficeInfo.OfficeNum = AccountInfo.OfficeNum) " & _
"INNER JOIN UserInfo ON ( " & tblrst!UserNumber & " = AccountInfo!UserNumber) AND " & _
"(OfficeInfo.OfficeNum = " & tblrst!OfficeNum & ");"

Should I remove the parenthesis? The first time I ran the macro I didn't use tblrst! in the join and it returned the same data for every excel sheet...

[CreganTur]

The error is because you're declaring your join incorrectly.... or to be more specific, you're declaring your join twice.
Your second Inner Join is where your WHERE clause should be.

Try this:



strTable = "SELECT AccountInfo.*, OfficeInfo.OfficeName, OfficeInfo.OfficeEmail, UserInfo.UserName, UserInfo.UserEmail " & _
"FROM (OfficeInfo INNER JOIN AccountInfo ON OfficeInfo.OfficeNum = AccountInfo.OfficeNum) " & _
"WHERE (AccountInfo.UserNumber =" & tblrst!UserNumber & ") AND " & _
"(OfficeInfo.OfficeNum = " & tblrst!OfficeNum & ");"

[FrymanTCU]

Okay that helped the Join error but it now the error states 'Too few parameters. Expected 2.' I used debug.print to view the strTable shown below and it looks like it is passing 2 variables. I also tried to put single quotes around the variables but recieved the same error.

SELECT AccountInfo.*, OfficeInfo.OfficeName, OfficeInfo.OfficeEmail, UserInfo.UserName, UserInfo.UserEmail FROM (OfficeInfo INNER JOIN AccountInfo ON OfficeInfo.OfficeNum = AccountInfo.OfficeNum) WHERE (AccountInfo.UserNumber ='100') AND (OfficeInfo.OfficeNum = '100');

SELECT AccountInfo.*, OfficeInfo.OfficeName, OfficeInfo.OfficeEmail, UserInfo.UserName, UserInfo.UserEmail FROM (OfficeInfo INNER JOIN AccountInfo ON OfficeInfo.OfficeNum = AccountInfo.OfficeNum) WHERE (AccountInfo.UserNumber =100) AND (OfficeInfo.OfficeNum = 100);

[CreganTur]

Hmmm... it might be balking at the fact that you're referencing parameters in 2 different tables. Judging from your SQL statements, both the AccountInfo and OfficeInfo tables share the OfficeNum field... so:

SELECT AccountInfo.*, OfficeInfo.OfficeName, OfficeInfo.OfficeEmail, UserInfo.UserName, UserInfo.UserEmail
FROM (OfficeInfo INNER JOIN AccountInfo ON OfficeInfo.OfficeNum = AccountInfo.OfficeNum)
WHERE (AccountInfo.UserNumber =100) AND (AccountInfo.OfficeNum = 100);

[FrymanTCU]

Ok I was missing the WHERE statement it did need both JOIN statements.

strTable = "SELECT AccountInfo.*, OfficeInfo.OfficeName, OfficeInfo.OfficeEmail, UserInfo.UserName, UserInfo.UserEmail " & _
"FROM (OfficeInfo INNER JOIN AccountInfo ON OfficeInfo.OfficeNum = AccountInfo.OfficeNum) " & _
"INNER JOIN UserInfo ON (UserInfo.UserNumber=AccountInfo.UserNumber) AND (OfficeInfo.OfficeNum=UserInfo.OfficeNum) " & _
"WHERE (AccountInfo.UserNumber = '" & tblrst!UserNumber & "') AND " & _
"(AccountInfo.OfficeNum = '" & tblrst!OfficeNum & "');"


But when I exported the files for each user in the UserInfo Table, it did not include all the related information from the table AccountInfo. I tried to change the tblrst = AccountInfo and created a file for each UserNumber but it still only included one account per file. :dunno

Is there a loop or group by statement I am missing? I just re-zipped the file because I dont know if I'm making any sense.

[CreganTur]

I found the error- It was my fault.

Where the procedure grabs the number of records to write to the Excel files- it was only counting the first record, which meant it only pulled the first record.

I added this:
rst.MoveLast
rowsToReturn = rst.RecordCount
rst.MoveFirst
I forgot that with DAO recordsets, you have to move to the end of the recordset to get the total number of records. Then I move back to the first record, so that the cursor is at the right position when the records get written to Excel.

I've included a corrected version of your database.

[FrymanTCU]

Yes! Thanks Randy, I was just searching the Help file about RecordsCount and was trying to add that piece.



Use the RecordCount property to find out how many records in a Recordset or TableDef object have been accessed. The RecordCount property doesn't indicate how many records are contained in a dynaset-, snapshot-, or forward-only?type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast (mk:@MSITStore:C:\Program%20Files\Common%20Files\Microsoft%20Shared\OFFICE1 1\1033\dao360.chm::/damthmovefirst.htm) method on the Recordset object. You can also use an SQL Count function to determine the approximate number of records your query will return.


Now I have coded emails in VBA before but I anticipate another question or two. I'll mark it as solve once the test is done but thanks again you've been a big help. Hopefully if I keep learning from everyone here I'll be able to actually help you for once!:bow: