not in list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I know there is a lot of posting here about this topic but I couldn't find a
good solution.

I will try to clear the situation. I have one form with 3 comboboxes that
are all connected to each other with queries But now i would like to enter a
new value in the second combobox project with the following code:

Private Sub txt_klant_NotInList(NewData As String, Response As Integer)
Dim msg As String, answer As Byte
msg = "Mag " & NewData & " toegevoegd worden aan de lijst?"
answer = MsgBox(msg, vbYesNo + vbQuestion)
If answer = vbYes Then

Dim db As Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblproject", dbOpenDynaset)

With rs
.AddNew
![txtproject] = NewData
.Update
End With

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


The value is added in the table but i get the error that the value is no
item in the list.

That's strange because in the first combobox the data comes directly from
the table. But in the second combobox I use a query because the information
has to be linked with the data from the first combobox.

I use the following query for the first combobox:

SELECT DISTINCT tblklant.txtklant
FROM tblklant
ORDER BY tblklant.txtklant;

I use the following query for the second combobox:

SELECT DISTINCT tblproject.txtproject
FROM Qinvoeren_onderdeel_gegevens_project INNER JOIN tblproject ON
Qinvoeren_onderdeel_gegevens_project.txt_Project = tblproject.txtproject
ORDER BY tblproject.txtproject;

And I think that this query wil be the problem. But I need to write data in
this table without any errors if possible.

Can somebody tell me how?

I hope i'm clear if not let me know please, because I strugle for a long
time with this form.

Thanks a lot so far.

Thunder
 
You have to requery the combobox before you leave the Procedure

Basically i write it like this

private sub txt_Klant_NotInList(NewData as stringm Response as integer)
Dim db As Database
Dim rs As DAO.Recordset

txt_Klant.Undo
if msgbox("xxx",vbyesno)=vbyes then
Set rs = CurrentDb.OpenRecordset("tblproject", dbOpenDynaset)
With rs
.AddNew
![txtproject] = NewData
.Update
End With
txt_Klant.requery
txt_klant=rs("ID")
rs.close
set rs=nothing
Response = acDataErrAdded
else
Response = acDataErrContinue
endif

End Sub
 
JaRa said:
You have to requery the combobox before you leave the Procedure
Click to expand...


You do not have to requery the combo box. In fact, it would
be a waste of time since the line:
Response = acDataErrAdded
tells Access to automatically requery the combo box and
check the item again.
 
H. v.d. Bunte said:
I know there is a lot of posting here about this topic but I couldn't find a
good solution.

I will try to clear the situation. I have one form with 3 comboboxes that
are all connected to each other with queries But now i would like to enter a
new value in the second combobox project with the following code:

Private Sub txt_klant_NotInList(NewData As String, Response As Integer)
Dim msg As String, answer As Byte
msg = "Mag " & NewData & " toegevoegd worden aan de lijst?"
answer = MsgBox(msg, vbYesNo + vbQuestion)
If answer = vbYes Then

Dim db As Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblproject", dbOpenDynaset)

With rs
.AddNew
![txtproject] = NewData
.Update
End With

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


The value is added in the table but i get the error that the value is no
item in the list.

That's strange because in the first combobox the data comes directly from
the table. But in the second combobox I use a query because the information
has to be linked with the data from the first combobox.

I use the following query for the first combobox:

SELECT DISTINCT tblklant.txtklant
FROM tblklant
ORDER BY tblklant.txtklant;

I use the following query for the second combobox:

SELECT DISTINCT tblproject.txtproject
FROM Qinvoeren_onderdeel_gegevens_project INNER JOIN tblproject ON
Qinvoeren_onderdeel_gegevens_project.txt_Project = tblproject.txtproject
ORDER BY tblproject.txtproject;

And I think that this query wil be the problem. But I need to write data in
this table without any errors if possible.
Click to expand...


I think the problem might be because there is no matching
record in the other table, but I have no idea how
Qinvoeren_onderdeel_gegevens_project fits into this
situation.
 
But why do I get the error that the item is not in the list?

Is that because i use a query underneath this combobox?

hvdbunte

"Marshall Barton" schreef:
 
Is there a possibility that there is a criterium set in this query which is
not fulfilled by adding the new value?

You can check this by closing the form and opening it again. if the newly
added value is then shown in the combobox then it's not the case otherwise
this is causing the problem.

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


Marshall Barton said:
H. v.d. Bunte said:
I know there is a lot of posting here about this topic but I couldn't find a
good solution.

I will try to clear the situation. I have one form with 3 comboboxes that
are all connected to each other with queries But now i would like to enter a
new value in the second combobox project with the following code:

Private Sub txt_klant_NotInList(NewData As String, Response As Integer)
Dim msg As String, answer As Byte
msg = "Mag " & NewData & " toegevoegd worden aan de lijst?"
answer = MsgBox(msg, vbYesNo + vbQuestion)
If answer = vbYes Then

Dim db As Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblproject", dbOpenDynaset)

With rs
.AddNew
![txtproject] = NewData
.Update
End With

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


The value is added in the table but i get the error that the value is no
item in the list.

That's strange because in the first combobox the data comes directly from
the table. But in the second combobox I use a query because the information
has to be linked with the data from the first combobox.

I use the following query for the first combobox:

SELECT DISTINCT tblklant.txtklant
FROM tblklant
ORDER BY tblklant.txtklant;

I use the following query for the second combobox:

SELECT DISTINCT tblproject.txtproject
FROM Qinvoeren_onderdeel_gegevens_project INNER JOIN tblproject ON
Qinvoeren_onderdeel_gegevens_project.txt_Project = tblproject.txtproject
ORDER BY tblproject.txtproject;

And I think that this query wil be the problem. But I need to write data in
this table without any errors if possible.
Click to expand...


I think the problem might be because there is no matching
record in the other table, but I have no idea how
Qinvoeren_onderdeel_gegevens_project fits into this
situation.
Click to expand...
 
That is the problem indeed.

But how do I solve it. I will try to explain what I would like to achieve.

I have a form with two comboboxes. The first one is the conbobox costumers.
The second one is the combobox project.

When I select the costumer I would like to select or add a project for that
costumer. But i use a query underneath the second combobox with the value
from the first one. So that i can select the right values for the combobox
project

I hope somebody can give me a solution how I can make this form work with
two comboboxes.

Thanks for your help so far.

JaRa said:
Is there a possibility that there is a criterium set in this query which is
not fulfilled by adding the new value?

You can check this by closing the form and opening it again. if the newly
added value is then shown in the combobox then it's not the case otherwise
this is causing the problem.

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


Marshall Barton said:
H. v.d. Bunte said:
I know there is a lot of posting here about this topic but I couldn't find a
good solution.

I will try to clear the situation. I have one form with 3 comboboxes that
are all connected to each other with queries But now i would like to enter a
new value in the second combobox project with the following code:

Private Sub txt_klant_NotInList(NewData As String, Response As Integer)
Dim msg As String, answer As Byte
msg = "Mag " & NewData & " toegevoegd worden aan de lijst?"
answer = MsgBox(msg, vbYesNo + vbQuestion)
If answer = vbYes Then

Dim db As Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblproject", dbOpenDynaset)

With rs
.AddNew
![txtproject] = NewData
.Update
End With

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


The value is added in the table but i get the error that the value is no
item in the list.

That's strange because in the first combobox the data comes directly from
the table. But in the second combobox I use a query because the information
has to be linked with the data from the first combobox.

I use the following query for the first combobox:

SELECT DISTINCT tblklant.txtklant
FROM tblklant
ORDER BY tblklant.txtklant;

I use the following query for the second combobox:

SELECT DISTINCT tblproject.txtproject
FROM Qinvoeren_onderdeel_gegevens_project INNER JOIN tblproject ON
Qinvoeren_onderdeel_gegevens_project.txt_Project = tblproject.txtproject
ORDER BY tblproject.txtproject;

And I think that this query wil be the problem. But I need to write data in
this table without any errors if possible.
Click to expand...


I think the problem might be because there is no matching
record in the other table, but I have no idea how
Qinvoeren_onderdeel_gegevens_project fits into this
situation.
Click to expand...
Click to expand...
 
Can you please provide me with the structure of the table for which you add a
record and also the sql statements of the queries which you use to populate
the comboboxes.

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


H. v.d. Bunte said:
That is the problem indeed.

But how do I solve it. I will try to explain what I would like to achieve.

I have a form with two comboboxes. The first one is the conbobox costumers.
The second one is the combobox project.

When I select the costumer I would like to select or add a project for that
costumer. But i use a query underneath the second combobox with the value
from the first one. So that i can select the right values for the combobox
project

I hope somebody can give me a solution how I can make this form work with
two comboboxes.

Thanks for your help so far.

JaRa said:
Is there a possibility that there is a criterium set in this query which is
not fulfilled by adding the new value?

You can check this by closing the form and opening it again. if the newly
added value is then shown in the combobox then it's not the case otherwise
this is causing the problem.

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


Marshall Barton said:
H. v.d. Bunte wrote:
I know there is a lot of posting here about this topic but I couldn't find a
good solution.

I will try to clear the situation. I have one form with 3 comboboxes that
are all connected to each other with queries But now i would like to enter a
new value in the second combobox project with the following code:

Private Sub txt_klant_NotInList(NewData As String, Response As Integer)
Dim msg As String, answer As Byte
msg = "Mag " & NewData & " toegevoegd worden aan de lijst?"
answer = MsgBox(msg, vbYesNo + vbQuestion)
If answer = vbYes Then

Dim db As Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblproject", dbOpenDynaset)

With rs
.AddNew
![txtproject] = NewData
.Update
End With

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


The value is added in the table but i get the error that the value is no
item in the list.

That's strange because in the first combobox the data comes directly from
the table. But in the second combobox I use a query because the information
has to be linked with the data from the first combobox.

I use the following query for the first combobox:

SELECT DISTINCT tblklant.txtklant
FROM tblklant
ORDER BY tblklant.txtklant;

I use the following query for the second combobox:

SELECT DISTINCT tblproject.txtproject
FROM Qinvoeren_onderdeel_gegevens_project INNER JOIN tblproject ON
Qinvoeren_onderdeel_gegevens_project.txt_Project = tblproject.txtproject
ORDER BY tblproject.txtproject;

And I think that this query wil be the problem. But I need to write data in
this table without any errors if possible.


I think the problem might be because there is no matching
record in the other table, but I have no idea how
Qinvoeren_onderdeel_gegevens_project fits into this
situation.
Click to expand...
Click to expand...
Click to expand...
 
Please and also the structure of tblProject.

I think the solution will be something like this but
so you should have 2 comboboxes namely:
cmbCustomer and cmbProject

cmbCustomer is populated with the query Customer_Select which contains the
sql statement : SELECT Customer_ID, Customer_Name FROM Customers ORDER BY
Customer_Name

cmbProject is populated with the query Project_Select which contains the sql
statement : SELECT Project_ID, Project_Name FROM Projects WHERE
Project_Customer_ID=Forms("YourForm").cmbCustomer ORDER BY Customer_Name

the not in list statement should then be attached to cmbProject
and the insert of the record would become then

With rs
.AddNew
!Project_Name = NewData
!Project_Customer_ID=cmbCustomer
.Update
End With


--
- Raoul Jacobs

The nature of developping is sharing knowledge.


H. v.d. Bunte said:
That is the problem indeed.

But how do I solve it. I will try to explain what I would like to achieve.

I have a form with two comboboxes. The first one is the conbobox costumers.
The second one is the combobox project.

When I select the costumer I would like to select or add a project for that
costumer. But i use a query underneath the second combobox with the value
from the first one. So that i can select the right values for the combobox
project

I hope somebody can give me a solution how I can make this form work with
two comboboxes.

Thanks for your help so far.

JaRa said:
Is there a possibility that there is a criterium set in this query which is
not fulfilled by adding the new value?

You can check this by closing the form and opening it again. if the newly
added value is then shown in the combobox then it's not the case otherwise
this is causing the problem.

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


Marshall Barton said:
H. v.d. Bunte wrote:
I know there is a lot of posting here about this topic but I couldn't find a
good solution.

I will try to clear the situation. I have one form with 3 comboboxes that
are all connected to each other with queries But now i would like to enter a
new value in the second combobox project with the following code:

Private Sub txt_klant_NotInList(NewData As String, Response As Integer)
Dim msg As String, answer As Byte
msg = "Mag " & NewData & " toegevoegd worden aan de lijst?"
answer = MsgBox(msg, vbYesNo + vbQuestion)
If answer = vbYes Then

Dim db As Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblproject", dbOpenDynaset)

With rs
.AddNew
![txtproject] = NewData
.Update
End With

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


The value is added in the table but i get the error that the value is no
item in the list.

That's strange because in the first combobox the data comes directly from
the table. But in the second combobox I use a query because the information
has to be linked with the data from the first combobox.

I use the following query for the first combobox:

SELECT DISTINCT tblklant.txtklant
FROM tblklant
ORDER BY tblklant.txtklant;

I use the following query for the second combobox:

SELECT DISTINCT tblproject.txtproject
FROM Qinvoeren_onderdeel_gegevens_project INNER JOIN tblproject ON
Qinvoeren_onderdeel_gegevens_project.txt_Project = tblproject.txtproject
ORDER BY tblproject.txtproject;

And I think that this query wil be the problem. But I need to write data in
this table without any errors if possible.


I think the problem might be because there is no matching
record in the other table, but I have no idea how
Qinvoeren_onderdeel_gegevens_project fits into this
situation.
Click to expand...
Click to expand...
Click to expand...
 
That is exacly the way I created my form and my tables. I only don't use ID's
because i have unique projects and costumers.

But i think i did it the same way as you discribe below and i still get the
error that I have to choose a value from the list.

But maybe it's possible for you to make a database and see of it works.
Because i don't understand what i do wrong.

Thanks for your help so far.

"JaRa" schreef:
Please and also the structure of tblProject.

I think the solution will be something like this but
so you should have 2 comboboxes namely:
cmbCustomer and cmbProject

cmbCustomer is populated with the query Customer_Select which contains the
sql statement : SELECT Customer_ID, Customer_Name FROM Customers ORDER BY
Customer_Name

cmbProject is populated with the query Project_Select which contains the sql
statement : SELECT Project_ID, Project_Name FROM Projects WHERE
Project_Customer_ID=Forms("YourForm").cmbCustomer ORDER BY Customer_Name

the not in list statement should then be attached to cmbProject
and the insert of the record would become then

With rs
.AddNew
!Project_Name = NewData
!Project_Customer_ID=cmbCustomer
.Update
End With


--
- Raoul Jacobs

The nature of developping is sharing knowledge.


H. v.d. Bunte said:
That is the problem indeed.

But how do I solve it. I will try to explain what I would like to achieve.

I have a form with two comboboxes. The first one is the conbobox costumers.
The second one is the combobox project.

When I select the costumer I would like to select or add a project for that
costumer. But i use a query underneath the second combobox with the value
from the first one. So that i can select the right values for the combobox
project

I hope somebody can give me a solution how I can make this form work with
two comboboxes.

Thanks for your help so far.

JaRa said:
Is there a possibility that there is a criterium set in this query which is
not fulfilled by adding the new value?

You can check this by closing the form and opening it again. if the newly
added value is then shown in the combobox then it's not the case otherwise
this is causing the problem.

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


:

H. v.d. Bunte wrote:
I know there is a lot of posting here about this topic but I couldn't find a
good solution.

I will try to clear the situation. I have one form with 3 comboboxes that
are all connected to each other with queries But now i would like to enter a
new value in the second combobox project with the following code:

Private Sub txt_klant_NotInList(NewData As String, Response As Integer)
Dim msg As String, answer As Byte
msg = "Mag " & NewData & " toegevoegd worden aan de lijst?"
answer = MsgBox(msg, vbYesNo + vbQuestion)
If answer = vbYes Then

Dim db As Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblproject", dbOpenDynaset)

With rs
.AddNew
![txtproject] = NewData
.Update
End With

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


The value is added in the table but i get the error that the value is no
item in the list.

That's strange because in the first combobox the data comes directly from
the table. But in the second combobox I use a query because the information
has to be linked with the data from the first combobox.

I use the following query for the first combobox:

SELECT DISTINCT tblklant.txtklant
FROM tblklant
ORDER BY tblklant.txtklant;

I use the following query for the second combobox:

SELECT DISTINCT tblproject.txtproject
FROM Qinvoeren_onderdeel_gegevens_project INNER JOIN tblproject ON
Qinvoeren_onderdeel_gegevens_project.txt_Project = tblproject.txtproject
ORDER BY tblproject.txtproject;

And I think that this query wil be the problem. But I need to write data in
this table without any errors if possible.


I think the problem might be because there is no matching
record in the other table, but I have no idea how
Qinvoeren_onderdeel_gegevens_project fits into this
situation.
Click to expand...
Click to expand...
Click to expand...
 
Well i think that the thing you do wrong is that you forget to add the
customer (klant) key to the newly created project-record.

Which version of access do you use?

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


H. v.d. Bunte said:
That is exacly the way I created my form and my tables. I only don't use ID's
because i have unique projects and costumers.

But i think i did it the same way as you discribe below and i still get the
error that I have to choose a value from the list.

But maybe it's possible for you to make a database and see of it works.
Because i don't understand what i do wrong.

Thanks for your help so far.

"JaRa" schreef:
Please and also the structure of tblProject.

I think the solution will be something like this but
so you should have 2 comboboxes namely:
cmbCustomer and cmbProject

cmbCustomer is populated with the query Customer_Select which contains the
sql statement : SELECT Customer_ID, Customer_Name FROM Customers ORDER BY
Customer_Name

cmbProject is populated with the query Project_Select which contains the sql
statement : SELECT Project_ID, Project_Name FROM Projects WHERE
Project_Customer_ID=Forms("YourForm").cmbCustomer ORDER BY Customer_Name

the not in list statement should then be attached to cmbProject
and the insert of the record would become then

With rs
.AddNew
!Project_Name = NewData
!Project_Customer_ID=cmbCustomer
.Update
End With


--
- Raoul Jacobs

The nature of developping is sharing knowledge.


H. v.d. Bunte said:
That is the problem indeed.

But how do I solve it. I will try to explain what I would like to achieve.

I have a form with two comboboxes. The first one is the conbobox costumers.
The second one is the combobox project.

When I select the costumer I would like to select or add a project for that
costumer. But i use a query underneath the second combobox with the value
from the first one. So that i can select the right values for the combobox
project

I hope somebody can give me a solution how I can make this form work with
two comboboxes.

Thanks for your help so far.

:

Is there a possibility that there is a criterium set in this query which is
not fulfilled by adding the new value?

You can check this by closing the form and opening it again. if the newly
added value is then shown in the combobox then it's not the case otherwise
this is causing the problem.

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


:

H. v.d. Bunte wrote:
I know there is a lot of posting here about this topic but I couldn't find a
good solution.

I will try to clear the situation. I have one form with 3 comboboxes that
are all connected to each other with queries But now i would like to enter a
new value in the second combobox project with the following code:

Private Sub txt_klant_NotInList(NewData As String, Response As Integer)
Dim msg As String, answer As Byte
msg = "Mag " & NewData & " toegevoegd worden aan de lijst?"
answer = MsgBox(msg, vbYesNo + vbQuestion)
If answer = vbYes Then

Dim db As Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblproject", dbOpenDynaset)

With rs
.AddNew
![txtproject] = NewData
.Update
End With

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


The value is added in the table but i get the error that the value is no
item in the list.

That's strange because in the first combobox the data comes directly from
the table. But in the second combobox I use a query because the information
has to be linked with the data from the first combobox.

I use the following query for the first combobox:

SELECT DISTINCT tblklant.txtklant
FROM tblklant
ORDER BY tblklant.txtklant;

I use the following query for the second combobox:

SELECT DISTINCT tblproject.txtproject
FROM Qinvoeren_onderdeel_gegevens_project INNER JOIN tblproject ON
Qinvoeren_onderdeel_gegevens_project.txt_Project = tblproject.txtproject
ORDER BY tblproject.txtproject;

And I think that this query wil be the problem. But I need to write data in
this table without any errors if possible.


I think the problem might be because there is no matching
record in the other table, but I have no idea how
Qinvoeren_onderdeel_gegevens_project fits into this
situation.
Click to expand...
Click to expand...
Click to expand...
 
I use Access 2002

Thanks so far :)


"JaRa" schreef:
Well i think that the thing you do wrong is that you forget to add the
customer (klant) key to the newly created project-record.

Which version of access do you use?

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


H. v.d. Bunte said:
That is exacly the way I created my form and my tables. I only don't use ID's
because i have unique projects and costumers.

But i think i did it the same way as you discribe below and i still get the
error that I have to choose a value from the list.

But maybe it's possible for you to make a database and see of it works.
Because i don't understand what i do wrong.

Thanks for your help so far.

"JaRa" schreef:
Please and also the structure of tblProject.

I think the solution will be something like this but
so you should have 2 comboboxes namely:
cmbCustomer and cmbProject

cmbCustomer is populated with the query Customer_Select which contains the
sql statement : SELECT Customer_ID, Customer_Name FROM Customers ORDER BY
Customer_Name

cmbProject is populated with the query Project_Select which contains the sql
statement : SELECT Project_ID, Project_Name FROM Projects WHERE
Project_Customer_ID=Forms("YourForm").cmbCustomer ORDER BY Customer_Name

the not in list statement should then be attached to cmbProject
and the insert of the record would become then

With rs
.AddNew
!Project_Name = NewData
!Project_Customer_ID=cmbCustomer
.Update
End With


--
- Raoul Jacobs

The nature of developping is sharing knowledge.


:

That is the problem indeed.

But how do I solve it. I will try to explain what I would like to achieve.

I have a form with two comboboxes. The first one is the conbobox costumers.
The second one is the combobox project.

When I select the costumer I would like to select or add a project for that
costumer. But i use a query underneath the second combobox with the value
from the first one. So that i can select the right values for the combobox
project

I hope somebody can give me a solution how I can make this form work with
two comboboxes.

Thanks for your help so far.

:

Is there a possibility that there is a criterium set in this query which is
not fulfilled by adding the new value?

You can check this by closing the form and opening it again. if the newly
added value is then shown in the combobox then it's not the case otherwise
this is causing the problem.

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


:

H. v.d. Bunte wrote:
I know there is a lot of posting here about this topic but I couldn't find a
good solution.

I will try to clear the situation. I have one form with 3 comboboxes that
are all connected to each other with queries But now i would like to enter a
new value in the second combobox project with the following code:

Private Sub txt_klant_NotInList(NewData As String, Response As Integer)
Dim msg As String, answer As Byte
msg = "Mag " & NewData & " toegevoegd worden aan de lijst?"
answer = MsgBox(msg, vbYesNo + vbQuestion)
If answer = vbYes Then

Dim db As Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblproject", dbOpenDynaset)

With rs
.AddNew
![txtproject] = NewData
.Update
End With

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


The value is added in the table but i get the error that the value is no
item in the list.

That's strange because in the first combobox the data comes directly from
the table. But in the second combobox I use a query because the information
has to be linked with the data from the first combobox.

I use the following query for the first combobox:

SELECT DISTINCT tblklant.txtklant
FROM tblklant
ORDER BY tblklant.txtklant;

I use the following query for the second combobox:

SELECT DISTINCT tblproject.txtproject
FROM Qinvoeren_onderdeel_gegevens_project INNER JOIN tblproject ON
Qinvoeren_onderdeel_gegevens_project.txt_Project = tblproject.txtproject
ORDER BY tblproject.txtproject;

And I think that this query wil be the problem. But I need to write data in
this table without any errors if possible.


I think the problem might be because there is no matching
record in the other table, but I have no idea how
Qinvoeren_onderdeel_gegevens_project fits into this
situation.
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Sorry for the delay but an example is online at

www.opmaat.be/files/ComboBox_Example.zip

I hope this gives you a solution.


--
- Raoul Jacobs

The nature of developping is sharing knowledge.


H. v.d. Bunte said:
I use Access 2002

Thanks so far :)


"JaRa" schreef:
Well i think that the thing you do wrong is that you forget to add the
customer (klant) key to the newly created project-record.

Which version of access do you use?

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


H. v.d. Bunte said:
That is exacly the way I created my form and my tables. I only don't use ID's
because i have unique projects and costumers.

But i think i did it the same way as you discribe below and i still get the
error that I have to choose a value from the list.

But maybe it's possible for you to make a database and see of it works.
Because i don't understand what i do wrong.

Thanks for your help so far.

"JaRa" schreef:

Please and also the structure of tblProject.

I think the solution will be something like this but
so you should have 2 comboboxes namely:
cmbCustomer and cmbProject

cmbCustomer is populated with the query Customer_Select which contains the
sql statement : SELECT Customer_ID, Customer_Name FROM Customers ORDER BY
Customer_Name

cmbProject is populated with the query Project_Select which contains the sql
statement : SELECT Project_ID, Project_Name FROM Projects WHERE
Project_Customer_ID=Forms("YourForm").cmbCustomer ORDER BY Customer_Name

the not in list statement should then be attached to cmbProject
and the insert of the record would become then

With rs
.AddNew
!Project_Name = NewData
!Project_Customer_ID=cmbCustomer
.Update
End With


--
- Raoul Jacobs

The nature of developping is sharing knowledge.


:

That is the problem indeed.

But how do I solve it. I will try to explain what I would like to achieve.

I have a form with two comboboxes. The first one is the conbobox costumers.
The second one is the combobox project.

When I select the costumer I would like to select or add a project for that
costumer. But i use a query underneath the second combobox with the value
from the first one. So that i can select the right values for the combobox
project

I hope somebody can give me a solution how I can make this form work with
two comboboxes.

Thanks for your help so far.

:

Is there a possibility that there is a criterium set in this query which is
not fulfilled by adding the new value?

You can check this by closing the form and opening it again. if the newly
added value is then shown in the combobox then it's not the case otherwise
this is causing the problem.

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


:

H. v.d. Bunte wrote:
I know there is a lot of posting here about this topic but I couldn't find a
good solution.

I will try to clear the situation. I have one form with 3 comboboxes that
are all connected to each other with queries But now i would like to enter a
new value in the second combobox project with the following code:

Private Sub txt_klant_NotInList(NewData As String, Response As Integer)
Dim msg As String, answer As Byte
msg = "Mag " & NewData & " toegevoegd worden aan de lijst?"
answer = MsgBox(msg, vbYesNo + vbQuestion)
If answer = vbYes Then

Dim db As Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblproject", dbOpenDynaset)

With rs
.AddNew
![txtproject] = NewData
.Update
End With

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


The value is added in the table but i get the error that the value is no
item in the list.

That's strange because in the first combobox the data comes directly from
the table. But in the second combobox I use a query because the information
has to be linked with the data from the first combobox.

I use the following query for the first combobox:

SELECT DISTINCT tblklant.txtklant
FROM tblklant
ORDER BY tblklant.txtklant;

I use the following query for the second combobox:

SELECT DISTINCT tblproject.txtproject
FROM Qinvoeren_onderdeel_gegevens_project INNER JOIN tblproject ON
Qinvoeren_onderdeel_gegevens_project.txt_Project = tblproject.txtproject
ORDER BY tblproject.txtproject;

And I think that this query wil be the problem. But I need to write data in
this table without any errors if possible.


I think the problem might be because there is no matching
record in the other table, but I have no idea how
Qinvoeren_onderdeel_gegevens_project fits into this
situation.
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much for your help so far.

But there is one thing that I don't think is nessesary, but correct me if
i'm wrong.

In the table Customer you use a Customer_id and a Customer_customerGroup_id.
But in my case i would like to have identical customer_names. So then I don't
need the Groupid field if i'm correct.

Again thanks for your help and for the work of making a demo for me :)

"JaRa" schreef:
Hi,

Sorry for the delay but an example is online at

www.opmaat.be/files/ComboBox_Example.zip

I hope this gives you a solution.


--
- Raoul Jacobs

The nature of developping is sharing knowledge.


H. v.d. Bunte said:
I use Access 2002

Thanks so far :)


"JaRa" schreef:
Well i think that the thing you do wrong is that you forget to add the
customer (klant) key to the newly created project-record.

Which version of access do you use?

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


:

That is exacly the way I created my form and my tables. I only don't use ID's
because i have unique projects and costumers.

But i think i did it the same way as you discribe below and i still get the
error that I have to choose a value from the list.

But maybe it's possible for you to make a database and see of it works.
Because i don't understand what i do wrong.

Thanks for your help so far.

"JaRa" schreef:

Please and also the structure of tblProject.

I think the solution will be something like this but
so you should have 2 comboboxes namely:
cmbCustomer and cmbProject

cmbCustomer is populated with the query Customer_Select which contains the
sql statement : SELECT Customer_ID, Customer_Name FROM Customers ORDER BY
Customer_Name

cmbProject is populated with the query Project_Select which contains the sql
statement : SELECT Project_ID, Project_Name FROM Projects WHERE
Project_Customer_ID=Forms("YourForm").cmbCustomer ORDER BY Customer_Name

the not in list statement should then be attached to cmbProject
and the insert of the record would become then

With rs
.AddNew
!Project_Name = NewData
!Project_Customer_ID=cmbCustomer
.Update
End With


--
- Raoul Jacobs

The nature of developping is sharing knowledge.


:

That is the problem indeed.

But how do I solve it. I will try to explain what I would like to achieve.

I have a form with two comboboxes. The first one is the conbobox costumers.
The second one is the combobox project.

When I select the costumer I would like to select or add a project for that
costumer. But i use a query underneath the second combobox with the value
from the first one. So that i can select the right values for the combobox
project

I hope somebody can give me a solution how I can make this form work with
two comboboxes.

Thanks for your help so far.

:

Is there a possibility that there is a criterium set in this query which is
not fulfilled by adding the new value?

You can check this by closing the form and opening it again. if the newly
added value is then shown in the combobox then it's not the case otherwise
this is causing the problem.

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


:

H. v.d. Bunte wrote:
I know there is a lot of posting here about this topic but I couldn't find a
good solution.

I will try to clear the situation. I have one form with 3 comboboxes that
are all connected to each other with queries But now i would like to enter a
new value in the second combobox project with the following code:

Private Sub txt_klant_NotInList(NewData As String, Response As Integer)
Dim msg As String, answer As Byte
msg = "Mag " & NewData & " toegevoegd worden aan de lijst?"
answer = MsgBox(msg, vbYesNo + vbQuestion)
If answer = vbYes Then

Dim db As Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblproject", dbOpenDynaset)

With rs
.AddNew
![txtproject] = NewData
.Update
End With

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub


The value is added in the table but i get the error that the value is no
item in the list.

That's strange because in the first combobox the data comes directly from
the table. But in the second combobox I use a query because the information
has to be linked with the data from the first combobox.

I use the following query for the first combobox:

SELECT DISTINCT tblklant.txtklant
FROM tblklant
ORDER BY tblklant.txtklant;

I use the following query for the second combobox:

SELECT DISTINCT tblproject.txtproject
FROM Qinvoeren_onderdeel_gegevens_project INNER JOIN tblproject ON
Qinvoeren_onderdeel_gegevens_project.txt_Project = tblproject.txtproject
ORDER BY tblproject.txtproject;

And I think that this query wil be the problem. But I need to write data in
this table without any errors if possible.


I think the problem might be because there is no matching
record in the other table, but I have no idea how
Qinvoeren_onderdeel_gegevens_project fits into this
situation.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top