Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    7

    Question Unanswered: find record with composite PK

    Hi. I am trying to move to a particular record on a form where the form has a composite PK.

    The PK is made of 3 fields: ModuleID, Precedence and RecordTypeID

    I have always used docmd.findrecord before but I can't see how to do this with a composite PK.

    Any suggestions?

    Thanks
    Kirsty

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    hmmm...you say the form has the composite PK ?? ie and unbound control? does that mean it does not exist in a table??

    if it doesn't exist in a table - then - you can't find it because there is nothing to be found

    or maybe you just mis-typed....not sure....

  3. #3
    Join Date
    Aug 2009
    Posts
    7
    Of course, I meant that the view on which the form is based has a composite PK.

    And I want to call the find command from another form just to add a bit of extra info.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Code:
        Dim rst As Recordset
        Dim strCriteria As String
    
        Set rst = Me.RecordsetClone
        StrCriteria = "ModuleID = " & <ModuleID> & " AND Precedence = " & <Precedence> & "AND RecordTypeID = " & <RecordTypeId>
        rst.FindFirst strCriteria
            If rst.NoMatch Then
                MsgBox "Record not found"
            Else
                Me.Bookmark = rst.Bookmark
            End If
        rst.Close
    If ModuleId, Precedence or RecordTypeID are of non numeric data types you have to enclose them in the proper delimiters (ex. ... Precedence = '" & <Precedence> & "' AND ... if Precedence is of Text type)
    Have a nice day!

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I might create a unbound field on the form (called FKey) and for the controlsource = [Field1]+[Field2]+[Field3]

    Then I'd also have a 'search' type combobox which has the fields again combined into 1 field in the query for the recordsource.

    Then in the Afterupdate event of the combobox, do my find against the FKey field.

    ie.

    me.FKey.setfocus
    docmd.findrecord me!MyComboboxName
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

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
  •