[AccessD] Getting info from a single record table

John,

It looks like your rate information won't change often.  You could copy the
table from the BE to the FE when the FE opens, and then use DLookup without
creating a new connection.

Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Clark
Sent: Tuesday, April 17, 2007 9:27 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Getting info from a single record table

Well, I thank you all. I am going to look into both of these solutions, and
see which one is for me...it'll be the one I have lease trouble with ;o)

I'll post again to let you know how it turns out.

John W. Clark


>>> "Bobby Heid" <bheid at sc.rr.com> 4/16/2007 7:21 PM >>>
I agree it would be simpler, but I have tried to get away from dlookup and
the like because it opens another connection to the database.

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Monday, April 16, 2007 6:18 PM
To: Access Developers discussion and problem solving
Subject: Re: [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 
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com 


-- 
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com