467,146 Members | 1,294 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,146 developers. It's quick & easy.

How to run a module using a menu bar

jpr
Hello,

I have created menu bars for my access application and now would like
to transfer many pieces of code in modules so that they can run using
macros. I beleive this is the only way I can make run my code from the
menu bar. Correct?

I am having problems with the modules since I have never used them and
would like some help.

Here is a sample of code I now have on a push button in a form:

Dim mydb As DAO.Database, MyRs As DAO.Recordset
Dim strCode As String
Dim strFilter As String
Dim stDocName As String
Dim stLinkCriteria As String

Set mydb = CurrentDb
Set MyRs = mydb.OpenRecordset("master")

stDocName = "MASTER"

stLinkCriteria = "[SSN]=" & "'" & Me![SSN] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
If Not IsNull(DLookup("[SSN]", "TEMPLATES", "[SSN] = '" & Me!SSN &
"'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "TEMPLATES", "[SSN] = '" & Me!SSN & "'"))
Then
DoCmd.OpenQuery ("Appendtemplates")
End If
End If

If Not IsNull(DLookup("[SSN]", "195", "[SSN] = '" & Me!SSN & "'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "195", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("Append195")
End If
End If

If Not IsNull(DLookup("[SSN]", "795", "[SSN] = '" & Me!SSN & "'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "795", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("Append795")
End If
End If

If Not IsNull(DLookup("[SSN]", "21", "[SSN] = '" & Me!SSN & "'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "21", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("Appendssa21tax")

End If
End If
Forms!eforms.lstPreInterview.Value = Null
DoCmd.Close
DoCmd.OpenForm ("Eforms")
DoCmd.RunMacro ("CloseEforms")
DoCmd.OpenForm ("Eforms")

End Sub

What I need is to add this code to a module, and make it run using a
manu bar (I assume via macro). Thanks.

Aug 26 '06 #1
  • viewed: 8316
Share:
4 Replies
jpr wrote:
Hello,

I have created menu bars for my access application and now would like
to transfer many pieces of code in modules so that they can run using
macros. I beleive this is the only way I can make run my code from the
menu bar. Correct?
Incorrect. 8)
What I need is to add this code to a module, and make it run using a
manu bar (I assume via macro). Thanks.
Generally, code that sits in standard modules is available all the time,
regardless of what forms are opened. The sub you have with your button
on a form is in the form module for that form and is only available when
your form is open.

First create a standard module and save it. Cut your procedure from the
form module it is in and paste it to the standard module and save your mdb.

To be able to run your procedure from a menu bar control, it must be a
function. Change it from Sub Whatever to:

Public Function fProcedure()

<blah blah>

End Function

To make this run from your menu, go to the customize dialog, right click
the menu control and choose properties.

In the on action control, put in:

=fProcedure()

You can also include arguments as well.

Hope this gets you going.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Aug 27 '06 #2
jpr
thanks for your help. I have followed your advise but still getting an
error message:
"The expression you have entered has a function that MS Office can't
fint"

Any idea of what is wrong based on my code? THanks.
Tim Marshall wrote:
jpr wrote:
Hello,

I have created menu bars for my access application and now would like
to transfer many pieces of code in modules so that they can run using
macros. I beleive this is the only way I can make run my code from the
menu bar. Correct?

Incorrect. 8)
What I need is to add this code to a module, and make it run using a
manu bar (I assume via macro). Thanks.

Generally, code that sits in standard modules is available all the time,
regardless of what forms are opened. The sub you have with your button
on a form is in the form module for that form and is only available when
your form is open.

First create a standard module and save it. Cut your procedure from the
form module it is in and paste it to the standard module and save your mdb.

To be able to run your procedure from a menu bar control, it must be a
function. Change it from Sub Whatever to:

Public Function fProcedure()

<blah blah>

End Function

To make this run from your menu, go to the customize dialog, right click
the menu control and choose properties.

In the on action control, put in:

=fProcedure()

You can also include arguments as well.

Hope this gets you going.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Aug 28 '06 #3
jpr wrote:
thanks for your help. I have followed your advise but still getting an
error message:
"The expression you have entered has a function that MS Office can't
fint"
The function (do NOT use a sub, it MUST BE a function) is spelt
incorrectly, or is not public or is not saved in a standard module.
Also, the = sign and the closing parenthesis must be used.

So, the following will cause the error you mentioned if entered into the
on action control:

fProcedure() <WRONG>

fProcedure <WRONG>

=fProcedure <WRONG>

You must enter:

=fProcedure() <CORRECT>

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Aug 28 '06 #4
Just for the record: you can't run a Module. A Module is a container for
code, and the code in a Module is organized into Procedures (Sub or
Function) and a General Area that can contain Variables and Constants.

You can run a Procedure that exists in a Module. You should NOT give a
Module and a Procedure the same name.

Larry Linson
Microsoft Access MVP
"jpr" <jp***@tin.itwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
Hello,

I have created menu bars for my access application and now would like
to transfer many pieces of code in modules so that they can run using
macros. I beleive this is the only way I can make run my code from the
menu bar. Correct?

I am having problems with the modules since I have never used them and
would like some help.

Here is a sample of code I now have on a push button in a form:

Dim mydb As DAO.Database, MyRs As DAO.Recordset
Dim strCode As String
Dim strFilter As String
Dim stDocName As String
Dim stLinkCriteria As String

Set mydb = CurrentDb
Set MyRs = mydb.OpenRecordset("master")

stDocName = "MASTER"

stLinkCriteria = "[SSN]=" & "'" & Me![SSN] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
If Not IsNull(DLookup("[SSN]", "TEMPLATES", "[SSN] = '" & Me!SSN &
"'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "TEMPLATES", "[SSN] = '" & Me!SSN & "'"))
Then
DoCmd.OpenQuery ("Appendtemplates")
End If
End If

If Not IsNull(DLookup("[SSN]", "195", "[SSN] = '" & Me!SSN & "'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "195", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("Append195")
End If
End If

If Not IsNull(DLookup("[SSN]", "795", "[SSN] = '" & Me!SSN & "'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "795", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("Append795")
End If
End If

If Not IsNull(DLookup("[SSN]", "21", "[SSN] = '" & Me!SSN & "'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "21", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("Appendssa21tax")

End If
End If
Forms!eforms.lstPreInterview.Value = Null
DoCmd.Close
DoCmd.OpenForm ("Eforms")
DoCmd.RunMacro ("CloseEforms")
DoCmd.OpenForm ("Eforms")

End Sub

What I need is to add this code to a module, and make it run using a
manu bar (I assume via macro). Thanks.

Aug 28 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Marcus Schneider | last post: by
2 posts views Thread by Marc Shapiro | last post: by
1 post views Thread by THDWWSIJQUOK@spammotel.com | last post: by
7 posts views Thread by allyn44@cox.net | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.