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!
