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: ua-cam.com/channels/eXAUvo5xxDY_b-lSknPC1A.html
Great explanation Steve! it's incredible how short your videos are and how easy you make to understand all those concepts. It's easier to me listening to you in english than listening videos in my own lenguage by other people. Thank you very much for sharing.
There was a time when i gave up trying to understand what does Byref and Byval means and how to apply it. I just couldn't get it from other tutorials and websites. So i just don't use them. But this tutorial and the way you demonstrate the lesson made me understand it even without freezing the video or repeating it. Thanks a lot Steve.
I would've also included the line of code op = "something else" and run the sub before changing byval to byref, it would've made the difference even more clear. Great tutorial nonetheless, really appreciate it.
Hi, thanks for the video. I would like to ask you if you made a video on how to pass a value from one masche to another. E.g. Master Form, I click on the button "Other Data" and open Form B in Form B there is a field called Provenance and in this field the name of the Master Form will be entered. Is this possible? Thanks
I would like to add to my earlier comment, which I posted for video 37. It would be useful to have text in the 'SHOW MORE' section which said 'difference between ByRef and ByVal' or 'passing multiple parameters' with links to those parts of the video. The search engines would have discern more hits to your videos because there are more text, that is, information, for the search engines to key off and send more customers your way. Just a thought........
Hi Steve, thanks very much for your incredibly well constructed youtube page and very useful videos. I am researching the functionality of Access and its ability to to create the amortisation of a loan. Which of your videos do you recommend watching to gain insight on this topic. I imagine a good understanding of the Array functionality would be useful.
Well, I avoid arrays in VBA like the plague. If anything I use Dictionaries or collections. That being said, since an array is nothing more than a table with 2 columns, perhaps that would be a better route for you to go since tables are very easy to work with and the data persists.
Dear Steve ! Thank you so much for your teaching ! Could you please explain the differences between parameters and variables. I am kind of confused. Thank in advance !
Hello Steve. Help. Access, multiple (10-15) simultaneous users and public variables. I am informed a lot can go wrong? What goes wrong and what measures can I take - apart from front end and back end separation? Thanks
I created a button to open a form . the button worked well . But there is a criteria in its QUERY . I want to pass the criteria through the coding . Ca I do it , with your kind help ?
Hey Steve Thanks a ton for your insight and knowledge. I self taught a lot in access and you helped re-enforce what I learned and then clarified so much, then expanded my knowledge so much more. I still have many questions but for today, wanted to ask this. I have a fe-be db. I think that I will make improvements with feedback and expansion of capabilities. Before I check the user on login, I want the fe to check that it’s the most updated one. I can’t use the be db because that where I want to store the latest update number. I was thinking of creating local version table for the fe and checking that to the be version table with a variable rs.movelast. Any advice?
This is a great video. However, at the end when you make the changes and have the code that is at the end with the bulk of the code in the function, the error handling doesn't work properly for if there is nothing entered into the box. So if the values or blank, it gives this message: Now, we were supposed to handle this is the code with the On Error GoTo problem line and the problem handles divide by zero and values not numeric, and then everything else. Well values entered as blank will not pass into the function and will give the above error.
Jaz Isom Yes if someone were to leave it blank it would not work. The video was directed towards demonstrating how to pass parameters, not on what to do if someone doesn't pass one. That is for another video.
I tried passing values to the function without using the msgbox function, but could only get it to work when passing the operator, it expected an end of statement or = sign if I tried passing value1 and value2. Thoughts?
+Programming Option Compare Database Option ExplicitDim op As String Dim a As Double Dim b As DoublePrivate Sub cmdAdd_Click() op = "+" getData' calc (op, a, b) does not work, but: just discovered that Call calc(op, a, b) does work. MsgBox calc(op, a, b) Debug.Print op End SubPrivate Sub getData() a = Me.txtValue1 b = Me.txtValue2 End SubPrivate Function calc(ByVal Oper As String, val1 As Double, val2 As Double) As Double On Error GoTo PROBLEM Dim a As Double Dim b As Double Select Case Oper Case Is = "+" calc = val1 + val2 Case Is = "-" calc = val1 - val2 Case Is = "*" calc = val1 * val2 Case Is = "/" calc = val1 / val2 End Select Me.txtResult = calc Exit Function PROBLEM: Select Case Err.Number Case Is = 11 MsgBox "Cannot divide by zero" Case Is = 13 MsgBox "Both values must be numbers" Case Else MsgBox Err.Number End Select End Function
+bgray79 Ahhh, think I know the problem. When you are calling a function and are not assigning the return value to anything and it is not being used within another function, you don't wrap the arguments in parenthesis. So this line: calc (op, a, b) Is incorrect. Just drop the parenthesis. calc op, a, b
I'm on Chapter 37. VBA - Functions and Subroutines. But it's beginning to feel like a bit of a slog.The whole reason I'm trying to learn VBA is because I want to create a button on a form that will either ask for a value or, better still, show a list of values, and then pull up all the records that correspond to the value typed in or selected. So here's my question: Up to what chapter do I need to cover in order to know how to accomplish that programming task? (Forgive my impatience!)
Are you looking for a way to filter data on a form based on a keyword? AKA a search option? If so, I have a single video on that. Just check out my How To playlist and it's in there.
Yes, I have an Advanced Access course that covers some of the more advanced topics. I also have another series of just How To videos on various subjects.
Programming When using byref, can you ignore adding a datatype? public function helloWorld (byRef myString as String) or can I do public function helloWorld (byRef myString)
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: ua-cam.com/channels/eXAUvo5xxDY_b-lSknPC1A.html
Where were you when I went to school ?? With a teacher like you the IT lessons would actually have been effective and fun.
Great explanation Steve! it's incredible how short your videos are and how easy you make to understand all those concepts.
It's easier to me listening to you in english than listening videos in my own lenguage by other people. Thank you very much for sharing.
Steve, that is the best explanation of ByRef/ByVal I have ever heard. Thank you!
There was a time when i gave up trying to understand what does Byref and Byval means and how to apply it. I just couldn't get it from other tutorials and websites. So i just don't use them. But this tutorial and the way you demonstrate the lesson made me understand it even without freezing the video or repeating it. Thanks a lot Steve.
I'm glad it could be so helpful!
Just the best tutorial for Access, in detail and systematic, thank you so much. Hope I can stay up to the Advanced part of your video.
Excellent - Love watching your tutorials... Thank you so much for making such a nice and valuable tutorials.
Thank you Ranjeet Kumar! Thanks for watching.
Awesome class. Great Job
I would've also included the line of code op = "something else" and run the sub before changing byval to byref, it would've made the difference even more clear. Great tutorial nonetheless, really appreciate it.
Hi, thanks for the video. I would like to ask you if you made a video on how to pass a value from one masche to another. E.g. Master Form, I click on the button "Other Data" and open Form B in Form B there is a field called Provenance and in this field the name of the Master Form will be entered. Is this possible?
Thanks
I would like to add to my earlier comment, which I posted for video 37. It would be useful to have text in the 'SHOW MORE' section which said 'difference between ByRef and ByVal' or 'passing multiple parameters' with links to those parts of the video. The search engines would have discern more hits to your videos because there are more text, that is, information, for the search engines to key off and send more customers your way. Just a thought........
Thank you for this important information
Hi
how can store vb array into access table and also how to retrieve the table as array i m using oledb for connection
Hi Steve, thanks very much for your incredibly well constructed youtube page and very useful videos. I am researching the functionality of Access and its ability to to create the amortisation of a loan. Which of your videos do you recommend watching to gain insight on this topic. I imagine a good understanding of the Array functionality would be useful.
Well, I avoid arrays in VBA like the plague. If anything I use Dictionaries or collections. That being said, since an array is nothing more than a table with 2 columns, perhaps that would be a better route for you to go since tables are very easy to work with and the data persists.
Dear Steve ! Thank you so much for your teaching ! Could you please explain the differences between parameters and variables. I am kind of confused. Thank in advance !
+bann heng Parameters are variables that contain information you pass to the function.
Hello Steve. Help. Access, multiple (10-15) simultaneous users and public variables. I am informed a lot can go wrong? What goes wrong and what measures can I take - apart from front end and back end separation? Thanks
Thank you
I created a button to open a form . the button worked well . But there is a criteria in its QUERY . I want to pass the criteria through the coding . Ca I do it , with your kind help ?
Hey Steve
Thanks a ton for your insight and knowledge.
I self taught a lot in access and you helped re-enforce what I learned and then clarified so much, then expanded my knowledge so much more.
I still have many questions but for today, wanted to ask this.
I have a fe-be db. I think that I will make improvements with feedback and expansion of capabilities. Before I check the user on login, I want the fe to check that it’s the most updated one. I can’t use the be db because that where I want to store the latest update number.
I was thinking of creating local version table for the fe and checking that to the be version table with a variable rs.movelast.
Any advice?
Thank you . Great Class :)
But why dont you have to give the function the value of the operator as a parameter? I thought thats how functions work?
This is a great video. However, at the end when you make the changes and have the code that is at the end with the bulk of the code in the function, the error handling doesn't work properly for if there is nothing entered into the box. So if the values or blank, it gives this message:
Now, we were supposed to handle this is the code with the On Error GoTo problem line and the problem handles divide by zero and values not numeric, and then everything else. Well values entered as blank will not pass into the function and will give the above error.
Jaz Isom Yes if someone were to leave it blank it would not work. The video was directed towards demonstrating how to pass parameters, not on what to do if someone doesn't pass one. That is for another video.
Ok got it. Just curious on your intentions. Thanks.
I tried passing values to the function without using the msgbox function, but could only get it to work when passing the operator, it expected an end of statement or = sign if I tried passing value1 and value2. Thoughts?
+bgray79 Can you paste your code?
+Programming Option Compare Database
Option ExplicitDim op As String
Dim a As Double
Dim b As DoublePrivate Sub cmdAdd_Click()
op = "+"
getData' calc (op, a, b) does not work, but: just discovered that Call calc(op, a, b) does work.
MsgBox calc(op, a, b)
Debug.Print op
End SubPrivate Sub getData()
a = Me.txtValue1
b = Me.txtValue2
End SubPrivate Function calc(ByVal Oper As String, val1 As Double, val2 As Double) As Double
On Error GoTo PROBLEM
Dim a As Double
Dim b As Double
Select Case Oper
Case Is = "+"
calc = val1 + val2
Case Is = "-"
calc = val1 - val2
Case Is = "*"
calc = val1 * val2
Case Is = "/"
calc = val1 / val2
End Select
Me.txtResult = calc
Exit Function
PROBLEM:
Select Case Err.Number
Case Is = 11
MsgBox "Cannot divide by zero"
Case Is = 13
MsgBox "Both values must be numbers"
Case Else
MsgBox Err.Number
End Select
End Function
+bgray79 Ahhh, think I know the problem. When you are calling a function and are not assigning the return value to anything and it is not being used within another function, you don't wrap the arguments in parenthesis. So this line:
calc (op, a, b)
Is incorrect. Just drop the parenthesis.
calc op, a, b
Thank you.
I'm on Chapter 37. VBA - Functions and Subroutines. But it's beginning to feel like a bit of a slog.The whole reason I'm trying to learn VBA is because I want to create a button on a form that will either ask for a value or, better still, show a list of values, and then pull up all the records that correspond to the value typed in or selected. So here's my question: Up to what chapter do I need to cover in order to know how to accomplish that programming task? (Forgive my impatience!)
Are you looking for a way to filter data on a form based on a keyword? AKA a search option? If so, I have a single video on that. Just check out my How To playlist and it's in there.
Do you mean you have a separate series from the one on Access that I'm looking at? If so, can you send me a link? I'm not finding it. Thx.
Yes, I have an Advanced Access course that covers some of the more advanced topics. I also have another series of just How To videos on various subjects.
Thank you so match for your time
Wish you Luke in your live
Badi Alotaibi Thank you Badi! I hope you continue to find my videos helpful.
Programming When using byref, can you ignore adding a datatype?
public function helloWorld (byRef myString as String)
or can I do
public function helloWorld (byRef myString)
Joseph Kreifels II Not so as long as you have Option Explicit turned on.
Hi Steve, I love video but cannot access Work Files. Can you send me a correct link?
github.com/Xipooo/ProgrammingInAccess2013