I'm New--entering code

G

Guest

I have a NotInList Event Code. In that code I have an strSQL and I want it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person]) " & _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) " & _
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
 
G

Guest

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

Not sure the syntax is exactly correct, but this is the basic idea.
 
G

Guest

Hey dude!

I entered that, but I am getting a syntax error. I don't know how to
correct it.

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

Not sure the syntax is exactly correct, but this is the basic idea.

Christian > said:
I have a NotInList Event Code. In that code I have an strSQL and I want it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person]) " & _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) " & _
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
 
G

Guest

Without all the objects to play with it, I can't fix it from here. As I
said, it may have syntax errors. Try this version (no guarantees)

strSQL = "INSERT INTO [Customer Contacts Table] ([CC_Contact Person], " & _
"[CC_Company] )" & _
"VALUES '" & NewData & "', '" & _
Forms![Job Work Orders Form]![Customer] & "';"
This assumes both fields in the table are text fields.

Christian > said:
Hey dude!

I entered that, but I am getting a syntax error. I don't know how to
correct it.

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

Not sure the syntax is exactly correct, but this is the basic idea.

Christian > said:
I have a NotInList Event Code. In that code I have an strSQL and I want it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person]) " & _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) " & _
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
Click to expand...
 
B

Brian Bastl

Christian,

using what you have provided:

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

CurrentDb.Execute strSQL, dbFailOnError

HTH,
Brian
 
G

Guest

Thanks guys, I got it to work--sort of using the following code:

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

Here's the problem. This particular combo box is sync'd with another by
using a SQL statement as the 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];

So now, with my not in list code, it adds the contact to the contacts table,
but I get an error after and it still doesn't have it in the combo box for
this field--not until i open a new form...

So I learned somehting from both of you, but I learned the wrong thing. I'm
not even sure what to ask at this point. :|

Brian Bastl said:
Christian,

using what you have provided:

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

CurrentDb.Execute strSQL, dbFailOnError

HTH,
Brian


Christian > said:
I have a NotInList Event Code. In that code I have an strSQL and I want it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person]) " & _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) " & _
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
 
G

Guest

Can I insert the new data into a query instead of a table?

Klatuu said:
Without all the objects to play with it, I can't fix it from here. As I
said, it may have syntax errors. Try this version (no guarantees)

strSQL = "INSERT INTO [Customer Contacts Table] ([CC_Contact Person], " & _
"[CC_Company] )" & _
"VALUES '" & NewData & "', '" & _
Forms![Job Work Orders Form]![Customer] & "';"
This assumes both fields in the table are text fields.

Christian > said:
Hey dude!

I entered that, but I am getting a syntax error. I don't know how to
correct it.

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

Not sure the syntax is exactly correct, but this is the basic idea.

:

I have a NotInList Event Code. In that code I have an strSQL and I want it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person]) " & _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) " & _
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
Click to expand...
 
B

Brian Bastl

Christian >,

have you set the response to acDataErrAdded? Then in the AfterUpdate event
of the first combobox, you'll need to issue a requery of the second
combobox.

HTH,
Brian


Christian > said:
Thanks guys, I got it to work--sort of using the following code:

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

Here's the problem. This particular combo box is sync'd with another by
using a SQL statement as the 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];

So now, with my not in list code, it adds the contact to the contacts table,
but I get an error after and it still doesn't have it in the combo box for
this field--not until i open a new form...

So I learned somehting from both of you, but I learned the wrong thing. I'm
not even sure what to ask at this point. :|

Brian Bastl said:
Christian,

using what you have provided:

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

CurrentDb.Execute strSQL, dbFailOnError

HTH,
Brian


Christian > said:
I have a NotInList Event Code. In that code I have an strSQL and I
Click to expand...
want
it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person]) " & _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) " & _
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yeah, I've done both.

Brian Bastl said:
Christian >,

have you set the response to acDataErrAdded? Then in the AfterUpdate event
of the first combobox, you'll need to issue a requery of the second
combobox.

HTH,
Brian


Christian > said:
Thanks guys, I got it to work--sort of using the following code:

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

Here's the problem. This particular combo box is sync'd with another by
using a SQL statement as the 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];

So now, with my not in list code, it adds the contact to the contacts table,
but I get an error after and it still doesn't have it in the combo box for
this field--not until i open a new form...

So I learned somehting from both of you, but I learned the wrong thing. I'm
not even sure what to ask at this point. :|

Brian Bastl said:
Christian,

using what you have provided:

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

CurrentDb.Execute strSQL, dbFailOnError

HTH,
Brian


I have a NotInList Event Code. In that code I have an strSQL and I want
it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person]) " & _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) " & _
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

No, queries do not store data, they only act on it or retrieve it. What your
SQL is trying to do (when we get it working) is to put the data into the
table.
It is essentially an Append query.

What you might do, is try using the Query Builder to create an append query,
then you can switch to SQL view, copy the code, and paste it into your VBA
code.

Christian > said:
Can I insert the new data into a query instead of a table?

Klatuu said:
Without all the objects to play with it, I can't fix it from here. As I
said, it may have syntax errors. Try this version (no guarantees)

strSQL = "INSERT INTO [Customer Contacts Table] ([CC_Contact Person], " & _
"[CC_Company] )" & _
"VALUES '" & NewData & "', '" & _
Forms![Job Work Orders Form]![Customer] & "';"
This assumes both fields in the table are text fields.

Christian > said:
Hey dude!

I entered that, but I am getting a syntax error. I don't know how to
correct it.

:

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

Not sure the syntax is exactly correct, but this is the basic idea.

:

I have a NotInList Event Code. In that code I have an strSQL and I want it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person]) " & _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) " & _
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

DUDE!

I entered the following line in my code and it WORKS!:

Me.Contact_Person.Requery

Except, I get that anoying message "You must save the current field before
you run the Requery action."

Can I enter code to make it save the field before the requery?


Brian Bastl said:
Christian >,

have you set the response to acDataErrAdded? Then in the AfterUpdate event
of the first combobox, you'll need to issue a requery of the second
combobox.

HTH,
Brian


Christian > said:
Thanks guys, I got it to work--sort of using the following code:

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

Here's the problem. This particular combo box is sync'd with another by
using a SQL statement as the 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];

So now, with my not in list code, it adds the contact to the contacts table,
but I get an error after and it still doesn't have it in the combo box for
this field--not until i open a new form...

So I learned somehting from both of you, but I learned the wrong thing. I'm
not even sure what to ask at this point. :|

Brian Bastl said:
Christian,

using what you have provided:

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

CurrentDb.Execute strSQL, dbFailOnError

HTH,
Brian


I have a NotInList Event Code. In that code I have an strSQL and I want
it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person]) " & _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) " & _
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
Click to expand...
 
B

Brian Bastl

shouldn't it be:

WHERE ((([Customer Contacts Table].[CC_Contact Person]) = Forms![Job Work
Orders Form]!Customer ... ?

or better still (assuming that the bound columns for your respective
comboboxes is a numeric ID:
WHERE (((((([Customer Contacts Table].[CC_CustomerContactID]) = Forms![Job
Work Orders Form]!Customer

Brian


Christian > said:
Yeah, I've done both.

Brian Bastl said:
Christian >,

have you set the response to acDataErrAdded? Then in the AfterUpdate event
of the first combobox, you'll need to issue a requery of the second
combobox.

HTH,
Brian


Christian > said:
Thanks guys, I got it to work--sort of using the following code:

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

Here's the problem. This particular combo box is sync'd with another by
using a SQL statement as the 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];

So now, with my not in list code, it adds the contact to the contacts table,
but I get an error after and it still doesn't have it in the combo box for
this field--not until i open a new form...

So I learned somehting from both of you, but I learned the wrong
Click to expand...
thing.
I'm
not even sure what to ask at this point. :|

:

Christian,

using what you have provided:

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

CurrentDb.Execute strSQL, dbFailOnError

HTH,
Brian


I have a NotInList Event Code. In that code I have an strSQL and
Click to expand...
I
want
it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact
Click to expand...
Person]) "
& _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) " & _
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
Click to expand...
 
B

Brian Bastl

WHERE ((([Customer Contacts Table].[CC_CustomerContactID]) = Forms![Job
Work Orders Form]!Customer))

Brian


Brian Bastl said:
shouldn't it be:

WHERE ((([Customer Contacts Table].[CC_Contact Person]) = Forms![Job Work
Orders Form]!Customer ... ?

or better still (assuming that the bound columns for your respective
comboboxes is a numeric ID:
WHERE (((((([Customer Contacts Table].[CC_CustomerContactID]) = Forms![Job
Work Orders Form]!Customer

Brian


Christian > said:
Yeah, I've done both.

Brian Bastl said:
Christian >,

have you set the response to acDataErrAdded? Then in the AfterUpdate event
of the first combobox, you'll need to issue a requery of the second
combobox.

HTH,
Brian


Thanks guys, I got it to work--sort of using the following code:

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

Here's the problem. This particular combo box is sync'd with
Click to expand...
Click to expand...
another
by
using a SQL statement as the 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];

So now, with my not in list code, it adds the contact to the contacts
table,
but I get an error after and it still doesn't have it in the combo
Click to expand...
Click to expand...
box
for
this field--not until i open a new form...

So I learned somehting from both of you, but I learned the wrong thing.
I'm
not even sure what to ask at this point. :|

:

Christian,

using what you have provided:

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

CurrentDb.Execute strSQL, dbFailOnError

HTH,
Brian


I have a NotInList Event Code. In that code I have an strSQL
Click to expand...
Click to expand...
and
I
want
it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person]) "
& _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) "
Click to expand...
Click to expand...
&
_
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
Click to expand...
 
B

Brian Bastl

which event are you using to issue the requery???


Christian > said:
DUDE!

I entered the following line in my code and it WORKS!:

Me.Contact_Person.Requery

Except, I get that anoying message "You must save the current field before
you run the Requery action."

Can I enter code to make it save the field before the requery?


Brian Bastl said:
Christian >,

have you set the response to acDataErrAdded? Then in the AfterUpdate event
of the first combobox, you'll need to issue a requery of the second
combobox.

HTH,
Brian


Christian > said:
Thanks guys, I got it to work--sort of using the following code:

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

Here's the problem. This particular combo box is sync'd with another by
using a SQL statement as the 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];

So now, with my not in list code, it adds the contact to the contacts table,
but I get an error after and it still doesn't have it in the combo box for
this field--not until i open a new form...

So I learned somehting from both of you, but I learned the wrong
Click to expand...
thing.
I'm
not even sure what to ask at this point. :|

:

Christian,

using what you have provided:

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

CurrentDb.Execute strSQL, dbFailOnError

HTH,
Brian


I have a NotInList Event Code. In that code I have an strSQL and
Click to expand...
I
want
it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact
Click to expand...
Person]) "
& _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) " & _
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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
Me.Contact_Person.Requery
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

Brian Bastl said:
which event are you using to issue the requery???


Christian > said:
DUDE!

I entered the following line in my code and it WORKS!:

Me.Contact_Person.Requery

Except, I get that anoying message "You must save the current field before
you run the Requery action."

Can I enter code to make it save the field before the requery?


Brian Bastl said:
Christian >,

have you set the response to acDataErrAdded? Then in the AfterUpdate event
of the first combobox, you'll need to issue a requery of the second
combobox.

HTH,
Brian


Thanks guys, I got it to work--sort of using the following code:

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

Here's the problem. This particular combo box is sync'd with another by
using a SQL statement as the 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];

So now, with my not in list code, it adds the contact to the contacts
table,
but I get an error after and it still doesn't have it in the combo box for
this field--not until i open a new form...

So I learned somehting from both of you, but I learned the wrong thing.
I'm
not even sure what to ask at this point. :|

:

Christian,

using what you have provided:

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

CurrentDb.Execute strSQL, dbFailOnError

HTH,
Brian


I have a NotInList Event Code. In that code I have an strSQL and I
want
it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person]) "
& _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company]) " & _
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
Click to expand...
 
B

Brian Bastl

with setting the response, you do not want to also issue a requery. Delete
that line.


Christian > said:
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
Me.Contact_Person.Requery
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

Brian Bastl said:
which event are you using to issue the requery???


Christian > said:
DUDE!

I entered the following line in my code and it WORKS!:

Me.Contact_Person.Requery

Except, I get that anoying message "You must save the current field before
you run the Requery action."

Can I enter code to make it save the field before the requery?


:

Christian >,

have you set the response to acDataErrAdded? Then in the AfterUpdate event
of the first combobox, you'll need to issue a requery of the second
combobox.

HTH,
Brian


Thanks guys, I got it to work--sort of using the following code:

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

Here's the problem. This particular combo box is sync'd with
Click to expand...
another
by
using a SQL statement as the 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];

So now, with my not in list code, it adds the contact to the contacts
table,
but I get an error after and it still doesn't have it in the combo
Click to expand...
box
for
this field--not until i open a new form...

So I learned somehting from both of you, but I learned the wrong thing.
I'm
not even sure what to ask at this point. :|

:

Christian,

using what you have provided:

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

CurrentDb.Execute strSQL, dbFailOnError

HTH,
Brian


I have a NotInList Event Code. In that code I have an strSQL
Click to expand...
and
I
want
it
to do two things, not just one.

Here are both of what I want to happen:

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Contact Person]) "
& _
"VALUES ('" & NewData & "');"

strSQL = "INSERT INTO [Customer Contacts Table]([CC_Company])
Click to expand...
" &
_
"VALUES (Forms![Job Work Orders Form]!Customer);"

How do I combine the above code correctly?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Associating New Data with a SQL statement 1
Not In List and Synchronized Combo Box CODE CONFLICTS 6
Need help with this NotInList code for a combo box 4
NotInList Issue 1
Sync Multiple Combo Boxes 3
Formatting 1
Error 2465; Can't Find Field 5
How do you enter a string that contain's an "'"? Is this a bug? 3

Top