Applying functions in module to a table

[scarlet1978]

Hi everyone,

I have just composed a module in Access that consists of various functions. These functions use Excel to perform various calculations.

It works fine when I calculate it for a given set of input variables, however what I want to do is get the functions to perform these calculations for a whole table, where the table provides all of the input variables.

In the following example I want to calculate x for each row in a table, where the inputs (8, 2, 10) are the columns in the table.

Can anyone help?

Example:
Sub LogNormalDist()
Dim objExcel As Excel.Application
Dim x As Double
Set objExcel = CreateObject("Excel.Application")

x = LognormMoments(50000, 8, 2)

objExcel.Quit
Set objExcel = Nothing
End Sub

 

[Crowley16]

you can open the table with a recordset, and then step through the recordset to calculate values on each...

or you could call the function with input parameters directly from a sql query...

--------------------
Procrastinate Now!

 

[JerryKlmns]

scarlet1978,

YourTableName is the table that provides all of the input data for the variables. A recordset reads all the table.
The loop While Not .EOF ....Wend for every record of the table (your variables' input set), performs the calculations. Input value of i-variable , is stored at the recordsets field name. So if you have many operands for your functions, pass them using the: .Fields("FieldName-i")
to the function for its i-variable. Just, use the same names for the fields of YourTableName and those written at the relative .Fields("FieldName-i").

Dim rst As ADODB.Recordset
Set rst=New ADODB.Recordset
    With rst
        .ActiveConnection = CurrentProject.Connection
        .CursorLocation = adUseServer
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Source = "YourTableName"
        .Open
        While Not .EOF
          x = LognormMoments(.Fields("FieldName1"), .Fields("FieldName2"), .Fields("FieldName3"))
          .MoveNext
        Wend
        .Close
     End With
Set rst=Nothing

Reference your ADO 2.x version.

Post if more help or an explaination is needed

 

[scarlet1978]

OK, this is what I have in there at the moment using that code, but it doesn't like the set statement (regardless of whether ADODB is in there or not). Any clues? My VB is terriblle.

Sub LogNormalDist()
Dim objExcel As Excel.Application
Dim x As Double
Dim rst As Recordset

Set objExcel = CreateObject("Excel.Application")


Set rst = New Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseServer
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Source = "ParameterSummary"
.Open
While Not .EOF
x = LognormParetoMoments(.Fields("mu"), .Fields("sigma"), ....)
.MoveNext
Wend
.Close
End With
Set rst = Nothing

objExcel.Quit
Set objExcel = Nothing
End Sub

 

[JerryKlmns]

Define rst like this

Dim rst As ADODB.Recordset
Set rst=New ADODB.Recordset

and also what 's the err.description? err.number???
Have you referenced ADO 2.x ?????

 

[scarlet1978]

Using that I get the error message "user defined type not defined". I am using Access 97 - could this be the problem?

 

[PHV]

When in VBE, menu Tools -> References ...
Tick the checkbox for Microsoft Active X Data Objects

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JerryKlmns]

Oh yes my dear... It is!

would you give me a moment to translate that to DAO 3.6 from ADO 2.x ?????

 

[scarlet1978]

I admit I know nothing.

I haven't referenced ADO 2.x or DAO 3.6 and I am clueless as to what that means. I never meant to be a programmer!

The Tools -> References -> ActiveXObjects didn't solve it.

 

[scarlet1978]

but hang on... I seem to have somehow clicked enough boxes under Tools --> References that it is OK with the new record.

Now it doesn't like the "currentproject" bit.

 

[JerryKlmns]

Looks like PHV writes faster than I

But I 'll keep my promise for DAO 3.6 version

Define rst like this

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("YouTableName", dbOpenSnapshot, dbReadOnly)

leave the rest as is.
But do check that on VBE menu Tools --> References that box for Microsoft DAO 3.6 Object Library is checked

 

[JerryKlmns]

Ok, programmers do it one finger at time but i 'm a mathematician translating into english on the fly .....

Is that parallel programming ???????????????
Do I need an MLU ???????

 

[scarlet1978]

It now looks something like this? If the "with" should stay, it is still getting caught up on "currentproject" - variable undefined.

Am eternally grateful!

Sub LogNormalDist()
Dim objExcel As Excel.Application
Dim x As Double
Dim rst As DAO.Recordset

Set objExcel = CreateObject("Excel.Application")

Set rst = CurrentDb.OpenRecordset("ParameterSummary", dbOpenSnapshot, dbReadOnly)
With rst
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseServer
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Source = "ParameterSummary"
.Open
While Not .EOF
x = LognormParetoMoments(.Fields("mu"), .Fields("sigma"), ...)
.MoveNext
Wend
.Close
End With
Set rst = Nothing

objExcel.Quit
Set objExcel = Nothing
End Sub

 

[scarlet1978]

Got it. THANKS!

 

[PHV]

Glad you solved your issue.
Next time don't forget to tellus which version of access you play with (I guess 97 ...)