Populate List box from TextBox

[Shamli]

Hi
I have two tables :
1. Employee : EmployeeID, EmployeeName ..
2. Project: ProjectID, EmployeeID, ProjectName, ElementName , WBS

TextBox (EmployeeID) : displays employee ID (Populates from another list Box selection)
List Box (ListProject) : displays ProjectName, ElementName, WBS

Once the EmployeeID populated from another list Box
I want the ListProject should populate the list of projects associated with that employee ID.

Could you please help me with this.
I tried placing this query in the after update function in the text box:
Private Sub EmployeeID_AfterUpdate()
Dim SQL As String
SQL = "Select ElementName,ProjectName,WBS From[Project] where [EmployeeID] = " & Me![EmployeeID].Value
Me!
[ListProject].RowSource = SQL
Me!
[ListProject].Requery
End Sub

And added the rowsource of the List box as this :
to the employee ID: In criteria:

[Forms]![formname]![EmployeeID]




But the values are not populating.

[ranman256]

you dont need to write code for queries. just : docmd.openquery "qsMyQuery"

the query would use the SQL based on the item on the form
Select ElementName,ProjectName,WBS From[Project] where [EmployeeID] = forms!MyForm!EmployeeID

are you trying to assign the project to the person on the form?
or
are you trying to view all persons assigned to the project in the listBox?

[Shamli]

I am trying to view the project assigned to the person in the list box.

you dont need to write code for queries. just : docmd.openquery "qsMyQuery"

the query would use the SQL based on the item on the form
Select ElementName,ProjectName,WBS From[Project] where [EmployeeID] = forms!MyForm!EmployeeID

are you trying to assign the project to the person on the form?
or
are you trying to view all persons assigned to the project in the listBox?

[davegri]

Sounds very confusing. You have a listbox selection that populates a text box that is used as criteria to populate yet another listbox?
We could grasp this a bit better if you post your DB.

[Shamli]

sorry for not being clear.
I would like to populate the listbox from the data in the text box .

Sounds very confusing. You have a listbox selection that populates a text box that is used as criteria to populate yet another listbox?
We could grasp this a bit better if you post your DB.

[Shamli]

I have attached the sample db.
My goal is to display the already assigned projects, when the employee is selected from the sub form.

And whenever a new project is assigned to the employee, the list should update.
Could you please help me with this.

[Shamli]

I have attached the Db for your reference.

Sounds very confusing. You have a listbox selection that populates a text box that is used as criteria to populate yet another listbox?
We could grasp this a bit better if you post your DB.

[davegri]

Test-davegri-v01.zip

Try this. Revised the form completely. The employee listbox did not need to be in a subform.
The form itself needs no recordsource.
Added 'ADD' and 'REMOVE' buttons between the project listboxes and added code to
update the junction table as projects are added or removed from the listboxes.
Added a couple more employee names for testing.

[Shamli]

Thank you so much ! I will go through the solution!
Thanks again!!

Test-davegri-v01.zip

Try this. Revised the form completely. The employee listbox did not need to be in a subform.
The form itself needs no recordsource.
Added 'ADD' and 'REMOVE' buttons between the project listboxes and added code to
update the junction table as projects are added or removed from the listboxes.
Added a couple more employee names for testing.

[Shamli]

I have one more question. How do I add a search the employee List using the text box?
I added a text box
Apply filter in macro:
[Employee].[FullName] Like "*" & [Forms]![EmployeeProjectForm]![lstEmployee]![txtSearch] & "*"
but I am getting an error. What am I missing out?

Test-davegri-v01.zip

Try this. Revised the form completely. The employee listbox did not need to be in a subform.
The form itself needs no recordsource.
Added 'ADD' and 'REMOVE' buttons between the project listboxes and added code to
update the junction table as projects are added or removed from the listboxes.
Added a couple more employee names for testing.

[davegri]

Can't use a filter as form is unbound.
Add a textbox named txtSearch and set its AfterUpdate event like this:
This will add criteria to the listbox rowsource.

Private Sub txtSearch_AfterUpdate()
    Dim sRowsource As String
    sRowsource = "SELECT Employee.[Employee_PK], [Employee].[Fullname] FROM Employee WHERE Employee.Fullname LIKE """ & "*" & txtSearch & "*" & """"
    lstEmployee.RowSource = sRowsource
    lstEmployee.Requery
End Sub

[Shamli]

Thank you so much! It worked!!

Can't use a filter as form is unbound.
Add a textbox named txtSearch and set its AfterUpdate event like this:
This will add criteria to the listbox rowsource.

Private Sub txtSearch_AfterUpdate()
    Dim sRowsource As String
    sRowsource = "SELECT Employee.[Employee_PK], [Employee].[Fullname] FROM Employee WHERE Employee.Fullname LIKE """ & "*" & txtSearch & "*" & """"
    lstEmployee.RowSource = sRowsource
    lstEmployee.Requery
End Sub

[Shamli]

Hi I have a problem with the changes I am trying to implement.<br>
I am attaching the Db for your reference.<br>
What I am trying to implement is:
1. To have the element name along with the project details in the "Lst assigned and Lst available " List boxes.
2. Next, once the employee is selected : Out of the projects in the "LstAvailable box" , the User can also filter it through the "Element " Combo box placed on its top.

Could you please help me with this.
I tried querying. But its not displaying any results.

Can't use a filter as form is unbound.
Add a textbox named txtSearch and set its AfterUpdate event like this:
This will add criteria to the listbox rowsource.

Private Sub txtSearch_AfterUpdate()
    Dim sRowsource As String
    sRowsource = "SELECT Employee.[Employee_PK], [Employee].[Fullname] FROM Employee WHERE Employee.Fullname LIKE """ & "*" & txtSearch & "*" & """"
    lstEmployee.RowSource = sRowsource
    lstEmployee.Requery
End Sub

[davegri]

1. To have the element name along with the project details in the "Lst assigned and Lst available " List boxes.

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

2. Next, once the employee is selected : Out of the projects in the "LstAvailable box" , the User can also filter it through the "Element " Combo box placed on its top.

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.

Testx1-davegri-v02.zip

[Shamli]

Yes! That makes sense!!
It doesn't filter according to the combo box value.
Thank you so much for your prompt replies every time !!

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.

Testx1-davegri-v02.zip