Results 1 to 9 of 9
  1. #1
    Join Date
    May 2009
    Posts
    48

    Unanswered: Report displays Product ID instead of Product Name

    Hello all.
    I have a report that I want to display a Product Name selected from a combo box. The names can be selected in the combo box just fine but when the report is run, the Product ID (basically the line number the Product Name is on in the table) is displayed. The Product ID is in column 1 and the Product Name is in column 2. How do I get the Product Name to display in the report?

    Here is an example of the problem.

    Open the Table1Form and select a product from ProductSelect1. This will show the correct product that I need on the report.

    Open the test report and you will see the line number that contains the product on the Products Table. How do I get the report to display the Product?

    Thanks in advance,
    Andrew
    Attached Files Attached Files
    Last edited by drewship; 06-05-09 at 10:09.

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Have a query as the basis of the report, not a table.

    In the query design, pull in your orders table and products table, link the tables via the relavant field and drag the product name into the query output so it can be used on the report.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    May 2009
    Posts
    48
    garethdart, the tables I am using do not have anything in common than can be related. I use the Table1Form to select the desired product. Isn't this value saved as a variable that can be used in the report somehow?

    Thanks,
    Andrew

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Hi Andrew, whilst you could refer to the value of a combo box on a form, it is not much use if you are not recording the data in your database.

    You need a ProductID field in your main table which will store the value of the product selected in the combo box.

    Assuming you have a integer primary key in the products table, add intProductID as a field in the main table and use this as the recordsource of the combobox.

    Then whenever you want a report of the 'chosen' products, you can employ the technique I described earlier.

    I think you would benefit from a little swatting up on database design - are you studying this in school / college? Have you been given / recommended any books or study material?

    I will try and upload an example of the above shortly...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    May 2009
    Posts
    48
    Thanks garethdart. This is a database that has been used at work for some time. I was recently hired and one of the items they tasked me with was to streamline several databases and spreadsheets. I have some basic knowledge of Access but it is difficult to jump in the middle of something like this and make changes. I understand that normally the tables need to be related to function properly but the purpose of this specific report is only to print a shipping document for items that are maintained in a separate application. I know this is not the way to operate and that is another thing I will need to fix. Eventually, I hope to have my employer using this database correctly.

    I will see what I can come up with using your suggestions.

    Andrew

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Ok - good luck;

    If you did want to directly refer to the value of the combo, use the index;

    forms!yourformname.yourcontrolname.index(1).value

    index(0) would be the first 'field' in the query behind the combo etc.

    ...Mind you, you are better of correcting it!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  7. #7
    Join Date
    May 2009
    Posts
    48
    I'll look at it this weekend.

    Thanks!!

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    OK Drew,

    But you really would be better sorting the database - quick fixes do not work!

    Let us know how you get on.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    May 2009
    Posts
    48
    I have the report working..YEAAAAAA. Now I am going to go back and try to store the data before the report runs. I created a 'Main' table with fields from both my other tables so now I will see if I can implement your recommendations.

    The 'Main' table in the attached db should store the data selected using the 'MainMenu' form.

    Thanks,
    Andrew
    Attached Files Attached Files

Posting Permissions

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