A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Changing Multiple Record Values At The Same Time



 
 
Thread Tools Display Modes
  #1  
Old February 13th, 2005, 04:21 PM
.::Kay-Dija::.
external usenet poster
 
Posts: n/a
Default Changing Multiple Record Values At The Same Time

..::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?

  #2  
Old February 13th, 2005, 05:52 PM
Arvin Meyer
external usenet poster
 
Posts: n/a
Default

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::." wrote in message
...
.::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?



  #3  
Old February 13th, 2005, 06:57 PM
DebbieG
external usenet poster
 
Posts: n/a
Default

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


".::Kay-Dija::." wrote in message ...
| .::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?
|


  #4  
Old February 13th, 2005, 11:11 PM
.::Kay-Dija::.
external usenet poster
 
Posts: n/a
Default

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?
  #5  
Old February 13th, 2005, 11:22 PM
Arvin Meyer
external usenet poster
 
Posts: n/a
Default

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::." wrote in message
...
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?


  #6  
Old February 14th, 2005, 09:48 AM
DebbieG
external usenet poster
 
Posts: n/a
Default

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


".::Kay-Dija::." wrote in message ...
| .::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?
|


  #7  
Old February 15th, 2005, 01:21 AM
.::Kay-Dija::.
external usenet poster
 
Posts: n/a
Default

Thank you, your suggestion worked perfectly....

"Arvin Meyer" wrote:

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::." wrote in message
...
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?



  #8  
Old February 15th, 2005, 01:47 AM
.::Kay-Dija::.
external usenet poster
 
Posts: n/a
Default

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
  #9  
Old February 15th, 2005, 05:12 AM
DebbieG
external usenet poster
 
Posts: n/a
Default

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.


".::Kay-Dija::." wrote in message
...
| 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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Meeting Requests in Multiple Time Zones Tom G. Calendar 1 October 5th, 2004 02:27 PM
Concatenating multiple values into a single field on a report Kevin Running & Setting Up Queries 8 July 16th, 2004 03:31 PM
STORE multiple values from a lookup table to another table. beller Database Design 0 June 15th, 2004 04:42 AM
Form Does Not Go To New Record Steve New Users 1 May 12th, 2004 03:15 AM


All times are GMT +1. The time now is 07:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.