How to Open a RecordSet in Access VBA and Loop Through the Records
ฝัง
- เผยแพร่เมื่อ 11 มิ.ย. 2020
- In this week’s episode, we return to our Microsoft Access playlist for a video on one KEY skill you need to know when engineering data: How to loop through a recordset. Knowing this skill can take your Microsoft Access skills to the next level, because it brings a whole host of new data transformations to your fingertips. We’ll use Visual Basic for Applications (VBA) which is the native language included with Microsoft Office and used by millions of people around the globe.
If you can loop through a recordset, you gain the ability to do things like comparing the current record to the previous record, or performing very complex calculations based on context on each row that just aren’t possible using queries alone. Every data analyst, engineer, or scientist should have this skill in their toolkit. Find out how in this week’s episode!
Related Videos:
How to Use Left, Right, Mid, Instr, and Trim on Strings in MS Access and VBA
• How to Use Left, Right...
How to Use DateAdd, DateDiff, and DatePart to Calculate Dates in MS Access
• How to Use DateAdd, Da...
How to Handle Line Breaks in Microsoft Access
• How to Handle Line Bre...
How to Open a RecordSet in Access VBA and Loop Through the Records
You are watching this video now!
How to Use BOF and EOF on Recordsets in MS Access
• How to Use BOF and EOF...
How to use With.. End With in MS Access VBA
• How to use With.. End ...
How to Use Global Variables in Microsoft Access VBA
• How to Use Global Vari...
How to Use For Next Loops in MS Access VBA
• How to Use For Next Lo...
How to Use a Do While Loop in MS Access VBA
• How to Use a Do While ...
Follow us on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a TH-cam Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
For developers looking for a new role, check out:
www.toptal.com/qKaO2b/worlds-...
Needing to hire technical resources for your project? Get the best:
www.toptal.com/qKaO2b/worlds-...
vba recordset
dao recordset
dao recordset in vba
open recordset in vba
open recordset in access vba
how to use vba
MS Access
Data Base
Big Data
Data Analytics
Sean Mackenzie - วิทยาศาสตร์และเทคโนโลยี
This is great information. Thank you for all you do for the community!
My pleasure!
Hi Sean, you save my life, I don't know why my coding is not working MS Access 2016 .... after 10 years not coding any more, you highlight the keywords to me dbSeeChanges
Thank you.
I’m glad it was helpful! Very welcome!
Most concise explanation of VBA loops and how to use them. I wish I had found this before. Cheers
Glad it was helpful!
Same here. R. Rost (Computerlearning Zone) has most likely covered this aswell but only in his "extended cuts" for premium members. Dont get me wrong - he should be able to make money. But its annoying to watch rost clips..
Im glad Sean is not butchering his videos. Let us Keep supporting him ;)
@@siclucealucks Thanks for supporting! cheers
Thanks Sean, great, brief but clear tutorials.
Glad it was helpful!
Thank you for this tutorial! It is exactly what was looking for!
Glad it was helpful!
I'm grateful for your generosity
I'm glad this was helpful, cheers
Too brilliant. Thanks very much. I love access programming
Glad it was helpful!
Excellent presentation, thank you so much
Glad it was helpful!
FANTASTIC VERY SIMPLE AND VERY CLEAR THANKS
Welcome!
Excellent teaching.Thank you Sir.
Glad you liked it! Thanks for watching.
Thanks Sean really good explanation and clear steps to follow. I hope you might expand your Access VBA content, that would be great. Thanks again, I enjoyed that you included DAO recordsets as well, they are so powerful.
Thanks! You bet, lots of VBA stuff coming. DAO is what Access was created with and it works so awesome! Especially when you link to things like ODBC.. needing almost no changes to how you program your app.. just swap out the tables. Powerful!
Thank you!
You bet! Glad it helped
Sean, I have a query that selects items from a table.
In a separate column I need sequential numbers for each of the sorted lines.
Autonumber is not acceptable because the numbering has to start with 1, every year.
How can I achieve that?
In SQL Server or other databases, you could use PARTITION .. OVER for this. MS Access does not support this, but you could easily do something like:
1. Write your query to a temporary table (using "make table query") with an added integer column for the new sequential number.
2. Write a simple loop in VBA to loop through the set and put the number in, resetting back to zero when the year changes.
My other video on simulating Lead and Lag functions uses a similar technique:
th-cam.com/video/9iSuI3dR75o/w-d-xo.html
Or,
You can do a query like the one shown here, using a self join:
stackoverflow.com/questions/21917637/achieving-row-number-partition-by-in-ms-access
Good luck on your project!
I need to loop through an order and make a list of certain characteristics of the order. I tried to create a query, but can't seem to get the correct data. My solution was to create a table to temporarily store the output generated by the loop code, then use that tbl data to perform aggregate operations on the data, then display that data in a report. Finally I use code to clear the tbl of all the data. I looked at some info on 'temp tables ' in MS Access, but not sure if that's the better solution than mine. Any thoughts?
I think you're going in the right direction. Nothing wrong with using a table to store some temporary results! I use this method all the time.
I recommend my team to join your Channel!!!😋
Hey that's great! Thanks for the recommendation!
Hey there Sean, I have a question about the application of this concept.
I currently have a query in MS Access but basicly too many rows and double information.
I'd like to combine certain rows in this query using this concept.
My data looks something like this:
ID|NAME|LASTNAME|Year1|YEAR2
12Q|Mad|Brown|1972|2020|
12Q|Mad|Withaker|1973|2019
12Q|..
13Q|..
13Q|Mad|Smalls|1943|2003|
13Q|Jon|Seed|193|2003
The ID has to be unique in the new output table/query. Is it possible to Open the query as a Recordset, loop through and mold it into the shape:
12Q|Mad/Name2/3...|Brown/Withaker/Lastname3/4/etc../|MIN Year1|MAX Year2|
13Q|Mad/Jon|Smalls/Seed|etc.
Or is there a better approach to this? Any help is greatly appreciated Im stuck on this problem for weeks
This is a perfect example of a task that can be solved (several different ways) by using VBA like you see in this video. The difference is that you will use several loops to accomplish this. One inside the other.
*For the SQL Server guys out there, Access does not support the STRING_AGG or FOR_XML_PATH solutions to this problem so we gotta do some RBAR here. I show how to do this in (old and new) versions of SQL Server here: th-cam.com/video/zi_D8EyW418/w-d-xo.html
@@seanmackenziedataengineering I looked at your video and am not sure how to start in VBA. Would it be possible to give me a few lines of code to start out in VBA? My data is currently selected through a select-query in Access called 'FSelect'.
Can I start like this?:
Dim db As Database
Dim rs As Recordset
Dim str1 As String
Dim str2 As String
set db = FSelect
Set rs= db.OpenRecordset("Not sure about parameters")
Do Until rs.EOF
"Insert checks here"
Im not sure how to compare the different values in each column and write them as 1 row. Could you help me out?
Anyhow, merry christmas and best wishes for 2021!
That was a fun one! Here is one solution of several you could choose:
Sub CombineRows()
Dim db As Database
Dim rstPerson As DAO.Recordset
Dim rstCombined As DAO.Recordset
Dim strSQL As String
Dim strFirst As String
Dim strLast As String
Dim strID As String
'Delete from the target table (you might try a few times so this is handy)
strSQL = "Delete * From tblPersonCombined"
DoCmd.RunSQL strSQL
'Fill ID, Min Year, Max Year in target table
strSQL = "Insert Into tblPersonCombined (ID, Year1, Year2)" & _
" Select ID, Min(Year1), Max(Year2) From tblPerson" & _
" Group By ID;"
DoCmd.RunSQL strSQL
Set db = CurrentDb
Set rstCombined = db.OpenRecordset("tblPersonCombined")
Set rstPerson = db.OpenRecordset("tblPerson")
Do Until rstCombined.EOF
'Get ID
strID = rstCombined!ID
'Reset first and last name variables for this row
strFirst = ""
strLast = ""
'Set the row of the original table back to start
If Not rstPerson.BOF Then rstPerson.MoveFirst
Do Until rstPerson.EOF
If rstPerson!ID = strID Then
'First Names
If Len(strFirst) = 0 Then
strFirst = rstPerson!FName
Else
strFirst = strFirst & "/" & rstPerson!FName
End If
'Last Names
If Len(strLast) = 0 Then
strLast = rstPerson!LName
Else
strLast = strLast & "/" & rstPerson!LName
End If
End If
'Do this or the loop will go forever!
rstPerson.MoveNext
Loop
'Edit your combined record with the concatenated values
rstCombined.Edit
rstCombined!FName = strFirst
rstCombined!LName = strLast
rstCombined.Update
'Do this or the loop will go forever!
rstCombined.MoveNext
Loop
'Close recorsets
rstPerson.Close
Set rstPerson = Nothing
rstCombined.Close
Set rstCombined = Nothing
Set db = Nothing
Debug.Print "Done."
End Sub
@@seanmackenziedataengineering Excellent solution Sean! Love your videos and coding style.
Watching the 4th time through, it's easy.
Glad it worked! My first time I probably looked at it ten times :-) Once you get it, you get it. Nice work
Brilliant explanation. please I got a form Customers and subform sales. I need to duplicate a records. My customers hold customers details like name, address, tel... whereas sales hold details of product barcode of products. I need to duplicate say an invoice. please can you help.. thanks
Good question.. Your invoice output will probably be a Report object that looks at a query or a table. You can show a duplicate by changing the query to show a duplicate, even if there isn't one in the database. Or, you can just add a duplicate sale.
Hi, but dont have any idea how to do that.
@@jrsam5075 Do you mean that you need to actually create a duplicate invoice and actually make duplicate data? Or just show the same invoice twice?
do you have a sample data file of the data base?
Not right now, but I'm working on getting a repository up for that purpose. Stay tuned!
But, again I have stumbled on generating Defaulters' List. How to generate a list comprising names of defaulters monthwise
Great question! I think that would be a good idea for a video.
thanks
You're welcome!
Do you have anything on how to exit all subs. I have a calls procedure in a module
My call sub imports data with error goto and runs some deletions from tables if error code was 3051 else if not that code then delete data from tables and provides msg box with error code
When I do exit sub it resumes to main module
Please help, I’m learning vba. Self taught
Michelle
Great question! The best way to do it is to change your Sub to a Function. When you run the function, it is exactly the same as a Sub, but you can return a value. So, instead of your Sub, you'll have:
Function MyProcessing()
MyProcessing = False
'Do a bunch of stuff
'If it makes it to end then
MyProcessing = True
Exit_MyProcessing:
Exit Function
Error_MyProcessing:
'Check Error etc
End Function
When you call it from the main procedure, then you can determine whether to exit everything:
x = MyProcessing()
If x = False Then
'Do exit stuff
Exit Sub
End If
'Continue processing other functions (it was true if it gets to here)
You can also choose to return status values ie. 0 for failed, 1 for completed but errors, 2 total fail etc
Thank you, I will definitely try that
ini sangat membantu
Thanks for your comment. I'm glad it helped you!
HELLO.How to Generate All Possible Combination IN ms ACCESS ?tHANK U
Say you're looking at a list in tblA and a list in tblB. You want possible combinations of ID_A from tblA and ID_B from B. Create ribbon > Query design > Select tblA and tblB in table selector pop up > Double-click ID_A from tblA and ID_B from tblB so they show in the query grid > If any little black "join lines" automatically appeared between the tables, right-click Delete those lines > Home ribbon > view > datasheet view > voila, all combinations
when I tried the openRecordset there was an error - too few parameters
Maybe you spelled a field wrong. A typo can cause this.
@@seanmackenziedataengineering nope. the reason was trying to put a name of query instead of it's string. thank you
How to prevent duplicate record while entering data through ms access 2007 form. Roll no & monthly fee should not be entered for a student twice.
Good question! Add an index over two fields in the table, and set it to Yes (No Duplicates). Then it will stop users every time they make this mistake. Google something like "access 2019 2 field index" and the Microsoft instructions should show up.
@@seanmackenziedataengineering But, Sit it is not available in MS Access 2007. Can't it be done through VBA. You are so knowledgeable and can explain things so simply. I like you very much because you are different from others.
Yes. It is available in Access 2007 too. I have done it as advised by you. Thanks
Vb code for adding records in access table
Set rst = db.OpenRecordset("MYTABLE")
With rst
.AddNew
!Field1 = "ABC"
!Field2 = 19
!Field3 = #2023-12-01#
.Update
End With
rst.Close
ThAnks but I want add one by one trough vba code and refreshing table through code. Again thanks
Private Sub Command5_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Shar")
Do Until rst.EOF
ID = rst!ID
Shar = rst!Shar
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
Only shows the last record,and not display all data.
2nd question : when i put it on a module i cant call the module in my form to execute the code to display all data
It shows only the last record because that is where it stopped after finishing, leaving ID and Shar as the values in the last record. You need to do something with the values after you load each time. Just before rst.MoveNext, you could insert a line like Debug.Print ID & ", " & Shar
Look in the Immediate Window (Ctrl+g) and you will see all of the values.
@@seanmackenziedataengineering actually i don't understand, could u help to me what should I do or rewrite the code to me
Appreciate
@@ballaomer6738 In this section, put:
Do Until rst.EOF
ID = rst!ID
Shar = rst!Shar
Debug.Print ID & ", " & Shar
rst.MoveNext
Loop
Run your code/push your button, then press Ctrl+g to see values.
@@seanmackenziedataengineering yes it shows all records in immediate window, but why it doesn't show all data in a form , its my question
Regards
How to loop (and edit) recordset containing a multivalued field?
Eg. Containing the Values ("Express", "Insured") - rst!MultiShipmentType.
In the Table you could either choose both or just one or none. But how to work in the loop with it?
Maybe not the most perfect example but it fits into the above example.
Im looping through a recordset. More precicely im duplicating an existing record where one value has to be changed.
Its a "batch process" - Records have the same values and need to be attached to a bunch of preselected deviceids. however works quite fine until the loop hits a Multivalued field.
Explained in the example from above:
rst!MultiShipmentType = "whatever I pass"
gives me in the Empty source Field case - Error 64224
or if populated with at least one value - Error 3421.
Even if simply pass the source field to it which should have the correct type?
I have also tried .Value but I guess that doesnt work since the table is not normalized.
What I try is copy existing multivaluedfield into a new but within the exsiting table. So im not even altering the type simple A -> B
But I guess its boiling down to how to "address" the multivalued field.
rst!MultiShipmentType(0) ..or rst!MultiShipmentType.Item ...?
This is a great question and good topic for a video! The short answer is that the multi-value field is itself a recordset. When you get to rst!MultiShipmentType, you need to put that into a child recordset. Something like:
Set rstMST = rst!MultiShipmentType.Value
Do normal recordset stuff with rstMST, like process it inside its own loop then close it and move on with your main loop.
@@seanmackenziedataengineering Thx for your suggestion I will try it !
Best Content and not like Richard Rost stuff hidden behind a paywall.
Thx I hope you will have a lot of clicks to get monetized by your marvelous content!
Paypal donate button anywhere?
Thanks, I appreciate the feedback! If you like a particular video and want to donate, you can click on the Thanks button just below the video! Cheers!
@@seanmackenziedataengineering I choose Patreon instead ;)
@@siclucealucks cool!