Open form to a specific record

[Al]

I would like to open form to a specific record and have
all records available to move through. I know I can use
the following wizard code:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFOLLOWUP"

stLinkCriteria = "[DATE]=" & "#" & Me![DATE] & "#"
DoCmd.openform stDocName, , , stLinkCriteria

However, when the form opens, only the selected record is
available when you try to move next or previous. Is there
an easy way to open a form to a specific record and still
have all records available?

TIA,
Al

 

[Cameron Sutherland]

Let's say you want to open a form called Form1 and go to
the record where the Field1 ='camisccol'. In whatever
event you currently have to open the form add the two
lines that will tell that form to find the record
(FindFirst) and display it (Bookmark):

DoCmd.OpenForm "Form1"
[Forms]![Form1].RecordsetClone.FindFirst "[Field1]
= 'camiscool'"
[Forms]![Form1].Bookmark = [Forms]!
[Form1].RecordsetClone.Bookmark

-Cameron Sutherland

 

[Marshall Barton]

I would like to open form to a specific record and have
all records available to move through. I know I can use
the following wizard code:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFOLLOWUP"

stLinkCriteria = "[DATE]=" & "#" & Me![DATE] & "#"
DoCmd.openform stDocName, , , stLinkCriteria

However, when the form opens, only the selected record is
available when you try to move next or previous. Is there
an easy way to open a form to a specific record and still
have all records available?

Use the OpenForm's OpenArgs to pass the search string to the
form along with the usual find approach.

DoCmd.openform stDocName, _
OpenArgs:= Format(Me![DATE], "\#m\/d\/yyyy\#")

then, in the form's Load event:

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst "[DATE]=" & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

 

[Al]

Thanks Cameron,
I think your suggestion will work better in my situation
but I can't get the form to open linked by a date field.
I'm sure it has to do with the syntax, here is the line
that has the problem:

[Forms]![F - CHECKLIST].RecordsetClone.FindFirst "[Date]"
= " " & "#" & LDate & "#"

Any suggestions?
TIA,
Al

-----Original Message-----
Let's say you want to open a form called Form1 and go to
the record where the Field1 ='camisccol'. In whatever
event you currently have to open the form add the two
lines that will tell that form to find the record
(FindFirst) and display it (Bookmark):

DoCmd.OpenForm "Form1"
[Forms]![Form1].RecordsetClone.FindFirst "[Field1]
= 'camiscool'"
[Forms]![Form1].Bookmark = [Forms]!
[Form1].RecordsetClone.Bookmark

-Cameron Sutherland

-----Original Message-----
I would like to open form to a specific record and have
all records available to move through. I know I can use
the following wizard code:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFOLLOWUP"

stLinkCriteria = "[DATE]=" & "#" & Me![DATE] & "#"
DoCmd.openform stDocName, , , stLinkCriteria

However, when the form opens, only the selected record is
available when you try to move next or previous. Is there
an easy way to open a form to a specific record and still
have all records available?

TIA,
Al

.

.

 

[Rick Brandt]

Thanks Cameron,
I think your suggestion will work better in my situation
but I can't get the form to open linked by a date field.
I'm sure it has to do with the syntax, here is the line
that has the problem:

[Forms]![F - CHECKLIST].RecordsetClone.FindFirst "[Date]"
= " " & "#" & LDate & "#"

[Forms]![F - CHECKLIST].RecordsetClone.FindFirst "[Date] = #" & LDate & "#"