Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2020
    Posts
    3

    Question Trouble with report data from form

    Hello Guru's
    The problem I'm having is probably very simple but I haven't used Access for several years and I have hit a stumbling block that I hope someone can clear up for me.
    I have a form in which I am searching a Query of several tables via several combo boxes that I select from dropdown to filter underlying query for only that selection.
    Then a selection from another cbo to further filter the data etc. etc. This is working fine but I have a button to view a report of filtered data.

    In the report one of my fields is displaying column 1 of a 2 column query, but col 1 is an ID num and useless. In the form the text I want is displayed but in the report it displays
    the ID field. Can't figure out how to get this to display the text value that is displayed on the form.

    Form properties


    The field in question is [Brands].[Brand]
    It's RowSource = "Select Brands.BrandsID, Brands.Brand FROM Brands"
    Bound Column = 1
    Column Count = 2
    Column Widths = 0;1"


    In the Report
    RecordSource is same as the forms
    The Brand textbox in the reports Control Source = [Brand]

    But the report is displaying BrandID value not the [Brand] name

    Any help would be appreciated
    Wally



    In the Report the Brand field is the same as the forms Record Source.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Either build a query that includes the lookup tables and pull in those fields or use a combobox on report. Combobox will appear as a textbox (no interactive dropdown). Both approaches work for me.

    If you want to provide db for analysis, follow instructions at bottom of my post.

    Your controls used for input of search/filter criteria should be UNBOUND.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jul 2020
    Posts
    3
    Thanks for the reply but a cbo in the report won't work for my situation. Other than that I still can't get it to work as desired.
    I'm going to try and attach the DB so you can see exactly what I have and perhaps have some fix.

    Open the frm_Search_Models, use cbo's to filter the recordset and click the View Report Button and you will see that the first field [Brand] is displaying ID number not the Brand name.

    Thanks for any help you might suggest!
    Wally

    See attached zip

    Trane Models.zip

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Because Brand in Models is saving BrandID.

    This is what happens when lookup fields are built in tables. You see one value when something else is actually stored.

    Need to include Brands table in the RecordSource then bind to its Brand field.
    Will have to change Models Brand field to number type (long integer).
    Then you will be able to set Relationship.

    Why won't a combobox work for your situation?

    Helps not to use exact same field name in multiple tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I made some mods to your dB. Actually, I made quite a few.

    I don't know why you want to have a sub form when you could have the sub form data in the main form detail section...... but it is you dB.


    I changed the code from modifying the sub form record source to setting a filter - be sure to look through the subroutine "SearchCriteria()".


    I added suffixes to the PK and FK fields - easier for me to keep things straight...
    Attached Files Attached Files

  6. #6
    Join Date
    Jul 2020
    Posts
    3
    Thanks, I haven't had time to dive into your modifications yet but, silly me, I did change my report textbox to a combobox and now it is working. I don't know what I was thinking.
    If I was going to redo this program I would definitely do it somewhat different but all I need is a functional Db. (It's for my business, I'm a lone ranger)
    Thanks so much,
    Wally

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Regardless, really need to make the change to Models table Brand field I suggested in post 4.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 04-16-2018, 11:31 AM
  2. Replies: 4
    Last Post: 12-01-2015, 06:37 PM
  3. Replies: 15
    Last Post: 02-27-2014, 12:04 PM
  4. Trouble Getting the Data I want
    By rts in forum Queries
    Replies: 6
    Last Post: 08-21-2013, 01:29 PM
  5. Trouble counting items in a report
    By Walt Stypinski in forum Access
    Replies: 2
    Last Post: 06-13-2011, 08:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums