Results 1 to 5 of 5
  1. #1
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    179

    Report Drill Down - Click On Report Result, Open Form and Populate Field

    Similar request to this thread: https://www.accessforums.net/reports...pen-45598.html

    Report Name: rDashboard
    Form Name: fUpdateProjectStatus
    Primary Table: tProject



    I would like to click on the Project Name in the report, open the form, and load the related project information in the update form.

    I already have some automation rules, so that After Update, the other fields run a DLookup command to auto-update from information in the table tProject.

    Code:
    Private Sub cmCustomer_AfterUpdate()
    Me.cmProjectName = DLookup("ProjectName", "tProject", "CustomerID= " & "cmCustomer")
    Me.cmProjectPhase = DLookup("ProjectPhase", "tProject", "ProjectName= " & "cmProjectName")
    Me.cmProjectCompletionRisk = DLookup("ProjectCompletionRisk", "tProject", "ProjectName= " & "cmProjectName")
    Me.txProjectNotes = DLookup("ProjectNotes", "tProject", "ProjectName= " & "cmProjectName")
    End Sub
    In the report, in an OnClick command, I have this line:

    Code:
    DoCmd.OpenForm "fUpdateProjectStatus", , , "cmCustomer = '" & Me.CustomerID & "'"
    I was hoping this line would open the form, enter the desired value into cmCustomer, and start the cmCustomer_AfterUpdate command. However, no value is entered into cmCustomer. The form opens, but nothing else happens.

    Any help?

    Click image for larger version. 

Name:	rDashboard.png 
Views:	11 
Size:	293.0 KB 
ID:	22972

    Click image for larger version. 

Name:	fUpdateProjectStatus.png 
Views:	11 
Size:	243.2 KB 
ID:	22973

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,510
    I am confused. If the form is opened filtered to a specific CustomerID, why would you need to populate this value into field? It should already be there.

    Is cmCustomer a field name or combobox name? The OpenForm WHERE ARGUMENT should reference a field in the criteria, not a combobox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    179
    The form is unbound. Does this type of command require a form bound to a record source? DoCmd.OpenForm "fUpdateProjectStatus", , , "cmCustomer = '" & Me.CustomerID & "'"

    cmCustomer name is a combobox. I'm guessing I can't enter a value into the combo box from a report field?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,510
    Using the WHERE CONDITION argument requires a bound form, otherwise, what should the filter criteria apply to?

    Code can pass the CustomerID by using the OpenForm OpenArgs argument and then set the value of the unbound combobox.

    DoCmd.OpenForm "fUpdateProjectStatus", , , , , , Me.CustomerID

    Then code behind the form:

    Me.cmCustomer = Me.OpenArgs
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    179
    Genius as always, June7!

    After opening the form, I did have to insert the DLookup commands again to fill in the rest.

    Report:
    Code:
    Private Sub ProjectName_Click()
    DoCmd.OpenForm "fUpdateProjectStatus", , , , , , Me.CustomerID
    End Sub
    Form:
    Code:
    Private Sub Form_Load()
    Me.cmCustomer = Me.OpenArgs
    Me.cmProjectName = DLookup("ProjectName", "tProject", "CustomerID= " & "cmCustomer")
    Me.cmProjectPhase = DLookup("ProjectPhase", "tProject", "ProjectName= " & "cmProjectName")
    Me.cmProjectCompletionRisk = DLookup("ProjectCompletionRisk", "tProject", "ProjectName= " & "cmProjectName")
    Me.txProjectNotes = DLookup("ProjectNotes", "tProject", "ProjectName= " & "cmProjectName")
    End Sub
    Completed form:
    Click image for larger version. 

Name:	fUpdateProjectStatus-complete.png 
Views:	7 
Size:	188.2 KB 
ID:	22980

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

Similar Threads

  1. Replies: 3
    Last Post: 03-11-2015, 12:36 PM
  2. Replies: 3
    Last Post: 07-22-2014, 11:37 AM
  3. click on report to open more information
    By benjammin in forum Reports
    Replies: 3
    Last Post: 10-08-2012, 11:36 AM
  4. Report does NOT open on BUTTON CLICK
    By taimysho0 in forum Programming
    Replies: 3
    Last Post: 01-04-2012, 02:38 PM
  5. Open report from query result
    By shank in forum Programming
    Replies: 11
    Last Post: 10-04-2011, 12:59 PM

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 - Senior Forums