Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

How to rewrite a routine to append the values in a field via a loop ?

I am developing an Access application using an ADP file. I use Access as the front end and SQL Server as the back end database.

I perform a SQL Statement which reads records from 2 tables. The tables are named
tblPSEmail and tblSpLNExp. I use a Select statement as follows:

 rsList.Open "SELECT tblPSEmail.Branch, tblPSEmail.[EMAILADD] From tblPSEmail
WHERE EXISTS (SELECT * FROM tblSpLNExp
WHERE Left(tblSpLNExp.[CPS Account Number],3)=tblPSEmail.Branch)
ORDER BY BRANCH;", cn, 2, 2

The result set has values such as the following:

Branch           EMAILADD
--------            ------------
101                 john.smith@yahoo.com
101                 robert.doe@yahoo.com
101                 joe.donaldson@yahoo.com
102                 henry.lee@yahoo.com
102                 bill.hands@yahoo.com
----------------------------------------------------------------
My goal is to read all the records within a single branch (for example: 101) from this result set while appending the values of the field EMAILADD to a variable named str_BCC. Thus, for BRANCH 101,
after all this branch's records are read, the string str_BCC would have the following value:

john.smith@yahoo.com;robert.doe@yahoo.com;joe.donaldson@yahoo.com

Then I will write out the values for this branch 101 in the form of an email.
For example  

To: john.smith@yahoo.com;robert.doe@yahoo.com;joe.donaldson@yahoo.com
From: ...
Subject: Branch 101
--------------------------------------------------------------------------------------------------
and then I will loop back to read the values for all other branches (including Branch 102) and perform the same process.

Can you think of a routine that would loop through the records and break on the branches ?

Do Until rsList.EOF
    If rsList("Branch")
Loop                    
---------------------------------------------------------------------------------------

 
0
zimmer9
Asked:
zimmer9
  • 3
  • 2
1 Solution
 
jmantha709Commented:
Somtehing Like :

Dim strPreviousBranch
strPreviousBranch=""
Do Until rsList.EOF
    If rsList("Branch") = strPreviousBranch
      'Do Stuff
    Else
      'Do Stuff
       strPreviousBranch = rsList("Branch")
     End If
    rsList.movenext
Loop      
0
 
zimmer9Author Commented:
Just as there is an "EXIT DO" statement to break out of a DO Until LOOP,
is there a statement to go back to the top of the Do Until LOOP without
executing statements that follow a break point ?

0
 
jmantha709Commented:
aside from GOTO, I don't think so, but why would you need such a statement, can you give me an example of code
0
 
zimmer9Author Commented:
Let's assume that Do Stuff entails simply writing out a record.

Then in the example sample records I cited above as follows:
Branch           EMAILADD
--------            ------------
101                 john.smith@yahoo.com
101                 robert.doe@yahoo.com
101                 joe.donaldson@yahoo.com
102                 henry.lee@yahoo.com
102                 bill.hands@yahoo.com

When the first record is read,  strPreviousBranch = ""
Thus, rsList("Branch") <> strPreviousBranch and a record is written.
Then strPreviousBranch is assigned the value of Branch 101.

When the second record is read,
another record is written for the same Branch 101.

My goal is only to "write out a record" (Do Stuff) when there is a change in Branches.
0
 
jmantha709Commented:
The example I provided needs rafinement...

For example, you can replace the statement
strPreviousBranch=""
by
If not rsList.EOF Then strPreviousBranch=rsList("Branch")

That would fix the first record problem...  Sorry I don't really have time to go in to details today !
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now