combobox not in list

[Thunder2781]

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

 

[earthandfire]

Requery the combobox before exiting the NotInList Event


hope this helps.

 

[PHV]

You use an INNER JOIN so the combo shows only the txtproject existing in both tables.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Thunder2781]

Thanks earthandfire and PHV,

I have tried to put after the end if the line
txtproject.Requery
but then i get an error message that i can do this when i leave the field. But i cann't because then i get the error showed in my first post.

And PVH I know that I made that Inner join because i would like to see only the records that exists in both tables. Because a costumer can have more projects. So they may not have the option to choose a project from an other costumer.