How can I update a table each time I run a query?

[keek2a4]

Hello Everyone!

I am using Microsoft access and I have a table with two fields: ID and Date. The ID is set to autoincrement.

How can I update a table with the next ID and date to current, each time a query is run.

Thanks,
Ket

[mattj]

First thing to do would be to change the name of the "Date" field. Date is a reserved word in Access and could cause problems...

Assuming that this qeury is run from some event, just execute an Append query after the code that runs the first query:


CurrentDb.Execute "INSERT INTO your table ( YourDateField )
SELECT Date();", dbFailOnError

[keek2a4]

yep, thats the exact query I was after. I have another question that is If I wanted to run or execute this query in vba, how can i go about in doing that? Thanks for your help

[mattj]

Thats what I posted...

[keek2a4]

aah sorry my mistake i wasn't paying attention. Thanks:mayi:

[mattj]

np :thumb

[keek2a4]

I finally have one last question,

If I have a query that displays the ID and Date, how can I set a criteria that only displays the last date record.

Hence, for example if I have a query that displays
ID Date
01 01/02/02
02 02/02/02
03 03/02/02

I want to set a criteria that only displays the last record with the latest date, which is
ID DATE
03 03/02/02.

Thanks,
Ket

[mattj]

In your query, enable the Totals icon, then in the Date column, select MAX.

[Norie]

Ket

Why do you want/need to update the date each time you run the query?

Couldn't you just add a field for the current date to the query?