Hi All,

I have a form(A) and subform(B).
Form B is a Datasheet form containing records. One of the fields is a
unique number and
consists of the year followed by a category number. For example:
2007-45

Now my database holds records from 2001 until now.
I would like to have a popup form with the years 2001 to 2010.
And when I click on a year the popup form dissapears and formB jumps
to the first record
of that year. This record should be projected at the top of the form.

Does anyone know how to do this or has a sample for me?

Regards
Marco

Co wrote:
In your main form I might have a dropdown. This is a sample row source.
SELECT DISTINCT CStr(Year(YourDateFieldName))AS Year, _
2 AS SortVar FROM YourTableName
UNION
SELECT "All" As Year, 1 As SortVar From YourTableName
Order By 2, 1 Desc

I selected unique years from the table so only valid years are
selected/presented. Since the first sort is on SortVar the word "ALL"
floats to the top and I ordered 1 Desc so that the most current year
floats to the second row, oldest year to the last row.

I might use a filter and display only records for a particular year in
the subform. In the AfterUpdate event I might use
Dim strF As String
If Me.ComboYear <> "ALL" then
strF = "Year(YourDateFieldName) = " & Me.ComboYear
Endif
Me.SubFormName.Form.Filter = strF 'set the filter
Me.SubFormName.Form.Filter = (strF <> "") 'turn filter on/off


Me.YourSubFormName.Form.Filter = _

On 6 okt, 19:47, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
I Put following code in the subform.

Public Function FindNaam(vName As Variant) As Boolean

Dim sCriteria As String
Dim sName As String
Dim rRs As DAO.Recordset
Dim iPos As Integer
On Error GoTo HandleErr

' Init
FindName = True
' Move to First record if Null
If IsNull(vName) Then
Me.Recordset.MoveFirst
Exit Function
End If
' Parse Name
sName = CStr(Nz(vName, ""))
sName = Replace(sName, "'", "''")
If Right(sName, 1) <> "*" Then
sName = sName & "*"
End If
' Criteria - use Like comparison
sCriteria = "[mynumber] Like '" & sName & "'"

' Search Clone
Set rRs = Me.RecordsetClone
With rRs
..FindFirst sCriteria
iPos = .AbsolutePosition
If .NoMatch Then
FindName = False
Beep
Else
FindName = True
mynumber.SetFocus
Me.SelTop = iPos + 1
Me.Recordset.Bookmark = rRs.Bookmark
' Force Scroll to selected Record
' Control must be able to recieve focus


End If
End With

Exit Function

HandleErr:
Err.Raise Number:=Err.Number, _
Description:=Err.Description, _
Source:="Form_MyForm.FindName" & vbCr & Err.Source
Exit Function

End Function

This code finds the first record from a certain year.
The problem is that I want to have this record at the top of the
subform.

I think it can be done with SelTop and .AbsolutePosition but I'm not
sure how.

MArco

Co wrote:

Don't know. Seems silly to me. I suppose you go could reset the
subform to go to the last record, bookmark it, then search for the
record you want and bookmark it. It wouldn't help with a list of
records with multiple years that can fit in the subform's view, only for
long lists.

Co wrote:

Once you have navigated to the desired record, I think you
can use this to position the record in the form
http://www.lebans.com/setgetsb.htm

--
Marsh