Append Query Update Most Recent Record Only

[KenF]

I am writing a macro the opens a form and allows me to add
a record to a table. I want the macro to open an append
query I wrote to automatically apend another table with
particular information. Whenever I run the append query,
it appends all the records from the source table to the
target table. How can I tell it to append only the most
recent record? I don't want to manually open the apend-
query and specify a criteria each time I run it -- it
sould be automated. I am writing the database in Access
97.

Thanks.

Ken

 

[John Vinson]

I am writing a macro the opens a form and allows me to add
a record to a table. I want the macro to open an append
query I wrote to automatically apend another table with
particular information. Whenever I run the append query,
it appends all the records from the source table to the
target table. How can I tell it to append only the most
recent record? I don't want to manually open the apend-
query and specify a criteria each time I run it -- it
sould be automated. I am writing the database in Access
97.

Access doesn't keep track of "the most recent record", and I suspect
that's not really what you want anyway - you want the record that's on
the form.

You can use a Parameter Query instead of editing the query criterion
to a literal value; just use

=[Forms]![NameOfYourForm]![NameOfAControl]

as a criterion on the query to limit the append to a single record,
drawing the criterion automatically from your form.

 

[KenF]

Hi John,

Thanks for the reply. I tried it out and it works fine,
however it appears you must have the Form open and then
run the Query with the criteria to obtain the current
update.

As I mentioned I am running this from a macro and I can't
find a way to run the Form, enter the data, and keep it
open while the query opens. I'm sure there must be a way,
but how do you do it??

I will then later have to close both out from the macro.
I don't want to intervene and do things manually; I want
the macro to contain all the logic. How do you close them
down after.


THanks.

-----Original Message-----

I am writing a macro the opens a form and allows me to add
a record to a table. I want the macro to open an append
query I wrote to automatically apend another table with
particular information. Whenever I run the append query,
it appends all the records from the source table to the
target table. How can I tell it to append only the most
recent record? I don't want to manually open the apend-
query and specify a criteria each time I run it -- it
sould be automated. I am writing the database in Access
97.

Access doesn't keep track of "the most recent record", and I suspect
that's not really what you want anyway - you want the record that's on
the form.

You can use a Parameter Query instead of editing the query criterion
to a literal value; just use

=[Forms]![NameOfYourForm]![NameOfAControl]

as a criterion on the query to limit the append to a single record,
drawing the criterion automatically from your form.


.

 

[John Vinson]

Hi John,

Thanks for the reply. I tried it out and it works fine,
however it appears you must have the Form open and then
run the Query with the criteria to obtain the current
update.

As I mentioned I am running this from a macro and I can't
find a way to run the Form, enter the data, and keep it
open while the query opens. I'm sure there must be a way,
but how do you do it??

Consider turning your logic around: run the macro from the Click event
of a button on the Form itself. That way you can be sure the form will
be open!

 

[KenF]

John,

I created a form with an action button as you requested.
The action button initiates the macro that opens a query
first and then another form. The query has the criteria
to look for the record ID number obtained from the Form
(using the statement you suggested.) The ID number from
the form does not appear to become available until you
perform other action on the form and accept the value.
The query finds no data until I change the data in the
Form by steping back one record and then back to the new
one I just created. The query and second form that opens
up can then see the correct information.

How can you "activate/commit" the data in the form without
changing the screen or closing the form? I didn't see an
option on the action button properties that would do that.

Thanks.

 

[John Vinson]

John,

I created a form with an action button as you requested.
The action button initiates the macro that opens a query
first and then another form.

My suggestion was (or should have been!) to base the second form on
the Query; and have the button *open the Form*. There is no need to
open the query datasheet at all.

The query has the criteria
to look for the record ID number obtained from the Form
(using the statement you suggested.)

Please post the actual SQL of the query. If it's referencing the Form
control it shouldn't matter whether the records been saved or not!

The ID number from
the form does not appear to become available until you
perform other action on the form and accept the value.
The query finds no data until I change the data in the
Form by steping back one record and then back to the new
one I just created. The query and second form that opens
up can then see the correct information.

How can you "activate/commit" the data in the form without
changing the screen or closing the form? I didn't see an
option on the action button properties that would do that.

It's not one of the action button properties, but you can put a line
of code in the click event of the button to save the record:

DoCmd.RunCommand acCmdSaveRecord