Forms - Control Source to Record

[NicC]

Is there a way that I can ensure a text box on a form displays a specific record from a field in a table?

I've been asked to prepare an Organisational Chart for our office. I've created the org chart diagram in a form using command buttons for each staff member ('Org Chart'), which when clicked displays a second form ('Details Form') showing the staff member's contact details and current projects (mostly free text fields). All the data is in one table, "Staff Details". I'd like to display the 'Current Projects' field (a free text field) for each staff member in the 'Org Chart' form under their relevant command button.

I've created text fields with the control source as the 'Current Projects' field in the table, but I can't see how I can filter each specific text box to only display the 'Current Projects' entry for the individual staff members. I've tried building an expression in the control source for each text box ([Staff Details]![Current Projects],[StaffName]="Andrew Davis"), and in various configurations of the same, but I keep receiving errors. I'm not experienced at all with expressions and can't find any easy answers in the 'help' pages.

Any assistance would be greatly appreciated!

[meratigoerr]

With everything in one table you'll have to use something like a DLookup. assuming the field you want to display is called "CurrentProject" it would look something like:

=dlookup("[CurrentProject]","[StaffDetails]","[StaffName] = 'Andrew Davis'")

2 cents: This actually doesn't sound like a project suited to access. At least not the way you have it set up. You might have more luck acheiving your desired output thru prublisher or even exel. Generally if you can build a database with only table, there's not really a need to do it access. Excel will work just fine.
This doesn't mean you shouldn't try, but you may want to rethink how you set it up. (first name and last name should always be seperate fields). Put all of your employees and their details in one table. Put their projects in another. Now you can not only track their current project, but keep a history of all of the past projects as well. You can add other tables as you go. But it makes the whole think more dynamic and user friendly.