Populate List box from TextBox

[Shamli]

1. To have the element name along with the project details in the "Lst assigned and Lst available " List boxes.
The solution you provided I have tried it already. The problem is we have a different table for the elements and that's why we have the elementID in the project table. So the element name is being called from the element table and not from the project table.
Element and project table have one to many relationship.
Its my mistake to not explain it clearly.
So I am planning to delete the element field from the project table.
And in query I want the element to being called from the element table.

This functionality is already present. The reason the element in the listbox is blank is because the element field in the table is blank.

Added the combobox and the criteria for the lstAvailable listbox.
This might be a bit confusing, because the right listbox is not altered by the contents of the combobox. It acts like it did before the combobox was added.
If we filter the right listbox the same as the left, that would mean that assigned projects might not show when they were actually assigned.

Attachment 34961

[Shamli]

But the problem is: I have to take the element from the element table and not project table.
I have removed the element name from the project table and I have create a relation ship between Element and project table as one to many relationship.[
QUOTE=davegri;405084]This functionality is already present. The reason the element in the listbox is blank is because the element field in the table is blank.

Added the combobox and the criteria for the lstAvailable listbox.
This might be a bit confusing, because the right listbox is not altered by the contents of the combobox. It acts like it did before the combobox was added.
If we filter the right listbox the same as the left, that would mean that assigned projects might not show when they were actually assigned.

Attachment 34961

[/QUOTE]

[davegri]

Your attachment is unchanged from what I last posted here in post# 14.
There is no Elements table.

[Shamli]

Sorry about that. I think I took the wrong attachment.
Please find the new one.

Your attachment is unchanged from what I last posted here in post# 14.
There is no Elements table.

[Shamli]

Is this query right to pull the element from the element table and not from project table.
I have tried them but the data is not pulled right. What am I missing here. Could you please tell me.




sChosenSQL = "SELECT [Copy Of Projects_T].ProjectID, [Copy Of Projects_T].ProjectName, [Copy Of Element_T].Element " _
& "FROM [Copy Of Element_T] INNER JOIN [Copy Of Projects_T] ON [Copy Of Element_T].ElementID = [Copy Of Projects_T].ElementID " _
& "INNER JOIN EmpToProj ON [Copy Of Projects_T].ProjectID = EmpToProj.ProjectID " _
& "WHERE EmpToProj.EmployeeID = " & lstEmployee.Column(1) _
& " ORDER BY ProjectName;"

sAvailSQL = "SELECT [Copy Of Projects_T].ProjectID, [Copy Of Projects_T].ProjectName, [Copy Of Element_T].Element " _
& "FROM [Copy Of Element_T] INNER JOIN [Copy Of Projects_T] ON [Copy Of Element_T].ElementID = [Copy Of Projects_T].ElementID " _
& "WHERE NOT EXISTS (SELECT EmployeeID FROM EmpToProj " _
& "WHERE EmployeeID = " & lstEmployee.Column(1) _
& " AND [Copy Of Projects_T].ProjectID = EmpToProj.ProjectID ) ORDER BY ProjectName;"

Your attachment is unchanged from what I last posted here in post# 14.
There is no Elements table.

[Shamli]

I have updated the Db you provided me.
Please have a look.

Your attachment is unchanged from what I last posted here in post# 14.
There is no Elements table.

[Shamli]

The problem is I am not able to pull the element from Element table in the " Add Employees to project form" in "lstAssigned"

Is it because I am doing inner join the element table & project table and also inner join Project Table & EmptoProj table.

[Shamli]

Its working Now.

I modified the query a little. and its working
Thank you so much for your help

Your attachment is unchanged from what I last posted here in post# 14.
There is no Elements table.