Update Yes/No field in table if date is less than today

[micko1]

Having issues with getting auto update of Yes/No field dependant on todays date.
When users open the form the code runs through and checks all entries and updates the records if either of the dates in two separate fields is less than today. The issue I have is when some users are already using this form a second user cannot open it. Error shows other user information as having the form locked and code halts at highlighted spot below. Is there another way of ensuring this field is updated to Yes or No if the date in the field is less than current date.

Private Sub Form_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("contractors")
rst.MoveFirst
Do Until rst.EOF
If rst!WorkersComp < Date Or rst!PublicLiability < Date Then
    
    [COLOR=red]rst.Edit "Code halts here'
[/COLOR]   rst!Compliant = False
    rst.Update
    
Else
    rst.Edit
    rst!Compliant = True
    rst.Update
 
End If
 rst.MoveNext
Loop
rst.Close
Set rst = Nothing
 
End Sub

 

[Galaxiom]

Maintaining the checkbox field dependent on other data is a breach of normalization.

You should calculate this status as required for display or other processing.

 

[micko1]

Galaxiom Thanks very much for your reply. Learning a lot about correct way to do things. Still very green when it comes to Access. I have created 2 update queries that run silently as soon as the data base is entered to update the yes/no field. This appears to work fine, is this the best option??.
Thanks again for your assistance.

 

[Galaxiom]

I have created 2 update queries that run silently as soon as the data base is entered to update the yes/no field. This appears to work fine, is this the best option??

No. Whatever queries you have that currently read the Y/N field should directly compare the date fields with today's date every time that status is required.

Databases should not store calculated information.