Open specific record from report (1 Viewer)

fosterg100

New member
Local time
Today, 03:38
Joined
Nov 29, 2019
Messages
9
Hi
I have a report that all works fine, created from a query. The report is opened from a button on the main form.
What I am after, is that when I double click on the ID in the report, it closes the report and then goes to the record in the form I have just double clicked on.

So, if the report shows a list of projects, when I double click on the project ID in the report, it opens it up in the form and also closing the report.

Can anyone point me in the right direction?

Thanks.
 

vba_php

Forum Troll
Local time
Today, 05:38
Joined
Oct 6, 2019
Messages
2,884
foster,

I must say that I've never, in 12 years of doing this, heard of someone wanting to do that. Why do you want to? Regardless though, if you're forced to do it for reasons you have no control of, maybe something like this would work:
Code:
private sub MyIDtextBox_Dbl_Click()
   dim id as long
   id = me.myIDtextBox

   forms("form name here").filter = "[id field on the form] = " & id
   forms("form name here").filteron = true

   docmd.close acreport, me.name

end sub
that is just "pseudo-code". I typed it into this post without testing it. some of it is probably a little bit off, but you can look it up I'm sure. also note that it applies a *filter*, which only would display one record to you. if you want to just *skip* to the record you want instead, I believe you'll have to use the *docmd.gotorecord* method.
 

fosterg100

New member
Local time
Today, 03:38
Joined
Nov 29, 2019
Messages
9
Hi
Thanks for your reply.
The reason I am looking at doing it, was a short cut to go back to the record to carry out any changes.

I have a list of projects, and I click on the button to open the report. When looking at the report, I would like to click on the project ID to go back to the form to carry out any changes I need to make.

I will give your code a go.

Many thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:38
Joined
May 7, 2009
Messages
19,175
another approach, without filtering the form.
add code to DblClick Event of ProjectID on the Report.
(note that the code will only work on Report View)
Code:
Private Sub ProjectID_DblClick(Cancel As Integer)

    With Forms[COLOR="Red"]("theProjectFormNameHere[/COLOR]").RecordsetClone
	'if project id is Numeric
        .FindFirst "[ProjectID] = " & Me.ProjectID
        
	'if project id is Text
        '.FindFirst "[ProjectID] = '" & Me.ProjectID & "'"

        If Not .NoMatch Then
            Forms("[COLOR="red"]theProjectFormNameHere[/COLOR]").Bookmark = .Bookmark
            
        End If
        
        DoCmd.Close acReport, Me.Name
    
    End With
    
    
End Sub
 

Users who are viewing this thread

Top Bottom