Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    26

    Combo boxes both primary keys to select record

    Hello,



    I have 2 combo boxes. One that selects a plane, and the other that selects the plane "work package". So i have two tables, one for the planes(tblPlaneModel), the other for the work packages(tblWorkPackages), and in between I have tblWorkPackageSpecs, which contains two primary keys, which are the foreing keys from tblPlaneModel and tblWorkPackages


    tblPlaneModel
    -----------
    -pkPlaneID-
    -----------


    tblWorkpackages
    ------------------
    -pkWorkPackageID-
    -------------------


    tblWorkPackageSpecs
    --------------------
    -fkPlaneID----------
    -fkWorkPackageID ---


    So my form has a first combo box to select my plane, which works fine. Here is the AfterUpdate code:
    Code:
    Private Sub Combo147_AfterUpdate()
    'this first bit is to set the proper key for the plane'
        
    DoCmd.ShowAllRecords
        Me
    !pkPlaneID.SetFocus
        DoCmd
    .FindRecord Me!Combo147
     
    'this second bit tells the second combo box to update itself with the proper work packages'
    Me!Combo145.Requery
    End Sub 
    The Combo145.Requery refers to this SQL query in combo 145, the combo for the Work Package:

    SELECT qryOutput.pkWorkPackageID, tblWorkPackages.txtWorkPackageName FROM tblWorkPackages INNER JOIN qryOutput ON tblWorkPackages.pkWorkPackageID = qryOutput.pkWorkPackageID WHERE (((qryOutput.pkPlaneID)=[Forms]![frmTabbedWorkPackage]![pkPlaneID]));


    All of this works perfecty from the combo box point of view. It also updates accordingly the record for the fkPlaneID in the form.

    But when comes the time to do the selection of the WorkPackage, that is when it goes wrong. The selection is good, I only see the relevant workpackages, but when the fkWorkPackageID is set, it resets the fkPlaneID to the initial value. Therefore it brings me back to the same plane all the time. Here is the code afterupdate:
    Code:
    Private Sub Combo145_AfterUpdate()
     
        
    DoCmd.ShowAllRecords
        Me
    !pkWorkPackageID.SetFocus
        DoCmd
    .FindRecord Me!Combo145
     
    End Sub 

    Does anyone understand why? Or is there a better approach for what I am trying to do?

    Thanks a million in advance

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    you write "but when the fkWorkPackageID is set, it resets the fkPlaneID to the initial value. "

    this is where you lose me. Why if the WorkPackageID being 'set'? what do you mean by this?

  3. #3
    Join Date
    Jul 2010
    Posts
    26
    This is what i wanted it to do:

    1. choose plane model --> it updates plane model primary key
    2. choose work package --> it updates work package primary key

    result: both primary keys have been selected and we see the record for that plane/workpackage combination

    instead, when I select the work package, the plane that was initially selected returns to 1.

    My solution to this problem was to create an autonumber in the table without assining it to primary key. That way, the referential integrity is kept, but when we select a work package it refers to the specific combination i'm looking for.

  4. #4
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    Speaking generically; one doesn't change 'primary keys'. That is a contradiction in terms. So that idea simply can not happen.

    Again generically; one cross references primary keys in a separate transaction or details table. So that i.e. Widget 1 is to go with Details A...in a separate table you put their 2 keys in a record together. This record has its own primary key too, and possibly other info such as 'Combo1A'

    Hopes this helps a little. Generally a form & subform is all one needs - but I'm not totally clear in your case as to the end result goal.

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

Similar Threads

  1. Multiple primary keys
    By elektrisk in forum Access
    Replies: 5
    Last Post: 02-11-2010, 04:39 PM
  2. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM
  3. Primary and Secondary Keys...
    By LittleOleMeDesigns in forum Database Design
    Replies: 5
    Last Post: 07-24-2009, 11:33 AM
  4. Are text primary keys less efficient than autonumbers?
    By bar tomas in forum Database Design
    Replies: 4
    Last Post: 05-11-2009, 09:37 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