Not In List Error Msg

[Guest]

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

 

[Guest]

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

Klatuu,

Thanks for the prompt reply. I inserted Me.Requery and
Me.ContactNameCombo.Requery between the DoCmd.OpenForm line and the Response=
line and after the Response=line (all at separate times, of course). After
each time, the pop up form will open and when I enter phone number, etc., and
try to close it blinks but will not close.

If you have any further suggestions, I would appreciate them.
Thanks for your help,
Pam

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

Is the PopUp form Modal?
It needs to be. If it is not, the code in the calling form doesn't pause,
so the new record wont be in the table yet. A Modal form will cause the code
in the calling form to stop until the the modal form is closed.

Klatuu,

Thanks for the prompt reply. I inserted Me.Requery and
Me.ContactNameCombo.Requery between the DoCmd.OpenForm line and the Response=
line and after the Response=line (all at separate times, of course). After
each time, the pop up form will open and when I enter phone number, etc., and
try to close it blinks but will not close.

If you have any further suggestions, I would appreciate them.
Thanks for your help,
Pam

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

:

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

I changed it to Modal and it's still doing the same thing.

Is the PopUp form Modal?
It needs to be. If it is not, the code in the calling form doesn't pause,
so the new record wont be in the table yet. A Modal form will cause the code
in the calling form to stop until the the modal form is closed.

Klatuu,

Thanks for the prompt reply. I inserted Me.Requery and
Me.ContactNameCombo.Requery between the DoCmd.OpenForm line and the Response=
line and after the Response=line (all at separate times, of course). After
each time, the pop up form will open and when I enter phone number, etc., and
try to close it blinks but will not close.

If you have any further suggestions, I would appreciate them.
Thanks for your help,
Pam

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

:

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

:

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

Klatuu,

The form is now closing after I enter additional info, but then I get msg
"text not in list". I just know it has something to do with the customers
being on one table and the contacts on another. I'm basing the Contact Combo
off of a query joins them together on the main screen. The contact hasn't
been joined to this customer yet and it's not putting them in the list, but
I'm not sure how to fix it.

Is the PopUp form Modal?
It needs to be. If it is not, the code in the calling form doesn't pause,
so the new record wont be in the table yet. A Modal form will cause the code
in the calling form to stop until the the modal form is closed.

Klatuu,

Thanks for the prompt reply. I inserted Me.Requery and
Me.ContactNameCombo.Requery between the DoCmd.OpenForm line and the Response=
line and after the Response=line (all at separate times, of course). After
each time, the pop up form will open and when I enter phone number, etc., and
try to close it blinks but will not close.

If you have any further suggestions, I would appreciate them.
Thanks for your help,
Pam

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

:

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

:

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

Is the contact table a child table to the customer table?

It is unusual to do a join in a combo.

Klatuu,

The form is now closing after I enter additional info, but then I get msg
"text not in list". I just know it has something to do with the customers
being on one table and the contacts on another. I'm basing the Contact Combo
off of a query joins them together on the main screen. The contact hasn't
been joined to this customer yet and it's not putting them in the list, but
I'm not sure how to fix it.

Is the PopUp form Modal?
It needs to be. If it is not, the code in the calling form doesn't pause,
so the new record wont be in the table yet. A Modal form will cause the code
in the calling form to stop until the the modal form is closed.

Klatuu,

Thanks for the prompt reply. I inserted Me.Requery and
Me.ContactNameCombo.Requery between the DoCmd.OpenForm line and the Response=
line and after the Response=line (all at separate times, of course). After
each time, the pop up form will open and when I enter phone number, etc., and
try to close it blinks but will not close.

If you have any further suggestions, I would appreciate them.
Thanks for your help,
Pam

:

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

:

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

:

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

They are two separate tables. Should I not base the combo on the query of
the two tables?

Is the contact table a child table to the customer table?

It is unusual to do a join in a combo.

Klatuu,

The form is now closing after I enter additional info, but then I get msg
"text not in list". I just know it has something to do with the customers
being on one table and the contacts on another. I'm basing the Contact Combo
off of a query joins them together on the main screen. The contact hasn't
been joined to this customer yet and it's not putting them in the list, but
I'm not sure how to fix it.

Is the PopUp form Modal?
It needs to be. If it is not, the code in the calling form doesn't pause,
so the new record wont be in the table yet. A Modal form will cause the code
in the calling form to stop until the the modal form is closed.

:

Klatuu,

Thanks for the prompt reply. I inserted Me.Requery and
Me.ContactNameCombo.Requery between the DoCmd.OpenForm line and the Response=
line and after the Response=line (all at separate times, of course). After
each time, the pop up form will open and when I enter phone number, etc., and
try to close it blinks but will not close.

If you have any further suggestions, I would appreciate them.
Thanks for your help,
Pam

:

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

:

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

:

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

I don't think you need to. Normally, what you do in the Contact combo is to
use the foreign key field that relates to the Customer primary key field and
filter the row source on that. That is why you requery the contact combo, is
to show only those that match:

SELECT ContactID, CustomerId FROM ContactTable WHERE CustomerID =
Me.cboCustomer;

This asumes that Me.cboCustomer contains the primary key value for the
customer selected in the Customer combo and that CustomerID is a field in the
Contact table that relates it to the customer table.

They are two separate tables. Should I not base the combo on the query of
the two tables?

Is the contact table a child table to the customer table?

It is unusual to do a join in a combo.

Klatuu,

The form is now closing after I enter additional info, but then I get msg
"text not in list". I just know it has something to do with the customers
being on one table and the contacts on another. I'm basing the Contact Combo
off of a query joins them together on the main screen. The contact hasn't
been joined to this customer yet and it's not putting them in the list, but
I'm not sure how to fix it.

:

Is the PopUp form Modal?
It needs to be. If it is not, the code in the calling form doesn't pause,
so the new record wont be in the table yet. A Modal form will cause the code
in the calling form to stop until the the modal form is closed.

:

Klatuu,

Thanks for the prompt reply. I inserted Me.Requery and
Me.ContactNameCombo.Requery between the DoCmd.OpenForm line and the Response=
line and after the Response=line (all at separate times, of course). After
each time, the pop up form will open and when I enter phone number, etc., and
try to close it blinks but will not close.

If you have any further suggestions, I would appreciate them.
Thanks for your help,
Pam

:

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

:

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

:

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

"and that CustomerID is a field in the Contact table that relates it to the
customer table." -it doesn't. The contact table only has record number,
name, phone, fax, and email. There is no reference to the customer. The only
way they are referenced together is by the queries for the forms they are
used on. But tomorrow there will be and I will use the info you provided. I
hope you check this thread in case I run into more problems along the way.
Thanks again,
Pam

I don't think you need to. Normally, what you do in the Contact combo is to
use the foreign key field that relates to the Customer primary key field and
filter the row source on that. That is why you requery the contact combo, is
to show only those that match:

SELECT ContactID, CustomerId FROM ContactTable WHERE CustomerID =
Me.cboCustomer;

This asumes that Me.cboCustomer contains the primary key value for the
customer selected in the Customer combo and that CustomerID is a field in the
Contact table that relates it to the customer table.

They are two separate tables. Should I not base the combo on the query of
the two tables?

Is the contact table a child table to the customer table?

It is unusual to do a join in a combo.

:

Klatuu,

The form is now closing after I enter additional info, but then I get msg
"text not in list". I just know it has something to do with the customers
being on one table and the contacts on another. I'm basing the Contact Combo
off of a query joins them together on the main screen. The contact hasn't
been joined to this customer yet and it's not putting them in the list, but
I'm not sure how to fix it.

:

Is the PopUp form Modal?
It needs to be. If it is not, the code in the calling form doesn't pause,
so the new record wont be in the table yet. A Modal form will cause the code
in the calling form to stop until the the modal form is closed.

:

Klatuu,

Thanks for the prompt reply. I inserted Me.Requery and
Me.ContactNameCombo.Requery between the DoCmd.OpenForm line and the Response=
line and after the Response=line (all at separate times, of course). After
each time, the pop up form will open and when I enter phone number, etc., and
try to close it blinks but will not close.

If you have any further suggestions, I would appreciate them.
Thanks for your help,
Pam

:

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

:

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

:

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

I'll be here.
You are doing the right thing. If the contacts belong to customers, then
therer should be fields to link the two together.

One other thing to keep in mind. When you create a new contact record in
your popup form, you will want to include the customer record primary key.
There are a couple of ways you can to that. One is to pass the value in the
OpenArgs of the form. The other is for the popup form to reference the
control on the other form to populate the field.

"and that CustomerID is a field in the Contact table that relates it to the
customer table." -it doesn't. The contact table only has record number,
name, phone, fax, and email. There is no reference to the customer. The only
way they are referenced together is by the queries for the forms they are
used on. But tomorrow there will be and I will use the info you provided. I
hope you check this thread in case I run into more problems along the way.
Thanks again,
Pam

I don't think you need to. Normally, what you do in the Contact combo is to
use the foreign key field that relates to the Customer primary key field and
filter the row source on that. That is why you requery the contact combo, is
to show only those that match:

SELECT ContactID, CustomerId FROM ContactTable WHERE CustomerID =
Me.cboCustomer;

This asumes that Me.cboCustomer contains the primary key value for the
customer selected in the Customer combo and that CustomerID is a field in the
Contact table that relates it to the customer table.

They are two separate tables. Should I not base the combo on the query of
the two tables?

:

Is the contact table a child table to the customer table?

It is unusual to do a join in a combo.

:

Klatuu,

The form is now closing after I enter additional info, but then I get msg
"text not in list". I just know it has something to do with the customers
being on one table and the contacts on another. I'm basing the Contact Combo
off of a query joins them together on the main screen. The contact hasn't
been joined to this customer yet and it's not putting them in the list, but
I'm not sure how to fix it.

:

Is the PopUp form Modal?
It needs to be. If it is not, the code in the calling form doesn't pause,
so the new record wont be in the table yet. A Modal form will cause the code
in the calling form to stop until the the modal form is closed.

:

Klatuu,

Thanks for the prompt reply. I inserted Me.Requery and
Me.ContactNameCombo.Requery between the DoCmd.OpenForm line and the Response=
line and after the Response=line (all at separate times, of course). After
each time, the pop up form will open and when I enter phone number, etc., and
try to close it blinks but will not close.

If you have any further suggestions, I would appreciate them.
Thanks for your help,
Pam

:

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

:

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

:

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

That will be my challenge for tomorrow. Until then...

I'll be here.
You are doing the right thing. If the contacts belong to customers, then
therer should be fields to link the two together.

One other thing to keep in mind. When you create a new contact record in
your popup form, you will want to include the customer record primary key.
There are a couple of ways you can to that. One is to pass the value in the
OpenArgs of the form. The other is for the popup form to reference the
control on the other form to populate the field.

"and that CustomerID is a field in the Contact table that relates it to the
customer table." -it doesn't. The contact table only has record number,
name, phone, fax, and email. There is no reference to the customer. The only
way they are referenced together is by the queries for the forms they are
used on. But tomorrow there will be and I will use the info you provided. I
hope you check this thread in case I run into more problems along the way.
Thanks again,
Pam

I don't think you need to. Normally, what you do in the Contact combo is to
use the foreign key field that relates to the Customer primary key field and
filter the row source on that. That is why you requery the contact combo, is
to show only those that match:

SELECT ContactID, CustomerId FROM ContactTable WHERE CustomerID =
Me.cboCustomer;

This asumes that Me.cboCustomer contains the primary key value for the
customer selected in the Customer combo and that CustomerID is a field in the
Contact table that relates it to the customer table.

:

They are two separate tables. Should I not base the combo on the query of
the two tables?

:

Is the contact table a child table to the customer table?

It is unusual to do a join in a combo.

:

Klatuu,

The form is now closing after I enter additional info, but then I get msg
"text not in list". I just know it has something to do with the customers
being on one table and the contacts on another. I'm basing the Contact Combo
off of a query joins them together on the main screen. The contact hasn't
been joined to this customer yet and it's not putting them in the list, but
I'm not sure how to fix it.

:

Is the PopUp form Modal?
It needs to be. If it is not, the code in the calling form doesn't pause,
so the new record wont be in the table yet. A Modal form will cause the code
in the calling form to stop until the the modal form is closed.

:

Klatuu,

Thanks for the prompt reply. I inserted Me.Requery and
Me.ContactNameCombo.Requery between the DoCmd.OpenForm line and the Response=
line and after the Response=line (all at separate times, of course). After
each time, the pop up form will open and when I enter phone number, etc., and
try to close it blinks but will not close.

If you have any further suggestions, I would appreciate them.
Thanks for your help,
Pam

:

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

:

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

:

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

Klatuu,
I have my main entry form "fGeneralInfo" based on a query of the
tGeneralInfo, tContacts, and tCustomers. tGeneralInfo has fields ContactName
and CustomerName which is joined to the same fields on the respective tables.
The other info from each table-address, phone, etc.-is also on the design
grid of this query. I set a CustomerID field on the Customer table and a
ContactID on the Contact table. I also included the CustomerID on the
Contact table. When I join tGeneralInfo.ContactID to tContacts.ContactID and
the same for customers, the query shows no lines, only headings and my form
is blank. When I change things around to get it to show, the addresses and
phone numbers from each table aren't filled in. For some reason, it's not
joining properly. Will you help with this?

Thanks,
Pam

I'll be here.
You are doing the right thing. If the contacts belong to customers, then
therer should be fields to link the two together.

One other thing to keep in mind. When you create a new contact record in
your popup form, you will want to include the customer record primary key.
There are a couple of ways you can to that. One is to pass the value in the
OpenArgs of the form. The other is for the popup form to reference the
control on the other form to populate the field.

"and that CustomerID is a field in the Contact table that relates it to the
customer table." -it doesn't. The contact table only has record number,
name, phone, fax, and email. There is no reference to the customer. The only
way they are referenced together is by the queries for the forms they are
used on. But tomorrow there will be and I will use the info you provided. I
hope you check this thread in case I run into more problems along the way.
Thanks again,
Pam

I don't think you need to. Normally, what you do in the Contact combo is to
use the foreign key field that relates to the Customer primary key field and
filter the row source on that. That is why you requery the contact combo, is
to show only those that match:

SELECT ContactID, CustomerId FROM ContactTable WHERE CustomerID =
Me.cboCustomer;

This asumes that Me.cboCustomer contains the primary key value for the
customer selected in the Customer combo and that CustomerID is a field in the
Contact table that relates it to the customer table.

:

They are two separate tables. Should I not base the combo on the query of
the two tables?

:

Is the contact table a child table to the customer table?

It is unusual to do a join in a combo.

:

Klatuu,

The form is now closing after I enter additional info, but then I get msg
"text not in list". I just know it has something to do with the customers
being on one table and the contacts on another. I'm basing the Contact Combo
off of a query joins them together on the main screen. The contact hasn't
been joined to this customer yet and it's not putting them in the list, but
I'm not sure how to fix it.

:

Is the PopUp form Modal?
It needs to be. If it is not, the code in the calling form doesn't pause,
so the new record wont be in the table yet. A Modal form will cause the code
in the calling form to stop until the the modal form is closed.

:

Klatuu,

Thanks for the prompt reply. I inserted Me.Requery and
Me.ContactNameCombo.Requery between the DoCmd.OpenForm line and the Response=
line and after the Response=line (all at separate times, of course). After
each time, the pop up form will open and when I enter phone number, etc., and
try to close it blinks but will not close.

If you have any further suggestions, I would appreciate them.
Thanks for your help,
Pam

:

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

:

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

:

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam

 

[Guest]

I figured out that I need the ContactID and CustomerID to update in the
tGeneralInfo. I tried an update query and could not get it to work. Is
there code to do this?

I'll be here.
You are doing the right thing. If the contacts belong to customers, then
therer should be fields to link the two together.

One other thing to keep in mind. When you create a new contact record in
your popup form, you will want to include the customer record primary key.
There are a couple of ways you can to that. One is to pass the value in the
OpenArgs of the form. The other is for the popup form to reference the
control on the other form to populate the field.

"and that CustomerID is a field in the Contact table that relates it to the
customer table." -it doesn't. The contact table only has record number,
name, phone, fax, and email. There is no reference to the customer. The only
way they are referenced together is by the queries for the forms they are
used on. But tomorrow there will be and I will use the info you provided. I
hope you check this thread in case I run into more problems along the way.
Thanks again,
Pam

I don't think you need to. Normally, what you do in the Contact combo is to
use the foreign key field that relates to the Customer primary key field and
filter the row source on that. That is why you requery the contact combo, is
to show only those that match:

SELECT ContactID, CustomerId FROM ContactTable WHERE CustomerID =
Me.cboCustomer;

This asumes that Me.cboCustomer contains the primary key value for the
customer selected in the Customer combo and that CustomerID is a field in the
Contact table that relates it to the customer table.

:

They are two separate tables. Should I not base the combo on the query of
the two tables?

:

Is the contact table a child table to the customer table?

It is unusual to do a join in a combo.

:

Klatuu,

The form is now closing after I enter additional info, but then I get msg
"text not in list". I just know it has something to do with the customers
being on one table and the contacts on another. I'm basing the Contact Combo
off of a query joins them together on the main screen. The contact hasn't
been joined to this customer yet and it's not putting them in the list, but
I'm not sure how to fix it.

:

Is the PopUp form Modal?
It needs to be. If it is not, the code in the calling form doesn't pause,
so the new record wont be in the table yet. A Modal form will cause the code
in the calling form to stop until the the modal form is closed.

:

Klatuu,

Thanks for the prompt reply. I inserted Me.Requery and
Me.ContactNameCombo.Requery between the DoCmd.OpenForm line and the Response=
line and after the Response=line (all at separate times, of course). After
each time, the pop up form will open and when I enter phone number, etc., and
try to close it blinks but will not close.

If you have any further suggestions, I would appreciate them.
Thanks for your help,
Pam

:

I think you need to requery the contact combo in the Not In List event after
you have completed the Popup.

:

I just realized that the row source for the ContactNameCombo is now based on
a query that joins the customer names and contact names. So unless I already
have a case where the two have been joined, is this possible?

:

Hi,
I have two combo boxes on a form - CustomerNameCombo and ContactNameCombo.
Originally, it was set up to list names from drop down for both boxes. When
the name wasn't in the list, another form would automatically open with the
new typed name entered in the name field and then all other info would be
added. When user closed pop up form, the info was added to the main form.
Then I decided to limit the contact names to those related to each customer
name.

The pop up form opens when new entry is typed, I can enter all other info,
but when I close this form, I get an error message that the value is not in
the list. Also, on this line of code

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

I get a Compile Error Msg - "Argument Not Optional".

All code associated with each combo box is listed below. If anyone can help
resolve this, it will be greatly appreciated. I've searched previous posts,
but can't find any fixes.

Private Sub ContactNameCombo_AfterUpdate()
Me.Requery

End Sub

Private Sub ContactNameCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
stLinkCriteria = "[ContactName]=" & "'" & Me.ContactNameCombo & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog, NewData

Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

Private Sub CustomerNameCombo_AfterUpdate()

Me.ContactNameCombo.Requery

Thanks in advance for any help!
Pam