UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Event That Would Trigger On Move To Next Record Or Closing Form Without Any Data Changes, Access 2016    
 
   
Kim
post Sep 19 2019, 08:23 PM
Post#1



Posts: 21
Joined: 25-July 18



I need to compare two rows on a subform and popup a message box if values in the two rows are not in harmony with each other. The event needs to fire when you switch from one parent record to the next and also when you close the form, even if no data has been updated in pthe parent form or child subform. Is there a single event that will meet this criteria, or do I need to put the code in multiple events?
Go to the top of the page
 
RJD
post Sep 19 2019, 08:48 PM
Post#2


UtterAccess VIP
Posts: 10,095
Joined: 25-October 10
From: Gulf South USA


Hi Kim: I'm not sure I understand your situation. You seem to have two rows of controls in a subform, one over the other (two records?). Is this correct? Will there only be two rows? And you want to compare the value in the same controls between rows, and see if they match (I assume this is what you mean by "in harmony").

Rather than have us guess about your data, perhaps you could post a small db with relevant objects and some test data for us to work with. That should help us help you.

But in any case, you might try the On Current event to compare the values (or read a control that indicates if the rows compare) and pop up the message.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
MadPiet
post Sep 19 2019, 09:24 PM
Post#3



Posts: 3,332
Joined: 27-February 09



Double-entry accounting system or similar?

If you have an "A" set and a "B" set, and A(n) is supposed to be equal to B(n), then you could maybe use the Me.Controls("B" & iCounter).Value = Me.Controls("A" & iCounter).Value trick?

Before saving check that they're all the same. If not, don't save. Just show a message and maybe highlight the mismatch?
Go to the top of the page
 
Kim
post Sep 20 2019, 10:32 AM
Post#4



Posts: 21
Joined: 25-July 18



Sorry I wasn't clear enough. I was trying to be succinct and just ended up being vague.

The database is for bids for a General Contractor. I have two rows within a datasheet - one holds the number of hours a Superintendent is estimated to work on the project, the other is the number of months estimated that we would rent a Sanican. We want to be sure we are estimating enough Superintendent hours for the project. The number of months for the Sanican tells us how long we expect the project to take, so we use that number to double check our Superintendent hours to be sure they meet the minimum threshold.

CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim iSuperintendentHours As Integer
Dim iTempSanitationMonths As Integer
Dim iMinimumSuperintendentHours As Integer
Dim iYesNo As Integer

iSuperintendentHours = DLookup("Unit", "qBid_LineItemsWithBase", "CSI_ID = 5 and BidRevisionID = " & Me!txtRevisionID)
iTempSanitationMonths = DLookup("Unit", "qBid_LineItemsWithBase", "CSI_ID = 12 and BidRevisionID = " & Me!txtRevisionID)

'Check to make sure that there are enough Superintendent Hours by comparing them with Sanican Months
'Formula: 160 hours per month should be allotted for superintendent.
'# of units (months) allotted for Sanican is # of months for project
'Minimum # of units (hours) for Superintendent should be no less than 50% of Sanican months  (160 * months) / 2
iMinimumSuperintendentHours = (160 * iTempSanitationMonths) / 2

If iSuperintendentHours < iMinimumSuperintendentHours Then
    iYesNo = MsgBox("Based on the number of units for Temp Sanitation, " & vbCrLf & _
        "Superintendent hours should be greater than " & iMinimumSuperintendentHours & "." & vbCrLf & _
        "Do you wish to change one of those values before you leave the current bid?", vbExclamation + vbYesNo)
    If iYesNo = vbYes Then
        Cancel = True
    End If
End If

End Sub


Right now the code is on the form BeforeUpdate, but that event only fires if a change was made to at least one piece of data on the form. As I mentioned in my original post: "The event needs to fire when you switch from one parent record to the next and also when you close the form, even if no data has been updated in the parent form or child subform." I want the estimator to be able to have these two values disagree if there is a valid reason for it, but I want to remind him they are not in agreement any time he closes the form or steps to a new record. That way, if the reason they were not in agreement has changed, he can change either the months for the Sanican or the hours for the Superintendent so they are in agreement.

Joe: The problem I see with the OnCurrent event is that it may fire multiple times and I do not want this message to come up repeatedly.
Go to the top of the page
 
RJD
post Sep 20 2019, 02:28 PM
Post#5


UtterAccess VIP
Posts: 10,095
Joined: 25-October 10
From: Gulf South USA


Hi Kim: I think I basically see what you are doing - but it would still be more helpful if we could see your db (a cutdown of relevant objects, with example data, zipped). Could you post something like that for us to work with?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Kim
post Sep 25 2019, 06:16 PM
Post#6



Posts: 21
Joined: 25-July 18



Joe: Sorry it has taken me so long. I have not been able to get back to the database until today.

I am attaching a database that has been stripped down to just what is needed to see the issue I am wishing to address. What I would like to be able to do is flag the estimator if he steps off the parent record or closes the form when the Superintendent and Sanican (Temp Sanitation) child records do not meet the correct criteria. He then has a choice to cancel and go back to the record to make the update or allow them to disagree. I tried it in OnCurrent, but the data that it is checking is from the new record in the child form rather than the one he just stepped off of. Also, that event will not fire when he closes the form. I recognize that this code may have to be in more than one event, so that will be fine as long as I can figure out which events those would be.
When the estimator comes into the main form, he will be making changes to the child records, but not many to the revision after the initial input.
Also, the validation needs to happen even if he does not make a change to any data in the main form or subforms, i.e. if he is just coming in to review the bid, he needs to be flagged. There are occasional instances when the numbers will not agree with each other, so it has to allow him to leave them in disagreement, but always be aware of it in case the original reason for leaving it out of sync changes.

Let me know if any of this is not clear and thanks for your time.
Kim
Attached File(s)
Attached File  Validate_Superintendent_Hours.zip ( 56.65K )Number of downloads: 3
 
Go to the top of the page
 
Kim
post Oct 9 2019, 03:31 PM
Post#7



Posts: 21
Joined: 25-July 18



The data requirements changed so it turns out this is no longer necessary.
Go to the top of the page
 
RJD
post Oct 9 2019, 06:34 PM
Post#8


UtterAccess VIP
Posts: 10,095
Joined: 25-October 10
From: Gulf South USA


Hi Kim: My apologies for not seeing your Sept 25th attachment before now. We should have done better ... but now it looks like the requirement has gone away, so the issue is moot, I guess.

Continued success with your efforts - and let us know if we can be of assistance. I'll try to be more responsive next time!

Regards,
Joe


--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th October 2019 - 08:11 PM