Learning Before_Update, or something similar

[WDSnews]

Beyond normal validation, what's the correct way to check for a legitimate
change in a field and to cancel the user's input if incorrect? In this case
I need to collect the user's input, then run a query to count the number of
records in which the data can be found. Depending on the record count, I
want to either accept the input or cancel it. I'd prefer to give a msgBox
notification before canceling the input.

Is Before_Update the right place for this?
How is data properly accepted or rejected?

 

[Piet Linden]

Beyond normal validation, what's the correct way to check for a legitimate
change in a field and to cancel the user's input if incorrect?  In thiscase
I need to collect the user's input, then run a query to count the number of
records in which the data can be found.  Depending on the record count,I
want to either accept the input or cancel it.  I'd prefer to give a msgBox
notification before canceling the input.

Is Before_Update the right place for this?
How is data properly accepted or rejected?

Yes, Before_Update of the control is correct. You could use DCount()
or a recordset and parameterized totals query to get the value

If DCount(...)=cMAX_ALLOWED Then
MsgBox "Already at limit...",vbokonly+vbInformation
Cancel = True
End If

 

[John W. Vinson]

Beyond normal validation, what's the correct way to check for a legitimate
change in a field and to cancel the user's input if incorrect? In this case
I need to collect the user's input, then run a query to count the number of
records in which the data can be found. Depending on the record count, I
want to either accept the input or cancel it. I'd prefer to give a msgBox
notification before canceling the input.

Is Before_Update the right place for this?
How is data properly accepted or rejected?

The Form's BeforeUpdate event would be the typical place for this purpose. The
BeforeUpdate event subroutine has a Cancel property - setting it to True will
cancel the update. You could use code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
If DCount("*", "SomeTable", "SomeCriteria") > 100 Then
MsgBox "Only 100 records allowed"
Cancel = True
End If
End Sub