How to move to specific record in Datasheet?

[deko]

I have a subform datasheet that contains a full year of records sorted by a
date field. I'm trying to programmaticall y 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.OpenR ecordset("SELEC T ApptDate FROM tblAppointments
WHERE ApptDate = " & Date())
rst.MoveFirst
Me.Bookmark = rst.Bookmark
rst.Close

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

thanks in advance...

[Wayne Morgan]

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.GoToRecor d 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.NameOfSubfor mControl.Form.R ecordsetClone
rst.FindFirst "[ApptDate]=" & Date
If rst.NoMatch Then
Msgbox "There were no records found for " & Date
Else
Me.NameOfSubfor mControl.Form.B ookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing

--
Wayne Morgan
MS Access MVP
"deko" <dj****@hotmail .com> wrote in message
news:QX******** ***********@new ssvr29.news.pro digy.com...

I have a subform datasheet that contains a full year of records sorted by a date field. I'm trying to programmaticall y 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.OpenR ecordset("SELEC T ApptDate FROM tblAppointments WHERE ApptDate = " & Date())
rst.MoveFirst
Me.Bookmark = rst.Bookmark
rst.Close

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

thanks in advance...

[deko]

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(Cance l 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("App t_ID", "qry920"), 0)
lngAidm = Nz(DLookup("App t_ID", "qry930"), 0)
MsgBox lngAidw
MsgBox lngAidm
Dim rst As DAO.Recordset
Set rst = Me.Form.Records etClone
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.Bookmar k = 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).Col umnWidth = -2
Next ctl
'Form(ctl.Appt_ ID).ColumnHidde n '<=== How to hide??
DoCmd.Save acForm, "frmAllAppt s"

End Sub

[Wayne Morgan]

> 'Form(ctl.Appt_ ID).ColumnHidde n '<=== How to hide?
Me.Appt_ID.Colu mnHidden = 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.Bookmar k = rst.Bookmark
could be changed to
Me.Bookmark = rst.Bookmark

--
Wayne Morgan
Microsoft Access MVP
"deko" <dj****@hotmail .com> wrote in message
news:tr******** **********@news svr29.news.prod igy.com...

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(Cance l 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("App t_ID", "qry920"), 0)
lngAidm = Nz(DLookup("App t_ID", "qry930"), 0)
MsgBox lngAidw
MsgBox lngAidm
Dim rst As DAO.Recordset
Set rst = Me.Form.Records etClone
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.Bookmar k = 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).Col umnWidth = -2
Next ctl
'Form(ctl.Appt_ ID).ColumnHidde n '<=== How to hide??
DoCmd.Save acForm, "frmAllAppt s"

End Sub

[deko]

Okay...

Dim lngAid As Long
lngAid = Nz(DLookup("App t_ID", "qry990"), 0) 'next appointment
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClo ne
rst.FindFirst "[Appt_ID] = " & lngAid
If rstNoMatch Then
rst.Close
Exit Sub
End If
Me.Form.Bookmar k = 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.GoToRecor d , , acGoTo, [Appt_ID] = Nz(DLookup("App t_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" <co************ *************** @hotmail.com> wrote in message
news:8G******** **********@news svr32.news.prod igy.com...

'Form(ctl.Appt_ ID).ColumnHidde n '<=== How to hide? Me.Appt_ID.Colu mnHidden = 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.Bookmar k = rst.Bookmark
could be changed to
Me.Bookmark = rst.Bookmark

--
Wayne Morgan
Microsoft Access MVP
"deko" <dj****@hotmail .com> wrote in message
news:tr******** **********@news svr29.news.prod igy.com...

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(Cance l 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("App t_ID", "qry920"), 0)
lngAidm = Nz(DLookup("App t_ID", "qry930"), 0)
MsgBox lngAidw
MsgBox lngAidm
Dim rst As DAO.Recordset
Set rst = Me.Form.Records etClone
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.Bookmar k = 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).Col umnWidth = -2
Next ctl
'Form(ctl.Appt_ ID).ColumnHidde n '<=== How to hide??
DoCmd.Save acForm, "frmAllAppt s"

End Sub

[Wayne Morgan]

> 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"?
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" <dj****@hotmail .com> wrote in message
news:DV******** ***********@new ssvr29.news.pro digy.com... Okay...

Dim lngAid As Long
lngAid = Nz(DLookup("App t_ID", "qry990"), 0) 'next appointment
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClo ne
rst.FindFirst "[Appt_ID] = " & lngAid
If rstNoMatch Then
rst.Close
Exit Sub
End If
Me.Form.Bookmar k = 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.GoToRecor d , , acGoTo, [Appt_ID] = Nz(DLookup("App t_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.