54. VBA - Recordsets Part 2 (Programming In Access 2013)

Recordsets in "Programming in Microsoft Access 2013". Hosted by Steve Bishop.
In this free beginner video tutorial series Steve will be going over Database Development in Access, creating the User Interface, and coding the backend of the application in Visual Basic for Applications (VBA).
Click here for the full playlist of "Programming in Access 2013"
• Programming in Microso...
Click here for the Work Files of this series:
github.com/Xip...

[@daxvilbar7761]

Again thank you for the wonderful tutorial.. I really neglect this kind of method when it comes to record operations. I rely more on bound forms but it has certain limitations. After I have seen your tutorial, I am starting to appreciate recordsets... Looking forward for the next tutorials. Keep up the good work

[@no_short_circuit]

Thanks for a great explanation. Helps me get a better understanding of what's going on with recordsets

[@josephvizanko5672]

You explain it very well, thanks.

[@newstepforward-softwareedu2841]

Excellent, Thank you very much, a lot of things finally declared

[@Wasifali302]

Wonderful. Learning a lot . Many Thanks

[@campeonperron]

Good explanation, Thanks a lot for this tutorial. Regards.

[@gavinmackay2424]

Nice video Steve, thank you, but surely after you test for BOF and EOF in the button code, there is no need to test in the Refresh code as you would not call the code if EOF or BOF? I often start out like that and then realise a better place for some code, which makes other code redundant?

[@OEstudante]

thanks for it. I'm from Brazil. I don't speak english very well. Even though, I got it.

[@wattjock2405]

@3:26 ~ I still get an error message regarding the Null values being added to the lstEmployees box. I used the on error resume next to ignore the Null values.

[@satyabanukil779]

Seen some of your tutorials. Is there any tutorial on updating data of a second column based on data of first column by using loop function

[@menelaosreizidis8538]

hi Steeve. I was wondering what if we omitted the check on the refresh function? since we do have the check for BOF and EOF for the move buttons wouldn't that be enough?

[@karnabudhathoki5311]

Why the rs.EOF and rs.BOF check in both the Refresh Sub and Click event of the forward backward buttons.why not only in the click events of the buttons?

[@azadehgholipour6781]

what about attachment fields? I've got some unbound objects, include string, number and attachment in a form which asks user to fill them and select one or more files, and then by pressing save butten, files would be saved in a table. I couldn't do that, just with filedialog. all fields are saved except attachment. how can I save an attachment field via recordset?

[@-SunnyWild-]

Good afternoon! What is a simpler command to make so that when adding data to one table, data is simultaneously added to another table with the same columns?

[@harisrg92]

Hi Steve, You are controlling the values of unbounded text fields programmatically or manually. However the same task could have been achieved using form's Record source property and have the fields bound to it.1) What is the difference between Record source and Recordset? Also I am planning to put this database on network by splitting it into front and back end. 2) Should I be concerned about any synchronizing problems that may occur because of my decision right now on using either Recordsource vs Recordset? 3) Can it affect the speed of the system when run over the network? I like to control fields programmatically or manually, just the way you did in these videos for the Recordsets. It gives me more control that's why I want to stick to the way you did it in this video.

[@ProgrammingMadeEZ]

First off, yes you could do this as a recordsource instead of a recordset that you're managing through controls on the form, that was the point actually. I just wanted to demonstrate how recordsets work. Second, it really doesn't matter which route you go since a rowsource is actually setting a recordset behind the form. Third, if you're looking for the best way to manage your data interactions with a split database, you're going to want to watch the advanced series. I go over it in depth there.

[@riceblues7548]

Excellent!

[@anthonyayres6536]

I'm not sure if you still watch the comments on this video but I have a question. Is there a way to set a criteria with the do until? Like in your example above, if I only wanted to get results if the users first name Sam?

[@vilastonape192]

How to search record ?

[@RnMohsen]

Steve tnx for the wonderful learning. I wondring if the recordset object actually needs to be closed (also database) or not! Would you please clarify this for me?!

[@coaching_engineer]

After you fix the arrows to not give you any run time errors, something weird still occurs. For example let's say someone clicks the back button a few times when it's on the first record. Well then he/she will need to click the next button twice to get to the second record. The same thing happens at the end of the record. If someone presses next a few times when they are at the end, he/she will have to click the previous button twice to get the second to last record. Any ideas on how to fix this? EDIT: You explained how to fix this in the next video.

[@ProgrammingMadeEZ]

Jaz Isom lol.. yeah, I fixed it in the next video. It was helpful to have problems in some areas and explain how to fix them in later videos because that helps explain the need for some methods.

[@coaching_engineer]

Gotcha. Thanks for the clarification. I actually half expected you to do that. Thanks!

[@howardammerman5961]

Steve: does order of columns in the source table have any bearing on when the information is read from that column? i.e. my ID field is at end of columns but form's ID txt box is at top. Issues?

[@ProgrammingMadeEZ]

Howard Ammerman No.

[@williamtarabalka4920]

good vid

[@TheMasterFung]

Hey Steve. Thanks a lot for the videos first time using access trying to build database for home business. Quick question, can the same thing by way of auto filling unbounded boxes be accomplished with a combo box instead of a list box? Fighting with that here..

[@ProgrammingMadeEZ]

Yup. Same properties and everything.

[@TheMasterFung]

Thanks a lot. Sorry for late acknowledgement. Got combo box to load customer names, however the other unbound boxes only reflect what's in the first record and don't change when I select a different customer. Think I understand why just not sure how to fix.

[@berhanegebreslassie4049]

Hi Steve, thanks for your great explanation to the all the videos you demonstrating. I have one problem, I am sure you could help me. I want make a keyword search (for name) on my form from the data table, and i want to display the searched name on separate text box. what I mean is not to fill the form, but type on the keyword text box and display on another text box Thanks very much steve

[@jooryalhamed938]

please i have a problem with compile error which says : loop without do or end if without if ,and this happens when i put if statements inside the loop, do you know how to solve that ??

[@ProgrammingMadeEZ]

+Goory Alhamed You must not have properly formatted your do loop and/or if statement. Make sure your do loop has a "loop" after your code block, and your "if" statements have an "end if" at the end of the code block.

[@jooryalhamed938]

thank you so much mr steve... i had discovered the reason of this error >>> end if must be inside the loop

[@erikatkinson4221]

is it possible to use a combo box to select and edit multiple records in datasheet view??

[@littlesuperheroes]

Did you ever receive a response to this? Was hoping to do the same thing.

[@MoooNMoooD]

hey steve , just a quick question, why dont we just do a "on error resume next" after clicks ?

[@ProgrammingMadeEZ]

What do you mean after clicks?

[@MoooNMoooD]

you know how you debugged the error u got when clicking back at BOF or Next at EOF ? by using the if statements , right ? . . . instead of doing all that , couldnt we just ignore the errors to begin with by using "on error resume next" after those previous ext buttons are triggered ?

[@ProgrammingMadeEZ]

I suppose you could, but it's better to code for what could happen.

[@MoooNMoooD]

I also found a bug and I can't fix it , I don't know how to fix it :( push the back button a few times so and then try to save a change , it gives you an error , I'm not sure how to debug it

[@ProgrammingMadeEZ]

I believe this is addressed in later videos.

[@vijayvizzu1]

Hi Steve I regularly follows your tutorials and learned a lot from your videos. Here is my problem which i had faced recently. After setting the record set in a form (set rs = currentdb.openrecordset(tableName)), when i tried to count the number of records through debug.print rs.recordcount, it gives only 1 record but actually it has 590 records. But when i start do while loop, at middle of this, when i put debug.print rs.recordcount it shows correctly 590 records, even if the do loop was not yet completed May i know the reason why the record set was not updated until i put do while loop ?

[@ProgrammingMadeEZ]

Sort of a bug in Access, but if you do an rs.movelast then get your record count, it will be correct.

[@SuperHanme]

Thanks a lot.

[@Eagletailq]

Hey can I achieve this same function without the list box?

[@littlesuperheroes]

Did you ever receive a response to this? Was hoping yo do this from a combo box.

[@franksmit4914]

Hello, hope i get some answere, I`m trying to add a new user, but i get eror on rs.AddNew , "Objectvariable or blokvariable with is not set", what i`m doind wrong. Than you for these great video`s, helps me a great deal !!!!

[@franksmit4914]

nevermind (Again) found the problem.. the fields in employees needs to be filled in to add new employees. Username, firstname,lastname etc etc ..

[@jeyosman1]

Me.txtID = rs!ID is not allowing. It say we can not assign value

[@ProgrammingMadeEZ]

I don't know what your code looks like but I recommend you compare what you've written to the work files.

[@jeyosman1]

Thanks I found the my error typing error

[@renejamis2149]

Steve, I'm sorry if this is a duplicate posting. I normally see my posts but don't see it after having refreshed and I really do need some help with this to try and complete a DB I'm working on for work. I posted a plea for help with a Super Long explanation of what I had done to try and resolve the issue with no luck. Below is my current code. I've got to be overlooking something, but after 3 weeks of trying, I just don't know where the problem is. Private Sub Form_Load() Dim db As Database Dim rs As DAO.Recordset Dim X As Integer Dim rc As Integer Dim SQLstr As String SQLstr = "SELECT Count([Validations.ValID]) AS [CountOfValID] " _ & "FROM [Listeners] INNER JOIN [CategoryDetails] INNER JOIN [Validations] ON [CategoryDetails].[CatD_ID] = [Validations].[CatD_ID] ON [Listeners].[ListenerID] = [Validations].[ListenerID] " _ & "GROUP BY Validations.CatD_ID, Validations.ListenerID " _ & "WHERE [Validations].[CatD_ID]=49 AND [Validations].[ListenerID]=22));" rc = rs.RecordCount Set db = CurrentDb Set rs = OpenDatabase("qryTotValCount", dbOpenDynaset, dbSeeChanges) 'Set rs = db.OpenRecordset(SQLstr) rs.MoveLast rs.MoveFirst Do Until X = rs.EOF rs.MoveNext Next X Debug.Print rc rs.Close Set rs = Nothing Set db = Nothing End Sub I get varying errors depending on which OpenDatabase method I use . Please help. Thanks

[@renejamis2149]

Steve, I tell ya. I know where I went wrong. I had been glazing right past the error. It took a 2nd pair of knowledgeable eyes to point out the fact that my Set rs was wrong. Funny thing is, I had caught that at one time but was plagued by a different error and when I copy pasted original code from a different version of my project, it copied over the original error. UGH!!! Now I have to get the counter to count correctly and I'm in business. Thanks for the good videos. I'm working my way through the ASP Core 1.0 videos b/c I'd like to take this DB to the web.