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
> VBA Looping Through Dataset, Access 2013    
 
   
raftingdon2
post May 14 2018, 01:37 PM
Post#1



Posts: 187
Joined: 12-August 09




This may be way below most of your experience level and I can't find specifically what I am looking for through looking at other posts.

I am currently creating a text file based off of data in a legacy table.



Set DB = CurrentDb
Set rs = DB.OpenRecordset("TableName")
Dim i As Integer

For i = 0 To rs.RecordCount - 1
Fileout.Write rs.Fields("AccountNumber") & vbCrLf
rs.MoveNext

Next I

I can cycle through all data that is in the table "TableName" in this way, but lets say AccountNumber is listed multiple times such as:

12345 data 1
12345 data 2
12345 data 3
54321 data 1
54321 data 2
54321 data 3

And I am wanting to cycle through the account number, do something with the data, and move on to the next line in the recordset, until complete with 12345, and then move on to 54321. Confused yet?

Do I manage this through the original identification of the data set? I really need to be able to isolate 12345 or 54321 before moving on to the next.

Thoughts?


--------------------
There are two ways to slide easily through life: to believe everything or to doubt everything; both ways save us from thinking - Theodore Rubin
Go to the top of the page
 
theDBguy
post May 14 2018, 01:45 PM
Post#2


Access Wiki and Forums Moderator
Posts: 72,169
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Let's say you have a recordset filled with all the data in one table. You can create a second recordset based on a filtered data from the first recordset to do your work. After you're done with the first batch, you can reload the second recordset with a new set of filtered data.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
raftingdon2
post May 14 2018, 02:08 PM
Post#3



Posts: 187
Joined: 12-August 09



Even when my subset could be 120 or so different subsets?


--------------------
There are two ways to slide easily through life: to believe everything or to doubt everything; both ways save us from thinking - Theodore Rubin
Go to the top of the page
 
theDBguy
post May 14 2018, 02:12 PM
Post#4


Access Wiki and Forums Moderator
Posts: 72,169
Joined: 19-June 07
From: SunnySandyEggo


Yes. Why not?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post May 14 2018, 02:54 PM
Post#5


UtterAccess VIP
Posts: 9,546
Joined: 10-February 04
From: South Charleston, WV


Why can't you put in If statement inside the loop?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
raftingdon2
post May 14 2018, 04:07 PM
Post#6



Posts: 187
Joined: 12-August 09



Because I don't know what the actual account number will be. It changes.

Could I do a Do While loop and assign the first account number to a variable. Do while the variable = the account number, then move on to another loop, that sets the account number to the next unique account number then cycles through the previous loop?


--------------------
There are two ways to slide easily through life: to believe everything or to doubt everything; both ways save us from thinking - Theodore Rubin
Go to the top of the page
 
tina t
post May 14 2018, 04:54 PM
Post#7



Posts: 5,379
Joined: 11-November 10
From: SoCal, USA


as DBguy suggested, two loops would handle it. something like

CODE
Dim strSQL As String
Dim rs2 As DAO.Recordset

strSQL = "SELECT DISTINCT AccountNumber From TableName;"
Set DB = CurrentDb
Set rs = DB.OpenRecordset(strSQL)
rs.MoveFirst

Do
     strSQL = "SELECT * FROM TableName WHERE AccountNumber = '" & rs("AccountNumber") & "';"
     Set rs2 = DB.OpenRecordset(strSQL)
     rs2.MoveFirst
     Do
         <do whatever action(s) you want, with the record(s) that have the account number from the outer loop.>
         rs2.MoveNext
     Loop Until rs2.EOF
     rs2.Close
     Set rs2 = Nothing
     rs.MoveNext
Loop Until rs.EOF

rs.Close
Set rs = nothing

hth
tina
This post has been edited by tina t: May 14 2018, 04:55 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
cheekybuddha
post May 14 2018, 05:55 PM
Post#8


UtterAccess VIP
Posts: 9,781
Joined: 6-December 03
From: Telegraph Hill


An example of Robert's suggestion using a single recordset:
CODE
  Dim strSQL As String, lAccountNo As Long

  strSQL = "SELECT * FROM TableName ORDER BY AccountNumber;"    ' <-- ORDER BY is important here
  With CurrentDb.OpenRecordset(strSQL)
    Do Until .EOF
      If .Fields("AccountNumber") <> lAccountNo Then
'       New account
'       Do whatever
        lAccountNo = .Fields("AccountNumber")
      Else
'       Same Account
'       Do whatever
      End If
      .MoveNext
    Loop
    .Close
  End With


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post May 15 2018, 02:58 PM
Post#9


UtterAccess VIP
Posts: 9,546
Joined: 10-February 04
From: South Charleston, WV


David's code is sort of what I had in mind. But the If is true the first time through and subsequently when a new account is encountered as opposed to after. Will this will work for you? I was thinking more of last-record logic but maybe this will do what you want. The 2-recordset approach should work but it involves hitting the table many more times than necessary.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th May 2018 - 08:43 AM