How to run a double loop in Access VBA

Question

i'm learning Access by few days and i'm having some problems running into a double loop. I'm doing something wrong because i'm not expert with Access. It seems i'm stuck in an infinite loop (or some other error). My goal is to retrieve some data from the same table. Here's an example:

Table1

Field1     Field2
AAA        1
BBB        2
CCC        3
CCC        4
AAA        5
BBB        6

i want to retrieve all the records in field 2 where Field1= x (but not repeating the action if the same record is found)

Here's my code:

Dim strSQL as String, rs as DAO.Recordset, rs2 as Dao.Recordset, result as String

strSQL = "SELECT * FROM Table1"
strSQL2 = "SELECT DISTINCT Field1 FROM Table1"
Set rs = Currentdb.OpenRecordset(strSQL)
Set rs2 = Currentdb.OpenRecordset(strSQL2)


rs2.movefirst

While not rs2.EOF
rs.movefirst
result = ""
While not rs.EOF
If rs.Fields("Field1") = rs2.Fields("Field1") Then
result = result & rs.fields("Field2") & "   "
rs.movenext
End if
Wend
rs2.movenext
Debug.print result
Wend

Set rs=Nothing
Set rs2 = Nothing

The result i want should be:

Result(first loop) = 1 5
Result(second loop) = 2 6
Result(third loop) = 3 4

EDIT: i'm running loops because i need to generate emails with the data i found. I know how to generate emails but i'm only stacked with this loop. An example of emails should be like this:

number of emails = number of unique values in [table1].[field1] (so 3 in the before mentioned example)

for each email a list of all records in field2 that has the value-in-loop in field1.

Your requirement seems like it can be solved with a single SQL statement. Can you share some sample data and your desired results. Your solution feels very clunky. — JNevill
– JNevill, Commented Oct 28, 2019 at 19:30
@JNevill i need to generate different emails (number of emails=number of unique values in field 1). and in each email the bodytext should be a list of all records i found in field 2 that have the same record in Field1, that's why i'm running a loop. — Zakiirim
– Zakiirim, Commented Oct 28, 2019 at 19:40
Then you probably want to create a single string of all values in Field2 for each value in Field1. This requires a VBA procedure. One code example commonly referenced is Allen Browne's ConcatRelated. — June7
– June7, Commented Oct 28, 2019 at 19:45

June7 · Accepted Answer · 2019-10-28 19:50:33Z

1

Assuming Field2 actually contains unique number values, consider:

TRANSFORM Max(Table1.Field2) AS MaxOfField2
SELECT Table1.Field1
FROM Table1
GROUP BY Table1.Field1
PIVOT DCount("*","Table1","Field1='" & Field1 & "' AND Field2<" & Field2)+1;

However, if there are too many values for a CROSSTAB to handle or if you really need a single string of values from Field2 for each value in Field1, use VBA to concatenate data. One version http://allenbrowne.com/func-concat.html

edited Oct 28, 2019 at 19:50

answered Oct 28, 2019 at 19:35

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SteveH · Accepted Answer · 2019-11-03 20:04:01Z

0

Another option using the getstring method of an ado recordset.

a) Paste into a new query in sql view and save: select field1,field2list(field1) from table1

b) In a vba module, paste field2list function:

Function field2list(field1) As String
Dim rs As New adodb.Recordset
Dim ColumnSeparator
Dim RowSeparator
Dim sql
ColumnSeparator = ""
RowSeparator = ","
sql = "select field2 from table1 where field1='" & field1 & "'"
Set rs = CurrentProject.Connection.Execute(sql)
If rs.RecordCount > 0 Then
    field2list = rs.GetString(, -1, ColumnSeparator, RowSeparator)
End If
End Function

c) Open query created in step a.

answered Nov 3, 2019 at 20:04

SteveH

112 bronze badges

1 Comment

SteveH Over a year ago

correction: change sql statement to group by field2 to get unique values of field2: "select field2 from table1 where field1='" & field1 & "' group by field2"

Collectives™ on Stack Overflow

How to run a double loop in Access VBA

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related