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
|
|||
|
|||
How to validate data for new and past records being entered
Hello All
I have a problem where I need to figure out how to know a record that is before and another record that is after a new record that I am about to create. For instance, I have a table where I store information of "running hour" history. The machine runs a number of hours and on a date I record the current amount of total hours the machine has run. At a later date I record the hours again, always greater than the last time I recorded the hours, but there is no predetermined schedule for recording the hours. So now a coworker has a record that I want to place in between the 2 dates but I need to make sure that the entry will be valid. Here is what it looks like: RHDate RunningHoursID HoursAtDate ServiceReportID BlockID 2009/07/04 21152 178 22466 1674 2009/06/28 21084 117 22399 1674 2009/06/24 21085 84 22400 1674 2009/06/22 20871 67 22161 1674 2009/06/21 20875 67 21043 1674 2009/06/21 20869 60 22159 1674 2009/06/19 20807 39 22094 1674 2009/06/17 20806 17 22093 1674 2009/06/09 20508 7 21796 1674 Some things to note about the data, the running hour date is the same as entered on the service report. The blockID is the machine ID, so all this is for one machine. Now let’s say that someone recorded a service report on 2009/06/26 and was entering it. How do I determine if the entry of hours will be valid? No machine can run more than 24 hours a day, and it has to be more than the hours recorded on the 24th and less that the hours on the 28th. I created a query to give me a list of all the hours, showing the prior hours and date, and then it calculates a lot of what I am talking about, but how do I get a form to do the check using the same logic? I need a way for the form to check against something to see if the hours work into the logic, but I don’t know if I should be creating a query, or a routine or function on my form. When someone enters the running hours on the service report I need the form to check the date of the report and the hours the user entered against the previous data and figure out if the amount of hours are valid and with in the range of hours already entered. So… RHDate RunningHoursID HoursAtDate ServiceReportID BlockID 2009/06/28 21084 117 22399 1674 2009/06/26 22085 96 25050 1674 2009/06/24 21085 84 22400 1674 Is good data… RHDate RunningHoursID HoursAtDate ServiceReportID BlockID 2009/06/28 21084 117 22399 1674 2009/06/25 22085 109 25050 1674 2009/06/24 21085 84 22400 1674 Is bad data, because the difference between 84 and 109 is 25, 1 hour more than it could run in a day. RHDate RunningHoursID HoursAtDate ServiceReportID BlockID 2009/06/28 21084 117 22399 1674 2009/06/27 22085 146 25050 1674 2009/06/24 21085 84 22400 1674 Is greater than the next date’s entry. I hope I have explained the situation well enough at this point. I am hoping someone can give me some ideas as to what to do to validate these entries on this form. I also apologize for the long post, just hope it all makes sense. As always, thanks for any and all help. |
#2
|
|||
|
|||
How to validate data for new and past records being entered
This is air code and could probably use some tweaking,
but it may get you started. You'll need to modify with your own field & table names, etc.; Private Sub Form_BeforeUpdate(Cancel As Integer) Dim PrevDate As Date Dim NextDate As Date Dim PrevHours As Long Dim NextHours As Long PrevDate = Nz(DMax("RHDate", "tblRunningHours", "RHDate#" _ & Me.RHDate & "# And BlockID=" & Me.BlockID), 0) NextDate = Nz(DMin("RHDate", "tblRunningHours", "RHDate#" _ & Me.RHDate & "# And BlockID=" & Me.BlockID), 0) PrevHours = Nz(DLookup("Hours", "tblRunningHours", "RHDate=#" _ & PrevDate & "# And BlockID=" & Me.BlockID), 0) NextHours = Nz(DLookup("Hours", "tblRunningHours", "RHDate=#" _ & NextDate & "# And BlockID=" & Me.BlockID), 0) If PrevHours 0 Then If Me.Hours PrevHours Then MsgBox "Hours value must be greater than the previous record." Cancel = True ElseIf (Me.Hours - PrevHours) _ (DateDiff("d", PrevDate, Me.RHDate) * 24) Then MsgBox "Not that many hours in the time frame" Cancel = True End If End If If NextHours 0 Then If Me.Hours NextHours Then MsgBox "Hours value must be less than the next record." Cancel = True End If End If End Sub -- _________ Sean Bailey "DawnTreader" wrote: Hello All I have a problem where I need to figure out how to know a record that is before and another record that is after a new record that I am about to create. For instance, I have a table where I store information of "running hour" history. The machine runs a number of hours and on a date I record the current amount of total hours the machine has run. At a later date I record the hours again, always greater than the last time I recorded the hours, but there is no predetermined schedule for recording the hours. So now a coworker has a record that I want to place in between the 2 dates but I need to make sure that the entry will be valid. Here is what it looks like: RHDate RunningHoursID HoursAtDate ServiceReportID BlockID 2009/07/04 21152 178 22466 1674 2009/06/28 21084 117 22399 1674 2009/06/24 21085 84 22400 1674 2009/06/22 20871 67 22161 1674 2009/06/21 20875 67 21043 1674 2009/06/21 20869 60 22159 1674 2009/06/19 20807 39 22094 1674 2009/06/17 20806 17 22093 1674 2009/06/09 20508 7 21796 1674 Some things to note about the data, the running hour date is the same as entered on the service report. The blockID is the machine ID, so all this is for one machine. Now let’s say that someone recorded a service report on 2009/06/26 and was entering it. How do I determine if the entry of hours will be valid? No machine can run more than 24 hours a day, and it has to be more than the hours recorded on the 24th and less that the hours on the 28th. I created a query to give me a list of all the hours, showing the prior hours and date, and then it calculates a lot of what I am talking about, but how do I get a form to do the check using the same logic? I need a way for the form to check against something to see if the hours work into the logic, but I don’t know if I should be creating a query, or a routine or function on my form. When someone enters the running hours on the service report I need the form to check the date of the report and the hours the user entered against the previous data and figure out if the amount of hours are valid and with in the range of hours already entered. So… RHDate RunningHoursID HoursAtDate ServiceReportID BlockID 2009/06/28 21084 117 22399 1674 2009/06/26 22085 96 25050 1674 2009/06/24 21085 84 22400 1674 Is good data… RHDate RunningHoursID HoursAtDate ServiceReportID BlockID 2009/06/28 21084 117 22399 1674 2009/06/25 22085 109 25050 1674 2009/06/24 21085 84 22400 1674 Is bad data, because the difference between 84 and 109 is 25, 1 hour more than it could run in a day. RHDate RunningHoursID HoursAtDate ServiceReportID BlockID 2009/06/28 21084 117 22399 1674 2009/06/27 22085 146 25050 1674 2009/06/24 21085 84 22400 1674 Is greater than the next date’s entry. I hope I have explained the situation well enough at this point. I am hoping someone can give me some ideas as to what to do to validate these entries on this form. I also apologize for the long post, just hope it all makes sense. As always, thanks for any and all help. |
#3
|
|||
|
|||
How to validate data for new and past records being entered
Hello Beetle
hmmm... that could work. thanks for the insight. sometimes walking away from a problem and getting a second pair of eyes on it can really help. "Beetle" wrote: This is air code and could probably use some tweaking, but it may get you started. You'll need to modify with your own field & table names, etc.; Private Sub Form_BeforeUpdate(Cancel As Integer) Dim PrevDate As Date Dim NextDate As Date Dim PrevHours As Long Dim NextHours As Long PrevDate = Nz(DMax("RHDate", "tblRunningHours", "RHDate#" _ & Me.RHDate & "# And BlockID=" & Me.BlockID), 0) NextDate = Nz(DMin("RHDate", "tblRunningHours", "RHDate#" _ & Me.RHDate & "# And BlockID=" & Me.BlockID), 0) PrevHours = Nz(DLookup("Hours", "tblRunningHours", "RHDate=#" _ & PrevDate & "# And BlockID=" & Me.BlockID), 0) NextHours = Nz(DLookup("Hours", "tblRunningHours", "RHDate=#" _ & NextDate & "# And BlockID=" & Me.BlockID), 0) If PrevHours 0 Then If Me.Hours PrevHours Then MsgBox "Hours value must be greater than the previous record." Cancel = True ElseIf (Me.Hours - PrevHours) _ (DateDiff("d", PrevDate, Me.RHDate) * 24) Then MsgBox "Not that many hours in the time frame" Cancel = True End If End If If NextHours 0 Then If Me.Hours NextHours Then MsgBox "Hours value must be less than the next record." Cancel = True End If End If End Sub -- _________ Sean Bailey "DawnTreader" wrote: Hello All I have a problem where I need to figure out how to know a record that is before and another record that is after a new record that I am about to create. For instance, I have a table where I store information of "running hour" history. The machine runs a number of hours and on a date I record the current amount of total hours the machine has run. At a later date I record the hours again, always greater than the last time I recorded the hours, but there is no predetermined schedule for recording the hours. So now a coworker has a record that I want to place in between the 2 dates but I need to make sure that the entry will be valid. Here is what it looks like: RHDate RunningHoursID HoursAtDate ServiceReportID BlockID 2009/07/04 21152 178 22466 1674 2009/06/28 21084 117 22399 1674 2009/06/24 21085 84 22400 1674 2009/06/22 20871 67 22161 1674 2009/06/21 20875 67 21043 1674 2009/06/21 20869 60 22159 1674 2009/06/19 20807 39 22094 1674 2009/06/17 20806 17 22093 1674 2009/06/09 20508 7 21796 1674 Some things to note about the data, the running hour date is the same as entered on the service report. The blockID is the machine ID, so all this is for one machine. Now let’s say that someone recorded a service report on 2009/06/26 and was entering it. How do I determine if the entry of hours will be valid? No machine can run more than 24 hours a day, and it has to be more than the hours recorded on the 24th and less that the hours on the 28th. I created a query to give me a list of all the hours, showing the prior hours and date, and then it calculates a lot of what I am talking about, but how do I get a form to do the check using the same logic? I need a way for the form to check against something to see if the hours work into the logic, but I don’t know if I should be creating a query, or a routine or function on my form. When someone enters the running hours on the service report I need the form to check the date of the report and the hours the user entered against the previous data and figure out if the amount of hours are valid and with in the range of hours already entered. So… RHDate RunningHoursID HoursAtDate ServiceReportID BlockID 2009/06/28 21084 117 22399 1674 2009/06/26 22085 96 25050 1674 2009/06/24 21085 84 22400 1674 Is good data… RHDate RunningHoursID HoursAtDate ServiceReportID BlockID 2009/06/28 21084 117 22399 1674 2009/06/25 22085 109 25050 1674 2009/06/24 21085 84 22400 1674 Is bad data, because the difference between 84 and 109 is 25, 1 hour more than it could run in a day. RHDate RunningHoursID HoursAtDate ServiceReportID BlockID 2009/06/28 21084 117 22399 1674 2009/06/27 22085 146 25050 1674 2009/06/24 21085 84 22400 1674 Is greater than the next date’s entry. I hope I have explained the situation well enough at this point. I am hoping someone can give me some ideas as to what to do to validate these entries on this form. I also apologize for the long post, just hope it all makes sense. As always, thanks for any and all help. |
Thread Tools | |
Display Modes | |
|
|