Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2005
    Posts
    79

    Red face Unanswered: Data on Form does not reflect on Report

    I have a database called employees with two three fields (Employee ID (autonumber, Employee Name, and Employee telephone number. On my form, the Employee's name can be selected per a combo box. However, when running my report, instead of the name of the employee being listed, the EmployeeID (the autonumber) is showing. How do I make the name of the employee to show on the report instead of the EmployeeID..

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Put the employee name textbox on the report instead?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2005
    Posts
    79

    Text Box

    I do have the textbox, however, when I look at the data in the main database, the EmployeeID is what is being displayed in the block for the employeeName. Thank you for your assistance, it is greatly appreciated.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    when I look at the data in the main database
    what does that entail? Also, what is a block?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2005
    Posts
    79

    Employee's Information

    I am so sorry, please forgive me. Not actually a block. When looking at the main "computer information" database (raw data). The employee's name in the field has a number in it instead of a name (from Employees database with 2 fields EmployeeID, EmployeeName, and EmployeeTelephone. When filling out the form, the employee's name shows up (which is correct). However, when running the report, the Employee's ID number shows up instead of the person's name.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Report

    It sounds like you may need to link in the Employees table to the SQL/Query for the report and bring in the name field from that table and use that on the report (unless I misread what your problem was).

    Otherwise, you can use the dlookup command to look up the name in the Employee's table where ID = ID but this can be quite slow in reports with a lot of records.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2005
    Posts
    79

    Sql

    Could you provide me with a sample on how I would do the SQL/Query? Thank you.

  8. #8
    Join Date
    Nov 2005
    Posts
    79

    Figured out the DLookup

    I finally figured out the command for the Dlookup, however, when I make changes, the employee name field shows as "Jane Doe" during data input. When I run the report, the report always selects 1 record before the actual record. The report lists record # 40 when it should list record #41 on the report. The data is fine in the table. Thank you again.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Report - accessing SQL

    In the design view of the report, show the properties and find "Record Source" (if you don't see it, you may need to click on a blank spot in the gray area.) Then click the ... next to it. This will open up the query the report is based on (if this is blank, then the Record Source is being populated some other way through code and you'll need to find the code which does this.)

    You want to link in the Employees table (1 to many) with the Employees table on the many side (has the arrow to it) linking it by ID. Then drag down the Employee's name from the Employees table into the query. Close this, and view the field names in the report (you may need to save and get back in - make sure you click off the Record Source field and then save it as MSAccess doesn't always save the new Record Source unless you click off of it, save it, then close it). You should see the new Name field you dragged into the query in the field list. Set the Control Source for that field on the report to this Name field.

    If you are populating the "Record Source" on this report through code, you'll need to modify the SQL Statement that the Record Source is populated with, adding in the linked table (I think outer join) and making sure the Name field from the Employees table is returned.

    If some names appear and others don't, check to make sure the ID field is populated in your main table where it should be populated. You'll get a blank name if there is no ID in that field.
    Last edited by pkstormy; 11-07-05 at 17:57.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Nov 2005
    Posts
    79

    Employee

    When I run t he query, I am prompted to enter an employee name. When I type in a name, the name appears on each record.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Having to enter the name

    That's because it can't find the name field you called it on the Control Source for that field. Show and Check the field list (if this report is bound to a Source Object) and make sure the field name you called it in the query is spelled right in the Control Source for that specific field. It should match exactly the name in the field list. Also, if there is any sorting/grouping, make sure the name is changed there also or that the ID field is also on the report if it's part of the sorting/grouping (you can make this field invisible.) You can also check the Conditional Formatting (under Format) to see if anything in that is referencing a field which doesn't exist in the report.

    Remember: When you click on the control in the report, there is a Name property and a Control Source Property. I usually like to make sure these both match to not cause any confusion if there is code based around the Name property. The Control Source value must match a name in the Field list!
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Nov 2005
    Posts
    79

    Smile Problems Table Relationships

    You led me to answer my own questions. Thank you.
    Last edited by miracleblake; 11-08-05 at 13:39. Reason: Query

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •