Command Button to record date

[Curt D.]

I am trying to figure out how to write a macro for different command buttons
to record the date and time every time the button is clicked. I have four
butons and everytime you select one it would post the date and time in the
first empty cell in that column in a table. In the table I have four
columns, one for each button. Any help would be appreciated. I'm just
beginning to understand access , slowly.

 

[Steve Schapel]

Curt,

It depends what you really mean by "the first empty cell in that
column". Tables don't have "cells". In fact, nothing in Access relates
to the idea of cells. Tables have records of data, and in fact they are
in an arbitrary order, so the idea of "first" is also a bit hard to
define. So the thinking required here is to use data-driven concepts,
as regards which record in the table you want this dtae/time entered
into. Normally the way to identify a record is via the value of its
Primary Key field. So I think that is what you will need to do with
your macro. Make an Update Query to update the value in the applicable
field to Now(), for the record you identify, and then use an OpenQuery
action in your macro to run the update.

 

[Curt D.]

Sorry about that Steve, I am use to working with Excel. What I need is to try
and record the date & time in a column everytime the button is clicked. I
would like to try the update query you mentioned. How would I go about it?

 

[Steve Schapel]

Curt,

Yes, I realised you were a spreadsheetist

Do you mean inserting the date into an existing record, or adding a new
record? If an existing record, how do you identify which one?

It will also help me to understand what you are doing if you can say
what the 4 date fields are about.

 

[Curt D.]

Steve, What I am trying to do is make a call log that records the date & time
any time one of the command buttons are clicked. I have 4 command buttons
(one for each desk), and every time a call comes in I just need them to click
the appropriate button and the date and time will be added to a table. In
that table I would like to have four columns (one for each desk) that stores
the the date & time every time the button was clicked.

Then I was going to have another way to count the total number of calls for
each desk and the totals for all combined.

Thanks for the help Steve, this spreadsheetist needs it. lol

Curt

 

[Steve Schapel]

Curt,

Ok, so no other data to be recorded, just the call times for each desk,
right?

Well, let's clear up one thing first. You absolutely shouldn't have a
separate field for each desk. This is the type of thing that you
sometimes see done in those s-things. In a database, it is called
various things, among them the "fields as data trap", otherwise known as
"wrong". I would recommend you change your table design to 2 fields,
being Desk (Number data type) and CallTime (Date/Time data type). All
the call times are recorded in one field, and the desk identified in the
other. Among other things, this will considerably simplify your later
need to do your totals and statistical analysis of the figures.

If you use your existing plan, and if you use macros rather than VBA
procedure, you will need a separate macro and a separate query for each
command button. I would not do it like this. I would have one command
button on the form. And I would put an unbound Option Group on the
form, with 4 option buttons, where the operator will select which Desk
at the beginning of the call logging session. Then, you can make a
query like this...
Select 'New' Query, and click 'Ok' for design view. Close the 'Show
Tables' dialog without doing anything with it. In the Field row of the
first column of the query design grid, enter the equivalent of...
[Forms]![NameOfYourForm]![NameOfOptionGroup]
In the second column, enter like this...
Now()
From the Query menu, select Append Query, and nominate your call
logging table. In the 'Append To' row of the grid, in the first column
select your Desk field, and in the second, select the CallTime field.
Close, name, and save the query.

Ok, now make a macro, with the OpenQuery action, and nominate this query
that you have just made. Assign this macro on the On Click event
property of the command button on your form. All done. Except that
after you have got it working properly, you might want to insert a
SetWarnings/No action in the macro before the OpenQuery, to suppress the
display of the action query confirmation prompt.

 

[Curt D.]

It took me a while but I got the call time, I guess I forgot to tell you that
I needed the coresponding desk number to be recorded along with the time.
When I clicked the command button it saved the time but not which desk it
came from. Is this something that is still possible by still selecting the
one option button and the command button?

thanks for the help Steve,

Curt

Curt,

Ok, so no other data to be recorded, just the call times for each desk,
right?

Well, let's clear up one thing first. You absolutely shouldn't have a
separate field for each desk. This is the type of thing that you
sometimes see done in those s-things. In a database, it is called
various things, among them the "fields as data trap", otherwise known as
"wrong". I would recommend you change your table design to 2 fields,
being Desk (Number data type) and CallTime (Date/Time data type). All
the call times are recorded in one field, and the desk identified in the
other. Among other things, this will considerably simplify your later
need to do your totals and statistical analysis of the figures.

If you use your existing plan, and if you use macros rather than VBA
procedure, you will need a separate macro and a separate query for each
command button. I would not do it like this. I would have one command
button on the form. And I would put an unbound Option Group on the
form, with 4 option buttons, where the operator will select which Desk
at the beginning of the call logging session. Then, you can make a
query like this...
Select 'New' Query, and click 'Ok' for design view. Close the 'Show
Tables' dialog without doing anything with it. In the Field row of the
first column of the query design grid, enter the equivalent of...
[Forms]![NameOfYourForm]![NameOfOptionGroup]
In the second column, enter like this...
Now()
From the Query menu, select Append Query, and nominate your call
logging table. In the 'Append To' row of the grid, in the first column
select your Desk field, and in the second, select the CallTime field.
Close, name, and save the query.

Ok, now make a macro, with the OpenQuery action, and nominate this query
that you have just made. Assign this macro on the On Click event
property of the command button on your form. All done. Except that
after you have got it working properly, you might want to insert a
SetWarnings/No action in the macro before the OpenQuery, to suppress the
display of the action query confirmation prompt.

--
Steve Schapel, Microsoft Access MVP

Steve, What I am trying to do is make a call log that records the date & time
any time one of the command buttons are clicked. I have 4 command buttons
(one for each desk), and every time a call comes in I just need them to click
the appropriate button and the date and time will be added to a table. In
that table I would like to have four columns (one for each desk) that stores
the the date & time every time the button was clicked.

Then I was going to have another way to count the total number of calls for
each desk and the totals for all combined.

Thanks for the help Steve, this spreadsheetist needs it. lol

 

[Steve Schapel]

Curt,

That was the whole point of the Option Group in my proposal. If you
follow the instructions in my earlier post, the Desk number should be
included in the record written to the table. Are you using an OpenQuery
action in your macro. Can you go to the design view of the query,
select SQL from the View manu, and then copy/paste the SQL of the query
into your reply here? Thanks.

 

[Steve Schapel]

Curt,

On reading your post more carefully, it occurs to me that you may not
undrstand the idea of an Option Group. My suggestion is to put an
Option Group control on the form, and put 4 Option Buttons within the
Option Group, one for each Desk. Then, the operator will select "their"
option button, and their Desk number will be included in the query. Let
me know if you need more help with this.

 

[Curt D.]

INSERT INTO [Call List] ( Desk, [Call Time] )
SELECT Forms![Call Log]![MD 88/90] AS Expr1, Now() AS Expr2;

I'll try and go back through the steps to see where I messed up on your
previous post. Thanks

Curt

 

[Curt D.]

Ay hoo, you da man Steve, I figured out my mistake and it works like I need
it to. thanks so much.

 

[Curt D.]

Steve,
One last thing I need to see if you can help with. I would like to take the
Call Log form with the 4 option buttons and 1 command button and be able to
open just the form with out opening access so it does not take up much room
on the desktop. Is there a way to do this without making a data access page?

Curt

 

[Steve Schapel]

Curt,

There is no way to have the form without Access being running,

However, you can go to the Tools|Startup menu, and hide the Database
Window (untick the 'Display Database Window' checkbox), and use the
Display Form/Page option to make your Call Log form automatically open
when the database opens. This might achieve your purpose.

 

[Steve Schapel]

Excellent, Curt. Very happy to know that we have made progress.