How to Open a RecordSet in Access VBA and Loop Through the Records

Подписаться 13 тыс.

Просмотров 26 тыс.

50% 1

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.linksyne...
Got a RU-vid Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
For developers looking for a new role, check out:
www.toptal.com...
Want my team to do a project for you? Let's get to it!
system.billzon...
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

Опубликовано:

19 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 80

@colaman1806 2 года назад

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.

@seanmackenziedataengineering 2 года назад

I’m glad it was helpful! Very welcome!

@henrybruin4109 2 года назад

Most concise explanation of VBA loops and how to use them. I wish I had found this before. Cheers

@seanmackenziedataengineering 2 года назад

Glad it was helpful!

@siclucealucks 2 года назад

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 ;)

@seanmackenziedataengineering 2 года назад

@@siclucealucks Thanks for supporting! cheers

@davegoodo3603 8 месяцев назад

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.

@seanmackenziedataengineering 8 месяцев назад

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!

@shaddwatson1833 2 года назад

This is great information. Thank you for all you do for the community!

@seanmackenziedataengineering 2 года назад

My pleasure!

@mahmoudbarrawi7092 2 года назад

FANTASTIC VERY SIMPLE AND VERY CLEAR THANKS

@seanmackenziedataengineering 2 года назад

Welcome!

@hermanbroeckx8329 3 года назад

Thanks Sean, great, brief but clear tutorials.

@seanmackenziedataengineering 3 года назад

Glad it was helpful!

@standman007 3 года назад

Too brilliant. Thanks very much. I love access programming

@seanmackenziedataengineering 3 года назад

Glad it was helpful!

@YvanFPerron 2 года назад

Thank you for this tutorial! It is exactly what was looking for!

@seanmackenziedataengineering 2 года назад

Glad it was helpful!

@lailasatriasuhaimi5231 Год назад

Excellent teaching.Thank you Sir.

@seanmackenziedataengineering Год назад

Glad you liked it! Thanks for watching.

@jasonfleishman9884 2 года назад

I'm grateful for your generosity

@seanmackenziedataengineering 2 года назад

I'm glad this was helpful, cheers

@garycurtis 3 года назад

Excellent presentation, thank you so much

@seanmackenziedataengineering 3 года назад

Glad it was helpful!

@digvijaygujale9433 3 года назад

I recommend my team to join your Channel!!!😋

@seanmackenziedataengineering 3 года назад

Hey that's great! Thanks for the recommendation!

@jasonfleishman9884 Год назад

Watching the 4th time through, it's easy.

@seanmackenziedataengineering Год назад

Glad it worked! My first time I probably looked at it ten times :-) Once you get it, you get it. Nice work

@imstevemcqueen 2 года назад

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?

@seanmackenziedataengineering 2 года назад

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.

@michellebaca1607 Год назад

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

@seanmackenziedataengineering Год назад

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

@michellebaca1607 Год назад

Thank you, I will definitely try that

@skylinemeup 3 года назад

Thank you!

@seanmackenziedataengineering 3 года назад

You bet! Glad it helped

@hermanbroeckx8329 3 года назад

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?

@seanmackenziedataengineering 3 года назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-9iSuI3dR75o.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!

@tamalchakraborty1277 2 года назад

But, again I have stumbled on generating Defaulters' List. How to generate a list comprising names of defaulters monthwise

@seanmackenziedataengineering 2 года назад

Great question! I think that would be a good idea for a video.

@siclucealucks 2 года назад

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 ...?

@seanmackenziedataengineering 2 года назад

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.

@siclucealucks 2 года назад

@@seanmackenziedataengineering Thx for your suggestion I will try it !

@jrsam5075 3 года назад

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

@seanmackenziedataengineering 3 года назад

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.

@jrsam5075 3 года назад

Hi, but dont have any idea how to do that.

@seanmackenziedataengineering 3 года назад

@@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?

@sukriereke9764 3 года назад

ini sangat membantu

@seanmackenziedataengineering 3 года назад

Thanks for your comment. I'm glad it helped you!

@ballaomer6738 8 месяцев назад

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

@seanmackenziedataengineering 8 месяцев назад

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.

@ballaomer6738 8 месяцев назад

@@seanmackenziedataengineering actually i don't understand, could u help to me what should I do or rewrite the code to me Appreciate

@seanmackenziedataengineering 8 месяцев назад

@@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.

@ballaomer6738 8 месяцев назад

@@seanmackenziedataengineering yes it shows all records in immediate window, but why it doesn't show all data in a form , its my question Regards

@ElshanJafarov 10 месяцев назад

HELLO.How to Generate All Possible Combination IN ms ACCESS ?tHANK U

@seanmackenziedataengineering 10 месяцев назад

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

@tamalchakraborty1277 2 года назад

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.

@seanmackenziedataengineering 2 года назад

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.

@tamalchakraborty1277 2 года назад

@@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.

@tamalchakraborty1277 2 года назад

Yes. It is available in Access 2007 too. I have done it as advised by you. Thanks

@docdoc8824 2 года назад

do you have a sample data file of the data base?

@seanmackenziedataengineering 2 года назад

Not right now, but I'm working on getting a repository up for that purpose. Stay tuned!

@ajitvyas19 9 месяцев назад

Vb code for adding records in access table

@seanmackenziedataengineering 9 месяцев назад

Set rst = db.OpenRecordset("MYTABLE") With rst .AddNew !Field1 = "ABC" !Field2 = 19 !Field3 = #2023-12-01# .Update End With rst.Close

@ajitvyas19 9 месяцев назад

ThAnks but I want add one by one trough vba code and refreshing table through code. Again thanks

@mAkAsh072 3 года назад

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

@seanmackenziedataengineering 3 года назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-zi_D8EyW418.html

@mAkAsh072 3 года назад

@@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!

@seanmackenziedataengineering 3 года назад

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

@charliesk6239 3 года назад

@@seanmackenziedataengineering Excellent solution Sean! Love your videos and coding style.

@dbdata9836 3 года назад

thanks

@seanmackenziedataengineering 3 года назад

You're welcome!

@tamirmoav3774 3 года назад

when I tried the openRecordset there was an error - too few parameters

@seanmackenziedataengineering 3 года назад

Maybe you spelled a field wrong. A typo can cause this.

@tamirmoav3774 3 года назад

@@seanmackenziedataengineering nope. the reason was trying to put a name of query instead of it's string. thank you

@siclucealucks 2 года назад

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?

@seanmackenziedataengineering 2 года назад

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!

@siclucealucks 2 года назад

@@seanmackenziedataengineering I choose Patreon instead ;)

@seanmackenziedataengineering 2 года назад

@@siclucealucks cool!