Results 1 to 5 of 5
  1. #1
    ItsRoland is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    111

    Creating a Field in a Continuous Report?

    I haven't messed with reports much, so bear with me on this.



    I've got a report that summarizes potential security scenarios about the assets in the database. I'd like to include some additional information onto this form, but using the "Add Existing Field" option causes a ton of duplicates. Basically what's happening here is, since each scenario and value of this new field is tied to an asset but are not directly related, when I put this additional field in the report it displays each scenario an additional time for each occurrence of this new field. There's probably a word for this that I can't think of, and I'm probably explaining it very poorly, so I apologize if this is the case.

    What I'm trying to do is throw all the values from the field I wanted to add into a single label that I've added manually. The result I have in mind will look something like this:

    Field: Value 1 | Value 2 | Value 3 | etc.

    I set up an OnLoad event for the report that checks "Me.AssetID.Value", uses that to grab all the values of the field I wanted to add, and throws them in a label formatted similar to my above example. When I open the report, it only displays the first value, and the rest aren't present. Long story short, I figured out that it was putting all the values in the label, and then going back to only the first value when the loop was done for some reason. Not only that, every occurrence of the label was using the values for the first Asset, and not the one they're displayed with.

    Is there any way to make this work, where I can display the values relevant to the AssetID the label is with in the report like it does for the rest of the fields?

    I apologize if I'm not making any sense, I'll try to sum it up in a way that might be easier to understand:


    I'm trying to add values to a continuous report that I can't add in via "Add Existing Fields" because it causes duplicates. However, when I do this via a label and VBA, it doesn't take the ID associated with that part of the report, and instead just repeats the information from the first ID for every ID on the report. Is there a way around this?


    Any help is appreciated. If I need to post more information just let me know. Thanks!

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You are trying to concatenate a query result.
    The proven method is to use Allen Browne's code from here - http://allenbrowne.com/func-concat.html
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ItsRoland is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    111
    How would I use this in a continuous report though? I must be missing something.

    I tried setting the control source of a text box in the way its shown in the link. But the 'where clause' part of the statement I used is ["AssetID = " & Me.AssetID.Value] so that it takes the appropriate ID for each section of the report, which the control source can't recognize. If I set the control source in VBA instead of properties, I'm prompted to enter a value for a parameter that's just a list of the values I want it to display.


    How would I set it up so that each instance of the text box shows values for the ID it's correlated with?

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Pull the data into the forms underlying query - probably the easiest way is by creating a separate query and linking it by ID.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    ItsRoland is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    111
    Apologies for the late response.

    Using the underlying query of the report was what I was looking for. I actually ended up not using the code you linked because I was able to achieve the desired result with only a few lines.

    I just wrote a function that grabs all the values I need and saves them to a string in the format I wanted, i.e. Value 1 | Value 2 | Value 3 | etc., and called the function in the report's query.

    Your assistance was much appreciated!

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

Similar Threads

  1. Replies: 16
    Last Post: 03-13-2018, 05:24 PM
  2. Replies: 6
    Last Post: 05-21-2017, 01:12 PM
  3. Creating a filter for continuous forms
    By NJMike64 in forum Macros
    Replies: 2
    Last Post: 02-17-2016, 10:44 AM
  4. Replies: 14
    Last Post: 03-22-2015, 05:37 PM
  5. Creating a report based on partial field search
    By pirseinkim in forum Reports
    Replies: 1
    Last Post: 03-22-2013, 11:08 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