How to move to specific record in Datasheet?

I have a subform datasheet that contains a full year of records sorted by a
date field. I'm trying to programmatically move the record selector on the
datasheet to the first record that matches a particular date. For example,
the user clicks a button and the record selector moves to the first record
that matches today's date.

I was thinking something like this, but I'm not having any luck:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT ApptDate FROM tblAppointments
WHERE ApptDate = " & Date())
rst.MoveFirst
Me.Bookmark = rst.Bookmark
rst.Close

Is it possible to use DoCmd.GoToRecord ? -- is it even possible to move
only the record selector in a datasheet?

thanks in advance...

re: How to move to specific record in Datasheet?

For the bookmarks to be the same between the recordsets, make a
RecordsetClone of the form's recordset. Also, where is the button located,
on the main form or the subform? Yes, DoCmd.GoToRecord is also a
possibility.

Dim rst As DAO.Recordset
'Here is where we need to know the location of the button
'I have assumed it is on the main form
Set rst = Me.NameOfSubformControl.Form.RecordsetClone
rst.FindFirst "[ApptDate]=" & Date
If rst.NoMatch Then
Msgbox "There were no records found for " & Date
Else
Me.NameOfSubformControl.Form.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing

--
Wayne Morgan
MS Access MVP


"deko" <dje422@hotmail.com> wrote in message
news:QXdBb.35478$TI4.29113@newssvr29.news.prodigy. com...[color=blue]
> I have a subform datasheet that contains a full year of records sorted by[/color]
a[color=blue]
> date field. I'm trying to programmatically move the record selector on[/color]
the[color=blue]
> datasheet to the first record that matches a particular date. For[/color]
example,[color=blue]
> the user clicks a button and the record selector moves to the first record
> that matches today's date.
>
> I was thinking something like this, but I'm not having any luck:
>
> Dim rst As DAO.Recordset
> Set rst = CurrentDb.OpenRecordset("SELECT ApptDate FROM[/color]
tblAppointments[color=blue]
> WHERE ApptDate = " & Date())
> rst.MoveFirst
> Me.Bookmark = rst.Bookmark
> rst.Close
>
> Is it possible to use DoCmd.GoToRecord ? -- is it even possible to move
> only the record selector in a datasheet?
>
> thanks in advance...
>
>[/color]

re: How to move to specific record in Datasheet?

thanks for pointing me in the right direction - your comments are helpful
two things I've discovered:

* need to use long with FindFirst
* need to use DAO RecordsetClone with Bookmark

The problem I'm still having is how to hide the Appt_ID column in the
datasheet -- see second to last line of sub.

Private Sub Form_Open(Cancel As Integer)

'order by datasheet by date
Me.OrderBy = "[ApptDate]"
Me.OrderByOn = True

'look for an appointment in current week
'so we don't have to scroll through
'previous months of appointments
'if we can't find one,
'expand range to current month
Dim lngAidw As Long
Dim lngAidm As Long
lngAidw = Nz(DLookup("Appt_ID", "qry920"), 0)
lngAidm = Nz(DLookup("Appt_ID", "qry930"), 0)
MsgBox lngAidw
MsgBox lngAidm
Dim rst As DAO.Recordset
Set rst = Me.Form.RecordsetClone
rst.FindFirst "Appt_ID = " & lngAidw
If rst.NoMatch Then
rst.FindFirst "Appt_ID = " & lngAidm
'if still no match, go back to start
If rst.NoMatch Then
rst.MoveFirst
End If
End If
Me.Form.Bookmark = rst.Bookmark
rst.Close

'set column widths to best fit
Dim ctl As Control
Dim strCl As String
For Each ctl In Me.Form
strCl = ctl.Name
Form(strCl).ColumnWidth = -2
Next ctl
'Form(ctl.Appt_ID).ColumnHidden '<=== How to hide??
DoCmd.Save acForm, "frmAllAppts"

End Sub

re: How to move to specific record in Datasheet?

> 'Form(ctl.Appt_ID).ColumnHidden '<=== How to hide?
Me.Appt_ID.ColumnHidden = True

Also, the "form" in the command line was to refer to the subform from the
subform control if the button was on the main form. It appears that you are
running the code from the subform so you don't need the word form (although,
since you say it works, it doesn't appear to be hurting anything).

Example:
Me.Form.Bookmark = rst.Bookmark
could be changed to
Me.Bookmark = rst.Bookmark

--
Wayne Morgan
Microsoft Access MVP


"deko" <dje422@hotmail.com> wrote in message
news:trkBb.35555$tv4.3854@newssvr29.news.prodigy.c om...[color=blue]
> thanks for pointing me in the right direction - your comments are helpful
> two things I've discovered:
>
> * need to use long with FindFirst
> * need to use DAO RecordsetClone with Bookmark
>
> The problem I'm still having is how to hide the Appt_ID column in the
> datasheet -- see second to last line of sub.
>
> Private Sub Form_Open(Cancel As Integer)
>
> 'order by datasheet by date
> Me.OrderBy = "[ApptDate]"
> Me.OrderByOn = True
>
> 'look for an appointment in current week
> 'so we don't have to scroll through
> 'previous months of appointments
> 'if we can't find one,
> 'expand range to current month
> Dim lngAidw As Long
> Dim lngAidm As Long
> lngAidw = Nz(DLookup("Appt_ID", "qry920"), 0)
> lngAidm = Nz(DLookup("Appt_ID", "qry930"), 0)
> MsgBox lngAidw
> MsgBox lngAidm
> Dim rst As DAO.Recordset
> Set rst = Me.Form.RecordsetClone
> rst.FindFirst "Appt_ID = " & lngAidw
> If rst.NoMatch Then
> rst.FindFirst "Appt_ID = " & lngAidm
> 'if still no match, go back to start
> If rst.NoMatch Then
> rst.MoveFirst
> End If
> End If
> Me.Form.Bookmark = rst.Bookmark
> rst.Close
>
> 'set column widths to best fit
> Dim ctl As Control
> Dim strCl As String
> For Each ctl In Me.Form
> strCl = ctl.Name
> Form(strCl).ColumnWidth = -2
> Next ctl
> 'Form(ctl.Appt_ID).ColumnHidden '<=== How to hide??
> DoCmd.Save acForm, "frmAllAppts"
>
> End Sub
>
>[/color]

re: How to move to specific record in Datasheet?

Okay...

Dim lngAid As Long
lngAid = Nz(DLookup("Appt_ID", "qry990"), 0) 'next appointment
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "[Appt_ID] = " & lngAid
If rstNoMatch Then
rst.Close
Exit Sub
End If
Me.Form.Bookmark = rst.Bookmark
rst.Close

Question:

There will always be only one "next appointment" -- must I use FindFirst and
NoMatch? Is there a more efficient way to say "go here or quit"?

BTW, I tried this one-liner:

DoCmd.GoToRecord , , acGoTo, [Appt_ID] = Nz(DLookup("Appt_ID", "qry990"),
First)

but GoToRecord just uses the "offset" of the current record set, which is
not much use if you don't know how many records you are going to have.

"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:8GkBb.35389$SZ5.3370@newssvr32.news.prodigy.c om...[color=blue][color=green]
> > 'Form(ctl.Appt_ID).ColumnHidden '<=== How to hide?[/color]
> Me.Appt_ID.ColumnHidden = True
>
> Also, the "form" in the command line was to refer to the subform from the
> subform control if the button was on the main form. It appears that you[/color]
are[color=blue]
> running the code from the subform so you don't need the word form[/color]
(although,[color=blue]
> since you say it works, it doesn't appear to be hurting anything).
>
> Example:
> Me.Form.Bookmark = rst.Bookmark
> could be changed to
> Me.Bookmark = rst.Bookmark
>
> --
> Wayne Morgan
> Microsoft Access MVP
>
>
> "deko" <dje422@hotmail.com> wrote in message
> news:trkBb.35555$tv4.3854@newssvr29.news.prodigy.c om...[color=green]
> > thanks for pointing me in the right direction - your comments are[/color][/color]
helpful[color=blue][color=green]
> > two things I've discovered:
> >
> > * need to use long with FindFirst
> > * need to use DAO RecordsetClone with Bookmark
> >
> > The problem I'm still having is how to hide the Appt_ID column in the
> > datasheet -- see second to last line of sub.
> >
> > Private Sub Form_Open(Cancel As Integer)
> >
> > 'order by datasheet by date
> > Me.OrderBy = "[ApptDate]"
> > Me.OrderByOn = True
> >
> > 'look for an appointment in current week
> > 'so we don't have to scroll through
> > 'previous months of appointments
> > 'if we can't find one,
> > 'expand range to current month
> > Dim lngAidw As Long
> > Dim lngAidm As Long
> > lngAidw = Nz(DLookup("Appt_ID", "qry920"), 0)
> > lngAidm = Nz(DLookup("Appt_ID", "qry930"), 0)
> > MsgBox lngAidw
> > MsgBox lngAidm
> > Dim rst As DAO.Recordset
> > Set rst = Me.Form.RecordsetClone
> > rst.FindFirst "Appt_ID = " & lngAidw
> > If rst.NoMatch Then
> > rst.FindFirst "Appt_ID = " & lngAidm
> > 'if still no match, go back to start
> > If rst.NoMatch Then
> > rst.MoveFirst
> > End If
> > End If
> > Me.Form.Bookmark = rst.Bookmark
> > rst.Close
> >
> > 'set column widths to best fit
> > Dim ctl As Control
> > Dim strCl As String
> > For Each ctl In Me.Form
> > strCl = ctl.Name
> > Form(strCl).ColumnWidth = -2
> > Next ctl
> > 'Form(ctl.Appt_ID).ColumnHidden '<=== How to hide??
> > DoCmd.Save acForm, "frmAllAppts"
> >
> > End Sub
> >
> >[/color]
>
>[/color]

re: How to move to specific record in Datasheet?

> Question:[color=blue]
>
> There will always be only one "next appointment" -- must I use FindFirst[/color]
and[color=blue]
> NoMatch? Is there a more efficient way to say "go here or quit"?
>[/color]
Not really.

In addition to the rst.Close, technically you should also Set rst=Nothing.
The general rule is that if you open it, close it. If you set it, then set
it to nothing when you're done.

--
Wayne Morgan
Microsoft Access MVP


"deko" <dje422@hotmail.com> wrote in message
news:DVwBb.36125$Y23.15205@newssvr29.news.prodigy. com...[color=blue]
> Okay...
>
> Dim lngAid As Long
> lngAid = Nz(DLookup("Appt_ID", "qry990"), 0) 'next appointment
> Dim rst As DAO.Recordset
> Set rst = Me.RecordsetClone
> rst.FindFirst "[Appt_ID] = " & lngAid
> If rstNoMatch Then
> rst.Close
> Exit Sub
> End If
> Me.Form.Bookmark = rst.Bookmark
> rst.Close
>
> Question:
>
> There will always be only one "next appointment" -- must I use FindFirst[/color]
and[color=blue]
> NoMatch? Is there a more efficient way to say "go here or quit"?
>
> BTW, I tried this one-liner:
>
> DoCmd.GoToRecord , , acGoTo, [Appt_ID] = Nz(DLookup("Appt_ID", "qry990"),
> First)
>
> but GoToRecord just uses the "offset" of the current record set, which is
> not much use if you don't know how many records you are going to have.[/color]