Results 1 to 9 of 9
  1. #1
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82

    Combine multiple records into appropriate textboxes on one report

    Hello,

    I've imported an image of a paper form we use in our office. It has five sections that represents assets an employee may have. If an employee has many assets, I want them to be listed on the same report.

    I've put textboxes in the appropriate location over the image: txtIDCard, txtProxCard, txtLaptopID, etc...

    An employee has an ID number, EmpID that is associated to each asset in the table tblAssets (ID[pk], AssetTypeID, EmpID) and the asset is described in tblAssetType (ID[pk], AssetDescription).

    An example of tblAssets:
    ID AssetTypeID EmpID
    1 1 sk1
    2 2 sk1
    3 3 sk1

    An example of tblAssetType:


    ID AssetDescription
    1 Laptop
    2 Prox Card
    3 ID Card


    In the report, I want one page per employee that will populate the appropriate text box. So far, all I've got is that the report should be grouped by EmpID, but after that I'm not sure where to evaluate the asset and place it in the appropriate box. I only have one detail section that shows the image of the document I want to print.

    I'm not 100% sure what event to put the code in.

    Thanks for any help and I hope that makes sense!

    Scott

  2. #2
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    I figured out a way:

    On Report_Activate I created a new ADODB Recordset that uses the reports rowsource. From there a do until rs.EOF loop to evaluate rs!AssetTypeID in a Select Case and then populate the appropriate textbox.

    It's probably not the most efficient, so if you can suggest a better way, please let me know!

    Thanks,

    Scott

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe this from Allen Browne???

    Concatenate values from related records http://www.allenbrowne.com/func-concat.html

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What image?

    Yes, Allen Browne's code may be what you are looking for. If you want the asset descriptions concatenated into a single long string for each employee.
    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
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    I had a look at that code, and it looks very useful if you don't need to evaluate what info is in the string. I need to determine which text box the data belongs in. After looking at that code and seeing that you still need to open a new recordset anyways, my original idea may not be too far off.

    Quote Originally Posted by June7 View Post
    What image?
    I laughed a bit when I read this. I don't do anything the easy way and apparently my co-workers want to help continue that practice.

    The paper form I want to populate automatically is a fillable PDF. I haven't been able to import a PDF successfully into an Access report and there's not very much information out there about doing so. Lots for exporting as a PDF, but not for importing.

    The fillable PDF is password protected from any kind of manipulation. I have no idea who created it and trying to find the password would be an exercise in futility.

    I found out yesterday that my office has a pro licence for Adobe, which kind of made my day. I "printed" the PDF into another PDF which removed the fillable fields and the password protection. I then exported the new PDF as a .PNG. Then I imported the .PNG into my Access report and stretched it out to be the size of a sheet of paper. I'm now putting text boxes and check boxes over top of the image where the data is going to populate.

    It's ugly! There's got to be a better way out there, but I haven't been able to find it yet!

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I resorted to that once because of subscripted text. Built document in Excel and converted pdf to jpg format. Actually looks fairly good.

    AFAIK, PDF can only be viewed in Access through Web Browser control, unless there are some 3rd-party add-ins. I don't know if a report can handle that control.

    The only other approach is to replicate the document with textboxes and label controls.

    Aside from that, not quite understanding what you want. Do you want each asset type output to its own column? That sounds like pivoting data as in a CROSSTAB query. How many asset types are there?

    Maybe mock up a table to demonstrate the desired output as you did to show the source 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.

  7. #7
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    Quote Originally Posted by June7 View Post
    Do you want each asset type output to its own column? That sounds like pivoting data as in a CROSSTAB query. How many asset types are there?

    Maybe mock up a table to demonstrate the desired output as you did to show the source tables.
    We have six assets on this form. The rough layout is below. In the recordset, I need to evaluate what type of asset the employee has, check a box on the form that they have it and put the asset number next to the check box. They may have some, all or none of the assets.

    So my recordset is coming back with EmpID, Asset Type and Asset Number (not exact match to field names from above... I don't have that post on my screen right now, but that's the idea).
    chkIDCard (Check box) txtIDCard (text box)
    chkLaptop txtLaptop
    chkProxCard txtProxCard

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I am still lost. I don't see 'asset number'. I am looking for example with data, not control names.

    I really don't understand how that output differs from the way data is saved. Build a query that joins tables so related data can be viewed.
    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.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I am still lost. I don't see 'asset number'. I am looking for example with data, not control names.

    I really don't understand how that output differs from the way data is saved. Build a query that joins tables so related data can be viewed.
    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. Combine and total records in a report
    By GraeagleBill in forum Reports
    Replies: 8
    Last Post: 04-16-2017, 03:29 PM
  2. Replies: 3
    Last Post: 05-29-2014, 09:50 AM
  3. Replies: 7
    Last Post: 06-04-2013, 01:10 PM
  4. Replies: 1
    Last Post: 03-13-2012, 06:11 PM
  5. Replies: 8
    Last Post: 01-21-2011, 10:28 AM

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