Solved

Access 2000 - Assign code in Modules to button

Posted on 2011-03-21
6
294 Views
Last Modified: 2012-06-22
Dear Experts,

I have some VBA code lines in Modules section, those I would like to assign to button, that pressing it would be executed so run.

But if I put the module name as button property as Hyperlink SubAddress, the Access just open the module in design view and not run. Similiar the case if I put the module to a macro as OpenModule action, it opens the code in design but does not start the run of it.

Could you please advise what is the method of it?

Basically if I would like to open a query by a button, I used to apply those two ways above

thanks,
0
Comment
Question by:csehz
  • 3
  • 2
6 Comments
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 250 total points
ID: 35179752
In the Click Event (Code) of the button Properties you need to have a line of code which calls the relevant function in the module
0
 
LVL 1

Author Comment

by:csehz
ID: 35179806
Thanks I was able to do it with copying the code lines after the Command1_Click(),

Could be also maybe call somehow the Module macro?

As I remember in Excel VBA it is with Call command, but this does not work in Access as I can see

Private Sub Command1_Click()
Call FR_RM_export
End Sub

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35179994
what is the name of the function?
post the first line of the function..

public functionName(?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:csehz
ID: 35180027
Basically I would like to start his exporting macro in the Code with the button
Sub ExportAndFormat()
Dim xlApp As Object 
Dim xlWb As Object 
Dim xlWs As Object 
Dim xlData As Object 
Dim xlTotals As Object 
Dim strPath As String
Dim intNoCols As String
Dim intNoRows As Long

    strPath = CurrentProject.Path & "\AccessExport.xls"

    If Dir(strPath) <> "" Then Kill strPath

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", strPath, True

    'open the excel file and format

    Set xlApp = CreateObject("Excel.Application")

    Set xlWb = xlApp.Workbooks.Open(strPath)

    Set xlWs = xlWb.Worksheets(1)

    Set xlData = xlWs.UsedRange

    intNoCols = xlData.Columns.Count

    intNoRows = xlData.Rows.Count

    With xlData.Rows(1)
        With .Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Interior
            .ColorIndex = 15
            .Pattern = xlSolid
        End With
    End With

    Set xlTotals = xlData.Cells(intNoRows + 1, 2).Resize(, intNoCols - 1)

    With xlTotals
        With .Borders(xlEdgeBottom)
            .LineStyle = xlDouble
            .Weight = xlThick
            .ColorIndex = xlAutomatic
        End With
        .Formula = "=SUM(" & xlWs.Cells(2, 2).Resize(intNoRows - 1).Address(0, 0) & ")"
        .Interior.ColorIndex = 36
        .Interior.Pattern = xlSolid
    End With
    
    xlWs.UsedRange.Font.Name = "Verdana"

    xlWs.UsedRange.EntireColumn.AutoFit
    
    xlWb.Save

    xlApp.Quit

    Set xlApp = Nothing

End Sub

Open in new window

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 35180039
try this

Private Sub Command1_Click()
ExportAndFormat
End Sub
0
 
LVL 1

Author Closing Comment

by:csehz
ID: 35180142
That is perfect like that, thanks very much the solutions
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question