Jumping to a particular record in MS Access

I have been working with Microsoft Access for a while but I had run up against a problem that took me a while to solve. I have a form, with two sub-forms side by side, both of which contain similar subforms displayed in continuous form mode. Each subform is a query based around activities on a daily basis, with each record representing a particular date. The reason the subforms are side by side, is to allow the user to compare dates with different filters (the filters are controlled by combo boxes at the head of each subform column.

What I had been trying to do was when a user clicks in one subform, the current record pointer in the other subform moves in tandem. I had some visual basic, that attempted to move to the first record of the subforms recordset and then search forward looking for the particular date in question. I was finding the record, but couldn’t find a way to move the current record two it.

I eventually found the solution, which was to do the searching on the recordSetClone of the form, and then set the forms bookmark to that of the recordSet. The snippet of code below shows how. “subFromDateField” is the field name of the field in the Subforms Data source (a query) which contains the date we want to jump to, and “ctlCalendar” is a calendar control set up by the users click on the other form to the date we are working with. “mySubForm” is the name of the control in the main form which holds the subform.

Dim rs AS DAO.recordset
Dim f AS Form
Dim q AS String

q = "subFormDateField <= #" & Format(ctlCalendar.value,"yyyy-mm-dd") & "#"
Set f = Me.mySubForm.Form
Set rs = f.RecordsetClone
If rs.RecordCount <> 0 Then
    rs.MoveFirst
    rs.FindFirst(q)
    f.Bookmark = rs.Bookmark
End If

Remarkably simple when you know how.

UPDATE: It turns out that you have to be rather careful about date format. I am working in UK date format, but queries like the above assume American! It seems the easiest to convert to ISO date format as there is less ambiguity that way

Author: Alan

I am Alan Chandler.

Leave a Reply

Your email address will not be published. Required fields are marked *