40. VBA - DoCmd (Programming In Microsoft Access 2013) 🎓

A newer version of this series has started for Microsoft Access 2016 here:
• Microsoft Access 2016 ...
----------
VBA - DoCmd 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/Xipooo/Programming...
----------
Become a member today for only $5 a month!
/ @programmingmadeez
When you join, here’s what you’ll get:
* Your name in the credits
* A form to ask me for help on your project
* 10% off in the Teespring store (discount code)
----------
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: / @coffeewithsteve

[@no_short_circuit]

Steve, I've been playing around with Access as a hobby for a long time and watched hundreds of video tutorials. I know a little VBA but these videos are just fantastic, I've learned so much more and far quicker than I would normally. Thanks so much for giving your time to produce these videos

[@synergybydesign6783]

ditto...add more superlatives "excellent,"clear and couldn't be better. thank you .....

[@kennedykariuki1019]

A very big Thanku Mr Steve Bishop for taking time to make this video. It makes it possible for self taught programmers like me to know even the minute details. I'll keep following the whole series and stay blessed

[@geekpros]

I still find Steve Bishop videos to be the best and most comprehensive explanations of any videos - even now Feb of 2017. Still works great with Access 2016. Also, there are a lot of bad videos with goals to get you to call them because they confuse you. Be careful of those videos and stick with Steve's.

[@ProgrammingMadeEZ]

Thanks Geof... Really appreciate it man.

[@raygonzales6861]

I have never come across videos like this before. By that I mean so helpful and with such good instructions. Thank you so much. What you have done is inspirational to me.

[@ProgrammingMadeEZ]

Thank you!

[@conradblume4274]

Very helpful for us beginners, thank you for taking the time to make this video!

[@prasadintergulf]

Sir, so kind of you to produce such an informative video........Thanks a lot..expecting more form you.

[@johndavy3820]

Steve Bishop's videos are excellent! Wow!

[@ProgrammingMadeEZ]

Thank you!

[@moc2034]

Thank you so much.. this is the best tutorial I have come across and I learned heaps!! Excellent!!

[@ProgrammingMadeEZ]

Michael OConnell Thank you for the kind words.

[@misterb1080]

Love your video series.

[@mariebrown3532]

Steve, great video. Thank you!

[@shaheenmasood9091]

Stay blessed brother You doing a nice job. Nice tutorial. You make VB look like a cake. A tasty one.

[@jalaluddin8276]

very great lecture. very helpful thanks . keep it up

[@fawazmusa]

Excellent Videos.... Learnt a lot from them... Thanks Buddy and keep it going....

[@ProgrammingMadeEZ]

Thank you Fawaz! I'm glad you are enjoying them.

[@nama6909]

Really helpful tutorials, thank you for your efforts!

[@ProgrammingMadeEZ]

You're welcome!

[@unhondurenomas]

thanks a lot from honduras, you are very kind form all those explanation

[@yidianwang491]

Great video.it is helpful! thanks !

[@anthonyrc6618]

If anybody is curious about using a "WhereCondition" with DoCmd.BrowseTo, when you get to that part of the syntax (it should be highlighted in dark black), type in something such as: "[ID] > 20" in order to open a form with any ID that is strictly greater than 20. Just expalining, given that the info on the MS site doesn't seem super clear.

[@navadgiarun]

Nice Tutorial sir. I Expect more videos from u

[@AliTwaij]

Really useful thankyou Steve

[@babargohar1747]

thanks steve

[@rajtorane6119]

Exllent class... Thank you very very much ❤️🥰🥰

[@laithhameedalazawy]

This is helpful

[@jerzykepinski]

Thanks!

[@ProgrammingMadeEZ]

Bill, I'm having a hard time replying to your comments. They're always a "Linked Comment" without any way to reply. First, thank you for the compliment about the video series. I honestly didn't feel like there was enough fully explained examples out there. It seems anywhere on KZbin or even several pay sites, they tend to gloss over some of the finer details and reasoning. So I am hoping to be much more thorough than elsewhere. Second, yes, you can only use SendObject if you have Outlook installed. It is presumed that if you have Access, chances are you have Outlook as well.

[@no_short_circuit]

Steve.....thanks for the prompt reply. I have Outlook installed but the above code fails. I use Windows Mail as my default mail client, is that the reason?

[@asadwarsame2155]

Programmi/ hi9&

[@marcinofsky]

Excellent

[@randalhauck7802]

Fabulous videos! What is the IMMEDIATE window exactly and how do you use it?

[@TAPTheArtsPeople]

if I have two tables builder and property, and the access database has records from more than one property, how can i see and use the property name selected in the opening form to pass on to the rest of the application, so that all reference are selected for the selected property and no values from another property are seen or used in the queries thereafter.

[@fahdbadr4715]

You are unique

[@vivvpprof]

Wow, I didn't know about this F1 trick with context help :O Thanks! BTW are you familiar with the "With" clause in VBA? It speeds things up and helps keep a cleaner code.

[@shahbaaz3667]

Hi! I want to export multiple tables to the same excel workbook sheet wise. For example: I've 3 tables named as tableA, tableB and tableC in access DB, I want to export all 3 tables into same excel workbook but each table to be exported into a separate worksheets under the same workbook.

[@OppositeAppearsToBeTrue]

Hi Steve, I have been trying to add a date picker to a On Click for a calendar image, is that possible using the RunCmd as there is no useful info or I I've been unable to find any. Any help or tutorial would be great and the only one. Thank you.

[@andreikhalo3268]

Hi steve, you've done a very good job with your video tutorials, Thank you so much on behalf all the Microsoft access newbies. Please in this video (40. VBA - DoCmd) you had a list of "do commands" you explained I am guessing there are obviously more so my question is as a beginner which of these DoCmd method/properties would be used frequently (should have at our finger tip)?

[@ProgrammingMadeEZ]

+Andre Ikhalo actually it's the ones I showed in this video

[@dr.marshamays-smith7862]

Thank you very much. Is there any chance you have done or will do a video on how to make a multilayered menu for an application?

[@ProgrammingMadeEZ]

I will be doing more complex concepts in my advanced series. Subforms, tabs, using recordsets for your forms instead of queries and tables, how to migrate to MS SQL... lots of good stuff.

[@lion_0077]

@Programming Made EZ How to use cascading combobox in continuous forms???? Or continue sub form???

[@mohamedalkaabi1231]

Oh man finally i found the command that get rid of the Ribbon !! Thank you..

[@satyabanukil779]

I have two tables with two separate forms. The first table based on the first table has a calculated text box. I want to show the textbox data to a field to another form. How to do it. Thanx and regards.

[@chajri]

How can I open a crystal report from a command button on a form in Access?

[@Lomaxient]

This is way out of my league, but somehow still possible to follow... looks like I'll be watching episodes 1 to 39.

[@stavrous100]

how can vba apply a value to a field of a new/existing record instead of applying it to a form control in case we dont want to display that control?

[@moodyrcf]

id like to go to a record using a textbox and a button. how do i do that?

[@gen7132]

Hi Mr. Steve! I watched this video and it helped me a lot (I'm a beginner). I would like to know how am I going to build a code that has an output of- when i open a form, the textboxes are cleared. I'm having a hard time doing it. Thank you!

[@ProgrammingMadeEZ]

Geneva Lopez Do this for all of your textboxes: me.textbox1 = Null Replace texbox1 with the name of the textbox.

[@youssefsaad9197]

Hi Steve what about if I open a form with the openform and I want to go to control on a subform on the form I just opened, I tried this code Docmd.openform "Form2" Docdm.GotoControl "TransactionID" >> which is text box name is located on a subform on Form2 (the one I jsut open in the previous step) and it didn't work. How can I get around this problem? Thank you in advance.

[@amith5736]

Hi Steve, Thank you so much for the great videos :) need your advise on OutputTo. It seems the command also prints (the reports in my case) and not only saves with a desired format. How do I command to cancel the printing thus saving the report only. Thank you!! Amit

[@ProgrammingMadeEZ]

+Amit H The docmd.OutputTo command only exports it to the specified format. It doesn't cause a print.

[@dr.marshamays-smith7862]

Steve, I have been trying to get a handle on Access and VBA for years as I have an idea for an application. I have paid for courses on-line, bought several books and still have trouble. Your course is amazing. I love how organized your brain is. I have learned so much already. Still, there is one thing, a very fundamental thing that alludes me. I think it is probably such an obvious thing that I should be embarrassed to ask but here goes.... I have a table named Family Impact in a database called Family Impact. I want to view a field in the table called school but I do not know how to reference the field! I understand variables, loops etc but just don't know how to grab the field school and then move to the next record in my loop. I used to work with Dbase and I think I am having some form of mental block. Can you help me over this hurdle?

[@ProgrammingMadeEZ]

Hi there Dr. Mays-Smith. When you get to the Recordsets section of the tutorials, you will see how to do precisely that. I would not recommend skipping ahead though. Many of the concepts you learn early on are involved in the later concepts. You could find yourself lost or misunderstanding something when you get there. You're only about 15 videos away from the Recordsets part.

[@jimvoorhees5569]

Very useful and helpful series. Thank you. I am converting from Visual FoxPro to Access. One thing I need to understand in VBA is how to extract specific fields from a large table and export those elements into and Excel spreadsheet. It is a client must have. Any suggestions would help?

[@ProgrammingMadeEZ]

You're looking for the command DoCmd.TransferSpreadsheet

[@weibinren92]

Can someone explain to me the differences between Refresh, Requery and Refreshrecord?

[@tosyoyamite6899]

2018!!

[@williamFrSFO16]

Okay . . . to answer my own question, below, here is what I had to do. First let me say the docmd.OutputTo did not work for me, since the file created was unreadable and could not be identified by Windows 10. The problem could be threefold, running Office 2010 on a Windows 10 OS on a 64-bit machine . . . whatever! It did NOT work. That is why I went to the TransferSpreadSheet method, which got the table there, as a workbook, but would not open it. So then I had to open the workbook, which was a bit easier to do. So here is what I had to do (The ErrorHandler is from the Calculator video, but was there just in case) . . . Private Sub btnOutputToExcel_Click() On Error GoTo ErrorHandler ' Variables for the FileName & Path Dim strFileName As String Dim strExportPath As String Dim strFileDest As String ' Variables for the Excel File Open Dim xlsApp As Object ' Populate FileName & Path strExportPath = "g:\AtBuffalo, Inc - Access 2010 & VBA\" strFileName = "Employees Worksheet" strFileDest = strExportPath & strFileName ' Check for and Delete the old workbook (Uses the Kill Function) If Len(Dir$(strFileDest)) > 0 Then Kill strFileDest End If DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tbl1Employees", strFileDest, True ' Prepare to open the new file Set xlsApp = CreateObject("Excel.Application") xlsApp.Visible = True 'Sets the file visibility to "front," otherwise it just "sits" on the Task Bar xlsApp.Workbooks.Open strFileDest, True, False Debug.Print xlsApp.Version Set xlsApp = Nothing ' On success Exit the subroutine! Exit Sub ErrorHandler: Select Case Err.Number Case Is = 11 MsgBox ("Cannot Divide by 0!") Case Is = 13 MsgBox ("A non-numeric value was entered in either Numbers 1 & 2! Check and try again!") Case Is = 94 MsgBox ("Please Enter A Numeric Value in Numbers 1 & 2") Case Else MsgBox ("Error performing funtion. Check your CODE and try again.") End Select End Sub

[@AbdurRahim-ot5gp]

A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control how to fix

[@howardammerman5961]

Steve: Really appreciate all the information you've presented. WOW. Watched #40, DoCmd, re: *.TransferDatabase, , , , Question, on export or import, are similarly named files overwritten? or what?

[@ProgrammingMadeEZ]

I haven't tried it, but I would imagine it does overwrite.

[@babargohar1747]

good evening mr. steve first of all thanks for loading these videos. can i access the example files which you are showing in the videos. Thanks and regards Babar Gohar Kiani

[@ProgrammingMadeEZ]

Yes you can. Just click on the link in the description of the video.

[@mohammadgilani9900]

Hi Steve Thank you for your effort . It is wonderful. Could you tell me how you create the table inside the form. I used the txtBox but only show me one record. How you create all record , or which command you used. I appreciate that. MG

[@ProgrammingMadeEZ]

I don't understand what you mean by create a table inside a form.

[@mohammadgilani9900]

I mean I create the textbox and put them together but I can only see one row of information at a time. But in your work file it shows like a table that you can add row and etc. I want to know which command you used inside the form in Design View that shows different from mine. Thanks M.G.

[@ProgrammingMadeEZ]

Change your form to a continuous form.

[@mohammadgilani9900]

Thanks Alot. Happy New Year. Cheers

[@tammybergen5244]

Steve, I was wondering if you knew of a solution or workaround for a 2501 error on a DoCmd.SendObject error [run-time error '2501: The SendObject action was canceled.] when a user closes an outlook email without sending it. Net searches show a variety of attempted/failed solutions, & the standard vba error trap (On Err go to Errhandler, Errhandler:, Select Case Err.Number, Case 2501 Msgbox "Message Cancelled.", Exit Sub, End Select, Resume Next) repeatedly fails to recognize the error. Apparently this has been going on since Access 2007. Do you know of a solution which would allow the user to edit the email, close it without sending should they choose, and still trap the error or exit the sub altogether? -thanks again for your wonderful work!

[@ProgrammingMadeEZ]

I have gone away from using SendObject. I would suggest you open the Outlook mail yourself as an object in VBA and set the properties. I demonstrate how to do that in the Advanced course.

[@marounsader318]

can someone help me please i can have these command to work although i download the tutorial file and waking step by step with the video but still cant have any of the command works, i dont even have a error just i hit the button and nothing happens please anything advice will be helpful +Programming

[@daneyvantoualeudjieukam6084]

Did you go on each "button", "event" and opened a code for each one? maybe you assumed that you could control any buttton via a single sub

[@marounsader318]

enable macros and content this was my issue :(

[@prasadintergulf]

Sir ..is it possible to make a menu in Access like window ribbon..like file, edit,,and drop down from that...is it possible to do for access..table, from, report....

[@ProgrammingMadeEZ]

Siva Prasad I'm afraid I don't understand the question.

[@coaching_engineer]

Steve, I'm kind of lost with this video because I'm trying to following along, but I can't figure out how to create the form. Did you go over how to create this type of form in previous videos. If so which video?

[@coaching_engineer]

Jaz Isom I figured it out. I still believe that a sort of brief overview of what you added to the video would help so that people can follow along better.

[@coaching_engineer]

Jaz Isom This video is very confusing at one point. You said that the field UserName is required, however you have the username for Joe as blank without getting any errors. How is that? Why didn't you say anything about that at the beginning?

[@ProgrammingMadeEZ]

Jaz Isom Sometimes I make mistakes. I'm making these videos with only a small plan ahead of time. No one is paying me to make them so I didn't go about putting a ton of pre-planning into them. I'm just trying to explain the concepts.

[@coaching_engineer]

Understood

[@giegoar]

Hi! I really need help with this one, it is frustrating. When I open the navigation form, the first subform (Home) is shown but not loaded, I have to click on the tab of the Home to load (functions and subroutines suppose to happen). How can I load the Home as soon as the Naviagation Form opens. Many thanks!! And keep up the good work :D

[@ProgrammingMadeEZ]

+Giego AR How are you populating data into the form? Is it through VBA code or a recordsource?

[@giegoar]

+Programming through VBA code, so whats happening is as soon as I login, the navigation form opens. So the last bit coding of the login form's (modal dialog type form) button is: DoCmd.OpenForm "Navigation Form" Forms![Navigation Form]![txtLogin] = TempLoginID Forms![Navigation Form]![txtUser] = UserName The navigation form shows the txtlogin and txtUser immediately properly as the form opens. So far so good. The first subform (tab) is Home and thats the coding: Me.User = Forms![Navigation Form]!txtLogin.Value Me.FullName = DLookup("UserName", "tblUser", "[LoginID] = '" & Me.User & "'") Me.Greeting = MyGreeting(SysTime) Me.Welcome = Me.Greeting & " " & Me.FullName Me.User.Visible = False Me.FullName.Visible = False Me.Greeting.Visible = False Me.SysTime.Visible = False So for some reason I need to make the home load twice (one auto, one by click) to retrieve the user from the navigation form. Please help :'( Many thanks!

[@balajimehtre5779]

How to fix error iApplication -defined or object - defined error in vba ms Access 2019

[@balajimehtre5779]

Please reply as soon as possible if possible . my email id is [e-mail] it is realy appreciated

[@howardammerman5961]

Hi again, Steve; in this video (#40), you start typing a SQL query as the object of a DoCmd.RunSQL. Great tease! Wish you would have done this completely, once at least, in the SQL videos, #61 ++. My question I was hoping would be answered is: Can a user pass a parameter for the query in, by entering their choice into a textbox on a form, have that saved as a variable, (string or variant), reference that variable in the query string ("SELECT......variablename FROM tablename WHERE columnname.txt = variablename.... "). In this pseudo code, the italicized items are choices selected by the user.

[@ProgrammingMadeEZ]

YES! In fact there are a couple of ways you can do this. You can actually edit the query itself: Currentdb.Querydefs("").SQL = "SELECT blah FROM blah WHERE Blah='" & me.txtBlah & "'" Or if you wish to do the DoCmd to run an update: Docmd.RunSQL "UPDATE blah SET blah='" & me.txtBlah & "' WHERE BlahID=" & me.cboBlahNumber I prefer Execute over RunSQL though... no popups. currentdb.execute "" I plan on showing this technique a bit during the advanced course. It's especially helpful when doing subforms.

[@TommyInVancouver]

Howard, why not make a query through the ribbon - create query - and use the qbe grid to put it together. Then when you right click in the background you can view it in sql. Simply copy and then paste this into your code.

[@ProgrammingMadeEZ]

Jonesy TJonesy But then his question is how to get the value from a textbox into the query. You can use the name of the textbox in a query, but I much prefer using the VBA to modify the query. It gets messy if the form is opened as a subform.

[@howardammerman5961]

Jonesy TJonesy yeah i thought of that. I have succeessfully written SQL statement in other _Click () events with no problems. But this one was kicking my tuckus. And none of the syntax i previously used, worked. Dont remember how i solved it, or what the workaround was. I am still trying to comprehend the syntactical rules.

[@bozhen-yuan7776]

Hi Steve, Do you have some tutorials for Excel?

[@ProgrammingMadeEZ]

Afraid not Bo. I specialize in Access, MS SQL, and .NET. I only use Excel when I don't want to take the time to make a database.

[@bozhen-yuan7776]

Programming thanks a lot. I feel that lesson #41,42 are pretty difficult. I guess I need more practice. Are you from the U.S. or Canada?

[@ProgrammingMadeEZ]

Bo Zhen-Yuan I am from the US.

[@williamFrSFO16]

Steve, I don't know if you're even monitoring this thread any more, but I thought I'd ask just in case. After building the form (which took some time (I missed your mention of "Continuous" the first and second times through), I tried to use the module to export the table to a worksheet and open it. I couldn't. It took a while on Access 2010/Windows 10 to get the Excel document to create, but I cannot get it to open. After trying the sub below, I got it to create the form, but it won't open. Private Sub btnOutputToExcel_Click() DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tbl1Employees", "g:\AtBuffalo, Inc - Access 2010 & VBA\Employees Worksheet", True End Sub Any ideas? Thanks and I greatly appreciate your time and videos, by the way.

[@ProgrammingMadeEZ]

You need the xlsx extension at the end of your file name.

[@williamFrSFO16]

Hi Steve! First, I am totally happy you are still monitoring this thread! I love your videos and I'm loving every minute of it! Anyway, adding the extension is fine, but the dang file still won't open. Anyway, please don't worry because I have a workaround. It's most likely out of the class scope, but I posted my solution above. I did modify my statement to put the path and filename as variables ('Probably should do the same with the table, too). Anyway, thank you, very much for your time in making these! You're a Saint!

[@babargohar1747]

i am asking about the access files .if these are available on net then please give me a link to download them and practice. access files in access format not the video. regards babar

[@Xipooo]

Yes, the Access database files are the work files. Just go to the work files link in the description.

[@babargohar1747]

can you send me a proper link for work file. or

[@babargohar1747]

how i can go to download these files

[@ProgrammingMadeEZ]

Babar, the link to the work files is in the description of every video I posted. drive.google.com/folderview?id=0BzRSeswr8T9kZUtXRmJ1VS1yQUU&usp=sharing You can go there to download the work files. They are separated into different folders based on the section being worked on, then the appropriate video number. For example, the work file for this video can be found by clicking on the link I provided, then go to the "5. VBA" folder. Then inside there you will find the "40. VBA - DoCmd" folder. Inside of that folder is the "Service Inc.zip" file. All necessary work files that were used in this video are in that zip file.

[@no_short_circuit]

Steve.....does the SendObject work with Windows Live Mail or is it only Outlook. I've tried this line of code and get a run time error 2293 Microsoft Access cannot send this eMail message DoCmd.SendObject acSendTable, "TblStudents", acFormatPDF, "[e-mail]",, , "Email Subject", "EMmail content", False, True

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

[@Ruunerwold]

Thank you for the great videos. How to open a record in a new form? (so I can make it edit form) Something like DoCmd.Openform with a Wherecondition? Is this correct? If so, can you give me the correct code? Thanks :) (like this video: kzbin.info/www/bejne/gmnRdnmCi7KEe5o accept he is using macros instead of VBA

[@ProgrammingMadeEZ]

Create a form to hold the data. In VBA set the recordsource to a SQL query that includes a where clause that filters the record down to just that one value.

[@Ruunerwold]

i created the form. Is it possible you make an example with some code?

[@ProgrammingMadeEZ]

Use the same method I show in the subform video of this series.