[AccessD] Getting info from a single record table

I think a DLookup is simpler:

Me.Control = Dlookup("Rate", "MyTable").

hth,
Arthur

On 4/16/07, Bobby Heid <bheid at sc.rr.com> wrote:
>
> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>