Make Changes To Specific Records at the same time

[Guest]

Okay... I am still working on this Employee Attendace database... In a case
where an employee goes on vacation, I had a update query that automatically
changes the attendance status for those days from "P" to "V" (the information
for the whole year is pre-entered so all employee status is defaulted to P
for "Present").

The update query would ask for the employees name, the start date of the
vacation and the end date of the vacation. It would then change the status
for those days to "V", and under the VACATION HOURS feild it would change
that value from 0 to 7, and it would change HOURS (hours spent on duty) to 0
(since it is defaulted to 7).

The user of the database however does not like the update query. I was
wondering if there was a way I could do this in a form. So far I have gotten
the query to pull up the records that I want to make the changes to. My
problem though is getting the form to update all the records at the same
time. For Example my form would pull up this information if she enters the
[EMPLOYEE NAME] as John Allen, [START DATE] as Jan 3 2005 and [END DATE] as
Jan 6 2005:

(main form)
*I have not placed anything here yet*

(sub form in datasheet view)
DATE NAME STATUS HOURS VACATION HOURS
1/3/2005 John Allen P 7
0
1/4/2005 John Allen P 7
0
1/5/2005 John Allen P 7
0
1/6/2005 John Allen P 7
0

How would I get the form to change that information to the following when
she clicks a button:

(main form)
*I have not placed anything here yet*

DATE NAME STATUS HOURS VACATION HOURS
1/3/2005 John Allen V 0
7
1/4/2005 John Allen V 0
7
1/5/2005 John Allen V 0
7
1/6/2005 John Allen V 0
7

I would aslo want to know how to display the two dates that she entered to
be placed in the form. In other words I want the form to look like this
after she enters the information:

(mainform)
Employee Name: John Allen
Vacation Start Date: Jan 3 2005
Vacation End Date: Jan 6 2005

(datasheet subform)
DATE STATUS HOURS VACATION HOURS
1/3/2005 P 7 0
1/4/2005 P 7 0
1/5/2005 P 7 0
1/6/2005 P 7 0

(Buttons)
[Update Records] [Close Form]
--------

And then when she clicks [UPDATE RECORDS] for the subform to change to this:

(mainform)
Employee Name: John Allen
Vacation Start Date: Jan 3 2005
Vacation End Date: Jan 6 2005

(datasheet subform)
DATE STATUS HOURS VACATION HOURS
1/3/2005 V 0 7
1/4/2005 V 0 7
1/5/2005 V 0 7
1/6/2005 V 0 7

(Buttons)
[Update Records] [Close Form]

 

[G. Vaught]

What the user wants and what the user gets are two different things. You
need to make them understand how you get something done is none of their
concern. Tell them it is either the update query or they do it all manually.
Take their choice.
I know this sounds harsh, but user's have no clue to the mechanics of what
goes behind Access. Tell them you can acheive what they want from a click of
a button as soon as they enter the 'seed' data and that is all they need to
know.

Okay... I am still working on this Employee Attendace database... In a
case
where an employee goes on vacation, I had a update query that
automatically
changes the attendance status for those days from "P" to "V" (the
information
for the whole year is pre-entered so all employee status is defaulted to P
for "Present").

The update query would ask for the employees name, the start date of the
vacation and the end date of the vacation. It would then change the
status
for those days to "V", and under the VACATION HOURS feild it would change
that value from 0 to 7, and it would change HOURS (hours spent on duty) to
0
(since it is defaulted to 7).

The user of the database however does not like the update query. I was
wondering if there was a way I could do this in a form. So far I have
gotten
the query to pull up the records that I want to make the changes to. My
problem though is getting the form to update all the records at the same
time. For Example my form would pull up this information if she enters the
[EMPLOYEE NAME] as John Allen, [START DATE] as Jan 3 2005 and [END DATE]
as
Jan 6 2005:

(main form)
*I have not placed anything here yet*

(sub form in datasheet view)
DATE NAME STATUS HOURS VACATION HOURS
1/3/2005 John Allen P 7
0
1/4/2005 John Allen P 7
0
1/5/2005 John Allen P 7
0
1/6/2005 John Allen P 7
0

How would I get the form to change that information to the following when
she clicks a button:

(main form)
*I have not placed anything here yet*

DATE NAME STATUS HOURS VACATION HOURS
1/3/2005 John Allen V 0
7
1/4/2005 John Allen V 0
7
1/5/2005 John Allen V 0
7
1/6/2005 John Allen V 0
7

I would aslo want to know how to display the two dates that she entered to
be placed in the form. In other words I want the form to look like this
after she enters the information:

(mainform)
Employee Name: John Allen
Vacation Start Date: Jan 3 2005
Vacation End Date: Jan 6 2005

(datasheet subform)
DATE STATUS HOURS VACATION HOURS
1/3/2005 P 7 0
1/4/2005 P 7 0
1/5/2005 P 7 0
1/6/2005 P 7 0

(Buttons)
[Update Records] [Close Form]
--------

And then when she clicks [UPDATE RECORDS] for the subform to change to
this:

(mainform)
Employee Name: John Allen
Vacation Start Date: Jan 3 2005
Vacation End Date: Jan 6 2005

(datasheet subform)
DATE STATUS HOURS VACATION HOURS
1/3/2005 V 0 7
1/4/2005 V 0 7
1/5/2005 V 0 7
1/6/2005 V 0 7

(Buttons)
[Update Records] [Close Form]

 

[MacDermott]

Yes, you can most certainly do it with a form.
In fact, you won't lose the work you've done on the update query, either -
you'll just hide it from your users,
which I'd guess is what they want anyhow.

What you'll need to do is change the parameters of your update query.
It probably now has a parameter named something like this:
=[Enter Employee Name]
On your form, which I'll call [MyForm], you can put a textbox named
[txtEmployeeName]
If you change your query's parameter to look like this:
=Forms![MyForm]![txtEmployeeName]
It will grab that parameter from the form (assuming the form is open)
instead of prompting you for it.

You can do the same thing for your other two parameters.

Note: If your form is named something besides MyForm, be sure to put its
name in where I have MyForm. And the same for the textbox(es).

Do post back if you need more help with this.

- Turtle

BTW, when you go to build the Update button, use the wizard and choose
Miscellaneous. You'll have the option to run a query there.

Okay... I am still working on this Employee Attendace database... In a case
where an employee goes on vacation, I had a update query that automatically
changes the attendance status for those days from "P" to "V" (the information
for the whole year is pre-entered so all employee status is defaulted to P
for "Present").

The update query would ask for the employees name, the start date of the
vacation and the end date of the vacation. It would then change the status
for those days to "V", and under the VACATION HOURS feild it would change
that value from 0 to 7, and it would change HOURS (hours spent on duty) to 0
(since it is defaulted to 7).

The user of the database however does not like the update query. I was
wondering if there was a way I could do this in a form. So far I have gotten
the query to pull up the records that I want to make the changes to. My
problem though is getting the form to update all the records at the same
time. For Example my form would pull up this information if she enters the
[EMPLOYEE NAME] as John Allen, [START DATE] as Jan 3 2005 and [END DATE] as
Jan 6 2005:

(main form)
*I have not placed anything here yet*

(sub form in datasheet view)
DATE NAME STATUS HOURS VACATION HOURS
1/3/2005 John Allen P 7
0
1/4/2005 John Allen P 7
0
1/5/2005 John Allen P 7
0
1/6/2005 John Allen P 7
0

How would I get the form to change that information to the following when
she clicks a button:

(main form)
*I have not placed anything here yet*

DATE NAME STATUS HOURS VACATION HOURS
1/3/2005 John Allen V 0
7
1/4/2005 John Allen V 0
7
1/5/2005 John Allen V 0
7
1/6/2005 John Allen V 0
7

I would aslo want to know how to display the two dates that she entered to
be placed in the form. In other words I want the form to look like this
after she enters the information:

(mainform)
Employee Name: John Allen
Vacation Start Date: Jan 3 2005
Vacation End Date: Jan 6 2005

(datasheet subform)
DATE STATUS HOURS VACATION HOURS
1/3/2005 P 7 0
1/4/2005 P 7 0
1/5/2005 P 7 0
1/6/2005 P 7 0

(Buttons)
[Update Records] [Close Form]
--------

And then when she clicks [UPDATE RECORDS] for the subform to change to this:

(mainform)
Employee Name: John Allen
Vacation Start Date: Jan 3 2005
Vacation End Date: Jan 6 2005

(datasheet subform)
DATE STATUS HOURS VACATION HOURS
1/3/2005 V 0 7
1/4/2005 V 0 7
1/5/2005 V 0 7
1/6/2005 V 0 7

(Buttons)
[Update Records] [Close Form]