53. VBA - Recordsets Part 1 (Programming In Microsoft Access 2013) 🎓

[@ProgrammingMadeEZ]

Looking for DAILY news and commentary? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things. Coffee With Steve: kzbin.info/door/eXAUvo5xxDY_b-lSknPC1A

[@artsafari8157]

I searched far and wide for videos explaining Access and VBA. I did purchase some books but your videos explain the processes in a simple, clear and thorough manner. I really appreciate it. My only regret is having to suffer through other youtubers before finding your page. Your channel should be the top result when someone searches youtube for Access help. Keep up the great work!

[@mahanpathak24]

You are a natural trainer. Very good method, examples and flow throughout the series.

[@ProgrammingMadeEZ]

Thank you very much for the kind words. I'm glad you are enjoying the series.

[@michaelliang8601]

SO FAR I THINK U TEACH THE MOST ADVANCED LEVEL IN MS ACCESS VBA CODE. I M VERY IMPRESS. THANK YOU FOR YOUR TEACHING.

[@campeonperron]

Thaks alot for the video. Your explanation is very clear and useful.

[@zhoudan4387]

I’m gonna go ahead and tell you please go ahead and keep doing this videos!

[@camandjacoby603]

Thank you so much I'm new to Access and have been trying to figure out how to do this for a bit now.

[@daniellepaz2906]

I'm using yung Videos as reference. I'm a beginner and you really help me out. Thanks!

[@alitalib1351]

Thank you very much I learned a lot from you

[@tomdytrych]

This an excellent training source. You make it so easy to understand. Thank you.

[@Abdurop2356]

Bundle of Thanks to you dear

[@andromedats]

Thank you so much! this video helped me so much!! congratz for you awesome work!

[@DolittleZoo]

Really great video. Can't believe how you made it so easy!

[@Tarun.g]

Thanks Steve.... I am learning ACCESS VBA from your videos. You might not have thought your videos can actually save someone's job. In my current job, I have been to asked to manage few complex Access database whereas I am DB user with little VBA (Excel) knowledge. Kindly let me know if you also provide private sessions for these videos. As mentioned by others, you are a Godsend for many.

[@ProgrammingMadeEZ]

I don't give out private lessons because most of the time people want work done on their database. Unfortunately I cannot work on a database outside of work since I would then be competing against my employer. I can answer questions with regards to the videos, but I cannot do any work on a database without charging through my employer.

[@Tarun.g]

Ok thanks Steve...

[@mrez0]

Thanks, that is the best video course on that subject

[@daxvilbar7761]

Loved your video.. Very well explained, defined, etc. tutorial.. My big thanks

[@df4250]

A very excellent set of videos and very well explained. Well Done.

[@ProgrammingMadeEZ]

Thank you!

[@EastArtBat]

It's simply perfect, thank you !

[@JCAlexandres]

SQL = Structured Query Language 😉 by the way, thank you for sharing and setting these lessons open for people to learn, WTG!

[@TheAnthonychalloner]

Thanks for your knowledge and support Steve Regards Tony

[@ProgrammingMadeEZ]

You're welcome! Glad you enjoy the videos.

[@joybinion1285]

Excellent Videos! Thank you! Please continue

[@TomMCgames]

Awesome video! You are helping alot of people ;)

[@KHBrainTV]

Can you make more Video for Topics relate to Recordset sir.

[@antongreffrath3581]

Hi Steve All your videos are exceptional. Thanks for all the time you spend on this learning initiative. Quick question, do you have any videos/learning material on Command Bars and Popup menus?

[@jackmiller8227]

Note: SQL = "Structured Query Language" You asked in the video...

[@jordanfrey9109]

Very good video. Thanks for the upload!

[@sharmaneaurora6332]

Hello Steve, how to use recordset for linked tables

[@jayd3578]

is there a way of setting a maximum number of records to 1000. eg records go from 0 to 999. thereis no 1000 or 1001 allowed? can you help?

[@SamnangIdea]

VERY GOOD DEAR

[@tamalchakraborty1277]

Sir, how can I generate a list of defaulters in student database in access. Fees are supposed to be deposited every month. So, after a period say six months we need to know the names of the students who have not deposited the fees in time.

[@larrywelch9359]

Do you have or know of any available course that I might purchase pertaining to: MS Access, VBA Code, creating a function that will set the ActiveForm and ActiveControl (of function) that can be used on the current open form.

[@loom-age330]

Thanks a lot

[@kadekrenipuspawati9652]

Thank you sir

[@wattjock2405]

Thank you for the video series! @17:42 I get: Run-Time Error '94' Invalid Use of Null My code is exactly as yours yet I didn't see that you get that Error Message... ? Do I put in Error Handling of Resume Next to skip over Null values? For now, I went and filled in the Null records.

[@tigerdataimarticus7088]

Showing error "too few argument required" on when set rs

[@bsquochoainew]

I'm working with access 2019, i want to implement a webcam to a form to capture image of my employees. Please give me some sugestions, thank you so much.

[@Moriarity09]

I am using the RowSource Property for my ListBox but the columns show in reverse order . Any suggestions to fix this ?

[@anjanaboddu8705]

PLEASE HELP ME IN FIXING THIS DCOUNT("[PROJECT_NUMBER]", "[JP_PO_VENDOR_INFO_TABLE]", "[PROJECT_NUMBER] =' " & Me.PROJECT_NUMBER & " ' " And "[PO_NUMBER] = " & Me.PO_NUMBER) USING THIS IN MACRO BUILDER WHERE [PROJECT_NUMBER ] IS STRING AND[ PO_ NUMBER] IS NUMBER

[@richardiru9587]

Hi would this work for Office 365?

[@aberseak]

i get a runtime error 3709 search key not found in any record when i use this. i cant find a solution. it occurs on the next line after the rs.eof

[@Adnan_Khan24]

Great excelent video.

[@nawidahmadnoori737]

How to save the result of a query into a variable

[@D3_Business_Analytics]

i cannot define rs as recordset.....it says user defined type not defined

[@shamimsworld1303]

Hello! Steve. Thanks a lot for your video. I am able to save data into table using this way. But how can I save image in database with data? Will you please help me?

[@Erika-tq1jo]

I am trying to populate a multicolumn combo box. I have the first column populating as shown in your video. How do you get the 2nd column to populate? Thanks!

[@radiusbecka1799]

This works for combo boxes too if anyone is wandering

[@Chopy61]

May I ask what the are advantages of using Recordsets?

[@df4250]

Just one question about populating the list box with the Me!lstEmployees.AddItem rs!UserName command - If I've set up the list box to display more than one column, what is the syntax for populating the second (or subsequent) columns?

[@ProgrammingMadeEZ]

You would use a semi-colon to distinguish between each column

[@JDKing-oe7jj]

Thanks for a very well explained and easy to follow set of tutorials. I have a question sort of related to this series of tutorials. I’m trying to do a dlookup on multiple item selected in a multi select list box and then pass those values to another form or report. For example in my listbox is my list of client names. I want to select multiple clients, perform a dlookup to get the clients address and phone number and then have those values displayed on another form or report. Can you point me in the right direction?

[@singda9803]

Dear steve , i have this problem and stuck on it from some days, while writing code on vba after i wrote set rs = db. It doesnot gives me option (properties) neither parenthesis later and also after typing me. Same thing happens no option to see, and while debugging compile service it shows compile error on Dim (db As Database) i.e error on bracket db as database, i tried the workfile its ok on that but doesnot work on my file, could u plz suggest me, thank you.

[@hottmesss3318]

nice oneee 👌

[@tajani59]

Excellent Job, keep it up, i am developer in VFP for last 10- years, I am going through your training, you did mention trim(a) or alltrim,, how do you do substring(a) if a="abcdef", you just want "abc" in vfp we use substr(a,1,3)?

[@ProgrammingMadeEZ]

I did not discuss Trim as I recall, but it's very easy. It's a built in function of Access. Trim(" MyAccessString ") converts to "MyAccessString". As for substring, the VBA version is mid()

[@TheRizwaanraja]

HI Steve, Firstly I just wanted to say that i really appreciate you taking the time to put these videos together. I have been using them to build my own db for a part time school.. The issue is that for some reason any macro / vb event /subform that i add to a form i get OLE / ActiveX errors "A problem occurred while MyDB was communicating with the OLE server or activeX control" I have migrated to a new DB and still have the issue (eventually), I scrapped the problem form and rebuilt it.. Same issue... I have found a multitude of articles online regarding other users that encountered the same issues but the workarounds dont work for me! Just wanted to know if you have come across this before? Regards, Riz

[@ProgrammingMadeEZ]

There are simply too many reasons this could be happening for me to even venture a guess. YOu would need to be much more specific about when the error occurs and what the code is trying to do when it happens.

[@jsk4774]

Hi Steve, Fantastic video lessons. Thank YOU. Can you tell me how do I reference a field on a continuous form using VBA? I tried using examples give on your Recordset video, but it didn't work. I ran into trouble when I set rs = db.openrecordset ("queryname") it saying there is missing parameter. Your example is simply enough to follow, so dont know what I did wrong, the only think I could think of is that my query is a parameter query. So not sure if your example is applicable to parameter query? Please help

[@BlondishTony]

Hi i have a doubt, the thing is that when i try to run my code it tells me the following error "Run-time error '3061': Too few parameters. Expected 4." And i don't understand where my problem is since i pretty much copied your code on mine. Here is my code: Dim db As Database Dim rs As Recordset Private Sub boton1_Click() Set db = CurrentDb Set rs = db.OpenRecordset("SaldoCap", dbOpenDynaset, dbSeeChanges) The line where i state the "OpenRecordset" command is where i get the error. Any help?

[@MRGhass]

I received the same error please any help guys?

[@arlettepwatsonify]

Thank you for this video!I'm not sure if you are still reading this thread, but in case you are. I've reached Part 3 of this video and was following just fine but I am now getting Error code 94 (Invalid Use of Null) on the Step: me.lstUser rs!UserLoginWhat am I missing here?

[@kaaa3485]

Thank you so much for the video. it was very helpful. I have a question. I created a bottom in access to update the tables and it was working fine but know it show me an error (Run-time Error '3052' "file sharing lock count exceeded. Increase Maxlocksperfile registry entry). I went and increased and used "ffffffff" (8 f) using Regedit.exe but it still get the message. this is one of my coding. Do I need to do anything else? Set rsglobal = dbs.OpenRecordset(SQL, dbOpenDynaset) Thanks in advance!!!

[@shafiushaibu6881]

Hi Steve! Thanks for the great work. You are one of my Access Heros. I have learnt a lot from you and want to thank you for the knowledge shared. Please I want to utilize the list box that I generated from the record set tutorial. I want to evaluate, on the click of a button, whether a value a user enters into a text box is among the items in the list box. I want to return a message to say "the item is not part of the list". Please help me with this. Kind regards, Shafiu.

[@danielpaulukinas9148]

Hi Steve, Do you have a video on how to extract data from a current form, which is controlled by a query and import the data into the body of an email? I tried the PDF and/or HTML attachments, but this does not work in our application. Your video series on the VBA coding is great. Thanks Dan

[@ProgrammingMadeEZ]

Not anything that specific, but I have videos on how to generate emails and attach files.

[@prabhumurugan8279]

How to avoid bloating after the Dao query is executed

[@ProgrammingMadeEZ]

Bloating?

[@SSH5000]

thank, this is really awesome; but I have a question, should we close the recordset ?

[@ProgrammingMadeEZ]

Should is the operative word. You don't have to, but you probably should to free up the memory immediately.

[@tamirmoav3774]

he insisted on 4 paramaters and not 3 as you showed. why?

[@Leonardo-ih9iy]

Hi Steve, super Job!! One question. I have a query which selects 1000 datasets. I like to add 1 new dataset, WITHOUT selecting all the others. I mean, if I do rs.AddNew -> and check rs.recordcount -> 1001 records, but I only want to have 1 record. It must also be possible rs.MoveFirst and rs.edit. Thanks a lot in advance!

[@jeffreyjansen]

Hello Steve, thanks again for your great video's. I have een specific question about this subject. I want to fill a field in a query with values from another table that meet some specific criteria. To be more specific: I have a table called tblTime with fields ID and Time (that contains values like 0:00, 0:15, 0:30 ....23:45). Another query holds fields like StartShow (short time), EndShow (short time), Stage_ID, Act_ID. I like to have some sort of output (a list or table) that contains ALL the values of the field Time and in the field Stage_ID the values of Stage_ID only when Time is between StartShow (>=) and EndShow (

[@michaelliang8601]

CAN YOU COVER DICTIONARY COLLECTION ?

[@aaronaaronaaron5922]

Hi Steve! Thank you again for your excelent videos. I have a little question: How can i add more than one column to my listbox? Nice day

[@ProgrammingMadeEZ]

aaron aaron aaron Just add it to the source query and increase the column number property of the listbox.

[@leomaredcampos4941]

now how to split that?

[@jerhummemarcelo7194]

hi mr. steve bishop, in adding items in recordset what should be the code if you want to retrieve all the records in a table not only the username for example. In the me.lstbox.additem rs("username")? thanks so much

[@ProgrammingMadeEZ]

+Jerhumme Marcelo I'm not understanding your question. First you are saying your adding items to a recordset, but then you're asking how to retrieve them. I don't understand.

[@jerhummemarcelo7194]

+Programming what i was trying to say was when want to display data in listbox for all the columns in the table reference or the recordset table

[@ProgrammingMadeEZ]

Well if I'm just going to make a query/table display to a listbox I usually just set it as the rowsource. You can also set the recordset directly to the listbox which I demonstrate how to do in my advanced series. But there is a third way to do this and that's by converting your recordset into one long semi-colon separated value string and set it as the rowsource after changing the list box to a value's list.

[@jerhummemarcelo7194]

+Programming ok I think ill just go by setting the table as its rowsource thanks mr bishop

[@kabukijoe99]

Hi. thanks for the lessons. Can you help me please? I have a table in database and i wan to take a value from "x" field an put it in a "y" field. my code looks like this (prnt.sc/vxu48v), but i have an error "update or cancelupdate without addnew or edit" What I do wrong? Thank you

[@bee333wasp]

Hi Steve, I used this technique to populate a combo box for selecting a record on a form, but I notice that any name in my text field which contains a comma (for example, "4.1.2 Name, Role, Value"--it's a db for web accessibility information) is truncated before the first comma. This means they won't find any match in the records. Any idea how to get the full string content of the field into the list? (Also, I find double clicks handy for quickly inserting [Event Procedure] and toggling property options, in case this is new information.) Cheers, Chris P.S. Your course is exactly the kind of program which should be on lynda.com--it's far superior to anything they have presently!

[@ProgrammingMadeEZ]

Thank you Chris. I know I could probably got to Lynda or Pluralsight and post videos there, but I am trying to share my knowledge with others freely. I don't like the idea of keeping the information to only those who can afford it. As for the comma's in the data, you can use the Replace function to replace a comma in the data with char(44). This will display as a comma, but the data will be a char function.

[@bee333wasp]

We're lucky in Auckland New Zealand that the public library gives its members free access to lynda.com. But you're right, I agree it's a great public service you are offering and I am very grateful indeed. As for commas, I added to the Do Until loop Me.cboSelect.AddItem Replace(rs("SuccessCriterionName"), ",", Chr(44)) , but the result is the same, truncated names! Even swapping out the record source with a query in which I'd updated the all the names containing commas didn't work. So I'm stumped for now.

[@jbrendanpeters]

Thanks for your knowledge and support Steve Regards JohnP

[@ProgrammingMadeEZ]

And thank you for supporting me!

[@kadekrenipuspawati9652]

Thank you sir

[@singda9803]

Dear steve , i have this problem and stuck on it from some days, while writing code on vba after i wrote set rs = db. It doesnot gives me option (properties) neither parenthesis later and also after typing me. Same thing happens no option to see, and while debugging compile service it shows compile error on Dim (db As Database) i.e error on bracket db as database, i tried the workfile its ok on that but doesnot work on my file, could u plz suggest me, thank you.

[@ProgrammingMadeEZ]

+bijay rai Three things. Make sure you are defining rs as a recordset. Also, make sure you are setting db = currentdb. Lastly, you may have to use DAO.Database and DAO.recordset when defining db and rs.

[@singda9803]

Is it because im using ms access 2013?

[@singda9803]

And why my Me. Is not showing available properties and list box, text box?

[@singda9803]

DAO. Worked thank you

[@singda9803]

+Dear steve, my problem is solved, but i have a question that why i need to write DAO. Before database and recordset?