Solved

Access 2000 - Assign code in Modules to button

Posted on 2011-03-21
6
265 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Supress Detail 4 19
MS Access Bound Objects. 6 28
Syntax using Declare 3 11
Access Delimiter for check box filter 3 7
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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

762 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

19 Experts available now in Live!

Get 1:1 Help Now