By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Reverting To Old Value if Validation Fails

P: n/a
I am developing an application for a Pathology Lab. There are Batches
(BatchID) which contain multiple Samples (SampleID), and there can be
multiple tests run from the same Sample, thus there can be multiple records
with the same SampleID.

The Basic Data for the samples is entered when the batch is set up
initially. There are also 3 results textboxes which will hold the results
of the Pathology tests. If the user realizes that there is something wrong
with the Basic Data and decides to change it, they will need to decide
whether to apply the change to "THIS SampleID ONLY "(across several tests)
or "ALL RECORDS IN THE BATCH." Regardless they do not want to copy the
Results textboxes from test to test or batch to batch.

To change the data, the user clicks on the "Change Data" button, which
unlocks the textboxes which can be changed. After the change is made to each
control, the user is then prompted to apply it to the Batch or Sample, as I
described above. The InputBox has a textbox for selecting either B or S or
they can click the Cancel button.

The code I have written is shown below. I put it in control_Before Update,
because if I put it in control_Change then the Inputbox pops up with every
character that is entered. But the problem I am having is that if the User
decides to Cancel the change (or inputs a letter other than B or S), I would
like it to revert back to the original value.

Using CancelUpdate cancels the routine from happening, but the current
record still remains changed.

If I put this code in the form_BeforeUpdate section then how do I get around
it applying this when the user has put something in one of the Results
boxes - which I want to remain in ONLY the current record.

Is there a better way to handle this? (See Case"" or Case Else below):

Private Sub RefrigFreezerNo_BeforeUpdate(Cancel As Integer)

Dim strQuery As String
Dim strSQL As String
Dim rsA As dao.Recordset
Dim Response As String
Dim rsC As dao.Recordset 'I put this in to see if using Recordset clone
would work, but it doesn't seem to

Set rsC = Me.RecordsetClone

Response = InputBox("What records do you want to change?" & vbNewLine &
vbNewLine & _
" All records in the Batch? - Type 'B'" & vbNewLine & _
" or" & vbNewLine & _
" Only the records with this SampleID? - Type 'S'", "Change
Info")
Select Case Response

Case "s"
'this query chenges the data in every record with the same SampleID
strQuery = "qryOpenChangeSamples"
strSQL = "SELECT * from qryChangeSampleInfo WHERE SampleID= " &
Me.SampleID
ChangeQueryDef strQuery, strSQL
Set rsA = CurrentDb.OpenRecordset("qryOpenChangeSamples",
dbOpenDynaset)

rsA.LockEdits = False
rsA.MoveFirst
Do Until rsA.EOF

rsA.Edit
rsA![RefrigFreezerNo] = Me.[RefrigFreezerNo]
rsA.Update

rsA.MoveNext
Loop
Case "b"
'this query applies to all records in the batch
strQuery = "qryOpenChangeSamples"
strSQL = "SELECT * from qryChangeSampleInfo WHERE BatchID= " &
Me.BatchID
ChangeQueryDef strQuery, strSQL
Set rsA = CurrentDb.OpenRecordset("qryOpenChangeSamples",
dbOpenDynaset)

rsA.LockEdits = False
rsA.MoveFirst
Do Until rsA.EOF

rsA.Edit
rsA![RefrigFreezerNo] = Me.[RefrigFreezerNo]
rsA.Update

rsA.MoveNext
Loop

Case "" 'User clicks the Cancel button
'this is one way I tried, but it dodn't seem to cancel in the
current record
rsC.Close
Exit Sub

Case Else 'User inputs another letter
'this is a second warning to enter the correct letters
MsgBox "Enter a B (Batch) or S (Sample)"
Response = InputBox("What records do you want to change?" &
vbNewLine & vbNewLine & _
" All records in the Batch? - Type 'B'" & vbNewLine & _
" or" & vbNewLine & _
" Only the records with this SampleID? - Type 'S'", "Change
Info")

'this is a dummy query to create a recordset on which to cancel the update.
(or should I use the original me.recordset?)
strQuery = "qryOpenChangeSamples"
strSQL = "SELECT * from qryChangeSampleInfo WHERE BatchID= " &
Me.BatchID
ChangeQueryDef strQuery, strSQL
Set rsC = CurrentDb.OpenRecordset("qryOpenChangeSamples",
dbOpenDynaset)

rsC.LockEdits = True
rsC.Edit
rsC!RefrigFreezerNo=Me.RefrigFreezerNo
rsc.CancelUpdate
Exit Sub
End Select

rsA.Close: Set rsA = Nothing
rsC.close:Set rsC=Nothing

End Sub
Thanks in advance to whomever helps me out of this problem!

Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.