By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,574 Members | 1,199 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,574 IT Pros & Developers. It's quick & easy.

How to move to specific record in Datasheet?

P: n/a
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...
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" <dj****@hotmail.com> wrote in message
news:QX*******************@newssvr29.news.prodigy. com...
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...

Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
> '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" <dj****@hotmail.com> wrote in message
news:tr******************@newssvr29.news.prodigy.c om...
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

Nov 12 '05 #4

P: n/a
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" <co***************************@hotmail.com> wrote in message
news:8G******************@newssvr32.news.prodigy.c om...
'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" <dj****@hotmail.com> wrote in message
news:tr******************@newssvr29.news.prodigy.c om...
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


Nov 12 '05 #5

P: n/a
> 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*******************@newssvr29.news.prodigy. com... 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.

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.