1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2013
    Rep Power

    Red face Forms - Control Source to Record

    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!
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    South Jersey
    Rep Power
    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.

Similar Threads

  1. Select Boxes Within Forms, and How to Control it all
    By sgmunson in forum ASP Development
    Replies: 3
    Last Post: April 30th, 2007, 07:54 AM
  2. Forms Control Soure error
    By tuktuk in forum Microsoft Access Help
    Replies: 0
    Last Post: June 22nd, 2006, 05:50 PM
  3. Remove Record Source/Control Source
    By Exodus in forum Microsoft Access Help
    Replies: 16
    Last Post: September 28th, 2005, 01:35 PM
  4. ListView Control (Windows Forms)
    By Tryst in forum .NET Development
    Replies: 4
    Last Post: March 16th, 2005, 07:52 AM
  5. Forms Record Source for Multiple Queries
    By JGM22 in forum Microsoft Access Help
    Replies: 1
    Last Post: September 21st, 2004, 09:45 AM

IMN logo majestic logo threadwatch logo seochat tools logo