Last record on a table

[Guest]

Is there a way to select only the last record from table to use in a apend
query? I need to be able to select the last record entered on a table and use
it in a append query to add it to a different table.

 

[Guest]

How do you determine which record is the "last" record entered?

 

[Guest]

The table only has two field an autonumber and a name. they enter the name
and access assigned the autonumber. than they press a cmd button to go to a
different form I want to kick a append query off to take the autonumber with
the name and add it to a table as a starting point for them.

 

[Guest]

I don't know whether DMax would work in the criteria row.

 

[John Spencer]

The problem here is that Autonumber fields are not guaranteed to be
sequential.

I would look at using a subform on the form where you are entering the name.
That can automatically "pass" and enter the value from the main form onto
the sub-form.

If you don't want to do that and your command button is on the form where
the name is entered, you should be able to grab the value of the automnumber
that is current for the form and use that .

INSERT INTO DestinationTable (FKNumber)
Values (Forms!NameOfForm!NameofAutonumberControl)

You can have the autonumber on the form in a control with its visible
property set to false if you don't want to display the value to the user.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jamie Collins]

The table only has two field an autonumber and a name. they enter the name
and access assigned the autonumber. than they press a cmd button to go to a
different form I want to kick a append query off to take the autonumber with
the name and add it to a table as a starting point for them.

Create a VIEW (Access Query object comprising a non-parameterized
SELECT query) which joins the two tables: e.g. (the following is
ANSI-92 Query Mode syntax):

CREATE VIEW View1 AS
SELECT T1.autonumber_col, T1.col2, T2.colX, T2.colY
FROM Table1 AS T1
INNER JOIN Table2 AS T2
ON T1.col1 = T2.colX;

(or manually create a Query object, omitting the first "CREATE VIEW"
line of course).

Now, instead of INSERTing to the two base tables, INSERT to the VIEW,
omitting the autonumber column and respective column in the other
table (colX) to allow them to be auto-generated e.g.

INSERT INTO View1 (col2, colY) VALUES ('Steve', 'Bill');

Jamie.

--