Solved

Open Form and Subform based on Criteria from Dashboard

Posted on 2014-09-16
3
316 Views
Last Modified: 2014-09-16
I have a Dashboard with a datasheet listing Audit questions. I have a On Click Event on the records to open up a Form based on detail in a login Form (this form is hidden on opening the dashboard).

The following code works fine for opening the Main form on the specific Record


Dim strWhere As String

strWhere = "[StdID]=" & Forms!frmdatabaselogin![cboStandard]
DoCmd.OpenForm "mjpAuditorsAnswerMaster", , , strWhere

What II want the subform to go to the specific question from the dashboard, something like Me!questionID = forms!mainForm!Subform!QuesionID



Can this be done, and if so, what code and order is needed.
0
Comment
Question by:SparkyP
  • 2
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40325070
You'd have to do this in the OpenArgs of your MainForm, I'd think. The Subform is opened and populated prior to your MainForm, so perhaps something like this:

Sub Form_Open()
If Nz(Me.OpenArgs, "") <> "" Then
  Dim QuestionID as Integer
  QuestionID = CInt(Me.OpenArgs)
  Dim rst As DAO.Recordset
  Set rst = Me.NameOfYourSubformCONTROL.Form.Recordset
  rst.FindFirst "QuestionID=" & QuestionID
  If Not rst.NoMatch Then
    Me.NameOfYourSubformCONTROL.Form.Bookmark = rst.Bookmark
  End If
End If

End Sub

Open in new window

This assumes that QuestionID is a Numeric value. If not, you'll have to make changes.

Note that NameOfYourSubformCONTROL is the name of the Subform Control on your main form. It may or may not be the same as the form you're using as the Subform

Subform Control
0
 

Author Comment

by:SparkyP
ID: 40325131
Scott

Please bear with me, OpenArgs is completely new to me.

Do I leave my original code on the click event of the Dashboard, which opens the correct Main Form record.

Where do I put the OpenArgs code?
How is it referring to the QstnID on the record in the dashboard

If Nz(Me.OpenArgs, "") <> "" Then
  Dim QuestionID as Integer
  QuestionID = CInt(Me.OpenArgs)
  Dim rst As DAO.Recordset
  Set rst = Me.mjpAuditorsAnswers.Form.Recordset
  rst.FindFirst "QuestionID=" & QuestionID
  If Not rst.NoMatch Then
    Me.mjpAuditorsAnswers.Form.Bookmark = rst.Bookmark
  End If
End If

Thank you
0
 

Author Closing Comment

by:SparkyP
ID: 40325514
Scott

Has been a steep learning curve this afternoon, but got there.

Thanks again for your help.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now