Hi all,
Here is an interesting one I am struggling with!

I have a frm Projects_Main on which I have cboProjectMgrID. The SQL is
as follows:

SELECT Company_Contacts.CompanyContactID, Company_Contacts.CompanyID,
Company_Contacts.ContactName, Company_Main.CompanyName,
Company_Contacts.ContactTitle
FROM Company_Main INNER JOIN Company_Contacts ON
Company_Main.CompanyID = Company_Contacts.CompanyID
WHERE (((Company_Main.CompanyName)="Elder Jones, Inc") AND
((Company_Contacts.ContactTitle)=4));
Bound Column = 1
Not in list code as Follows:

Private Sub ProjectMgrID_NotInList(NewData As String, Response As
Integer)
Dim strsql As String, x As Integer
   Dim LinkCriteria As String
x = MsgBox("Project Mgr not in Current List, Would you Like to Add?",
vbYesNo)
If x = vbYes Then
   strsql = "Insert Into Company_Contacts ([ContactName]) values ('"
& NewData & "')"
   'MsgBox strsql
   CurrentDb.Execute strsql, dbFailOnError
   LinkCriteria = "[ContactName] = '" & Me!ProjectMgrID.Text & "'"
   DoCmd.OpenForm "Company_Contacts", , , LinkCriteria

   Response = acDataErrAdded
Else
   Response = acDataErrContinue
End If
End Sub

OK, I get the error message when run:
"You cannot add or change a record because a related record is
required in tabble "Company_Main"

This is understandable as as on my frm Company_Contacts I have the
following value on a Text field clled CompanyName:
=[Forms]![Company_Main]![CompanyName]

This is done to carry value and be able to add contacts on my frm
"Company_Contacts"  so cbo box is not required and so no mistakes are
made adding a new contact to the list!

Question is:
Is there a way to link the CompanyName to this in the Not in List
function with out changing my frm Company_Contacts? so it will allow
me to use the not in list and if so, any suggestions?

Thanks so much,
Dave