By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,082 Members | 2,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,082 IT Pros & Developers. It's quick & easy.

Run a module using a button in a access form?

P: 72
Hi All,
is there a way to run a module using a button in a access form?
thanks in advance
Ismail
Feb 14 '07 #1
Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Are you talking about a function/sub? Just put the function in a non-form module and in the On Click event of the button you can Call the function.
Feb 14 '07 #2

P: 72
it a function. I put the name in onclick but nothing happened. what do you mean by: non form module?
thanks
Ismail
Feb 14 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
Can you post the code you used?

A form module is the place where all the code tied to a form is held. A function in a form module can not be called outside of the form.

A non-form module is any module not bound to a form. A bit of a misnomer here. I should be using bound and unbound modules as the same applies to reports. Any function in an unbound module can be called from other modules in the same project.

To call a function, use Call FunctionName(Arguments)
Feb 14 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
A procedure that is required to be called from outside of its module, must be defined as Public.
Feb 14 '07 #5

P: 72
thank you guys for your help. Here is the code I used:
Expand|Select|Wrap|Line Numbers
  1. Public Function GetBP(strPlanID As String, lngAssets As Long) As Double
  2.     Dim db As DAO.Database
  3.     Dim rs As DAO.Recordset
  4.     Dim strSQL As String
  5.     Dim lngRemainingAssets As Long
  6.     Dim lngCountedAssets As Long
  7.     Dim dblReturnValue As Double
  8.  
  9.     Set db = CurrentDb
  10.     strSQL = "SELECT MinAsset, MaxAsset, MaxAsset-MinAsset As LngRange, BPRate " & _
  11.             "FROM tblPlanRanges " & _
  12.             "WHERE PlanID =""" & strPlanID & """ " & _
  13.             "ORDER BY MinAsset"
  14.     Set rs = db.OpenRecordset(strSQL)
  15.     lngRemainingAssets = lngAssets
  16.     With rs
  17.         .MoveFirst
  18.         Do While lngRemainingAssets > 0
  19.             If lngRemainingAssets > .Fields("LngRange") Then
  20.                 dblReturnValue = dblReturnValue + .Fields("BPRate") * .Fields("LngRange")
  21.                 lngRemainingAssets = lngRemainingAssets - .Fields("LngRange")
  22.              Else
  23.                 dblReturnValue = dblReturnValue + .Fields("BPRate") * lngRemainingAssets
  24.                 lngRemainingAssets = 0
  25.             End If
  26.             .MoveNext
  27.         Loop
  28.         .Close
  29.     End With
  30.     GetBP = dblReturnValue
  31.     Set rs = Nothing
  32.     Set db = Nothing
  33. End Function
Feb 15 '07 #6

Rabbit
Expert Mod 10K+
P: 12,366
thank you guys for your help. Here is the code I used:
Expand|Select|Wrap|Line Numbers
  1. Public Function GetBP(strPlanID As String, lngAssets As Long) As Double
  2.     Dim db As DAO.Database
  3.     Dim rs As DAO.Recordset
  4.     Dim strSQL As String
  5.     Dim lngRemainingAssets As Long
  6.     Dim lngCountedAssets As Long
  7.     Dim dblReturnValue As Double
  8.  
  9.     Set db = CurrentDb
  10.     strSQL = "SELECT MinAsset, MaxAsset, MaxAsset-MinAsset As LngRange, BPRate " & _
  11.             "FROM tblPlanRanges " & _
  12.             "WHERE PlanID =""" & strPlanID & """ " & _
  13.             "ORDER BY MinAsset"
  14.     Set rs = db.OpenRecordset(strSQL)
  15.     lngRemainingAssets = lngAssets
  16.     With rs
  17.         .MoveFirst
  18.         Do While lngRemainingAssets > 0
  19.             If lngRemainingAssets > .Fields("LngRange") Then
  20.                 dblReturnValue = dblReturnValue + .Fields("BPRate") * .Fields("LngRange")
  21.                 lngRemainingAssets = lngRemainingAssets - .Fields("LngRange")
  22.              Else
  23.                 dblReturnValue = dblReturnValue + .Fields("BPRate") * lngRemainingAssets
  24.                 lngRemainingAssets = 0
  25.             End If
  26.             .MoveNext
  27.         Loop
  28.         .Close
  29.     End With
  30.     GetBP = dblReturnValue
  31.     Set rs = Nothing
  32.     Set db = Nothing
  33. End Function
From what I can tell this should work fine. Where is this function located and where and how are you calling this function?

Have you tried to compile the code to see if there's an error?
Feb 15 '07 #7

P: 72
I am trying to run the following query:

SELECT [tblGeneral Fees].PlanID, [tblGeneral Fees].Assets, [tblGeneral Fees].PLAN, GetBP([PlanID],[Assets]) AS BP
FROM [tblGeneral Fees]
WHERE PlanID Is Not Null And Assets Is Not Null;

the query stops and I am getting the following error:

Run-time error '3201'
No current record

thanks
Feb 15 '07 #8

P: 72
Yes, I compiled it and no error.
Feb 15 '07 #9

P: 72
Problem solved. Thank you so much for your help
ismail
Feb 15 '07 #10

Rabbit
Expert Mod 10K+
P: 12,366
Not a problem. By the way, what was the problem?
Feb 16 '07 #11

P: 72
The query I was running included all the plans. I am only interested in some not all plans. That's I was getting the error.
My query should be like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT [tblGeneral Fees].PlanID,
  2.        [tblGeneral Fees].Assets,
  3.        [tblGeneral Fees].PLAN,
  4.        GetBP([PlanID],[Assets]) AS BP
  5. FROM [tblGeneral Fees]
  6. WHERE [tblGeneral Fees].PlanID IN 
  7.        ("CBAH", "CREST", "ENERGY", "GLC", "GLCB",
  8.         "GLNQ", "GUARD", "JHC", "SSSH", "SVM")
  9.   AND [tblGeneral Fees].Assets Is Not Null;
Thanks again
Feb 16 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.