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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |