Not In List and Synchronized Combo Box CODE CONFLICTS

[Guest]

HERE IS WHAT I HAVE

Form: Job Work Orders Form (it’s bound to Job Work Order Query)
Query: Job Work Orders Query
Tables: Customers Table
Projects Table
Customer Contacts Table
Project Managers Table
Insurance Companies Table
Insurance Contacts Table
Job Superintendents Table
Job Work Order Table

On the Job Work Orders Form, each of the following fields are Combo Boxes:
Customer
Project
Customer Contact
Project Manager
Job Superintendent
Insurance Company
Insurance Contact

Each of these combo boxes has an On Not In List Event Procedure, and the
code is to add the data to the table. Here is one example:

Private Sub Customer_NotInList(NewData As String, Response As Integer)
On Error GoTo Customer_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("You are adding " & Chr(34) & NewData & _
Chr(34) & " as a new customer." & vbCrLf & _
"Are you sure this is not a typo and you want do this?" _
, vbQuestion + vbYesNo, "Customers")
If intAnswer = vbYes Then
strSQL = "INSERT INTO [Customers Table]([CU_Customer]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Response = acDataErrAdded
Else
MsgBox "Please select an existing customer from the list then." _
, vbInformation, "Customers"
Response = acDataErrContinue
End If
Customer_NotInList_Exit:
Exit Sub
Customer_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Customer_NotInList_Exit
End Sub

The rest are only different based on the name of the fields, tables, etc.

In addition, the Customer Contact, Project Manager, Job Superintendent Combo
Boxes are all synchronized to the Customer Combo Box, and the Insurance
Contact Combo Box is synchronized to the Insurance Company Combo Box. To do
this, they each have an SQL Statement in the Row Source. Here is the example
for Customer Contacts:

SELECT [Customer Contacts Table].CC_CustomerContactID, [Customer Contacts
Table].[CC_Contact Person], [Customer Contacts Table].CC_Company FROM
[Customer Contacts Table] WHERE ((([Customer Contacts
Table].CC_Company)=Forms![Job Work Orders Form]!Customer)) ORDER BY [Customer
Contacts Table].[CC_Contact Person];

The Customer Combo Box has the After Update Event Procedure, and the code is
to requery the three other synchronized combo boxes.

If the Not In List code is not in, the requery works in each of the
synchronized boxes. And, the Not In List code works for each of the fields
as long as the requery isn’t on. But when both codes are present I get the
following error when I select the Customer or the Insurance Company:

The expression After Update you entered as the event property setting
produced the following error: Ambiguous name detected: Project_Name_NotInList.

*The expression may have been a result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

And, the other synchronized combo boxes are blank… HOW DO I FIX THIS?

 

[Guest]

I noticed that both of the main Combo Boxes (Customer and Insurance Company)
get the same error...

Ambiguous name detected: Project_Name_NotInList.

The "Project_Name_NotInList" is part of the code for the NotInList Event of
the Project Name Combo Box. I double checked all of the code to make sure
that title wasn't coppied in other codes by mistake and it's not.

 

[Guest]

OK, I removed the code just for the Project Name Combo Box Not In List Event
Procedure and now everything works--except now the Project Name no longer
adds to the projects table. Why is this one code screwing up the others?

Here it is:

Private Sub Project_Name_NotInList(NewData As String, Response As Integer)
Private Sub Project_Name_NotInList(NewData As String, Response As
Integer)
On Error GoTo Project_Name_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The project " & Chr(34) & NewData & _
Chr(34) & " does not exist." & vbCrLf & _
"Would you like to add it?" _
, vbQuestion + vbYesNo, "Projects")
If intAnswer = vbYes Then
strSQL = "INSERT INTO [Projects Table]([PJ_Project Name]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Response = acDataErrAdded
Else
MsgBox "Please select an existing project from the list then." _
, vbInformation, "Projects"
Response = acDataErrContinue
End If
Project_Name_NotInList_Exit:
Exit Sub
Project_Name_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Project_Name_NotInList_Exit
End Sub

 

[Guest]

I take that back. The NotInList event works for the Customer Box, but it
still doesn't work correctly for the synchronized combo boxes still... The
synchro works, but it still explodes when I try to add new. It begins the
event and asks if I want to add the contact or whatever, but then when I
select YES, it gives the default Access Not In List error "The text you
entered isn't an item on the list..."

 

[Guest]

Sorry, I examined the code for the Project_Name NotInList event and figured
out the error. I had the heading line input twice (from copying it out and
back in). So now it's in and and the two main combo boxes no longer give me
that error.

The synchronized combo boxes still mess up when I try to add though...

 

[Guest]

OK, it's not working for these synchronized boxes because the Row Source for
them is not the table, but a SQL statement.

i can't figre out how to change the NotInList code to fix this.

 

[Guest]

I just figured out that it IS adding the Contanct, but since it adds it to
the table and not the SQL statement that's in the row source, that it's not
associating the appropriate company. They are in the Contacts table now
without a company name associated to them.