Linking criteria - Not in List

D

d9pierce

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
 
D

Douglas J. Steele

Assuming you know the value of the CompanyID associated with the contact,
you need to change your INSERT INTO statement to:

strsql = "Insert Into Company_Contacts ([ContactName], [CompanyID]) " &
_
"values ('"& NewData & "', " & lngCompanyID & ")"

If you can have the CompanyID as a field on the form (it needn't be
visible), you'drefer to it (rather than lngCompanyID).

You might also want some way of adding the ContactTitle to the table.
 
D

d9pierce

Assuming you know the value of the CompanyID associated with the contact,
you need to change your INSERT INTO statement to:

strsql = "Insert Into Company_Contacts ([ContactName], [CompanyID]) " &
_
"values ('"& NewData & "', " & lngCompanyID & ")"

If you can have the CompanyID as a field on the form (it needn't be
visible), you'drefer to it (rather than lngCompanyID).

You might also want some way of adding the ContactTitle to the table.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




Hi all,
Here is an interesting one I am struggling with!
Click to expand...
I have a frm Projects_Main on which I have cboProjectMgrID. The SQL is
as follows:
Click to expand...
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:
Click to expand...
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
Click to expand...
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Click to expand...
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"
Click to expand...
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]
Click to expand...
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!
Click to expand...
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?
Click to expand...
Thanks so much,
Dave- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi and thaks for such a quick reply!
I tried this and I got an error of:
Number of query values and destination fields are not the same.

I tried both methods as I do have CompanyID on my frm Company_Contacts
and I beleive the problem is I also have CompanyID on my frm
Projects_Main but it pertains to a different CompanyName then that of
what I am trying to insert. The CompanyID it is trying to insert is
Related to the cboClientID on my main form. This cbo I am using is is
one of many on this form Projects_Main that all relate to a different
company. I have mutipul areas of where I am using cbo's to obtain
certain data relating to different types of companies and contacts. In
this paticular case I do not have a CompanyID directly linked to this
cbo on frm Projects_Main, it is just in the Qry or Sql. So basically,
it is trying to insert the frm Project_Main (CompanyID) instead of the
Sql's CompanyID on the cbo.

Any Suggestions?
Thanks,
Dave
 
D

Douglas J. Steele

What did you change it to? The number of values and destination fields agree
in the sample I gave you...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Assuming you know the value of the CompanyID associated with the contact,
you need to change your INSERT INTO statement to:

strsql = "Insert Into Company_Contacts ([ContactName], [CompanyID]) "
&
_
"values ('"& NewData & "', " & lngCompanyID & ")"

If you can have the CompanyID as a field on the form (it needn't be
visible), you'drefer to it (rather than lngCompanyID).

You might also want some way of adding the ContactTitle to the table.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




Hi all,
Here is an interesting one I am struggling with!
Click to expand...
I have a frm Projects_Main on which I have cboProjectMgrID. The SQL is
as follows:
Click to expand...
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:
Click to expand...
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
Click to expand...
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Click to expand...
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"
Click to expand...
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]
Click to expand...
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!
Click to expand...
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?
Click to expand...
Thanks so much,
Dave- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi and thaks for such a quick reply!
I tried this and I got an error of:
Number of query values and destination fields are not the same.

I tried both methods as I do have CompanyID on my frm Company_Contacts
and I beleive the problem is I also have CompanyID on my frm
Projects_Main but it pertains to a different CompanyName then that of
what I am trying to insert. The CompanyID it is trying to insert is
Related to the cboClientID on my main form. This cbo I am using is is
one of many on this form Projects_Main that all relate to a different
company. I have mutipul areas of where I am using cbo's to obtain
certain data relating to different types of companies and contacts. In
this paticular case I do not have a CompanyID directly linked to this
cbo on frm Projects_Main, it is just in the Qry or Sql. So basically,
it is trying to insert the frm Project_Main (CompanyID) instead of the
Sql's CompanyID on the cbo.

Any Suggestions?
Thanks,
Dave
Click to expand...
 
D

d9pierce

What did you change it to? The number of values and destination fields agree
in the sample I gave you...

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




Assuming you know the value of the CompanyID associated with the contact,
you need to change your INSERT INTO statement to:
strsql = "Insert Into Company_Contacts ([ContactName], [CompanyID]) "
&
_
"values ('"& NewData & "', " & lngCompanyID & ")"
If you can have the CompanyID as a field on the form (it needn't be
visible), you'drefer to it (rather than lngCompanyID).
You might also want some way of adding the ContactTitle to the table.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

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- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Hi and thaks for such a quick reply!
I tried this and I got an error of:
Number of query values and destination fields are not the same.
Click to expand...
I tried both methods as I do have CompanyID on my frm Company_Contacts
and I beleive the problem is I also have CompanyID on my frm
Projects_Main but it pertains to a different CompanyName then that of
what I am trying to insert. The CompanyID it is trying to insert is
Related to the cboClientID on my main form. This cbo I am using is is
one of many on this form Projects_Main that all relate to a different
company. I have mutipul areas of where I am using cbo's to obtain
certain data relating to different types of companies and contacts. In
this paticular case I do not have a CompanyID directly linked to this
cbo on frm Projects_Main, it is just in the Qry or Sql. So basically,
it is trying to insert the frm Project_Main (CompanyID) instead of the
Sql's CompanyID on the cbo.
Click to expand...
Any Suggestions?
Thanks,
Dave- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


Hi,
This is what I changed the code to!
Did I do something wrong?

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].[CompanyID])
" & "values ('" & NewData & "', " & CompanyID & ")"

'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
 
D

Douglas J. Steele

That's supposed to be a comma between [ContactName] and [CompanyID], not a
period.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


What did you change it to? The number of values and destination fields
agree
in the sample I gave you...

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




On Mar 6, 5:02 am, "Douglas J. Steele"
Assuming you know the value of the CompanyID associated with the
contact,
you need to change your INSERT INTO statement to:
Click to expand...
strsql = "Insert Into Company_Contacts ([ContactName],
[CompanyID]) "
&
_
"values ('"& NewData & "', " & lngCompanyID & ")"
Click to expand...
If you can have the CompanyID as a field on the form (it needn't be
visible), you'drefer to it (rather than lngCompanyID).
Click to expand...
You might also want some way of adding the ContactTitle to the table.
Click to expand...
Hi all,
Here is an interesting one I am struggling with!
Click to expand...
I have a frm Projects_Main on which I have cboProjectMgrID. The SQL
is
as follows:
Click to expand...
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:
Click to expand...
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
Click to expand...
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Click to expand...
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"
Click to expand...
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]
Click to expand...
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!
Click to expand...
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?
Click to expand...
Thanks so much,
Dave- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Hi and thaks for such a quick reply!
I tried this and I got an error of:
Number of query values and destination fields are not the same.
Click to expand...
I tried both methods as I do have CompanyID on my frm Company_Contacts
and I beleive the problem is I also have CompanyID on my frm
Projects_Main but it pertains to a different CompanyName then that of
what I am trying to insert. The CompanyID it is trying to insert is
Related to the cboClientID on my main form. This cbo I am using is is
one of many on this form Projects_Main that all relate to a different
company. I have mutipul areas of where I am using cbo's to obtain
certain data relating to different types of companies and contacts. In
this paticular case I do not have a CompanyID directly linked to this
cbo on frm Projects_Main, it is just in the Qry or Sql. So basically,
it is trying to insert the frm Project_Main (CompanyID) instead of the
Sql's CompanyID on the cbo.
Click to expand...
Any Suggestions?
Thanks,
Dave- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


Hi,
This is what I changed the code to!
Did I do something wrong?

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].[CompanyID])
" & "values ('" & NewData & "', " & CompanyID & ")"

'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
Click to expand...
 
D

d9pierce

That's supposed to be a comma between [ContactName] and [CompanyID], not a
period.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




What did you change it to? The number of values and destination fields
agree
in the sample I gave you...
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

On Mar 6, 5:02 am, "Douglas J. Steele"
Assuming you know the value of the CompanyID associated with the
contact,
you need to change your INSERT INTO statement to:
strsql = "Insert Into Company_Contacts ([ContactName],
[CompanyID]) "
&
_
"values ('"& NewData & "', " & lngCompanyID & ")"
If you can have the CompanyID as a field on the form (it needn't be
visible), you'drefer to it (rather than lngCompanyID).
You might also want some way of adding the ContactTitle to the table.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

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- Hide quoted text -
- Show quoted text -
Hi and thaks for such a quick reply!
I tried this and I got an error of:
Number of query values and destination fields are not the same.
I tried both methods as I do have CompanyID on my frm Company_Contacts
and I beleive the problem is I also have CompanyID on my frm
Projects_Main but it pertains to a different CompanyName then that of
what I am trying to insert. The CompanyID it is trying to insert is
Related to the cboClientID on my main form. This cbo I am using is is
one of many on this form Projects_Main that all relate to a different
company. I have mutipul areas of where I am using cbo's to obtain
certain data relating to different types of companies and contacts. In
this paticular case I do not have a CompanyID directly linked to this
cbo on frm Projects_Main, it is just in the Qry or Sql. So basically,
it is trying to insert the frm Project_Main (CompanyID) instead of the
Sql's CompanyID on the cbo.
Any Suggestions?
Thanks,
Dave- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Hi,
This is what I changed the code to!
Did I do something wrong?
Click to expand...
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].[CompanyID])
" & "values ('" & NewData & "', " & CompanyID & ")"
Click to expand...
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
LinkCriteria = "[ContactName] = '" & Me!ProjectMgrID.Text & "'"
DoCmd.OpenForm "Company_Contacts", , , LinkCriteria
Click to expand...
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Wow, I think we are getting close.
I changed to coma and it actually opened form but it still wants to
open it to the wrong CompanyID. It is taking my companyID from my frm
and not the CompanyID from the Sql or Qry that the cbo relates to. I
also get an error on my frm Company_Contacts when it opens that
states, "The text you entered is not an item in the lsit" , Select an
item from the list that matches or enter text that one of the listed
items. Now this text shows in the list but with the wrong CompanyID
Listed being the text was entered as a record in the wrong company. I
tried this 4 times and was given 4 new records but in the wrong
companyid. Never a dull moment!

I have no idea on this?
Thanks
Dave
 
D

Douglas J. Steele

strsql = "Insert Into Company_Contacts ([ContactName], [CompanyID])
" & "values ('" & NewData & "', " & Me.cboCompany & ")"

(or whatever your combo box is named)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That's supposed to be a comma between [ContactName] and [CompanyID], not
a
period.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




On Mar 6, 5:49 am, "Douglas J. Steele"
What did you change it to? The number of values and destination fields
agree
in the sample I gave you...
Click to expand...
On Mar 6, 5:02 am, "Douglas J. Steele"
Assuming you know the value of the CompanyID associated with the
contact,
you need to change your INSERT INTO statement to:
Click to expand...
strsql = "Insert Into Company_Contacts ([ContactName],
[CompanyID]) "
&
_
"values ('"& NewData & "', " & lngCompanyID & ")"
Click to expand...
If you can have the CompanyID as a field on the form (it needn't be
visible), you'drefer to it (rather than lngCompanyID).
Click to expand...
You might also want some way of adding the ContactTitle to the
table.
Click to expand...
Hi all,
Here is an interesting one I am struggling with!
Click to expand...
I have a frm Projects_Main on which I have cboProjectMgrID. The
SQL
is
as follows:
Click to expand...
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:
Click to expand...
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
Click to expand...
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Click to expand...
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"
Click to expand...
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]
Click to expand...
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!
Click to expand...
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?
Click to expand...
Thanks so much,
Dave- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Hi and thaks for such a quick reply!
I tried this and I got an error of:
Number of query values and destination fields are not the same.
Click to expand...
I tried both methods as I do have CompanyID on my frm
Company_Contacts
and I beleive the problem is I also have CompanyID on my frm
Projects_Main but it pertains to a different CompanyName then that
of
what I am trying to insert. The CompanyID it is trying to insert is
Related to the cboClientID on my main form. This cbo I am using is
is
one of many on this form Projects_Main that all relate to a
different
company. I have mutipul areas of where I am using cbo's to obtain
certain data relating to different types of companies and contacts.
In
this paticular case I do not have a CompanyID directly linked to
this
cbo on frm Projects_Main, it is just in the Qry or Sql. So
basically,
it is trying to insert the frm Project_Main (CompanyID) instead of
the
Sql's CompanyID on the cbo.
Click to expand...
Any Suggestions?
Thanks,
Dave- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Hi,
This is what I changed the code to!
Did I do something wrong?
Click to expand...
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].[CompanyID])
" & "values ('" & NewData & "', " & CompanyID & ")"
Click to expand...
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
LinkCriteria = "[ContactName] = '" & Me!ProjectMgrID.Text & "'"
DoCmd.OpenForm "Company_Contacts", , , LinkCriteria
Click to expand...
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Wow, I think we are getting close.
I changed to coma and it actually opened form but it still wants to
open it to the wrong CompanyID. It is taking my companyID from my frm
and not the CompanyID from the Sql or Qry that the cbo relates to. I
also get an error on my frm Company_Contacts when it opens that
states, "The text you entered is not an item in the lsit" , Select an
item from the list that matches or enter text that one of the listed
items. Now this text shows in the list but with the wrong CompanyID
Listed being the text was entered as a record in the wrong company. I
tried this 4 times and was given 4 new records but in the wrong
companyid. Never a dull moment!

I have no idea on this?
Thanks
Dave
Click to expand...
 
D

d9pierce

strsql = "Insert Into Company_Contacts ([ContactName], [CompanyID])
" & "values ('" & NewData & "', " & Me.cboCompany & ")"

(or whatever your combo box is named)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




That's supposed to be a comma between [ContactName] and [CompanyID], not
a
period.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

On Mar 6, 5:49 am, "Douglas J. Steele"
What did you change it to? The number of values and destination fields
agree
in the sample I gave you...
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

On Mar 6, 5:02 am, "Douglas J. Steele"
Assuming you know the value of the CompanyID associated with the
contact,
you need to change your INSERT INTO statement to:
strsql = "Insert Into Company_Contacts ([ContactName],
[CompanyID]) "
&
_
"values ('"& NewData & "', " & lngCompanyID & ")"
If you can have the CompanyID as a field on the form (it needn't be
visible), you'drefer to it (rather than lngCompanyID).
You might also want some way of adding the ContactTitle to the
table.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

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- Hide quoted text -
- Show quoted text -
Hi and thaks for such a quick reply!
I tried this and I got an error of:
Number of query values and destination fields are not the same.
I tried both methods as I do have CompanyID on my frm
Company_Contacts
and I beleive the problem is I also have CompanyID on my frm
Projects_Main but it pertains to a different CompanyName then that
of
what I am trying to insert. The CompanyID it is trying to insert is
Related to the cboClientID on my main form. This cbo I am using is
is
one of many on this form Projects_Main that all relate to a
different
company. I have mutipul areas of where I am using cbo's to obtain
certain data relating to different types of companies and contacts.
In
this paticular case I do not have a CompanyID directly linked to
this
cbo on frm Projects_Main, it is just in the Qry or Sql. So
basically,
it is trying to insert the frm Project_Main (CompanyID) instead of
the
Sql's CompanyID on the cbo.
Any Suggestions?
Thanks,
Dave- Hide quoted text -
- Show quoted text -
Hi,
This is what I changed the code to!
Did I do something wrong?
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].[CompanyID])
" & "values ('" & NewData & "', " & CompanyID & ")"
'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- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Wow, I think we are getting close.
I changed to coma and it actually opened form but it still wants to
open it to the wrong CompanyID. It is taking my companyID from my frm
and not the CompanyID from the Sql or Qry that the cbo relates to. I
also get an error on my frm Company_Contacts when it opens that
states, "The text you entered is not an item in the lsit" , Select an
item from the list that matches or enter text that one of the listed
items. Now this text shows in the list but with the wrong CompanyID
Listed being the text was entered as a record in the wrong company. I
tried this 4 times and was given 4 new records but in the wrong
companyid. Never a dull moment!
Click to expand...
I have no idea on this?
Thanks
Dave- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Well,
I tried this:
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],
[CompanyID]) " & "values ('" & NewData & "', " & Me.ProjectMgrID & ")"

'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
And I got the same error as I had the first time. I also tried adding
on to the "Me.ProjectMgrID" Me.ProjectMgrID.Column(1) and also tried
(3) but still gave me an error. Could this be something in the Link
Criteria =?
Thanks,
Dave
 
D

Douglas J. Steele

You're trying to handle adding a new entry made in the ProjectMgrID combo
box: you cannot refer to it to try and determine additional data!

That means your SQL statement needs to be changed, and your criteria likely
needs to be

LinkCriteria = "[ContactName]= '" & NewData & "'"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


strsql = "Insert Into Company_Contacts ([ContactName], [CompanyID])
" & "values ('" & NewData & "', " & Me.cboCompany & ")"

(or whatever your combo box is named)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




On Mar 6, 6:04 am, "Douglas J. Steele"
That's supposed to be a comma between [ContactName] and [CompanyID],
not
a
period.
Click to expand...
On Mar 6, 5:49 am, "Douglas J. Steele"
What did you change it to? The number of values and destination
fields
agree
in the sample I gave you...
Click to expand...
On Mar 6, 5:02 am, "Douglas J. Steele"
Assuming you know the value of the CompanyID associated with the
contact,
you need to change your INSERT INTO statement to:
Click to expand...
strsql = "Insert Into Company_Contacts ([ContactName],
[CompanyID]) "
&
_
"values ('"& NewData & "', " & lngCompanyID & ")"
Click to expand...
If you can have the CompanyID as a field on the form (it needn't
be
visible), you'drefer to it (rather than lngCompanyID).
Click to expand...
You might also want some way of adding the ContactTitle to the
table.
Click to expand...
Hi all,
Here is an interesting one I am struggling with!
Click to expand...
I have a frm Projects_Main on which I have cboProjectMgrID.
The
SQL
is
as follows:
Click to expand...
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:
Click to expand...
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
Click to expand...
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Click to expand...
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"
Click to expand...
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]
Click to expand...
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!
Click to expand...
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?
Click to expand...
Thanks so much,
Dave- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Hi and thaks for such a quick reply!
I tried this and I got an error of:
Number of query values and destination fields are not the same.
Click to expand...
I tried both methods as I do have CompanyID on my frm
Company_Contacts
and I beleive the problem is I also have CompanyID on my frm
Projects_Main but it pertains to a different CompanyName then
that
of
what I am trying to insert. The CompanyID it is trying to insert
is
Related to the cboClientID on my main form. This cbo I am using
is
is
one of many on this form Projects_Main that all relate to a
different
company. I have mutipul areas of where I am using cbo's to obtain
certain data relating to different types of companies and
contacts.
In
this paticular case I do not have a CompanyID directly linked to
this
cbo on frm Projects_Main, it is just in the Qry or Sql. So
basically,
it is trying to insert the frm Project_Main (CompanyID) instead
of
the
Sql's CompanyID on the cbo.
Click to expand...
Any Suggestions?
Thanks,
Dave- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Hi,
This is what I changed the code to!
Did I do something wrong?
Click to expand...
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].[CompanyID])
" & "values ('" & NewData & "', " & CompanyID & ")"
Click to expand...
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
LinkCriteria = "[ContactName] = '" & Me!ProjectMgrID.Text & "'"
DoCmd.OpenForm "Company_Contacts", , , LinkCriteria
Click to expand...
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Wow, I think we are getting close.
I changed to coma and it actually opened form but it still wants to
open it to the wrong CompanyID. It is taking my companyID from my frm
and not the CompanyID from the Sql or Qry that the cbo relates to. I
also get an error on my frm Company_Contacts when it opens that
states, "The text you entered is not an item in the lsit" , Select an
item from the list that matches or enter text that one of the listed
items. Now this text shows in the list but with the wrong CompanyID
Listed being the text was entered as a record in the wrong company. I
tried this 4 times and was given 4 new records but in the wrong
companyid. Never a dull moment!
Click to expand...
I have no idea on this?
Thanks
Dave- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Well,
I tried this:
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],
[CompanyID]) " & "values ('" & NewData & "', " & Me.ProjectMgrID & ")"

'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
And I got the same error as I had the first time. I also tried adding
on to the "Me.ProjectMgrID" Me.ProjectMgrID.Column(1) and also tried
(3) but still gave me an error. Could this be something in the Link
Criteria =?
Thanks,
Dave
Click to expand...
 
D

d9pierce

You're trying to handle adding a new entry made in the ProjectMgrID combo
box: you cannot refer to it to try and determine additional data!

That means your SQL statement needs to be changed, and your criteria likely
needs to be

LinkCriteria = "[ContactName]= '" & NewData & "'"

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




strsql = "Insert Into Company_Contacts ([ContactName], [CompanyID])
" & "values ('" & NewData & "', " & Me.cboCompany & ")"
(or whatever your combo box is named)
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

On Mar 6, 6:04 am, "Douglas J. Steele"
That's supposed to be a comma between [ContactName] and [CompanyID],
not
a
period.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

On Mar 6, 5:49 am, "Douglas J. Steele"
What did you change it to? The number of values and destination
fields
agree
in the sample I gave you...
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

On Mar 6, 5:02 am, "Douglas J. Steele"
Assuming you know the value of the CompanyID associated with the
contact,
you need to change your INSERT INTO statement to:
strsql = "Insert Into Company_Contacts ([ContactName],
[CompanyID]) "
&
_
"values ('"& NewData & "', " & lngCompanyID & ")"
If you can have the CompanyID as a field on the form (it needn't
be
visible), you'drefer to it (rather than lngCompanyID).
You might also want some way of adding the ContactTitle to the
table.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

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- Hide quoted text -
- Show quoted text -
Hi and thaks for such a quick reply!
I tried this and I got an error of:
Number of query values and destination fields are not the same.
I tried both methods as I do have CompanyID on my frm
Company_Contacts
and I beleive the problem is I also have CompanyID on my frm
Projects_Main but it pertains to a different CompanyName then
that
of
what I am trying to insert. The CompanyID it is trying to insert
is
Related to the cboClientID on my main form. This cbo I am using
is
is
one of many on this form Projects_Main that all relate to a
different
company. I have mutipul areas of where I am using cbo's to obtain
certain data relating to different types of companies and
contacts.
In
this paticular case I do not have a CompanyID directly linked to
this
cbo on frm Projects_Main, it is just in the Qry or Sql. So
basically,
it is trying to insert the frm Project_Main (CompanyID) instead
of
the
Sql's CompanyID on the cbo.
Any Suggestions?
Thanks,
Dave- Hide quoted text -
- Show quoted text -
Hi,
This is what I changed the code to!
Did I do something wrong?
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].[CompanyID])
" & "values ('" & NewData & "', " & CompanyID & ")"
'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- Hide quoted text -
- Show quoted text -
Wow, I think we are getting close.
I changed to coma and it actually opened form but it still wants to
open it to the wrong CompanyID. It is taking my companyID from my frm
and not the CompanyID from the Sql or Qry that the cbo relates to. I
also get an error on my frm Company_Contacts when it opens that
states, "The text you entered is not an item in the lsit" , Select an
item from the list that matches or enter text that one of the listed
items. Now this text shows in the list but with the wrong CompanyID
Listed being the text was entered as a record in the wrong company. I
tried this 4 times and was given 4 new records but in the wrong
companyid. Never a dull moment!
I have no idea on this?
Thanks
Dave- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Well,
I tried this:
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],
[CompanyID]) " & "values ('" & NewData & "', " & Me.ProjectMgrID & ")"
Click to expand...
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
LinkCriteria = "[ContactName]= '" & Me!ProjectMgrID.Text & "'"
DoCmd.OpenForm "Company_Contacts", , , LinkCriteria
Click to expand...
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
And I got the same error as I had the first time. I also tried adding
on to the "Me.ProjectMgrID" Me.ProjectMgrID.Column(1) and also tried
(3) but still gave me an error. Could this be something in the Link
Criteria =?
Thanks,
Dave- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Well Sir,
i finally got it by adding some unbound fields to form and setting the
control source to the cbo column()
Wasnt clean but will work on it tonight.
just a big thanks for your help as i couldnt of done it without your
help!
Thanks a million
Dave
 

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

Not on list event 1
combo box not in list trouble 7
Not in the List Code Question 3
Not in List Event 4
not in list open a form 15
Help with Not in the list (combobox) 1
Not In List Function 1
ComboBox Not In List - Help needed 2

Top