Look up value in table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Leo - 17 Jun 2007 19:30 GMT

I have two tables, tablea and tableb. I have a field in tablea called
test. I have a field in tableb called test2.

When I create a new record in tableb, I want it to fill in the value
from latest record from tablea test into test2.

I guess it should be something like test2=tablea![test].last

Any help would be appreciated.

Reply to this Message

John W. Vinson - 17 Jun 2007 23:29 GMT

>I have two tables, tablea and tableb. I have a field in tablea called
>test. I have a field in tableb called test2.
>
>When I create a new record in tableb, I want it to fill in the value
>from latest record from tablea test into test2.

Ummm... why?

Storing data redundantly is generally a Very Bad Idea.

>I guess it should be something like test2=tablea![test].last

The "Last" record in a table is basically arbitrary. There is a Last totals
operator and a DLast() VBA function, but they return the last record *in disk
storage order* - an order which is meaningless since Access can store records
wherever there is room. The most recently entered record will often be the
"last" record, but you have absolutely *no* guarantee of that.

Please explain what *you* mean by Last, and - more importantly - why you want
to do this.

John W. Vinson [MVP]

Reply to this Message

Larry Linson - 18 Jun 2007 03:09 GMT

>I have two tables, tablea and tableb. I have a field in tablea called
> test. I have a field in tableb called test2.
[quoted text clipped - 3 lines]
>
> I guess it should be something like test2=tablea![test].last

Sorry, but that is not the way to access a Field in a Record in a Table.
You have to read it with a Query or SQL statement, or retrieve it with a
domain aggregate statement such as DLookup, about which you can find
information at
http://msdn2.microsoft.com/en-us/library/aa172176(office.11).aspx, or by
opening a module window, typing in DLookup, placing the cursor on it, and
pressing F1.

However, you are going to need some way to identify the "last" record, which
is a characteristic of sequential files, not database tables (which are, by
definition, unordered, so you have to determine what information used as a
criteria will give you the "last" record).

Larry Linson
Microsoft Access MVP

Reply to this Message

Leo - 19 Jun 2007 23:14 GMT

I am a student at a university.

I am running an experiment that will be run over many years by many
users. The experiment is calibrated roughly once a week. I set up a
key index for the calibration table, so first record is 1, next record
is 2, etc....

There will be dozens of experiments run each week. Those are going to
have different fields. I want the fields that are the same as in the
Calibration number table to be automatically filled in.

As I am typing this, I see why you think this is a stupid idea. A
better idea is to make two forms, but from a single table, and have
the field's default value be it's value from the previous record. How
would I do that?

Example:

For experiment 1, the pressure is 10 Torr. When I create a new record
in the table, I want the default value of 10 Torr filled in. But if
for record three, I change it to 20 Torr, I want 20 Torr filled in for
record four. Basically, a dynamic default value.

Reply to this Message

John W. Vinson - 20 Jun 2007 00:59 GMT

>I am a student at a university.
>
>I am running an experiment that will be run over many years by many
>users. The experiment is calibrated roughly once a week. I set up a
>key index for the calibration table, so first record is 1, next record
>is 2, etc....

Is this an Autonumber field, or are you incrementing it yourself (manually or
in code)?

>There will be dozens of experiments run each week. Those are going to
>have different fields. I want the fields that are the same as in the
[quoted text clipped - 4 lines]
>for record three, I change it to 20 Torr, I want 20 Torr filled in for
>record four. Basically, a dynamic default value.

You can do this with a little bit of VBA code in the Form (and yes, you must
use a form to do this; tables don't have any usable events).

In each such control's AfterUpdate event set the control's own DefaultValue
property:

Private Sub txtPressure_AfterUpdate()
Me!txtPressure.DefaultValue = Me.txtPressure
End Sub

John W. Vinson [MVP]

Reply to this Message

Leo - 20 Jun 2007 15:19 GMT

> Is this an Autonumber field, or are you incrementing it yourself (manually or
> in code)?

Yes, it is an Autonumber.

> You can do this with a little bit of VBA code in the Form (and yes, you must
> use a form to do this; tables don't have any usable events).

I have not yet had the chance to learn Visual Basic.

> In each such control's AfterUpdate event set the control's own DefaultValue
> property:
>
> Private Sub txtPressure_AfterUpdate()
> Me!txtPressure.DefaultValue = Me.txtPressure
> End Sub

I am not entirely sure of what this means. is a "control" a field in
the form?

Is there an ability to post a screen shot? If not, my e-mail address
is yakleo3@yahoo.com.

Thank you for all your help.

John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 20 Jun 2007 17:53 GMT

>> Is this an Autonumber field, or are you incrementing it yourself (manually or
>> in code)?
[quoted text clipped - 5 lines]
>
>I have not yet had the chance to learn Visual Basic.

For this, all you need to do is type one line. Change the txtPressure below to
the actual name of the form control.

>> In each such control's AfterUpdate event set the control's own DefaultValue
>> property:
[quoted text clipped - 5 lines]
>I am not entirely sure of what this means. is a "control" a field in
>the form?

Tables have Fields. Forms have Controls (such as Textbox controls, Combo Box
controls, Listbox controls...) which are bound to fields in the table or the
query. Forms don't have "fields" - what you see is a Control, a tool which can
be used to update a field.

>Is there an ability to post a screen shot? If not, my e-mail address
>is yakleo3@yahoo.com.

Posting binaries is neither necessary nor appropriate; and private EMail
support is reserved for paying customers. I'm a self employed consultant
donating time on the newsgroup (like everyone who answers here); I must
regretfully limit personal support.

Just try what I suggested.

John W. Vinson [MVP]

Reply to this Message