Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2011
    Posts
    5

    Unanswered: How to click on a field in a report and go to record in form view

    I'm still relatively new to creating Access databases, but have been able to construct a database with simple forms, queries, and reports without much trouble, until now.

    I want to be able to click on a field (Person_ID) displayed in a report and go directly to the corresponding person's data displayed in a form I've already designed (Working Query Form), after launching the appropriate form. (Person_ID is the database's primary key.)

    Any ideas for how to do this?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    put some code or a macro behind what ever control that opens the form / report / whatever
    persoanlly I wouldn't want to click on a textbox control, rather a command button. if you choose to uise a command button then the build event wizard should help you complete this task
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2011
    Posts
    5

    Okay, but....

    I'm able to create a command button but I run into the problem of not knowing which macro command I need for going directly to the corresponding record in form view. Would it be GoToRecord, FindRecord, or SearchForRecord. None of these seem to fit exacly what I want to do.

    More importantly, I'm afraid I wouldn't know how to set the condition in the macro to have it go to the appropriate record. What parameters would I need to set to create the condition tio going to a particular record?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by steelheadblj View Post
    I want to be able to click on a field (Person_ID) displayed in a report and go directly to the corresponding person's data displayed in a form
    This cannot work in a Report: Command Buttons are only "activable" in Forms.

    As for going to a record in a form, here's one solution (from the Form Module):
    Code:
    Sub MoveToRecord(ByVal SomeValue As Long)
    
        Dim rst As DAO.Recordset
        Dim strCriteria As String
    
        strCriteria = "Person_ID = " & SomeValue
        Set rst = Me.RecordsetClone
        rst.FindFirst strCriteria 
        If rst.NoMatch = True then
            MsgBox "Not found"
        Else
            Me.Bookmark = rst.Bookmark
        End If
        rst.Close
        Set rst = Nothing
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Oct 2011
    Posts
    5

    What if....

    Thanks for your responses. I think I haven't explained myself well, though.

    When I say I want to be able to go to a specific record, the record I[m thinking of is dictated by the field I click on in the report. Here's an example.

    Let's say I run a report that returns everyone in my database whose last name starts with "D." Each entry in the report represents a person with a unique ID number, the primary key assigned by Access. I'd like to be able to click on the unique ID number in the report and be taken to a form I've already constructed that displays that same person's record.

    So, let's say I click on ID number 302, corresponding to John Doe. I would want Access to open my already-created form and go to John Doe's record (ID number 302) in that form.

    Can it be done? If so, how? I can create a macro that opens up the form. That's not the problem. What's tough is finding a way to get Access to go to the person's record once the form has been opened.

    Thanks in advance for your help.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You explained yourself quite enough and the answer won't vary: would it be a command button or a textbox, controls on a report do not have associated events, i.e. you cannot click on one control and expect an "On_Click" event to be triggered. Or if you prefer, controls on a report are not "clickable", you cannot associate VBA procedures to them, nor for finding a record in a form, nor for any other purpose.
    Have a nice day!

  7. #7
    Join Date
    Oct 2011
    Posts
    5
    Thanks Sinndho. The thing is, I was able to create an On Click event in a report textbox. So right now, when you click on the textbox associated with the ID number I referenced earlier, it brings up my form. I swear on a stack of Bibles that it works.

    All I'm faulting is the ability to move to the record in my form that correponds to the person whose ID number I clicked on in the report.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Are we both speaking of the same thing, namely a Report object in an Access database? If yes, what version of Access do you use? As far as I now, when you open a Report in Design view and select a control in it, there is no event available for it in the Properties window.

    As for opening a Form for a specific record, that's not difficult:
    Code:
    Sub OpenFormForRecord(RowID As Long)
    
        DoCmd.OpenForm "Frm_CF_DATA", , , "Row_ID=" & RowID
        
    End Sub
    Where Frm_CF_DATA is the name of the form and Row_ID is the name of the Identity column (usually the primary key) for the rowset. If Row_ID is not numeric, use:
    Code:
    Sub OpenFormForRecord(RowID As String)
    
        DoCmd.OpenForm "Frm_CF_DATA", , , "Row_ID='" & RowID & "'"
        
    End Sub
    Have a nice day!

  9. #9
    Join Date
    Oct 2011
    Posts
    5

    Access 2010

    I'm using Access 2010. Let me try the code you suggest.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I did not know this was possible in Access 2010 (I never tried to tell the truth), it was not in Access 2003 (see attachment).

    The problem now is to determine the value for the row that was clicked. I had a quick look on the feature but the value returned did not seem to make sense. Maybe someone more used to Acc. 2010 will be able to help you better.
    Attached Thumbnails Attached Thumbnails Report001.jpg  
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •