UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Form Button To Call VBA, Access 2016    
 
   
isimj
post Dec 11 2017, 05:31 AM
Post#1



Posts: 6
Joined: 11-December 17



Hi all

I am pretty new to Access and VBA. I have been trying to get a button to work in my database using Access 2016 but I think it has beaten me so I am looking for someone to give me the answer; I will learn from there.

The code I am using is below

Public Sub ExportExcel()
Dim reportname As String
Dim excelreportname As String
Dim theFilePath As String
reportname = "StockNumProvided"
excelreportname = "StockIssuedReport"
theFilePath = "C:\Users\" & Environ("UserName") & "\Desktop\"
theFilePath = theFilePath & excelreportname & "_" & Format(Date, "dd-mm-yyyy") & ".xlsx"
DoCmd.TransferSpreadsheet acExport, 10, reportname, theFilePath, True
MsgBox "You will find the Stock Issued Report saved on your desktop"
End Sub

I can make this run in the VBA editor but I cannot call it from a button on a form. I have tried Onclick "=ExportExcel()", a macro calling the module and trying [Event Procedure] but I can't get it to work.

Can someone help me?

Go to the top of the page
 
GroverParkGeorge
post Dec 11 2017, 06:59 AM
Post#2


UA Admin
Posts: 32,833
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

Am I right in thinking you put this line in the Property Sheet?

If so, you need to change this from a Sub to a Function. Only functions can be invoked here:

Attached File  CallfromPS.png ( 17.76K )Number of downloads: 45


Of course, you can also put embedded macros in the click events.
This post has been edited by GroverParkGeorge: Dec 11 2017, 07:00 AM

--------------------
Go to the top of the page
 
doctor9
post Dec 11 2017, 09:37 AM
Post#3


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


isimj,

> I have tried Onclick "=ExportExcel()", a macro calling the module and trying [Event Procedure] but I can't get it to work.

An alternative to George's suggestion:

1. Remove the "=ExportExcel()" from the command button's Click event in the property sheet.
2. Click once on the white area to the right of "On Click" in the property sheet. A small button with three dots on it will appear on the right side of the property sheet.
3. Click the small button.
4. Select "Code Builder" from the dialog box that opens. You should be taken to the Visual Basic Editor, and two lines of code will be pre-written for you. These are the header and footer for the click event code. You can either add this line of code between the header and footer:
CODE
Call ExportExcel

...or you can cut/paste the contents of the ExportExcel subroutine to this new location.

The decider between these choices is this: Will the ExportExcel process ever be run from somewhere besides this button? If yes, then CALL the subroutine. If no, cut and paste.

Finally, if you ARE calling the ExportExcel code from multiple places, consider putting it in a standalone code module, rather than the one attached to the form.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
isimj
post Dec 12 2017, 04:36 AM
Post#4



Posts: 6
Joined: 11-December 17



Thanks for your reply. Have done this and when the button is clicked I get this message.
Attached File  2017_12_12.png ( 8.79K )Number of downloads: 16
Go to the top of the page
 
isimj
post Dec 12 2017, 04:40 AM
Post#5



Posts: 6
Joined: 11-December 17



Woops, I am getting the post mixed up. My previous reply was for GroverParkGeorge.

On a second note when I select the small button with three dots on it I cannot select "Code Builder" from the dialog box that opens. I see no dialog, I am just taken to the macro builder.

Thanks
Go to the top of the page
 
doctor9
post Dec 12 2017, 09:08 AM
Post#6


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


isimj,

> I see no dialog, I am just taken to the macro builder.

That's odd... Make sure you delete anything in that white space in the property sheet before clicking on it. Try clicking on the white space for another event that has nothing in it, and then click the three dot button - do you still go to the macro builder?

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
isimj
post Dec 19 2017, 03:17 AM
Post#7



Posts: 6
Joined: 11-December 17



Hi again - Completely frustrated with this. I cannot get the Choose Builder Dialog Box to appear. I have tried a reinstall of 2016, an install of version 2010 and none provide the Choose Builder Dialog Box. I have cleared fields and created new command buttons but cannot trigger the Choose Builder Dialog Box. I am modifying this MS template database http://clienttemplates.content.office.net/.../tp02483123.cab. If you have the time to check out the Report Centre page (the location I am attempting to place the button) Much appreciated if you or someone else could find the time to help. Thanks Simon
Go to the top of the page
 
GroverParkGeorge
post Dec 19 2017, 06:58 AM
Post#8


UA Admin
Posts: 32,833
Joined: 20-June 02
From: Newcastle, WA


It's hard for us to picture the exact problem because the behavior you describe is not typical and we're not sure what is behind that.

Let's go through some basic settings, starting with options.

Is this option checked for "Object Designers", under Form/Report design view?
Attached File  AlwaysuseEP.jpg ( 157.29K )Number of downloads: 22


When you are designing a form, there is an option available under the control gallery to indicate whether you want to use COntrol Wizards when adding controls.

Attached File  moreoptionsdesign.jpg ( 30.76K )Number of downloads: 11


Attached File  UseControlWizards.jpg ( 27.46K )Number of downloads: 9


What do you have selected here? It's not going to be too obvious because the color schemes favor light pastels on pale pastels, which makes it hard to differentiate unless you're quite alert to subtlety.

Finally, when you drop a command button onto a form, do you not see the builder button? What do you see here?

Attached File  BuilderButton.jpg ( 62.15K )Number of downloads: 9

--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 19 2017, 07:06 AM
Post#9


UA Admin
Posts: 32,833
Joined: 20-June 02
From: Newcastle, WA


I tried to open that template and received an error. Where did you get it from?

--------------------
Go to the top of the page
 
isimj
post Dec 19 2017, 03:17 PM
Post#10



Posts: 6
Joined: 11-December 17



Hi George - and thanks for the detailed response. I see everything you have pictured and have selected all the control setting referred but still no code builder.

The template is from Microsoft and its available through Access.

Attached File  2017_12_20.png ( 145.14K )Number of downloads: 8
Go to the top of the page
 
isimj
post Jan 6 2018, 04:09 PM
Post#11



Posts: 6
Joined: 11-December 17



Hi all - just an update if there is interest. I am using the Northwind Inventory Database from Microsoft which is a web app. It needs to be converted yo a client desktop database in order to use it the way I want. I am now exporting objects from the web app to the desktop. So hopefully all sorted. Thanks to those who has a crack at helping me.
Go to the top of the page
 
GroverParkGeorge
post Jan 6 2018, 05:28 PM
Post#12


UA Admin
Posts: 32,833
Joined: 20-June 02
From: Newcastle, WA


Ah, a web app. Well that would explain some things.

Glad you have it sorted. Continued success with the project.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd June 2018 - 02:58 PM