I have a main table based on the contracts table which has a primary ID of ContractID
Each contract can run for many years and has various Projects attached to it - the Projects table has a foreign key called ContractID and a primary key of ProjectID
Each project can have many different jobs attached to it
I have created a main form which holds the contract information
I have five tabbed subforms which hold the project information and then the various other job information etc
I have set up a search field in the header of the form where the Contract number and description (based on the contractid) can be selected and this correctly finds the correct contract and updates all the subforms correctly
However this is the bit i am struggling with
as contracts run for many years, the piece of information that is most commonly known is the current project number which is stored in the projects table
- I want to set up a search box in the header of the main form where the project number can be selected
- based on various googles I have come up with the following ,but cannot get any further
- I have set up a combo box in the header of the main form called cboJNumber
- the row source is
Code:SELECT tblProjects.ProjJNum, tblProjects.ContractID FROM tblProjects;- I have set up an After Update event procedure
the .requery was in the code i found online so I dont know if i need to have a procedure written to be called?Code:Private Sub cboJNumber_afterupdate()With Me![cboJNumber]If IsNull(Me.ContractID) Then.RowSource = ""Else.RowSource = "Select[ContractID] " & _ "from tblcontracts " & _ "where [contractid] = " & Me.ContractIDEnd IfCall .Requery End WithEnd Sub
Nothing seems to work, so if someone can point out the error of my ways it would be greatly appreciated
Many thanks