Solved

Access 2000 - Assign code in Modules to button

Posted on 2011-03-21
6
275 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 119

Expert Comment

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

public functionName(?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

932 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now