Loop thru filtered recordset? (1 Viewer)

[PaddyIrishMan]

Hi,
Currently I have a Db which stores Time sheets. The user fills out their weekly timesheet then submits. (A Filter is applied per-user so that only their own records are visible to them). A query then runs in the background & flags all of the submitted records to "Submitted" & makes them available for Managers to Approve.

Anyway, what I want to do is add in some validation to stop users from leaving fields blank. I dont want to make them "required" in the tables cos I dont like those nasty access messages.

So; I'd like to loop thru the filtered recordset checking each field in each record for blanks. I have used access to create the recordset so I assume I'll just be using me.recordset etc. I'm not familiar with using the recordset object at all so any advice or basic syntax to get me started would be very much appreciated.

Regards,
Patrick

 

[WayneRyan]

Patrick,

Here is an example of something that uses recordsets:

Dim dbs As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim sql As String

Set dbs = CurrentDb

sql = "select * from ActualCosts_Travel"
Set rst1 = dbs.OpenRecordset(sql)
rst1.MoveFirst
While Not rst1.EOF And Not rst1.BOF
sql = "select * from ActualCosts where chrg_uid = " & rst1!chrg_uid & ";"
Set rst2 = dbs.OpenRecordset(sql)
If rst2.EOF And rst2.BOF Then
MsgBox ("Orphan - " & rst1!chrg_uid)
Else
rst2.Edit
rst2!cost_type = "T"
rst2.Update
End If
rst1.MoveNext
Wend

BUT ...

I think your logic should be applied to the form. Before the
user hits the SAVE button, or on a BeforeUpdate trigger
you can check for blanks, improper values etc. This way the
user has the data at hand and problems can be dealt with more
easily.

hth,
Wayne

 

[PaddyIrishMan]

Hi Wayne,
this is great stuff & I'll start looking into this now.
Just on the other point about the logic being applied to the form - the form is a subform displaying continuous forms. This is why I feel the need to loop thru the records in the background as when I try to validate at the front end:
i.e. if myfield.text = "" then msgbox "Error" this only applies to the current record & not to all of the records in the selection.

I could be completely off the mark with my methodology here so any suggestions would be very welcome.

Thanks again,
Patrick.