Button to go to a specific record in a new form

[Steven Van Impe]

Hi all,

I'm trying to create a button in Access97 that opens up a new form on a
specific record, but allows the user to browse the new form for other
records. Starting from the Form Actions wizard I got to the code below:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Recordings"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.ShowAllRecords
DoCmd.Close acForm, "Works"

But this opens the new form with a filter, only showing the associated
record where I pressed the button. I want it to show all the records, but
focus on the record I started with. I've been trying to work with
DoCmd.ShowAllRecords but it doesn't seem to work.

Specifically, the first form is a set of musical compositions (classical),
with a subform showing the associated recordings of that work. In the
subform I have a button "Go To" next to each record that switches to the
detailed view of that recording. But I want the user to be able to browse
all the recordings when he gets to the new form.

Thanks,

Steven

 

[Tony C]

For the form that is being opened, ensure that there is no
Criteria set on the Data source, e.g. ensure that the
Query is not limited according to a specific Record Number.

Create a "Public" Variable (Publice SearchStr As String).
When you click the "Open Form" Form Button run this Code:
SearchStr = Me.RecordID (SETS THE SEARCH STRING).
DoCmd.OpenForm..... (OPENS THE FORM).

When the Form is opened, perform a Search: -

Forms![SubFormID]![RecordNo].SetFocus (FOCUSES ON FIELD TO
BE SEARCHED)
DoCmd.FindRecord SearchStr, acEntire, , acSearchAll, ,
acCurrent, True (FINDS THE RECORD)

HTH


Tony C

 

[Steven Van Impe]

Thank you very much Tony ! It works like a charm


Regards,
Steven

For the form that is being opened, ensure that there is no
Criteria set on the Data source, e.g. ensure that the
Query is not limited according to a specific Record Number.

Create a "Public" Variable (Publice SearchStr As String).
When you click the "Open Form" Form Button run this Code:
SearchStr = Me.RecordID (SETS THE SEARCH STRING).
DoCmd.OpenForm..... (OPENS THE FORM).

When the Form is opened, perform a Search: -

Forms![SubFormID]![RecordNo].SetFocus (FOCUSES ON FIELD TO
BE SEARCHED)
DoCmd.FindRecord SearchStr, acEntire, , acSearchAll, ,
acCurrent, True (FINDS THE RECORD)

HTH


Tony C

-----Original Message-----
Hi all,

I'm trying to create a button in Access97 that opens up a new form on a
specific record, but allows the user to browse the new form for other
records. Starting from the Form Actions wizard I got to the code below:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Recordings"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.ShowAllRecords
DoCmd.Close acForm, "Works"

But this opens the new form with a filter, only showing the associated
record where I pressed the button. I want it to show all the records, but
focus on the record I started with. I've been trying to work with
DoCmd.ShowAllRecords but it doesn't seem to work.

Specifically, the first form is a set of musical compositions (classical),
with a subform showing the associated recordings of that work. In the
subform I have a button "Go To" next to each record that switches to the
detailed view of that recording. But I want the user to be able to browse
all the recordings when he gets to the new form.

Thanks,

Steven


.