Loop Inside a Loop

S

Stephen Lynch

I am a novice here and I am trying to loop through a loop. My first loop
would be a groupof departments in a company, so I grouped a query on the
departments. I want to start with the first department and print each
employee in each department using that department as the parameter and print
the file and then move to the next department and print those employees and
so on. (This is really not what I am doing but it clarifies the example)

I keep getting a "no current record error" on myset.MoveFirst but I know
that there are records. It stops on the myset.MoveFirst highlighted below
with astericks.

Do I need to close a recordset first? Any help is appreciated. Thanks

****************************************************
Dim StrMasterID As String
Dim strMaster2Use As String
Dim strSQLMasterID As String

'go though each master so we can us the master as a parameter for the
loop below
strSQLMasterID = "SELECT tblSellSharesParticipant.MasterID From
tblSellSharesParticipant WHERE (((tblSellSharesParticipant.SellDecision) Not
Like 'Do Nothing'))GROUP BY tblSellSharesParticipant.MasterID ORDER BY
tblSellSharesParticipant.MasterID;"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQLMasterID, dbOpenDynaset)
Do While Not rst.EOF

'***2nd loop using the parameter strSQL
strMaster2Use = rst![MasterID]
strSQL = "SELECT tblSellSharesParticipant.SchwabAccNo,
tblSellSharesParticipant.SellDecision, tblSellSharesParticipant.Symbol,
tblSellSharesParticipant.Shares, tblSellSharesParticipant.SecurityType,
tblSellSharesParticipant.MktValue, tblSellSharesParticipant.SharePrice,
tblSellSharesParticipant.SecDesc1,
tblSellSharesParticipant.SharesSubject2STRP,
tblSellSharesParticipant.SharesNotSubjectValue,
tblSellSharesParticipant.SellDollarAmount, tblSellSharesParticipant.MasterID
From tblSellSharesParticipant WHERE
(((tblSellSharesParticipant.SellDecision) Not Like 'Do Nothing') AND
((tblSellSharesParticipant.MasterID)= ' " & strMaster2Use & " ' ));"
Set mydb = CurrentDb()
Set myset = mydb.OpenRecordset(strSQL, dbOpenDynaset)
intFile = FreeFile
strMaster = strMaster2Use
Open "C:\" & strMaster & "\schwab.txt" For Output As intFile
LSet Header = "HD FA M " & strMaster & " FA30 M" & Right(strMaster,
7)
Print #intFile, Header

myset.MoveFirst *****HERE I AM ERRORING OUT WITH NO CURRENT
RECORD
Do While Not myset.EOF

If myset![SellDecision] = "Sell All Shares" Then
strPercentageSell = "1.00"
strSharestoSell = 0
strDollarAmttoSell = 0
Else
If myset![SellDecision] = "Sell Shares Not Subject" Then
strPercentageSell = 0
strDollarAmttoSell = 0
strSharestoSell = myset![Shares] - myset![SharesSubject2STRP]
strSharestoSell = Round(strSharestoSell, 4)
Else
If myset![SellDecision] = "Sell Dollar Amount" Then
strPercentageSell = 0
strDollarAmttoSell = myset![SellDollarAmount]
strSharestoSell = 0
If myset![SellDecision] = "Do Nothing" Then
End If
End If
End If
End If

RSet strA = "TR"
LSet strB = myset![AccNo]
RSet strD = "SELL SHRS"
RSet strE = IIf(strDollarAmttoSell = 0, "", strDollarAmttoSell)
RSet strF = IIf(strSharestoSell = 0, "", strSharestoSell)
RSet strH = IIf(strPercentageSell = 0, "", strPercentageSell)
RSet strJ = myset![Symbol]
RSet strL = "N"
RSet strP = "13051299"
RSet strR = "0"

'Concatenate all of the variables together as in the following:
Print #intFile, strA; strB; strC; strD; strE; strF; strG; strH;
strI; strJ; strK; strL; strM; strN; strO; strP; strQ; strR
myset.MoveNext

Loop
Close intFile
myset.Close
mydb.Close
MsgBox "File sent to C:\" & strMaster & "\schwab.txt", , "Trades
Generated for Import!"

rst.MoveNext
Loop


End Sub
 
S

Steve Sanford

The first thing you need to do is to ensure that every module begins with
these two lines:

Option Compare Database
Option Explicit

"Option Explicit" requires variables to be declared before using them. To
have new modules automatically add the line, in the IDE, click on
TOOLS/Options. On the EDTIOR tab, everything should be checked *except* the
first checkbox "Auto Syntax Check". Any error will still turn red, but you
won't get the annoying error message.

I started to try and step thru your code, but you have lots of undeclared
variables. (I did add a few but.....)

I reformatted the SQL statements to make it easier to read. In the inner
loop, it looks like you had a couple of extra spaces in the WHERE clause:

AND ((tblSellSharesParticipant.MasterID)= ' " & strMaster2Use & " ' ));"

I changed it to:

AND ((tblSellSharesParticipant.MasterID)= '" & strMaster2Use & "' ));"

There should not be a space between the single quotes and the double quotes.

My changes to the code:
-I added an error handler to try and see where/what the error(s) were...
-And I changes the nested "If()"s to the "Select Case" syntax.

Not sure what you are trying to do with the "RSet" and "LSet" statements.
Once you get all of the declarations added, set a breakpoint and single step
thru the code to see where the errors are hiding.

These changes are untested!!!!!

'---------------------------------------
Option Compare Database
Option Explicit

Sub testSL() ' MY Name - yourws was missing
On Error GoTo Not_Again
Const conErrorBase = vbObjectError + 25000
Const conRS1 = conErrorBase + 1
Const conRS2 = conErrorBase + 2
Const conRS3 = conErrorBase + 3


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim myset As DAO.Recordset

Dim Header As String
Dim strSQL As String
Dim strMaster As String
Dim StrMasterID As String
Dim strMaster2Use As String
Dim strSQLMasterID As String

Dim intFile As Integer

Set db = CurrentDb()

'go though each master so we can us the master as a parameter for the
loop below
strSQLMasterID = "SELECT tblSellSharesParticipant.MasterID"
strSQLMasterID = strSQLMasterID & " FROM tblSellSharesParticipant"
strSQLMasterID = strSQLMasterID & " WHERE
(((tblSellSharesParticipant.SellDecision) Not Like 'Do Nothing'))"
strSQLMasterID = strSQLMasterID & " GROUP BY
tblSellSharesParticipant.MasterID"
strSQLMasterID = strSQLMasterID & " ORDER BY
tblSellSharesParticipant.MasterID;"

Set rst = db.OpenRecordset(strSQLMasterID, dbOpenDynaset)

'-----check for records in recordset
If rst.BOF And rst.EOF Then
' no records - exit with error message
'object.Raise number, source, description, helpfile, helpcontext
Err.Raise conRS1, "SQL statement 'strSQLMasterID' at Line 51", "No
Records in recordset = 'rst'. Exiting Sub!!"
End If
'----------

rst.MoveFirst
Do While Not rst.EOF

'***2nd loop using the parameter strSQL
'can [MasterID] ever be NULL or a ZLS ??
strMaster2Use = rst![MasterID]
strSQL = "SELECT tblSellSharesParticipant.SchwabAccNo,
tblSellSharesParticipant.SellDecision,"
strSQL = strSQL & " tblSellSharesParticipant.Symbol,
tblSellSharesParticipant.Shares,"
strSQL = strSQL & " tblSellSharesParticipant.SecurityType,
tblSellSharesParticipant.MktValue,"
strSQL = strSQL & " tblSellSharesParticipant.SharePrice,
tblSellSharesParticipant.SecDesc1,"
strSQL = strSQL & " tblSellSharesParticipant.SharesSubject2STRP,"
strSQL = strSQL & " tblSellSharesParticipant.SharesNotSubjectValue,"
strSQL = strSQL & " tblSellSharesParticipant.SellDollarAmount,"
strSQL = strSQL & " tblSellSharesParticipant.MasterID"
strSQL = strSQL & " FROM tblSellSharesParticipant"
strSQL = strSQL & " WHERE (((tblSellSharesParticipant.SellDecision)
Not Like 'Do Nothing')"
strSQL = strSQL & " AND ((tblSellSharesParticipant.MasterID)= '" &
strMaster2Use & "' ));"

' Set mydb = CurrentDb()
' Set myset = mydb.OpenRecordset(strSQL, dbOpenDynaset)
Set myset = db.OpenRecordset(strSQL, dbOpenDynaset)

'-----check for records in recordset
If myset.BOF And myset.EOF Then
Err.Raise conRS2, "SQL statement 'strSQL' at Line 77", "No
Records in recordset = 'myset'. Exiting Sub!!"
End If
'----------

intFile = FreeFile
strMaster = strMaster2Use
Open "C:\" & strMaster & "\schwab.txt" For Output As intFile
LSet Header = "HD FA M " & strMaster & " FA30 M" & Right(strMaster, 7)
Print #intFile, Header

'myset.MoveFirst *****HERE I AM ERRORING OUT WITH NO CURRENT RECORD
myset.MoveFirst
Do While Not myset.EOF

Select Case myset![SellDecision]
Case "Sell All Shares"
strPercentageSell = "1.00"
strSharestoSell = 0
strDollarAmttoSell = 0

Case "Sell Shares Not Subject"
strPercentageSell = 0
strDollarAmttoSell = 0
strSharestoSell = myset![Shares] -
myset![SharesSubject2STRP]
strSharestoSell = Round(strSharestoSell, 4)

Case "Sell Dollar Amount"
strPercentageSell = 0
strDollarAmttoSell = myset![SellDollarAmount]
strSharestoSell = 0
Case "Do Nothing"

Case Else
' not one of the above options
MsgBox "Invalid [SellDecision]"

End Select


RSet strA = "TR"
LSet strB = myset![AccNo]
RSet strD = "SELL SHRS"
RSet strE = IIf(strDollarAmttoSell = 0, "", strDollarAmttoSell)
RSet strF = IIf(strSharestoSell = 0, "", strSharestoSell)
RSet strH = IIf(strPercentageSell = 0, "", strPercentageSell)
RSet strJ = myset![Symbol]
RSet strL = "N"
RSet strP = "13051299"
RSet strR = "0"

'Concatenate all of the variables together as in the following:
Print #intFile, strA; strB; strC; strD; strE; strF; strG; strH;
strI; strJ; strK; strL; strM; strN; strO; strP; strQ; strR
myset.MoveNext

Loop 'Do While Not myset.EOF
Close intFile
myset.Close
MsgBox "File sent to C:\" & strMaster & "\schwab.txt", , "Trades
Generated for Import!"

rst.MoveNext
Loop 'Do While Not rst.EOF


Exit_Here:
On Error Resume Next
'close and release everything
rst.Close
Set rst = Nothing
myset.Close
Set myset = Nothing
Set db = Nothing

Close intFile

Exit Sub


Not_Again:

Dim E As Long
E = Err.Number
If E < 0 Then
E = E And &HFFFF&
End If

MsgBox "Error number = " & E & vbCrLf & vbCrLf & "Description = " &
Err.Description & vbCrLf & vbCrLf & "Source = " & Err.Source

Resume Exit_Here

End Sub
'---------------------------------------

HTH
 
S

Stephen Lynch

Steve:

Thanks for the help. I did have variables declared but I neglected to copy
them.

I really really apreaciate your time that you spent. I will copy it and go
over it.

Thanks again.

Steve



Steve Sanford said:
The first thing you need to do is to ensure that every module begins with
these two lines:

Option Compare Database
Option Explicit

"Option Explicit" requires variables to be declared before using them. To
have new modules automatically add the line, in the IDE, click on
TOOLS/Options. On the EDTIOR tab, everything should be checked *except*
the
first checkbox "Auto Syntax Check". Any error will still turn red, but
you
won't get the annoying error message.

I started to try and step thru your code, but you have lots of undeclared
variables. (I did add a few but.....)

I reformatted the SQL statements to make it easier to read. In the inner
loop, it looks like you had a couple of extra spaces in the WHERE clause:

AND ((tblSellSharesParticipant.MasterID)= ' " & strMaster2Use & " ' ));"

I changed it to:

AND ((tblSellSharesParticipant.MasterID)= '" & strMaster2Use & "' ));"

There should not be a space between the single quotes and the double
quotes.

My changes to the code:
-I added an error handler to try and see where/what the error(s) were...
-And I changes the nested "If()"s to the "Select Case" syntax.

Not sure what you are trying to do with the "RSet" and "LSet" statements.
Once you get all of the declarations added, set a breakpoint and single
step
thru the code to see where the errors are hiding.

These changes are untested!!!!!

'---------------------------------------
Option Compare Database
Option Explicit

Sub testSL() ' MY Name - yourws was missing
On Error GoTo Not_Again
Const conErrorBase = vbObjectError + 25000
Const conRS1 = conErrorBase + 1
Const conRS2 = conErrorBase + 2
Const conRS3 = conErrorBase + 3


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim myset As DAO.Recordset

Dim Header As String
Dim strSQL As String
Dim strMaster As String
Dim StrMasterID As String
Dim strMaster2Use As String
Dim strSQLMasterID As String

Dim intFile As Integer

Set db = CurrentDb()

'go though each master so we can us the master as a parameter for the
loop below
strSQLMasterID = "SELECT tblSellSharesParticipant.MasterID"
strSQLMasterID = strSQLMasterID & " FROM tblSellSharesParticipant"
strSQLMasterID = strSQLMasterID & " WHERE
(((tblSellSharesParticipant.SellDecision) Not Like 'Do Nothing'))"
strSQLMasterID = strSQLMasterID & " GROUP BY
tblSellSharesParticipant.MasterID"
strSQLMasterID = strSQLMasterID & " ORDER BY
tblSellSharesParticipant.MasterID;"

Set rst = db.OpenRecordset(strSQLMasterID, dbOpenDynaset)

'-----check for records in recordset
If rst.BOF And rst.EOF Then
' no records - exit with error message
'object.Raise number, source, description, helpfile, helpcontext
Err.Raise conRS1, "SQL statement 'strSQLMasterID' at Line 51", "No
Records in recordset = 'rst'. Exiting Sub!!"
End If
'----------

rst.MoveFirst
Do While Not rst.EOF

'***2nd loop using the parameter strSQL
'can [MasterID] ever be NULL or a ZLS ??
strMaster2Use = rst![MasterID]
strSQL = "SELECT tblSellSharesParticipant.SchwabAccNo,
tblSellSharesParticipant.SellDecision,"
strSQL = strSQL & " tblSellSharesParticipant.Symbol,
tblSellSharesParticipant.Shares,"
strSQL = strSQL & " tblSellSharesParticipant.SecurityType,
tblSellSharesParticipant.MktValue,"
strSQL = strSQL & " tblSellSharesParticipant.SharePrice,
tblSellSharesParticipant.SecDesc1,"
strSQL = strSQL & " tblSellSharesParticipant.SharesSubject2STRP,"
strSQL = strSQL & "
tblSellSharesParticipant.SharesNotSubjectValue,"
strSQL = strSQL & " tblSellSharesParticipant.SellDollarAmount,"
strSQL = strSQL & " tblSellSharesParticipant.MasterID"
strSQL = strSQL & " FROM tblSellSharesParticipant"
strSQL = strSQL & " WHERE (((tblSellSharesParticipant.SellDecision)
Not Like 'Do Nothing')"
strSQL = strSQL & " AND ((tblSellSharesParticipant.MasterID)= '" &
strMaster2Use & "' ));"

' Set mydb = CurrentDb()
' Set myset = mydb.OpenRecordset(strSQL, dbOpenDynaset)
Set myset = db.OpenRecordset(strSQL, dbOpenDynaset)

'-----check for records in recordset
If myset.BOF And myset.EOF Then
Err.Raise conRS2, "SQL statement 'strSQL' at Line 77", "No
Records in recordset = 'myset'. Exiting Sub!!"
End If
'----------

intFile = FreeFile
strMaster = strMaster2Use
Open "C:\" & strMaster & "\schwab.txt" For Output As intFile
LSet Header = "HD FA M " & strMaster & " FA30 M" & Right(strMaster,
7)
Print #intFile, Header

'myset.MoveFirst *****HERE I AM ERRORING OUT WITH NO CURRENT
RECORD
myset.MoveFirst
Do While Not myset.EOF

Select Case myset![SellDecision]
Case "Sell All Shares"
strPercentageSell = "1.00"
strSharestoSell = 0
strDollarAmttoSell = 0

Case "Sell Shares Not Subject"
strPercentageSell = 0
strDollarAmttoSell = 0
strSharestoSell = myset![Shares] -
myset![SharesSubject2STRP]
strSharestoSell = Round(strSharestoSell, 4)

Case "Sell Dollar Amount"
strPercentageSell = 0
strDollarAmttoSell = myset![SellDollarAmount]
strSharestoSell = 0
Case "Do Nothing"

Case Else
' not one of the above options
MsgBox "Invalid [SellDecision]"

End Select


RSet strA = "TR"
LSet strB = myset![AccNo]
RSet strD = "SELL SHRS"
RSet strE = IIf(strDollarAmttoSell = 0, "", strDollarAmttoSell)
RSet strF = IIf(strSharestoSell = 0, "", strSharestoSell)
RSet strH = IIf(strPercentageSell = 0, "", strPercentageSell)
RSet strJ = myset![Symbol]
RSet strL = "N"
RSet strP = "13051299"
RSet strR = "0"

'Concatenate all of the variables together as in the following:
Print #intFile, strA; strB; strC; strD; strE; strF; strG; strH;
strI; strJ; strK; strL; strM; strN; strO; strP; strQ; strR
myset.MoveNext

Loop 'Do While Not myset.EOF
Close intFile
myset.Close
MsgBox "File sent to C:\" & strMaster & "\schwab.txt", , "Trades
Generated for Import!"

rst.MoveNext
Loop 'Do While Not rst.EOF


Exit_Here:
On Error Resume Next
'close and release everything
rst.Close
Set rst = Nothing
myset.Close
Set myset = Nothing
Set db = Nothing

Close intFile

Exit Sub


Not_Again:

Dim E As Long
E = Err.Number
If E < 0 Then
E = E And &HFFFF&
End If

MsgBox "Error number = " & E & vbCrLf & vbCrLf & "Description = " &
Err.Description & vbCrLf & vbCrLf & "Source = " & Err.Source

Resume Exit_Here

End Sub
'---------------------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Stephen Lynch said:
I am a novice here and I am trying to loop through a loop. My first loop
would be a groupof departments in a company, so I grouped a query on the
departments. I want to start with the first department and print each
employee in each department using that department as the parameter and
print
the file and then move to the next department and print those employees
and
so on. (This is really not what I am doing but it clarifies the example)

I keep getting a "no current record error" on myset.MoveFirst but I know
that there are records. It stops on the myset.MoveFirst highlighted below
with astericks.
Click to expand...
Click to expand...
 
Top