How to stop some records being updated. 1

[petertickler]

I am displaying a Recordset on a form. I want to stop certain records in that recordset from being updated (depending on the value of one particular field in the recordset).

Can anyone tell me how to go about this?

 

[DrSimon]

The general concept would be to do the validation during the Before Update event and if it fails, to carry out Cancel Event
Simon Rouse

 

[petertickler]

Thanks Simon,but I think I was really hoping to do something as soon as the records are displayed. For example, changing the colour of those records that are being displayed for information, and are not meant to be updated on this occasion.

 

[DrSimon]

You could easily add code to change the backcolor at the same time e.g. to stop any value greater than 6:
If [Field] > 6 Then
[Field].BackColor = 255
DoCmd.CancelEvent
Else
[Field].BackColor = 16777215
End If
This makes Field Red if the value entered was greater than 6, but white otherwise

 

[petertickler]

The only problem I am having now is in changing the colour of the record. I have tried to do it two different ways:

(1) Me![Result].BackColor = 255

In this case I get a runtime error "Object doesn’t support this propery or method".

(2) Me.txtResult.BackColor = 255

This works rather too well as it colours red the Result field of every in the dataset, not just the current one.

Can you help?

Peter

 

[DrSimon]

Ah, yes it will. I was under the (mistaken) impression that you were using a single form view. The form gives all occurances of the same object the same attributes.
OK 2 possible solutions:
a) Replace Me.txtResult.BackColor = 255 with a msgbox message
b) This only work if you are using continuous forms - not with a datasheet view. Create a Form Header section and put an unbound text box in it called say [Message]. Change its visible property to 'No'.
Now change the code to something like:
if (Logical Test ) then
[Message].Visible = True
[Message] = "Bad Entry"
DoCmd.CancelEvent
Else
[Message].Visible = False
end if
I hope this does the job.

 

[flip79]

Why don't you do a logical test and lock the field
if (test) then
me.field.locked = true
Else
me.fieldname.locked = false)
End if