Associating New Data with a SQL statement

[Guest]

I have a combo box that is synced to another by using a SQL statement as its
Row Source:

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];

I also have a NotInList Event Code for this box. I can get the code to
enter the data into the right table, but it screws up the box because it is
still stuck on the original SQL statement. Is there a requery or something
that I can add to the code so that this will work, or is there a way to make
it apply the new data to the SQL statement instead of the table?

Here is my NotInList Code:

Private Sub Contact_Person_NotInList(NewData As String, Response As Integer)
On Error GoTo Contact_Person_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("You are adding " & Chr(34) & NewData & _
Chr(34) & " as a new contact for this customer." & vbCrLf & _
"Are you sure you want do this?" _
, vbQuestion + vbYesNo, "Customer Contacts")
If intAnswer = vbYes Then
strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person],
" & _
"[CC_Company] )" & _
"VALUES ('" & NewData & "', " & _
"Forms![Job Work Orders Form]!Customer);"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Response = acDataErrAdded
Else
MsgBox "Please select an existing contact from the list then." _
, vbInformation, "Customer Contacts"
Response = acDataErrContinue
End If
Contact_Person_NotInList_Exit:
Exit Sub
Contact_Person_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Contact_Person_NotInList_Exit
End Sub

 

[Marshall Barton]

I have a combo box that is synced to another by using a SQL statement as its
Row Source:

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];

I also have a NotInList Event Code for this box. I can get the code to
enter the data into the right table, but it screws up the box because it is
still stuck on the original SQL statement. Is there a requery or something
that I can add to the code so that this will work, or is there a way to make
it apply the new data to the SQL statement instead of the table?

Here is my NotInList Code:

Private Sub Contact_Person_NotInList(NewData As String, Response As Integer)
On Error GoTo Contact_Person_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("You are adding " & Chr(34) & NewData & _
Chr(34) & " as a new contact for this customer." & vbCrLf & _
"Are you sure you want do this?" _
, vbQuestion + vbYesNo, "Customer Contacts")
If intAnswer = vbYes Then
strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person],
" & _
"[CC_Company] )" & _
"VALUES ('" & NewData & "', " & _
"Forms![Job Work Orders Form]!Customer);"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Response = acDataErrAdded
Else
MsgBox "Please select an existing contact from the list then." _
, vbInformation, "Customer Contacts"
Response = acDataErrContinue
End If
Contact_Person_NotInList_Exit:
Exit Sub
Contact_Person_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Contact_Person_NotInList_Exit
End Sub

The line Response = acDataErrContinue will force a
requery for you.

I think(?) the issue could be that the RunSQL method
executes asynchrounously so the query might not have
finished adding the record before the automatic requery
runs. You can check this by opening the table and looking
to see if the record was actually added or not. If it was
added, then try using the Execute method instead of RunSQL:

DbEngine(0)(0).Execute strSQL

But, Execute will not resolve the parameter for you as
RunSQL does. Fortunately, in your case, it looks like you
only have to change this:

strSQL = "INSERT INTO [Customer Contacts Table] " _
& "([CC_Contact Person], [CC_Company] ) " _
& "VALUES ('" & NewData & "', " _
& Forms![Job Work Orders Form]!Customer)
if the company field is a numeric type field. If it's a
Text field, then use:

strSQL = "INSERT INTO [Customer Contacts Table] " _
& "([CC_Contact Person], [CC_Company] ) " _
& "VALUES ('" & NewData & "', """ _
& Forms![Job Work Orders Form]!Customer) &
""""