Find Record in another table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bbrazeau - 22 Feb 2006 19:44 GMT

I have a form based on a table(Products). One of the fields in that table is
a lookup field that looks up an autonumber field from another table
(materials).The materials table contains a field called "specific gravity".
On the form for Products there is a button that when pushed calculates a
formula(using VB code). Right now the formula is incomplete because I need to
use specific gravity from the materials table. Is there a way similar to
"Find Record" action that will get the specific gravity for the material by
looking up its Id? I think from what I've read that the find record action is
limited to records on its parent form only. I would like to do this without
resorting to making a new form that uses a querry to bring info from both
tables together. Any help would be appreciated.

Reply to this Message

Roger Carlson - 22 Feb 2006 20:05 GMT

You can use the DLookup domain aggregate function. You can do something
like this:

Dim SG as Double

SG = DLookup("[specific gravity]", "materials", "MaterialID = " &
MaterialID)

The first argument specifies the field, the second specifies the table, and
the third is a Where clause with the WHERE keyword. I'm assuming your
autonumber field is called MaterialID in both of your tables. If not, the
first "MaterialID" needs to be replaced with the actual field name in
"materials" and the second is the field in your form that holds the value
you want to match.

Then use SG in your formula.

Signature

--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

> I have a form based on a table(Products). One of the fields in that table is
> a lookup field that looks up an autonumber field from another table
[quoted text clipped - 7 lines]
> resorting to making a new form that uses a querry to bring info from both
> tables together. Any help would be appreciated.

Reply to this Message

bbrazeau - 22 Feb 2006 21:12 GMT

Thanks Roger,
This worked exactly as stated. I didn't need any "Where" keyword though.
hmmm. This will help greatly as I move forward on this database.
Thanks again

>You can use the DLookup domain aggregate function. You can do something
>like this:
[quoted text clipped - 18 lines]
>> resorting to making a new form that uses a querry to bring info from both
>> tables together. Any help would be appreciated.

Reply to this Message

Roger Carlson - 22 Feb 2006 21:24 GMT

Sorry, I mis-typed. I meant it is a Where clause *without* the WHERE
keyword.

Signature

> Thanks Roger,
> This worked exactly as stated. I didn't need any "Where" keyword though.
[quoted text clipped - 23 lines]
> >> resorting to making a new form that uses a querry to bring info from both
> >> tables together. Any help would be appreciated.

Reply to this Message

Roger Carlson - 23 Feb 2006 14:03 GMT

I'd be cautious about using too many Domain Aggregate functions. If you
find you're using them all over the place, it could mean that you haven't
designed your database or application properly. There may be faster and
better alternatives.

I wouldn't let that keep you from using them, but it *is* something to keep
in mind.

Signature

> You can use the DLookup domain aggregate function. You can do something
> like this:
[quoted text clipped - 30 lines]
> > resorting to making a new form that uses a querry to bring info from both
> > tables together. Any help would be appreciated.

Reply to this Message

bbrazeau - 23 Feb 2006 14:43 GMT

Thanks for the caution Roger. I plan on using them sparingly, but I run the
trade off between a database that maybe uses a little more resources and one
that has a user interface that starts to get cluttered and error prone. This
database is going to be used by non-Access oriented people in the company I
work for and an existing quality control database in the company, “not made
by me” has already become so cumbersome and error prone that many people have
stopped using it, and as I’m sure you know if it’s not used it isn’t
effective. Again I thank you for your help, the quickness and clearness of
your explanation lead me to believe that you are quite well versed in Access.
Regards

>I'd be cautious about using too many Domain Aggregate functions. If you
>find you're using them all over the place, it could mean that you haven't
[quoted text clipped - 6 lines]
>> You can use the DLookup domain aggregate function. You can do something
>> like this:

Reply to this Message