access 2007 a loop to loop through a particular textbox in one form

Question

my problem is as follows I have a properties form which contains a textfield called DateTo(this textfield contains the data type Date) I would like to loop through the DateTo textfield on each of the properties and prints this out in a message box as wasll as a tenantName textfield and a tenantAddress1 field. After this is complete i would like to display in the message box only the properties that meets a particular condition. A month before the Date in the DateTo textbox I would like these values to be printed out in a message box.

any help would be greately appreciated

Answer 1

Something like this should give you a framework to work from.

Dim db As DAO.Database
Dim rs1 As DAO.Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("SELECT DateTo, TenantName, TenantAddress1 FROM " & Me.RecordSource)

If rs1.RecordCount > 0 Then
    rs1.MoveFirst
    Do Until rs.EOF
        MsgBox rs.Fields("DateTo") & ", " & rs.Fields("TenantName") & ", " & rs.Fields("TenantAddress1")
        rs1.MoveNext
    Loop
End If
rs1.Close
set rs1 = Nothing
set db = Nothing

You'll need to change the set rs1 = db... line to include whatever criteria you want to add.

Something like:

set rs1 = db.OpenRecordset("SELECT DateTo, TenantName, TenantAddress1 FROM " & Me.RecordSource & " WHERE DateTo >= #1/1/2010#")

Or whatever criteria you are looking for.