Declaring Constants by a rookie

[Old Gnome]

I am real new to trying my hand at VB coding, and I'm not sure that's the answer to my dilemma. However, I think it is, so I will pose the question here.

I have about 200 values I have stored in a table. These values will occasionally change, but not very often. They are dollar and effort hours standards for a cost model I am building. There is and will be only one record in this table.

Can I create constants with this table record? I need to be able to multiply or divide values in all records in another table by each of the 'constant' values. I have searched the Access help file and have tried a few things, but I am pretty sure I am going about it the wrong way.

Can anyone point me in the right direction?

TIA

oldgnome

 

[Kitemaniac]

Maybe I'm not very bright or so but..... why not count with the values when they are in a table?

 

[Old Gnome]

The values in table1 represent the unit costs for various pieces of computer equipment and standard efforts for installing those pieces of equipment for generating an IT cost model for proposed projects.

I need to be able to produce reports that show (2 widgets times the price of a widget), along with (2 widgets times the effort to install a widget), for all records in table2.

I have had no luck in constructing a query to achieve this result. I had hoped that storing the data in a table and then creating the constants (or variables...I'm flexible) from that source would ease the arithmetic gymanstics.

Any help or ideas is most welcome.

oldgnome

 

[The_Doc_Man]

In the strict sense of VBA syntax, you cannot create a Constant (big C) because of the issue of binding time (i.e. at what point does VBA actually use the constant.)

Putting one long record in a table makes this table rather clumsy. Now this is your table and I would never tell you that you are wrong - but it might be more flexible if you changed your table to be many rows of two fields each. For instance, Table CC: <category as string(32), cost-factor as single>

Then you can write some VBA code using the DLookup function to get what you want. Get the category from some source into a string variable, then you could do

dim sngCost as single
dim stCatName as string

....

sngCost = DLookup( "[Cost-Factor]","CC","[Category]=""" & stCatName & """")
....


OR you can build a form that uses your category/cost table to drive a combo box for the elements you are costing out. When you enter a cost item, you use the combo box to look it up AND use the option that loads a field on your form from the other column at the same time. After that, you can just pick up the cost-factor directly from the form field.

 

[Old Gnome]

Thanks for the input. I managed to figure it out and I have it working this way:

The table of values remains one record and I have form to use in updating those values.

I created a function for each value that creates a variable containing the contents of each field for the record with ID=1. I removed the ability to add new records from the form.

I have also created a query that calculates all of the downstream values and is used as the basis for the reports.

Please let me know what you think. I am still at the point where I would like to know if I have created this thing the hard way or if I have used reasonable techniques.

oldgnome