Problem with OldValue

[Andi Plotsky]

I am running some code to validate the entry made into a form called
NumberOfSamples. If validation fails, then I want to revert back to the old
value. I keep getting an error msg that says:

"Run-time Error 2115: The macro or function set to the Before Update or
Validation Rule property for this field is preventing Access from saving the
data in the field." Debug points to the oldvalue statement. What might be
the problem? See code below.....

Thanks for your help!
Andi Plotsky
Private Sub NumberOfSamples_BeforeUpdate(Cancel As Integer)
Dim rsA As DAO.Recordset
Dim strQuery As String
Dim strSQL As String
Dim numrec As Integer
Dim varTestname As String

Set frmcurrent = Me.Form
varTestname = SelectTestsLast(frmcurrent)
Debug.Print "vartestname= "; varTestname

'Select 1 of each SampleID to count the current number of samples:
strQuery = "qryOpenSamples"
strSQL = "SELECT SampleID,[Testname],BatchID, SubjectName,HistNo,[Sample
Type],RefrigFreezerNo,ShelfNo, DateOfSample,TimeOfSample FROM qryblue WHERE
BatchID=" & Me.BatchID & " AND Testname=" & "'" & varTestname & "'" & "
order by SampleID"
ChangeQueryDef strQuery, strSQL

Set rsA = CurrentDb.OpenRecordset("qryOpenSamples", dbOpenDynaset)

rsA.MoveFirst
rsA.MoveLast
numrec = rsA.RecordCount 'the number of Samples in the batch previously
Debug.Print "numrec= "; numrec

If NumberOfSamples < numrec Then
MsgBox "Oops! The new Number of Samples is LESS than the current
Number of Samples. If you want to DELETE reccords, start at the Main Menu."
NumberOfSamples.Value = NumberOfSamples.OldValue
Exit Sub
End If

If NumberOfSamples = numrec Then
MsgBox "You have not changed the Number of Samples"
NumberOfSamples.Value = NumberOfSamples.OldValue
Exit Sub
End If

rsA.Close: Set rsA = Nothing

End Sub
--
Andi Plotsky
IRIS, LLC

[Salad]

Andi Plotsky wrote:

I am running some code to validate the entry made into a form called
NumberOfSamples. If validation fails, then I want to revert back to the old
value. I keep getting an error msg that says:

"Run-time Error 2115: The macro or function set to the Before Update or
Validation Rule property for this field is preventing Access from saving the
data in the field." Debug points to the oldvalue statement. What might be
the problem? See code below.....

Thanks for your help!
Andi Plotsky
Private Sub NumberOfSamples_BeforeUpdate(Cancel As Integer)
Dim rsA As DAO.Recordset
Dim strQuery As String
Dim strSQL As String
Dim numrec As Integer
Dim varTestname As String

Set frmcurrent = Me.Form
varTestname = SelectTestsLast(frmcurrent)
Debug.Print "vartestname= "; varTestname

'Select 1 of each SampleID to count the current number of samples:
strQuery = "qryOpenSamples"
strSQL = "SELECT SampleID,[Testname],BatchID, SubjectName,HistNo,[Sample
Type],RefrigFreezerNo,ShelfNo, DateOfSample,TimeOfSample FROM qryblue WHERE
BatchID=" & Me.BatchID & " AND Testname=" & "'" & varTestname & "'" & "
order by SampleID"
ChangeQueryDef strQuery, strSQL

Set rsA = CurrentDb.OpenRecordset("qryOpenSamples", dbOpenDynaset)

rsA.MoveFirst
rsA.MoveLast
numrec = rsA.RecordCount 'the number of Samples in the batch previously
Debug.Print "numrec= "; numrec

If NumberOfSamples < numrec Then
MsgBox "Oops! The new Number of Samples is LESS than the current
Number of Samples. If you want to DELETE reccords, start at the Main Menu."
NumberOfSamples.Value = NumberOfSamples.OldValue
Exit Sub
End If

If NumberOfSamples = numrec Then
MsgBox "You have not changed the Number of Samples"
NumberOfSamples.Value = NumberOfSamples.OldValue
Exit Sub
End If

rsA.Close: Set rsA = Nothing

End Sub

See if this works. Apply where necessary

If NumberOfSamples = numrec Then
MsgBox "You have not changed the Number of Samples"
me.NumberOfSamples.Undo
Cancel = True
End If

[Andi Plotsky]

It worked - thanks!!
"Salad" <oi*@vinegar.com> wrote in message
news:S_*****************@newsread2.news.pas.earthl ink.net...

Andi Plotsky wrote:

I am running some code to validate the entry made into a form called
NumberOfSamples. If validation fails, then I want to revert back to the old value. I keep getting an error msg that says:

"Run-time Error 2115: The macro or function set to the Before Update or
Validation Rule property for this field is preventing Access from saving the data in the field." Debug points to the oldvalue statement. What might be the problem? See code below.....

Thanks for your help!
Andi Plotsky
Private Sub NumberOfSamples_BeforeUpdate(Cancel As Integer)
Dim rsA As DAO.Recordset
Dim strQuery As String
Dim strSQL As String
Dim numrec As Integer
Dim varTestname As String

Set frmcurrent = Me.Form
varTestname = SelectTestsLast(frmcurrent)
Debug.Print "vartestname= "; varTestname

'Select 1 of each SampleID to count the current number of samples:
strQuery = "qryOpenSamples"
strSQL = "SELECT SampleID,[Testname],BatchID, SubjectName,HistNo,[Sample Type],RefrigFreezerNo,ShelfNo, DateOfSample,TimeOfSample FROM qryblue WHERE BatchID=" & Me.BatchID & " AND Testname=" & "'" & varTestname & "'" & "
order by SampleID"
ChangeQueryDef strQuery, strSQL

Set rsA = CurrentDb.OpenRecordset("qryOpenSamples", dbOpenDynaset)

rsA.MoveFirst
rsA.MoveLast
numrec = rsA.RecordCount 'the number of Samples in the batch previously Debug.Print "numrec= "; numrec

If NumberOfSamples < numrec Then
MsgBox "Oops! The new Number of Samples is LESS than the current Number of Samples. If you want to DELETE reccords, start at the Main Menu." NumberOfSamples.Value = NumberOfSamples.OldValue
Exit Sub
End If

If NumberOfSamples = numrec Then
MsgBox "You have not changed the Number of Samples"
NumberOfSamples.Value = NumberOfSamples.OldValue
Exit Sub
End If

rsA.Close: Set rsA = Nothing

End Sub

See if this works. Apply where necessary

If NumberOfSamples = numrec Then
MsgBox "You have not changed the Number of Samples"
me.NumberOfSamples.Undo
Cancel = True
End If