search for a record located on a sub form and update main form and other sub forms

[grapevine]

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

    SELECT tblProjects.ProjJNum, tblProjects.ContractID FROM tblProjects;

  • I have set up an After Update event procedure

Private Sub cboJNumber_afterupdate()
With Me![cboJNumber]
If IsNull(Me.ContractID) Then
.RowSource = ""
Else
.RowSource = "Select[ContractID] " & _
"from tblcontracts " & _
"where [contractid] = " & Me.ContractID
End If
Call .Requery
End With
End Sub

the .requery was in the code i found online so I dont know if i need to have a procedure written to be called?
Nothing seems to work, so if someone can point out the error of my ways it would be greatly appreciated

Many thanks

[June7]

You want to allow user to select a project then filter the main form to the contract that project is associated with?

I don't think you need to set the contract combobox RowSource, which is what I think you intended with your code but is actually referencing properties of the project combobox because of With Me![cboJNumber]. Why would it matter that the ContractID field is null if you want to restrict list by selected project?

Just use the ContractID from cboJNumber RowSource to apply filter to main form.

The 'Call' command is simply not needed in front of .Requery. However, becomes moot if you follow above advice.

[grapevine]

Thank you for your reply
So to clarify
I am deleting the update code completely?
can you confirm how i use the ContractID from cboJNumber RowSource to apply filter to main form
I have created a combo box on the main form (tblcontracts) with the following code in row source

SELECT tblProjects.ProjJNum, tblProjects.ContractID FROM tblProjects;

I have deleted the update event
The drop down box shows the different project numbers but nothing is changing on the form
do i need some code on afterupdate to query the main form with the contractId? if so could you please give some guidance
Many thanks


Many thanks

[grapevine]

I have managed to get this working so in case anyone else has this problem
in the header of the main form I created a combo box

[grapevine]

I have managed to get this working so in case anyone else has this problem
in the header of the main form I created a combo box, In the data properties, the row source was as follows

SELECT tblProjects.ContractID, tblProjects.ProjJNum FROM tblProjects;

on the Event tab in the after update area, i put the following code

Private Sub cboJNumSearch_AfterUpdate()
Dim rs As DAO.Recordset
If Not IsNull(Me.cboJNumSearch) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[ContractID]=" & Me.cboJNumSearch
If rs.NoMatch Then
MsgBox "Record Not Found: Check number"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

all seems to be working ok now
Many thanks for the pointers, much appreciated