Results 1 to 8 of 8
  1. #1
    cardgage is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    50

    Code to open specific record from report opening to blank record instead

    Good afternoon all,

    I have a subreport (sbrptToDoList) on frmDashboard which has a field called TaskDesc. I have written some VBA code to open to a specific record from tblTasks on frmTaskInfo (linked by TaskID) on Double Click of field TaskDesc. This was working fine until recently when I added a new field (TaskPriorityID) to tblTasks - now frmTaskInfo opens up to a blank record when TaskDesc is double-clicked.

    The Record Source of sbrptToDoList is as follows:
    Code:
    SELECT tblStatusTypes.StatusType, tblTaskTypes.TaskType, qryAddEllipsis.TaskDescEllipsis, qryAddEllipsis.TaskNotesEllipsis, tblTasks.TaskID, tblTasks.TaskDesc, tblUserTasks.UserID, tblUserTasks.OwnerStatus, tblTasks.TaskPriorityIDFROM tblTaskTypes INNER JOIN ((tblStatusTypes INNER JOIN (tblTasks INNER JOIN qryAddEllipsis ON tblTasks.TaskID = qryAddEllipsis.TaskID) ON tblStatusTypes.StatusTypeID = tblTasks.StatusTypeID) INNER JOIN tblUserTasks ON tblTasks.TaskID = tblUserTasks.TaskID) ON tblTaskTypes.TaskTypeID = tblTasks.TaskTypeID;
    The Record Source of frmTaskInfo is as follows:
    Code:
    SELECT tblTasks.TaskID, tblTasks.TaskTypeID, tblTasks.StatusTypeID, tblTasks.TaskDesc, tblTasks.DateCompleted, tblTasks.OtherStatusType, tblTasks.TaskNotes, tblTasks.TaskTime, tblTasks.TaskPriorityID, tblUserTasks.UserTaskID, tblUserTasks.OwnerStatus, tblUsers.UserID, tblUsers.UserName, tblUsers.UserInitials, tblUsers.CalStartTime, tblUsers.CalName
    FROM tblUsers INNER JOIN (tblTasks INNER JOIN tblUserTasks ON tblTasks.TaskID = tblUserTasks.TaskID) ON tblUsers.UserID = tblUserTasks.UserID;
    The VBA code that I have to open frmTaskInfo to the specific record in sbrptToDoList (identified by TaskID) is as follows:
    Code:
    Private Sub TaskDesc_DblClick(Cancel As Integer)
    
      DoCmd.OpenForm "frmTaskInfo", , , "TaskID=" & Me.txtTaskID.Value
    
    
    End Sub



    I'm not sure exactly what is going wrong now that I added the new field to tblTasks. I should also note that I have the Record Source of sbrptToDoList dynamically set via VBA to include a WHERE clause to filter based on OwnerStatus and StatusType, and that is working properly. When I include the code Debug.Print Me.txtTaskID.Value just before the DoCmd function in VBA it returns the correct TaskID.

    Any help with this will be greatly appreciated!

    EDIT: I added a copy of the database in case this is helpful.
    Attached Files Attached Files
    Last edited by cardgage; 02-10-2020 at 05:30 PM. Reason: Added attachment

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your database is requesting me to install Outlook which I don't use.

  3. #3
    cardgage is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    50
    Here is a version of the database with the Outlook-related commands commented out.
    Attached Files Attached Files

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I get Invalid use of Null (error 94) on this line

    Code:
    Private Sub Report_Open(Cancel As Integer)
    
      FilterDashboardTasks (Forms!frmDashboard.OpenArgs)  <==================
    
    End Sub
    Can you give readers explicit step by step how to get to the issue?

  5. #5
    cardgage is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    50
    Sorry for the confusion. To replicate the issue, select a user from the drop-down menu on the opening form and click Open Dashboard (opens frmDashboard). On the dashboard, select "All" from both the "Task Owner filter" and "Task Status filter" on the center panel. Then, double-click on one of the task names that appears (this is the TaskDesc control on sbfrmToDoList). This should open frmTaskInfo to the relevant task (based on the TaskID of the task that was double-clicked), but currently is opening the form to a new record.
    Last edited by cardgage; 02-10-2020 at 05:32 PM. Reason: Added object names

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Still trying to work through your logic. I find when I do get an error I get into cycle of errors an finally have to close the database and start again.

    These debug.prints do not get actioned.
    Code:
    Private Sub Report_Open(Cancel As Integer)
      Debug.Print "entered Report_Open " & vbCrLf _
      & "Forms!frmDashboard.OpenArgs  " & Forms!frmDashboard.OpenArgs
      FilterDashboardTasks (Forms!frmDashboard.OpenArgs)
    
    End Sub
    
    Private Sub TaskDesc_DblClick(Cancel As Integer)
    Debug.Print "entered TaskDesc_DblClick"
      DoCmd.OpenForm "frmTaskInfo", , , "TaskID=" & Me.txtTaskID.Value
    
    End Sub
    
    Private Sub TaskNotes_DblClick(Cancel As Integer)
    Debug.Print "entered TaskNotes_DblClick"
      DoCmd.OpenForm "frmTaskInfo", , , "TaskID=" & Me.txtTaskID.Value
    
    End Sub

  7. #7
    cardgage is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    50
    Quote Originally Posted by orange View Post
    Still trying to work through your logic. I find when I do get an error I get into cycle of errors an finally have to close the database and start again.

    These debug.prints do not get actioned.
    Code:
    Private Sub Report_Open(Cancel As Integer)
      Debug.Print "entered Report_Open " & vbCrLf _
      & "Forms!frmDashboard.OpenArgs  " & Forms!frmDashboard.OpenArgs
      FilterDashboardTasks (Forms!frmDashboard.OpenArgs)
    
    End Sub
    
    Private Sub TaskDesc_DblClick(Cancel As Integer)
    Debug.Print "entered TaskDesc_DblClick"
      DoCmd.OpenForm "frmTaskInfo", , , "TaskID=" & Me.txtTaskID.Value
    
    End Sub
    
    Private Sub TaskNotes_DblClick(Cancel As Integer)
    Debug.Print "entered TaskNotes_DblClick"
      DoCmd.OpenForm "frmTaskInfo", , , "TaskID=" & Me.txtTaskID.Value
    
    End Sub
    Thank you for your effort orange, I really appreciate it! Something must have gone wrong with the code after I commented out the Outlook-related functions, because those debug.prints do process when I run them in my version of the database.

    I ended up reverting back to an older version of the database before I added the TaskPriorityID to tblTasks, and then re-worked the code which resolved my problem. Essentially, the error was occurring in the txtTaskDesc.LostFocus sub on the Me.Dirty = False line of code. This was being run because I needed to save the record in case I wanted to add a consultant to the task (triggering the code to add a record to tblUserTasks via the DAO recordset commands) while adding a new task to tblTasks. However, I ended up reorganizing the code so that the required fields are now checked via a private function when either the "Add Consultant" or "Return to Dashboard" command buttons are pressed, and if all required fields are entered the Me.Dirty = False code fires and saves the record. Now everything works as planned when double-clicking on the subreport, the correct record pops up as it did previously.

    I'm not sure this makes sense to anyone else, but my mind works in a linear fashion and so naturally my code follows (I'm definitely a novice!). Thanks again for the time and effort to try to help!

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good to hear you have it working. My only comment was that it was difficult to decipher the code. The form could be opened from several different areas. I often put Debug.Print statements with some message so I can track down an issue. The debug.print can be left in the code if you put a boolean/flag that can be set On/Off at the beginning of a session. If flag is ON then Debug is actioned, otherwise the Debug statements are skipped.

    Good luck with your project. Great that you had some backups to turn to. Always a good policy.

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

Similar Threads

  1. opening specific record on a form from a report button
    By mainerain in forum Programming
    Replies: 4
    Last Post: 03-07-2019, 11:27 AM
  2. Replies: 20
    Last Post: 05-19-2015, 05:10 AM
  3. Replies: 4
    Last Post: 12-13-2013, 04:50 PM
  4. Replies: 6
    Last Post: 11-13-2012, 04:29 PM
  5. Open Report is opening one record only
    By kelann in forum Reports
    Replies: 4
    Last Post: 10-23-2012, 11:08 AM

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