If .. Then .. Else Against All Records

[mous]

Hello Again

I have a form whereby I have two fields : a date field and a yes/no checkbox.

I have a Save and Close button when pressed it runs a procedure to say if there is a date in the date field but the yes/no checkbox = 0 ( i.e. null) then show a message box. Which all works fine for the record that the cursor is currently in.

If a user enters many records (which they can) it only checks the current record - I would like it to look up all the dates on screen or in the table.

Thanks for your help

Dawn

 

[AlanS]

One way to do this is to simply put your error-checking code in the form's BeforeUpdate event procedure. If the record is not correctly filled in, display a message box and set the Cancel argument to True (which will cancel the update and allow the user a chance to correct the problem). That way, every record will be verified before it is saved, eliminating the need to go back later and check multiple records.

If you must check multiple records in a batch, you can set up a VBA procedure that uses DAO objects to loop through all records, something like this (written for Access 97; some coding changes will be necessary for later versions):

Sub CheckAndFix
Dim db as Database, rs as Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")
With rs
Do While Not .EOF
If (Not (IsNull(dtDateField))) And (Not Nz(chkCheckBox, False)) Then
.Edit
!chkCheckBox = True
.Update
End If
.MoveNext
Loop
End With
set rs = Nothing
set db = Nothing
End Sub