G
Guest
I am trying to learn to use Functions as I have been building queries with too many calculated fields with some calculated fields calculated off other calculated fields, which MS Access doesn't like. Therefore I am testing the use of Public Functions where I can refer to them in other objects such as forms and queries. I set up a test database to try this out. In my test database, I set up a table
named ProfitTest with two fields: one was 'Cost" and the
other "Units."
Next, I wrote a Public Function called "Price" that
multiplies Cost * Units. I then wrote another Public
Function that calculates off the "Price" Function. It is
called "Score." It uses an IIF statement and provides a
score, depending on the results of the public
Function "Price.
Next, I designed a query that had four
columns: "Cost," "Units," "Price," and "Score." In the
query everything worked. A Price was calculated and the
Score worked. So, I thought all was fine. However, when
I went to save the query, no matter what I try to name the
query, it gives me an error message "Cannot open
database "my newly assigned name." It may not be an
application that your application recognizes, or the file
may be corrupt.
Then, I thought, well I probably haven't saved the Module
that the Public Function is in, therefore, the query
probably can't find the reference. So, I try to save the
module and it asks me to save the following
objects: "Module1" and "Query1." If I say "Yes," it
asks me to save the query and I am thrown back into the
error message I got when trying to save the query
Could this be that I should have saved the Public Function
first in the module and then created the query and then
saved it? I'm thinking that since I didn't save either
and then tried to save both, that they were tied to each
other and couldn't find each other for the save operation,
although they seemed to find each other for the
calculation phase
Sorry for the long question
Ke
named ProfitTest with two fields: one was 'Cost" and the
other "Units."
Next, I wrote a Public Function called "Price" that
multiplies Cost * Units. I then wrote another Public
Function that calculates off the "Price" Function. It is
called "Score." It uses an IIF statement and provides a
score, depending on the results of the public
Function "Price.
Next, I designed a query that had four
columns: "Cost," "Units," "Price," and "Score." In the
query everything worked. A Price was calculated and the
Score worked. So, I thought all was fine. However, when
I went to save the query, no matter what I try to name the
query, it gives me an error message "Cannot open
database "my newly assigned name." It may not be an
application that your application recognizes, or the file
may be corrupt.
Then, I thought, well I probably haven't saved the Module
that the Public Function is in, therefore, the query
probably can't find the reference. So, I try to save the
module and it asks me to save the following
objects: "Module1" and "Query1." If I say "Yes," it
asks me to save the query and I am thrown back into the
error message I got when trying to save the query
Could this be that I should have saved the Public Function
first in the module and then created the query and then
saved it? I'm thinking that since I didn't save either
and then tried to save both, that they were tied to each
other and couldn't find each other for the save operation,
although they seemed to find each other for the
calculation phase
Sorry for the long question
Ke