To make a long story short I have this form with a list-box of tests on the left that can be clicked to change to change the Sub Form on the right to insert laboratory test data into. The data is being copy-pasted from Excel sheets into the database via the Sub Form at the bottom, which works just fine an has saved us a TON of time. However... after much frustration (and arguing) with the person that designed code to check the inputted data's integrity I have decided to come to the forums for help. Here is the currently running If statement in the database:
So as you can see this is a simple "nested If statement" that checks each column of data that is inserted from Excel into Access and it checks whether the "PASS" is really a Pass and the "FAIL" is really a Fail... The main issues with this is that whenever the Subform is changed, clicked, or when records are pasted into the database Access hangs for long periods of time... (it IS running a central server over a network). The person that wrote the code is claiming its a networking error and refuses to fix the problem... but I think its a programming error and that the "Private Sub Form_Current()Code:Private Sub Form_AfterInsert() If [TestReading] = "PASS" Then If [TestReading] < [Min] Then [PASSorFAIL] = "FAIL" End If If [TestReading] > [Max] Then [PASSorFAIL] = "FAIL" End If If [TestReading] = Null Then [PASSorFAIL] = "PASS" End If If [TestReading] > [Min] And [TestReading] < [Max] Then [PASSorFAIL] = "PASS" End If End If If [TestReading] = "FAIL" Then If [TestReading] < [Min] Then [PASSorFAIL] = "FAIL" End If If [TestReading] > [Max] Then [PASSorFAIL] = "FAIL" End If If [TestReading] = Null Then [PASSorFAIL] = "PASS" End If If [TestReading] > [Min] And [TestReading] < [Max] Then [PASSorFAIL] = "PASS" End If End If End Sub Private Sub Form_AfterUpdate() DoCmd.RefreshRecord If [TestReading] = "PASS" Then If [TestReading] < [Min] Then [PASSorFAIL] = "FAIL" End If If [TestReading] > [Max] Then [PASSorFAIL] = "FAIL" End If If [TestReading] = Null Then [PASSorFAIL] = "PASS" End If If [TestReading] > [Min] And [TestReading] < [Max] Then [PASSorFAIL] = "PASS" End If End If If [TestReading] = "FAIL" Then If [TestReading] < [Min] Then [PASSorFAIL] = "FAIL" End If If [TestReading] > [Max] Then [PASSorFAIL] = "FAIL" End If If [TestReading] = Null Then [PASSorFAIL] = "PASS" End If If [TestReading] > [Min] And [TestReading] < [Max] Then [PASSorFAIL] = "PASS" End If End If End Sub Private Sub Form_Current() DoCmd.RefreshRecord End Sub
DoCmd.RefreshRecord" command needs to be removed and replaced with something else.
And also, in my opinion shouldn't the VBA function be changed to something like this?:
I'm not sure if either my co-worker of myself is correct (I'm thinking we're are both wrong) but some help and direction would be greatly appreciated any if there are any questions I will answer them and provide more code if needed. I feel like this code is causing some major errors and upsets in our lab and to finally get it sorted out would be awesome! Thanks in advance.Code:Private Sub TestReading_Change() If [PASSorFAIL] = "PASS" Then If [TestReading] = Null Then [PASSorFAIL] = "PASS" End If If [TestReading] < [Min] Then [PASSorFAIL] = "FAIL" End If If [TestReading] <= [Max] Then [PASSorFAIL] = "PASS" End If If [TestReading] >= [Min] Then [PASSorFAIL] = "PASS" End If If [TestReading] > [Max] Then [PASSorFAIL] = "FAIL" End If End If If [PASSorFAIL] = "FAIL" Then If [TestReading] = Null Then [PASSorFAIL] = "PASS" End If If [TestReading] < [Min] Then [PASSorFAIL] = "FAIL" End If If [TestReading] <= [Max] Then [PASSorFAIL] = "PASS" End If If [TestReading] >= [Min] Then [PASSorFAIL] = "PASS" End If If [TestReading] > [Max] Then [PASSorFAIL] = "FAIL" End If End If End Sub