Run a module using a button in a access form?

[ielamrani]

Hi All,
is there a way to run a module using a button in a access form?
thanks in advance
Ismail

[Rabbit]

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.

[ielamrani]

it a function. I put the name in onclick but nothing happened. what do you mean by: non form module?
thanks
Ismail

[Rabbit]

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)

[NeoPa]

A procedure that is required to be called from outside of its module, must be defined as Public.

[ielamrani]

thank you guys for your help. Here is the code I used:

Public Function GetBP(strPlanID As String, lngAssets As Long) As Double
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim lngRemainingAssets As Long
    Dim lngCountedAssets As Long
    Dim dblReturnValue As Double
 
    Set db = CurrentDb
    strSQL = "SELECT MinAsset, MaxAsset, MaxAsset-MinAsset As LngRange, BPRate " & _
            "FROM tblPlanRanges " & _
            "WHERE PlanID =""" & strPlanID & """ " & _
            "ORDER BY MinAsset"
    Set rs = db.OpenRecordset(strSQL)
    lngRemainingAssets = lngAssets
    With rs
        .MoveFirst
        Do While lngRemainingAssets > 0
            If lngRemainingAssets > .Fields("LngRange") Then
                dblReturnValue = dblReturnValue + .Fields("BPRate") * .Fields("LngRange")
                lngRemainingAssets = lngRemainingAssets - .Fields("LngRange")
             Else
                dblReturnValue = dblReturnValue + .Fields("BPRate") * lngRemainingAssets
                lngRemainingAssets = 0
            End If
            .MoveNext
        Loop
        .Close
    End With
    GetBP = dblReturnValue
    Set rs = Nothing
    Set db = Nothing
End Function

[Rabbit]

thank you guys for your help. Here is the code I used:

Public Function GetBP(strPlanID As String, lngAssets As Long) As Double
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim lngRemainingAssets As Long
    Dim lngCountedAssets As Long
    Dim dblReturnValue As Double
 
    Set db = CurrentDb
    strSQL = "SELECT MinAsset, MaxAsset, MaxAsset-MinAsset As LngRange, BPRate " & _
            "FROM tblPlanRanges " & _
            "WHERE PlanID =""" & strPlanID & """ " & _
            "ORDER BY MinAsset"
    Set rs = db.OpenRecordset(strSQL)
    lngRemainingAssets = lngAssets
    With rs
        .MoveFirst
        Do While lngRemainingAssets > 0
            If lngRemainingAssets > .Fields("LngRange") Then
                dblReturnValue = dblReturnValue + .Fields("BPRate") * .Fields("LngRange")
                lngRemainingAssets = lngRemainingAssets - .Fields("LngRange")
             Else
                dblReturnValue = dblReturnValue + .Fields("BPRate") * lngRemainingAssets
                lngRemainingAssets = 0
            End If
            .MoveNext
        Loop
        .Close
    End With
    GetBP = dblReturnValue
    Set rs = Nothing
    Set db = Nothing
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?

[ielamrani]

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

[ielamrani]

Yes, I compiled it and no error.

[ielamrani]

Problem solved. Thank you so much for your help
ismail

[Rabbit]

Not a problem. By the way, what was the problem?

[ielamrani]

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:

SELECT [tblGeneral Fees].PlanID,
       [tblGeneral Fees].Assets,
       [tblGeneral Fees].PLAN,
       GetBP([PlanID],[Assets]) AS BP
FROM [tblGeneral Fees]
WHERE [tblGeneral Fees].PlanID IN 
       ("CBAH", "CREST", "ENERGY", "GLC", "GLCB",
        "GLNQ", "GUARD", "JHC", "SSSH", "SVM")
  AND [tblGeneral Fees].Assets Is Not Null;

Thanks again