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