Changing Multiple Record Values At The Same Time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

..::I posted this in Forms Coding but I decided to try my luck here also::.

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]
 
You can push a button to run your present query and then update your form
(aircode):

Sub cmdPushMe (Cancel As Integer)

DoCmd.SetWarnings False
DoCmd.OpenQuery "MyUpdateQuery"
DoCmd.SetWarnings True

Me.NameOfSubformControl.Form.Requery

End Sub

Add some error handling. You can also run the query in code using the
Exceute method, or even DoCmd.RunSQL. The easiest, of course, is to use what
you've got. If working from scratch, I'd opt for the Execute method.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

.::Kay-Dija::. said:
.::I posted this in Forms Coding but I decided to try my luck here also::.

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]
Click to expand...
 
When the user enters a value in VACATION HOURS you could change the values in HOURS and STATUS ... maybe in VACATION HOURS On Exit,
Form_AfterUpdate, or whenever you want to see the change.

(air code, not tested)
if me.[VACATION HOURS] > 0 then
me.[HOURS] = 0 'this assumes the employee has to take a full day of vacation
me.[STATUS] = V
end if

Just a thought.

Debbie


| .::I posted this in Forms Coding but I decided to try my luck here also::.
|
| 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]
| --------------
|
| Any suggestions?
|
 
I am about to try those suggestions... But what about getting the main form
to display the information that the user typed in such as the Employee Name,
the Vacation Start Date and the Vacation End date in the main form. I have
gotten the records to show in the datasheet. For example:

QUESTION USER ENTERS
What is the emplouee name John Allen
Vacation Start Date Jan-3-2005
Vaction End Date Jan-6-2005

How would I get the information to display this in the main form:

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

Is there a way that I can recall what the user entered in and assigned those
values to text boxes in my form? Keep in mind that the promts come from the
subform that displays the filtered information. Can anyone help me with this?
 
Use the form references in a query or SQL string as the recordsource for the
subform then Requery the subform in the click event of the dialog form. You
can do that by either just ostensibly changing the rowsource:

Forms!FormName!subformName.Form.RowSource = "Select ..."

or if you've used the form reference in your query:

Forms!FormName!subformName.Form.Requery
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


.::Kay-Dija::. said:
I am about to try those suggestions... But what about getting the main form
to display the information that the user typed in such as the Employee Name,
the Vacation Start Date and the Vacation End date in the main form. I have
gotten the records to show in the datasheet. For example:

QUESTION USER ENTERS
What is the emplouee name John Allen
Vacation Start Date Jan-3-2005
Vaction End Date Jan-6-2005

How would I get the information to display this in the main form:

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

Is there a way that I can recall what the user entered in and assigned those
values to text boxes in my form? Keep in mind that the promts come from the
subform that displays the filtered information. Can anyone help me with
Click to expand...
this?
 
What does the user not like about the update query?

Is this form's use just to change the employee's status from P to V? What if they have a status other than V? As a user I wouldn't
want to open a different form for every status. I think as a user I would want to see an employee's weekly records and make the
appropriate changes to their status.

If you have pre-entered a record for every employee for every working day of the year what are you going to do if an employee quits
or is terminated? Is the employee's name entered in every record or just once in a table that you've joined with the hours records?
What are your different tables and their fields and how are they joined together? When you enter a new record you can have the
status default to P but change it if necessary.

I guess I just don't understand the purpose of your database and what you're really wanting it to do. I saw in one of your posts
that you stored the date (e.g., 1/3/2005) and the day of the month (e.g., 3) for each record. You don't need both -- you can
extract the 3 from the date. It seems like you shouldn't be creating queries, forms, or reports until you've got your tables
designed correctly and normalized.

As someone who has learned Access the hard way (and still learning) and made (and continue to make) many mistakes , I finally
figured out that after I think I have the tables normalized it is better to organize how to get the data entered first with forms,
and then work on outputting the data to reports. Once the data is correct the reports kinda fall into place.

Debbie


| .::I posted this in Forms Coding but I decided to try my luck here also::.
|
| 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]
| --------------
|
| Any suggestions?
|
 
Thank you, your suggestion worked perfectly....

Arvin Meyer said:
Use the form references in a query or SQL string as the recordsource for the
subform then Requery the subform in the click event of the dialog form. You
can do that by either just ostensibly changing the rowsource:

Forms!FormName!subformName.Form.RowSource = "Select ..."

or if you've used the form reference in your query:

Forms!FormName!subformName.Form.Requery
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access



this?
Click to expand...
 
The reason I had the information pre-entered is because I did not want the
user have to enter information for each employee everyday... I was trying to
make her job easier. I figured that if the information was pre entered, all
she would have to do is edit the records whose information needed changing.

Maybe it is the wrong way to go about doing it... But if you have any other
suggestion or a better way of doing it... Let me know
 
Here's a suggestion:

I just created a Payroll database and I didn't want the users to enter dates
either. I have a form that allows the user to choose the employee (combo box)
and the work week -- I added a combo box showing the ending pay period and then
have a calculated textbox for the beginning pay period (= EndDate_Combo - 6).

Then this code adds 7 records, if needed:

'check to see how many records for this employee for the work week
Dim CkRecordCount
CkRecordCount = DCount("[EmpID] & [WorkDate]", "tblHoursWorked", _
"[EmpID] = '" & Me.Employee_combo & "'" & _
" AND [WorkDate] between #" & Me.txtBeginDate & "# and #" &
Me.EndDate_Combo & "#")

Dim dt As Date, CkWorkDate
If CkRecordCount < 7 Then
'to make sure there are 7 days displayed for the work week
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblHoursWorked", dbOpenDynaset, dbAppendOnly)

For dt = Me.txtBeginDate To Me.txtBeginDate + 6
CkWorkDate = DLookup("[EmpNo] & [WorkDate]", "tblHoursWorked", _
"[EmpNo] = '" & Me.Employee_combo & "'" & _
" AND [WorkDate] = #" & dt & "#")
If IsNull(CkWorkDate) Then
With rs
.AddNew
!EmpNo= Me.Employee_combo
!WorkDate = dt
.Update
End With
End If
Next

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End If

Then in Form_Close, I run a delete query on tblHoursWorked if all fields are
empty except EmpNo and Workdate so I don't have useless records in my table.


| The reason I had the information pre-entered is because I did not want the
| user have to enter information for each employee everyday... I was trying to
| make her job easier. I figured that if the information was pre entered, all
| she would have to do is edit the records whose information needed changing.
|
| Maybe it is the wrong way to go about doing it... But if you have any other
| suggestion or a better way of doing it... Let me know
 
Back
Top