[AccessD] Getting info from a single record table

John,

I'd probably load the rate value from the table via code at the start-up of
the form that uses it and put it in a module-level variable.  Allow the user
to change the rate by loading the value from the database into a text field,
then let the user change it, then update the database with the new value.


'load the data
dim mdRate as double
dim rs as recordset
dim db as database
dim strSQL as string

strSQL="SELECT Rate from RateTable;"
set db=currentdb()
set rs=db.openrecordset(strSQL,dbopensnapshot)
mdRate=rs(0)
rs.close
set rs=nothing
db.close
set db=nothing



'And use it like:
[AmtOwed] = [Miles] * mdRate


Note that this is air code, but it should get you started.

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Clark
Sent: Monday, April 16, 2007 2:45 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Getting info from a single record table

Years ago, I created a small program to track mileage of workers. There are
a bunch of things I'd have done differently, but they are very pleased w/it.
The bad thing though is that I couldn't figure out how to change the mileage
rate, on the fly, so I hard coded it. So, I have had to go in and alter this
rate, every quarter, if it changes.

This wasn't too awfully bad, but they didn't notify me, the last couple of
times, until we were well into the quarter, and they had already keyed in a
bunch of the entries.  The calculation is done at the time of entry, so the
allowances were off, and I had to run a quick update query to fix the old
amounts. This is one of the things I would do differently, and I may change
it to compare the date w/a table of dates and rates, but this immediate
question will be relevant for that too.

I would like to, for now at least, install a one-record table, to hold the
rate in it. I would have a very small form...basically a pop-up type
form...that would hold the rate. There would be no nav. buttons, so they are
stuck on the one record only. But...finally, we're at my question...how do I
reference this record?

Example:

Currently: [AmtOwed] = [Miles] * .485

Would like: [AmtOwed] = [Miles] * [tblMileageRate]![CurRate]

Make sense? I hope!

John W. Clark