Macros to update table?

  • Thread starter Thread starter gretchenschaeffer
  • Start date Start date
G

gretchenschaeffer

Hi,

Okay, here's what I'm trying to do... I have a form, and I want to be
able to automatically log the time that users are working on that
particular project. This is my thought process, and I just can't get
it to work. I'm using Access2000, and I'm still somewhat new to
figuring it out.

1st Time button - start time - this would be a macro that goes and
updates a table that has the start time, end time, project id and an
autonumber field (so it will just keep adding to the table). There
would be a date & time stamp that goes into the start time column.

2nd Time button - end time - Same basic function - it would put a date/
time stamp into the end time field.

I want to use this to create a record to show how long the user was
working on the project (end time - start time = time worked). I've
tried setting up macros, and I can't get them to actually update the
table. I've had little experience with macros and I could really use
some help.

Thanks!

Gretchen
 
(e-mail address removed) wrote in
Hi,

Okay, here's what I'm trying to do... I have a form, and I
want to be able to automatically log the time that users are
working on that particular project. This is my thought
process, and I just can't get it to work. I'm using
Access2000, and I'm still somewhat new to figuring it out.

1st Time button - start time - this would be a macro that goes
and updates a table that has the start time, end time, project
id and an autonumber field (so it will just keep adding to the
table). There would be a date & time stamp that goes into the
start time column.

2nd Time button - end time - Same basic function - it would
put a date/ time stamp into the end time field.

I want to use this to create a record to show how long the
user was working on the project (end time - start time = time
worked). I've tried setting up macros, and I can't get them to
actually update the table. I've had little experience with
macros and I could really use some help.

Thanks!

Gretchen
Click to expand...
You need two queries, one to append a new record to the table
filling in the user's name or id, the project id and the
date/time. you could run this from a macro or 1 line of visual
basic in the OnClick event of your button. You don't need an
autonumber.

The second query is a little more complex, because you have to
find then update an existing record, the one created previously,
so you need as criteria the user id, the project and either the
most recent start, or the start with no end.

Then you run this query using a macro or code from the en time
button, and from the form's OnClose event, in case the user
forgot to click the end time button.
 
The macro action is OpenQuery.

You actually need two types of queries. You need an append query to add a
new record to the table when the user begins working on a project (and to
put in the start time). Then you need an update query to modify that record
with the end time when the user is done.

Have this action run an update query. The queries will need to read data
from the open form to get the ProjectID value, most likely.
 
Back
Top