Start modules from a form

[Sardamil]

I feel stupid having to ask the next questions, but I tried to find a good book and searched the web, but couldn't find the answers I seek. Hope this doesn't look like I'm spamming tek-tips.
Like I said in my previous question, I'm new to vba in access. In my tool I have several (a lot really) macros that I start from buttons on my forms. I want to convert these macros to a module, using the access wizard. I was wondering how I can start these modules from a form. If possible, I'd like to start it from a button.
Does that also mean I can delete the original macro?

 

[JoeAtWork]

The VBA code to run a macro:

Private cmdMyButton_Click()
  DoCmd.RunMacro "MyMacroName"
End Sub

 

[JoeAtWork]

I think I misread your post. You want to run functions and subroutines from a module? (you don't "run" modules - a module is just a container for functions and subroutines).

To call a subroutine from a button click event:

Private Sub cmdMyButton_Click()
  'A subroutine with no parameters
  MySubroutineName

  'A subroutine with two parameters
  MySubroutineName2 "first parameter", "second parameter"
End Sub

Your subroutines and functions will need to be declared as Public to be reachable by forms, e.g.

Public Sub MySubroutineName()
....code....
End Sub

 

[Sardamil]

Thanks, that'll help a lot.

Anything that can go wrong will go wrong

Window to my world

 

[dhookom]

If your code is a function, you can just set the On Click property to the function name:

On Click:=MyPublicFunction()

I quick test of this is to set the On Click property to something like:
=MsgBox("test this")
A function that you have created is virtually the same as a built-in Access function.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Sardamil]

First of all, thanks for all the help. I solved this problem with the onclick command. My function is called and works, but I still get an error message though. Can you help me by telling what I did wrong. Here are the codes for my button and function.

Button:
Private Sub Schonen_tabellen_Click()
On Error GoTo Err_Schonen_tabellen_Click

Dim Schonen
Schonen.OnClick = Schonen_tabellen_voor_unloads()

Exit_Schonen_tabellen_Click:
Exit Sub

Err_Schonen_tabellen_Click:
MsgBox Err.Description
Resume Exit_Schonen_tabellen_Click

End Sub

Function:
Option Compare Database
Option Explicit

'------------------------------------------------------------
' Schonen_tabellen_voor_unloads
'
'------------------------------------------------------------
Public Function Schonen_tabellen_voor_unloads() As String
On Error GoTo Schonen_tabellen_voor_unloads_Err

DoCmd.SetWarnings False
' Schonen van tabellen

Dim Schonen As String

CurrentDb.Execute "DELETE * from ABNTB002"
CurrentDb.Execute "DELETE * from ABNTB003"
CurrentDb.Execute "DELETE * from ABNTB010"
' Messagebox
Beep
MsgBox "Bestanden zijn leeg", vbOKOnly, ""


Schonen_tabellen_voor_unloads_Exit:
Exit Function

Schonen_tabellen_voor_unloads_Err:
MsgBox Error$
Resume Schonen_tabellen_voor_unloads_Exit

End Function

I didn't post all the delete lines. Post is long enough as it is. I do get the msgbox from the function, but after that I get an error message "object required". Where do I go wrong?

Thanks.

Anything that can go wrong will go wrong

Window to my world

 

[dhookom]

I would not use the on click code. You should be able to just set the On Click property to:

=Schonen_tabellen_voor_unloads()

If you want to run code, try:

Private Sub Schonen_tabellen_Click()
On Error GoTo Err_Schonen_tabellen_Click

    Dim Schonen
    Schonen = Schonen_tabellen_voor_unloads()

Exit_Schonen_tabellen_Click:
    Exit Sub

Err_Schonen_tabellen_Click:
    MsgBox Err.Description
    Resume Exit_Schonen_tabellen_Click
    
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Sardamil]

Thanks, that solved my problem.

Anything that can go wrong will go wrong

Window to my world

 

[JoeAtWork]

    [COLOR=red]Dim Schonen
    Schonen.OnClick[/color] = Schonen_tabellen_voor_unloads()

You are declaring a variable Schonen, but not declaring it as a specific variable type, so it defaults to a Varient. This particular varient has not been set to any object that supports an OnClick event (such as a button).

It looks like all you need to do is take out the unnecessary code with the varient:

Private Sub Schonen_tabellen_Click()
On Error GoTo Err_Schonen_tabellen_Click

    [COLOR=green]'Call the subroutine[/color]
    Schonen_tabellen_voor_unloads

Exit_Schonen_tabellen_Click:
    Exit Sub

Err_Schonen_tabellen_Click:
    MsgBox Err.Description
    Resume Exit_Schonen_tabellen_Click
    
End Sub

 

[dhookom]

Actually since your function returns no value, it could/should be a sub...

Public [b][blue]Sub[/blue][/b] Schonen_tabellen_voor_unloads()

This is only a minor change and really isn't necessary. You can't call the Sub directly from an Event Property. You would have to call the Sub from code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]