Get recordset object from public module

[bbrazeau via AccessMonster.com]

I don't know if this is the proper subgroup to be discussing this in but here
goes: I have a database "TEST1" in it I have a query "MyTableQuery" in this
query there is a calculated field "TotalCost" which has its return value set
to a public module level function "performcalc1" the call looks like this:
TotalCost: performcalc1() . I am trying to get the recordset for this record
in the query so I can get the field values "Value1" in this simplified
function and perform the calculation. The code I have written so far is shown
below. I've read in the help files that in order to get a database, or
recordset object I need to include the data access object in the references
for the database. I looked for it and could only find "Microsoft DAO 3.6
object library so that is what I have included. When I open the query I get
an error "could not find file 'TEST1.mdb' . Is this because its already open?
I'm also not sure the line: fldOne=rstMyTableQuery.Fields!Value1 is correct.
What I want is the value thats held in the Value1 field of my query.Could any
one help Please?

Public Function performcalc1() As Currency
Dim dbsTest1 As Database
Dim rstMyTableQuery As Recordset
Dim fldOne As Integer

Set dbsTest1 = OpenDatabase("TEST1.mdb")
Set rstMyTableQuery = dbsTest1.OpenRecordset("MyTableQuery",
dbOpenDynaset)
fldOne = rstMyTableQuery.Fields!Value1
performcalc1 = fldOne * 100
rstMyTableQuery.Close
dbsTest1.Close

End Function

 

[Douglas J. Steele]

Try using the full path to Test1.mdb, not just the file name.

 

[bbrazeau via AccessMonster.com]

Thanks for the help. I added the entire path and now the error message reads
"The database has been placed in a state by user that prevents it from being
opened or locked.?

Try using the full path to Test1.mdb, not just the file name.

I don't know if this is the proper subgroup to be discussing this in but
here

[quoted text clipped - 36 lines]

End Function

 

[Douglas J. Steele]

Can you open the database from outside of your application?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the help. I added the entire path and now the error message
reads
"The database has been placed in a state by user that prevents it from
being
opened or locked.?

Try using the full path to Test1.mdb, not just the file name.

I don't know if this is the proper subgroup to be discussing this in but
here

[quoted text clipped - 36 lines]

End Function

 

[bbrazeau via AccessMonster.com]

I'm not sure what you mean by "outside of your application". I believe that
the reason for the error is: The function is called from a calculated field
in a query, so when the function is called the record is probably being lock
by access itself. I may be going at this the wrong way so let me rephrase the
question. How would I call the values from various fields in the query into
my function with out passing them all as arguements? When finished the
function may have to access 30 or more values.

Can you open the database from outside of your application?

Thanks for the help. I added the entire path and now the error message
reads

[quoted text clipped - 9 lines]

 

[Douglas J. Steele]

Why are you using a function like that in a query?

If you need data from another database, why not simply link to the table?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm not sure what you mean by "outside of your application". I believe
that
the reason for the error is: The function is called from a calculated
field
in a query, so when the function is called the record is probably being
lock
by access itself. I may be going at this the wrong way so let me rephrase
the
question. How would I call the values from various fields in the query
into
my function with out passing them all as arguements? When finished the
function may have to access 30 or more values.

Can you open the database from outside of your application?

Thanks for the help. I added the entire path and now the error message
reads

[quoted text clipped - 9 lines]

End Function

 

[bbrazeau via AccessMonster.com]

Why are you using a function like that in a query? The reasons I'm using the function call in a query are( and elighten me if I'm off base with these reasons as I'm fairly new to the intricasies of Access):1). I found that putting the calculated field in the query allowed me to easily create a report and form that showed the calculated field, and let it work properly.( when defined at the form level the report would not display the calculated field properly). 2).The query brings together data from different tables, some of which are linked by a combo box selection that the user makes in the form. For instance, if the user chooses wood as a material in one combobox, and defines the region of the build as "Alaska" the calculation will produce a different cost than if the user chooses wood and "Hawaii". Region and material are from different tables and the calculation uses values from other fields within those tables as parameters in the calculation.

If you need data from another database, why not simply link to the table?

I don't understand this question as I am not using data from another database.


More info: The calculated field is the "total" in a quote and there are many
more variables that will effect "total" as well as decision making constructs
within the code for the calculation. This is why I am trying to use a public
call to a module level function. Thanks for the help so far.

 

[Douglas J Steele]

If you're not using data from another database, why do you have an
OpenDatabase statement?

Public Function performcalc1() As Currency
Dim rstMyTableQuery As Recordset
Dim fldOne As Integer

Set rstMyTableQuery =
CurrentDb.OpenRecordset("MyTableQuery",dbOpenDynaset)
fldOne = rstMyTableQuery.Fields!Value1
performcalc1 = fldOne * 100
rstMyTableQuery.Close

End Function

although

Nz(DLookup("Value1", "MyTableQuery"), 0) * 100

should suffice.

However, are you sure you need to call the function at all? Can you not just
join the appropriate tables together in your query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


the function call in a query are( and elighten me if I'm off base with these
reasons as I'm fairly new to the intricasies of Access):1). I found that
putting the calculated field in the query allowed me to easily create a
report and form that showed the calculated field, and let it work
properly.( when defined at the form level the report would not display the
calculated field properly). 2).The query brings together data from different
tables, some of which are linked by a combo box selection that the user
makes in the form. For instance, if the user chooses wood as a material in
one combobox, and defines the region of the build as "Alaska" the
calculation will produce a different cost than if the user chooses wood and
"Hawaii". Region and material are from different tables and the calculation
uses values from other fields within those tables as parameters in the
calculation.

 

[bbrazeau via AccessMonster.com]

A very interesting thing happened when I tried to impliment your suggestion:
I first tried this:

Public Function performcalc1() As Currency
performcalc1 = Nz(DLookup("Value1", "MyTableQuery"), 0) * 100
End Function

Which worked but gave the same value for every record in the query and seemed
as though the value was based on the value of "Value1" in the first record. I
then tried:

Public Function performcalc1(opr1 As Integer) As Currency
performcalc1 = Nz(DLookup("Value1", "MyTableQuery", "[RecordID] = " &
opr1), 0) * 10
End Function

In which I altered the function call to include the primary key, and then
used criteria in the DLookup function to match up the key to each record.
This worked for query,form,and report. However since my calculation will need
more than one field value from the query I tried to set the value to a local
variable and then set the return value to that variable. see below.

Public Function performcalc1(opr1 As Integer) As Currency
Dim testit As Integer
testit = Nz(DLookup("Value1", "MyTableQuery", "[RecordID] = " & opr1), 0)
* 10
performcalc1 = testit
End Function

This began to work ,but after it had filled out about 15 or so records it
gave an "Overflow" error!? I feel I'm getting closer but am still at a loss
for how to get around this.Any thoughts?

 

[Douglas J. Steele]

Can you not just join the tables in your query, rather than needing to use a
function?

 

[bbrazeau via AccessMonster.com]

The purpose for the function call was that the calculation will make
decisions that affect price based on the info passed to it. For instance, say
the calculation is figuring price of a deck. if the customer wants a 10x10
deck made from pressure treated wood the price would be different than a deck
made from ceder. If the customer wants all the fasteners hidden it would cost
more than if they want to use screws. If the customers location is Alaska it
will cost more than Hawaii. All these decisions affect the outcome of the
calculation and complicate the procedure using them to the point where it
would be a nightmare to try and do them in the expression builder, further
more they would make the argument portion of the proceedure call enormous. I
think this last error I've gotten , the "Overflow" error is a limitation in
the calculated field in a query stemming from the fact that the calculated
field continually reevaluates the calculation.

Can you not just join the tables in your query, rather than needing to use a
function?

A very interesting thing happened when I tried to impliment your
suggestion:

[quoted text clipped - 35 lines]

loss
for how to get around this.Any thoughts?

 

[Douglas J Steele]

You're going to have to give far more details, then. I don't see anything in
your function that could handle that sort of functionality.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The purpose for the function call was that the calculation will make
decisions that affect price based on the info passed to it. For instance, say
the calculation is figuring price of a deck. if the customer wants a 10x10
deck made from pressure treated wood the price would be different than a deck
made from ceder. If the customer wants all the fasteners hidden it would cost
more than if they want to use screws. If the customers location is Alaska it
will cost more than Hawaii. All these decisions affect the outcome of the
calculation and complicate the procedure using them to the point where it
would be a nightmare to try and do them in the expression builder, further
more they would make the argument portion of the proceedure call enormous. I
think this last error I've gotten , the "Overflow" error is a limitation in
the calculated field in a query stemming from the fact that the calculated
field continually reevaluates the calculation.

Can you not just join the tables in your query, rather than needing to use a
function?

A very interesting thing happened when I tried to impliment your
suggestion:

[quoted text clipped - 35 lines]

loss
for how to get around this.Any thoughts?