Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2007
    Posts
    72

    Unanswered: link criteria to open form

    I have a form called StaffDirectory that shows a list of names. This is the code on the OnClick event:
    Code:
    Private Sub StaffName_Click()
    On Error GoTo Err_StaffName_Click
    
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "SummaryForm"
        
        stLinkCriteria = "[staffid] = " & Me![STAFFIDPK]
        
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Exit_StaffName_Click:
        Exit Sub
    
    Err_StaffName_Click:
        MsgBox err.Description
        Resume Exit_StaffName_Click
    End Sub
    This works properly.
    What I want is on another form ArchitectureQuals that gives a list of quals and the person's name is to make an onClick event that opens the same form.
    The difficulty is that where the Summary form is taking the person's name and details it is referencing another form e.g
    Code:
    =[Forms]![StaffDirectory]![STAFFIDPK]
    When I try to adjust the link criteria to
    Code:
    stLinkCriteria = "[staffid]=" & Me![staffid]
    this link is only referencing the subform detail and missing out the person's name and info at the top.
    Can I write a more complex link criteria to include the indirect reference?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This...
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "SummaryForm"
    stLinkCriteria = "[staffid] = " & Me![STAFFIDPK]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Can become...
    Docmd.Openform "SummaryForm",,,"[StaffID] = " & Forms!MyFormName!StaffIDPK & ""

    I personally like to use the Forms!MyFormName!FieldName versus me!FieldName just to make sure the field is grabbed from the correct form. I also like doing the 1 line of code versus dim variables and 5 lines of code.

    You really shouldn't need more complex link but maybe I'm missing something you're trying to do. But try out the statement above to see if that helps first. Just make sure that the StaffID field exists on the one form and StaffIDPK exists on the other form (ie. the field is actually on the form. It can be invisible/disabled/etc... but it should be on the form.)
    You can also add in this line of code before your docmd.openform.... statement
    msgbox "Staff ID = " & Forms!MyFormName!StaffIDPK
    just to make sure that you don't have a blank StaffID when opening the second form.

    You can also put criteria in the recordsource query of the 2nd form...ie...the sql statement would look like this if you viewed the code versus the query design: "Select * from MyTable where StaffID = " & Forms!MyFirstFormName!StaffIDPK & ""

    Then you would only need to open the form without the criteria in the docmd.openform....
    You'll should also test StaffIDPK that it is not blank (null) before the docmd.openform....
    ie..
    if not isnull(me!StaffIDPK) then
    docmd.openform.....
    else
    msgbox "Cannot open form with a blank StaffIDPK"
    end if

    I'm also assuming that StaffID and StaffIDPK are integer (number) fields and there is a relationship between these 2 fields in the relational design.
    Last edited by pkstormy; 11-21-08 at 23:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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