Form Text box

[NightWalker]

I have frmEmployees that has a text box (txtAreaSelect). I am trying to pull the AreaNickname field records from tblAreas whose EmpID matches the hidden EmpID textbox on the frmEmployees.



I am having a brainfart. I am having trouble getting this to happen. There could be multiple AreaNicknames for each EmpID. I want all the nicknames to populate the txtAreaSelect textbox as a list.

Thank you for any help.

Walker

[CJ_London]

if you want a list, use a listbox rather than a textbox. Other cosider using Allen Browne's concatrelated function

http://allenbrowne.com/func-concat.html

[NightWalker]

I tried a listbox also. I couldn't get that to work properly. I was trying to just get the query to work on a textbox. Right now I am getting ALL the records in the AreaNicknames field.

SELECT tblAreas.AreaNickname, tblAreas.EmpID FROM tblAreas INNER JOIN tblEmployees ON tblAreas.EmpID = tblEmployees.EmpID WHERE (((tblAreas.EmpID)=[tblEmployees]![EmpID]));

[CJ_London]

that sql will produce for all employees - in the listbox rowsource think you need something like

SELECT tblAreas.AreaNickname, tblAreas.EmpID FROM tblAreas WHERE tblAreas.EmpID =[txtEmpID];

where txtEmployee is the name of the control on your form that is bound to EmpID

[Missinglinq]

...I was trying to just get the query to work on a textbox...

Sorry, but you cannot use a SQL Statement/Query as the Control Source for a Textbox...period!

If there was one AreaNicknames per EmpID, you could use the DLookup() function to populate the Tetxtbox, but to list multiple AreaNicknames for a given EmpID you'll have to use either a Listbox, a Combobox or have the nicknames displayed in a Subform.

Linq ;0)>

[NightWalker]

I switched the textbox to a listbox but I am still having trouble getting the correct output to the listbox. I had DLookup() properly for the textbox until they added multiple areas for a few employees. Thank you all for your input and knowledge.

[CJ_London]

now you have a listbox, put the code in post #4. Noticed a small typo

where txtEmpID is the name of the control on your form that is bound to EmpID

[NightWalker]

Thanks Ajax That worked perfectly and I see where I was going wrong. I appreciate everyones help.

I did an after update event on the employees last name and put a requery in there to get the listbox to update to areas for new employee.