Open Form To Specific Record From A Report?

[shoelesscraig]

I have a report that has several columns, one of them being the column "ID". I would like (if it's possible) to add a column to this report with a button in it...so I understand EVERY row would have this button...where when I clicked the button, it would open up a report (call it ReportA) and go to the record in that form where the ID's match each other. So if I am looking at my report and I go to the row that has ID = 3, it would open ReportA and go to the record where ID = 3 in that form. (The ID's in that form are also displayed and visible when you view the form).



Is this doable?

[ranman256]

You don't need a button on every row, a single button in the form header will do the same thing without all the clutter.
the click event will open the record the cursor is on...

Sub btnOpen_click
   Docmd.openform "frmEdit",,,"[id]=" & txtID
end sub

the txtID is the name of the textbox on the screen

[shoelesscraig]

You don't need a button on every row, a single button in the form header will do the same thing without all the clutter.
the click event will open the record the cursor is on...

Sub btnOpen_click
   Docmd.openform "frmEdit",,,"[id]=" & txtID
end sub

the txtID is the name of the textbox on the screen

That is excellent and works great. I made this work for a button on each row (I think it may be a little cleaner for my particular situation).

If you don't mind, I'd like help with the next related issue. Once my form opens (and opens to the correct record) I can see what I want to. However, now the navigation buttons for my form (next, previous, first, last) no longer work. I have to close the form and reopen to make them work again. Not really a huge issue, but I was wondering if maybe there was a way to perhaps clear the restriction on the viewing when I clicked one of the navigation buttons? Maybe like an "OnClick" event or something? That way I wouldn't have to close the form and open again. Hopefully that makes sense...?

[shoelesscraig]

Update on this...I managed to figure out how to clear the filter by adding a button with the following code:

If FilterOn = True Then
DoCmd.RunCommand acCmdRemoveAllFilters
Else: End If

Works good. Now, if I'm being really picky....if I am on record #150 (because I got there by pressing the button in the report (from the above posts) and I press the "Clear Filter" button, it does clear the filter so I can browse records again, but takes me back to record #1. Obviously this is a long way from where I was. Any way to make it clear the filters but stay on the record its currently on and let me start browsing from there?

[MatthewGrace]

I think the sort of solution your looking for involves the FindFirst method. I'm not that good at Access that I can clearly articulate exactly where to put this but I think you could say something like:

btnOpen_click
     Docmd.openform "frmEdit" 'Skip the filter argument and do a search instead with the next line?
     Forms!frmEdit.Recordset.FindFirst "[id]=" & txtID 'I'm not sure if this exactly the code, but could hopefully be a starting place.
end sub

No filter necessary.

To comment on the aforementioned button issue, perhaps you could forego a button altogether and place the above code in an event handler for the ID column of your report (textbox, right?) ... something like the "Sub txtID_DoubleClick" of the textbox. If it works, and you want to make it obvious to the users eye that clicking the ID# directly will navigate to the record on frmEdit, you could try adjusting the textbox forecolor to blue and putting an Underline so it resembles a common hyperlink. Everyone knows that look indicates clicking on it will take you somewhere.
Hope it helps!!! good luck
matt

[breakingme10]

I would suggest the same as Matthew Grace.

In the reports design view,
select your id column
set the property for IsHyperlink to "Screen Only" (this will make the id look like a hyperlink and then be more obvious that it is indeed clickable)
you can even set the control source to this to make it show "Open" instead of the ID number. Unless you want the id number to show that is.

=IIf(IsNull([ID]),"(New)","Open")

then on the ID columns "OnClick" event you can either use a macro or VBA to open the form to the correct record.
I choose vba.

This is actual code from a report in one of my databases. my "ID" column is named "txtOpen" this is it's onclick event.

Private Sub txtOpen_Click()
On Error GoTo txtOpen_Click_Err
Dim CurrentID As Integer

    On Error Resume Next
    If (MacroError.Number <> 0) Then
        Beep
        MsgBox MacroError.Description, vbQuestion, "Error"
        Exit Sub
    End If
    On Error GoTo 0
    DoCmd.OpenForm "EmployeeDetails", acNormal, "", "[ID]=" & Nz(ID, 0), , acWindowNormal
    If (Not IsNull(ID)) Then
        CurrentID = ID
    End If
    If (IsNull(ID)) Then
        CurrentID = Nz(DMax("[ID]", Report.RecordSource), 0)
    End If
    DoCmd.Requery ""
    DoCmd.SearchForRecord , , acFirst, "[ID]=" & CurrentID


txtOpen_Click_Exit:
    Exit Sub

txtOpen_Click_Err:
    MsgBox Error$
    Resume txtOpen_Click_Exit

End Sub

Simplified i believe that you can just use this to find the record you are wanting, however i have not tested it without the error trapping and conditionals.
DoCmd.OpenForm "EmployeeDetails", acNormal, "", "[ID]=" & Nz(ID, 0), , acDialog
DoCmd.SearchForRecord , "", acFirst, "[ID]=" & Nz(ID, 0)

[Micron]

Or take a look at Form.Bookmark property as another way.
https://msdn.microsoft.com/en-us/lib.../ff835682.aspx