Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89

    Giving a report a number


    Hi guys,
    It's been a long time since I have posted! I'm posting this question to get a feel for options for giving a report a unique number. The report is based on data in two tables but at this point there is a lot of data already and the structure interacts with other things so while I could restructure the tables to achieve the ends I want I am hoping there is a way to simply have a sequential number on the report without that number being pulled from a table. Seems like a long shot and isn't an ideal solution, but does anyone have any thoughts or experience on doing this?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    You could use an Auto-Number field if the number just needs to be unique. You may already be using one as a Primary Key.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Depends on what you mean by sequential. If you mean no gaps then don't use an autonumber field. Regardless, a table will have to be involved one way or another as it is the only way to increment a value and know what the next value needs to be.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If you don't want to use a table, how about the registry?

    GetSetting function (Visual Basic for Applications) | Microsoft Docs

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Maybe use a running sum in a report control? Look at the line number example here:
    https://support.microsoft.com/en-ie/office/counting-in-reports-489791e0-dee2-46dd-8a72-f835998bd72e
    Cheers,

  6. #6
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Thanks everyone for the feedback. I think I have to accept that I will need a reference in a table somewhere. I didn't think this part through and have been using it a while so I was hesitant to go back in and rearrange things. I think what I need to do is write out a description of what I have and that will help me work out what the next step should be.

    The report is based on a query involving 8 tables, though only 2 of those tables are important for this problem. The main table which ties everything together is tblAssemblies, which contains information about assemblies I make for customers (Things such as a part number, description and type of assembly), and the second table is tblQuotes, which contains pricing information for those assemblies based on particular quantities. One assembly may appear several times in tblQuotes if there are several different quantities (I provide pricing at several quantities as there are volume discounts that apply). Entries in this table appear in a subreport. Because of this the PK in tblQuotes, QuoteID, is not a number I can use to run the report as I usually need several entries from tblQuotes to appear in the subreport.

    I am wondering if adding a table to document the report number and a junction table to connect that number to the necessary QuoteID records would be the most non-invasive way to add this function in.

    The report is generated by a macro in a command button, so I could insert into the macro an append query to update the tables mentioned above prior to running the report.

    Again, I appreciate the feedback here, and thank you for all of your patience, I know my original post was really too vague to be of much use.

    Also a special thanks to Dave and Micron - you have both been super helpful to me over the last three years, something I will not forget!

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I am wondering if adding a table to document the report number and a junction table to connect that number to the necessary QuoteID records would be the most non-invasive way to add this function in.
    So the requirement is not just a sequential report number, it is to tie that report number to a quote? Why not just use the quote number? It would probably help a lot if you gave some examples and explain where the number comes from if that's relevant. I don't see a need for a junction table if you have tblReportNums as

    RptNumID (PK auto) | QuoteID (FK) | ReportNum

    I think you'd just join on QuoteID and load the report with ReportNum. In the end, I don't see the sense in this if you can just use the unique quote value as the report id. That's why more clarification would help.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Quote Originally Posted by Micron View Post
    So the requirement is not just a sequential report number, it is to tie that report number to a quote? Why not just use the quote number? It would probably help a lot if you gave some examples and explain where the number comes from if that's relevant.
    Thanks for replying, and my apologies, I am struggling to find the words to explain things without the whole backstory... I would upload a blank copy for review but this is a small part of a large system. Doesn't help that the design for this particular aspect of it was poor to begin with.

    The main report fields are based on records in tblAssemblies, for each of which there are many related records in tblQuotes, which is the source of the subreport, so many QuoteIDs relate to one AssemblyID. As time progresses I may have to rerun the quote when the price or design of the assembly changes. I appear to need to be able to relate many QuoteIDs to many ReportIDs (Hence the junction table), but I don't necessarily need to be able to reproduce the previously run reports as I just want to issue a unique number to each one.

    I will try and upload a copy of this today and provide further information. Again, I am sorry for my inability to explain this.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I will try and upload a copy of this today and provide further information.
    Copy your db; remove what isn't necessary to replicate the problem, test to be sure the problem can be replicated with what's left, then compact/repair, zip it and post here. If you have confidential info to worry about, see if this will help.
    Make sure you clearly describe what to do with your db and what the result should look like. If it is split, don't forget the data.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    BVMRP1 copy for forum.zipBVMRP1 copy for forum.zipOK, I went through and removed/changed data as needed for confidentiality. When you open it a main dashboard form should open. On the left side are several button controls, click on the one which says 'Main Assembly Form'. The report generation is triggered by a command button which reads 'Generate Quote'. The resulting report is the one I would like to have numbered. The record source for the report is a query (Not a saved query but one written into the record source property of the report) that draws relevant info from tblAssemblies, related customer info from tblCustomers and other related tables, as well as information from tblQuotes related to the assembly ID.

    It's worth also noting here that tblQuotes does not contain every price ever generated; in order to get around my own shortcomings each time a new price is generated for any given assembly I delete the pricing that was previously on file. This is sloppy though it does function for my needs, and is done with a command button on the 'Current List Price' tab of of the 'Main Assembly Form' (frmAssemblies), after deletion I navigate back to the 'Assembly Quote Pricing' tab and use the command button labeled 'Update Price List' to append the new pricing.

    I hope that's enough information but please let me know if I need to elaborate more.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you forgot something
    and what the result should look like.
    So what is the report number supposed to look like? Quote ID? Quote ID & Assembly ID? One of those and PO number?
    Curious about what AssyID is in tblQuotes. It sure isn't AssyID from tblAssemblies.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    I will always forget something. Just ask the Mrs.
    What the result should look like: All I need to be able to do here is have a sequential number, probably in the top left between the date and the customer name, that someone can refer back to, instead of "... that one quote you sent on such and such a day..."

    So what is the report number supposed to look like?
    I would like to just have a sequential number, starting at 100. No need for anything more than that, and the report number won't be used for any kind of reference in the database, the reports get printed as PDF files, so the number will be used when saving the file and communications after the fact.

    Curious about what AssyID is in tblQuotes. It sure isn't AssyID from tblAssemblies.
    This looks goofy because I put some formatting on the field in tblAssemblies. What I didn't know at the time was that, when using a combobox to search for a record, Access doesn't recognize the formatting, so I will be removing that at some point. I have a couple of other tables with similar formatting. As long as I just use the whole number ID ('1' instead of 'ASSY00001') when using the comboboxes I get where I need to be.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You need to tie the report number back to something that's static in the db.
    - You don't create a report for every quote or you'd have hundreds of reports that all basically look the same, right?
    - And each time you open the same quote report, you don't want to advance the number so that when I open it today it's 100 and tomorrow it's 101.

    I don't understand the whole function of the db so I'm not seeing why you don't use the AssemblyNumAndRev data (if it's unique) OR a unique quote value, or the AssemblyID - something. I can create a table to store the last number used (or every one created) but I have no idea what it's going to relate to (per bulleted points). It kind of boggles my mind that you're placing significance on a report number instead of the quote/assembly but I have to believe that the quote is of primary importance if it identifies a build. If it doesn't identify a build because it gets altered and quoted again, then that should be a new quote and forget the old one - it gets a status of "cancelled" or something.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    To answer the first point, and the second:
    Each time I hit the 'Generate Quote' button the resulting report is saved as a PDF. That PDf is then sent to the customer, as well as being saved in two places along with being stored in my email. After the PDF is created I don't need the report any more. A new report is run each time something changes on the assembly - let's say the customer wants a different part in their assembly from what was on the original design. In addition the pricing listed in the report is only valid for 30 days. As such I essentially DO want a new number each time the report is generated, even if it is for the same assembly, even if nothing has changed. That number doesn't need to relate to anything as I won't need to recreate the report that it was assigned to and the number is used outside of the db as a reference for communications.



    Quote Originally Posted by Micron View Post
    You need to tie the report number back to something that's static in the db.
    - You don't create a report for every quote or you'd have hundreds of reports that all basically look the same, right?
    - And each time you open the same quote report, you don't want to advance the number so that when I open it today it's 100 and tomorrow it's 101.

    I don't understand the whole function of the db so I'm not seeing why you don't use the AssemblyNumAndRev data (if it's unique) OR a unique quote value, or the AssemblyID - something. I can create a table to store the last number used (or every one created) but I have no idea what it's going to relate to (per bulleted points). It kind of boggles my mind that you're placing significance on a report number instead of the quote/assembly but I have to believe that the quote is of primary importance if it identifies a build. If it doesn't identify a build because it gets altered and quoted again, then that should be a new quote and forget the old one - it gets a status of "cancelled" or something.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, see if this does what you want. First go in to tblReportNo and reset ReportNum to 99. The PK autonumber isn't required, but it's there out of habit and in case it's needed later. I replaced your embedded macro thinking I might teach about some code but I later saw you have similar code elsewhere.

    Notes
    - only 3 form modules have Option Explicit in them - should be in all. I always have this option turned on.
    - your code doesn't compile. Perhaps that is because you removed something for your post.

    BVMRP1_Micron.zip
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-15-2018, 09:39 PM
  2. Replies: 5
    Last Post: 11-30-2014, 12:46 PM
  3. Replies: 7
    Last Post: 10-29-2014, 11:48 AM
  4. Replies: 2
    Last Post: 10-27-2014, 01:23 PM
  5. Giving a specific number to some data
    By Knelou in forum Forms
    Replies: 1
    Last Post: 09-12-2013, 08:45 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