Results 1 to 8 of 8
  1. #1
    stacynh is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Michigan
    Posts
    8

    Go Directly to a Form Record from a Report

    Hello,



    I have a Report that lists a Contact Name field (called "Manager/Supervisor1"). When a user clicks on the "Manager/Supervisor1" field, I want to go directly to that record in the Contact Form.

    I have done this before on other fields using the On Click macro with both "OpenForm" and "SetTempVar" and then "Open Form." However, when I try those methods with this field, I receive a comma error because the Contact Name field is in the format "Last Name, First Name."

    It is not possible to remove the comma from the Contact Name field at this point in the database. Nor is it possible to change the name of the field.

    So, I have tried using a VBA DoCmd. The following code opens the Contact Form like I want, but it does not go to the specific record. Any help would be appreciated!



    Dim sWhere As String


    sWhere = "[Manager/Supervisor1] = '" & Me.Manager_Supervisor1 & "'"
    DoCmd.OpenForm "frmContacts", acNormal, sWhere


    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You have it in the wrong position. Try

    DoCmd.OpenForm "frmContacts", acNormal, , sWhere
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    stacynh is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Michigan
    Posts
    8
    Thanks so much for the quick reply!

    Unfortunately, that didn't quite fix the problem. Now, a "Enter Parameter Value" box appears with the field name. If I manually enter the contact name, it still only takes me to the Contact Form, and not to the correct record.

    I appreciate the help though!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The parameter prompt is Access telling you it can't find something (whatever is in the prompt). Make sure the field name is spelled correctly, and is in the record source of the form being opened.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    stacynh is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Michigan
    Posts
    8
    Yes, that person is in the Contacts Table and Form in the exact same spelling. The database is set up so that the name was originally pulled from the Contacts Table to appear on the Report.

    Here's a little more (simplified) info: This database has tables: Rooms, People, and Assignments. The Assignments Table pulls from the first two tables and allows for up to six People to be assigned to each Room. The Report outputs all the people that were assigned to each room. I want my users to be able to get back to a person's contact info just by clicking on the person's name in the Report.

    I have done this before, but Access is having trouble because the Name field has a comma in it. Any suggested work-arounds are appreciated!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    A comma in the person's name shouldn't matter (an apostrophe would, but could be handled). Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    stacynh is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Michigan
    Posts
    8

    Solved it!

    Solved it! ...And feeling a little silly. While the name of the person appeared exactly the same in the Form as the Report, the field names were different. Since my macro was not calling the correct field name in the Form, it could not find the person I wanted it to.

    I wish the error message had been a little more helpful as to what the actual problem was though...

    Anyway, thank you very much for your time and help on this issue!

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-24-2016, 06:59 AM
  2. Replies: 11
    Last Post: 01-12-2015, 01:30 PM
  3. Linking directly to an access form from an email
    By stardust1979 in forum Programming
    Replies: 2
    Last Post: 10-01-2014, 05:00 PM
  4. Export report directly to Word format (.doc)
    By rumenrs in forum Import/Export Data
    Replies: 2
    Last Post: 06-12-2013, 12:34 AM
  5. Report only returns 1 record unless run directly
    By teresamichele in forum Reports
    Replies: 3
    Last Post: 12-17-2010, 12:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums