Results 1 to 14 of 14
  1. #1
    RnGWorx is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Location
    Reno, Nv
    Posts
    8

    Intuitive report formatting question?

    I am developing an Access 2016 DBMS for my company. I need it to generate 10 different types of legal documents. I have already created and formatted these reports based on the versions provided by the Nevada Supreme court. Each report is opened by clicking a button on the main form. However, several of the courts (designated as [Township] in the program) here have different formats for the same document. All of the information is exactly the same, but it is displayed on the document differently.



    My question: Is it possible for a single report to change its format based on [Township]?

    If not, is it possible to use the code builder for each button to have Access open up the separately formatted reports simultaneously?

    I recognize this is a complicated question and not spelled out very precisely. I think I'm mostly looking for help to better define what I am trying to do as this is an area of development that I have never attempted before. Thank you all again in advance!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Is it possible for a single report to change its format based on [Township]?
    Possibly. It depends what you mean by format and how different they are. If it is just layout and each bit that can be laid out differently is displayed in separate controls then look at the report onformat event for the relevant sections to move each control around

    Your best option is to show some relevant data and some examples of how it should be laid out

  3. #3
    RnGWorx is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Location
    Reno, Nv
    Posts
    8
    Different Formats.zip

    Thank you for the quick reply, I have attached an example of the differences in formatting. These are the same legal document, but as you will can see the format is very different. The first is the same format as what I have in the report, the second is for a different court, but requires the same fields.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    As you say, effectively the same document, but completely different styles.

    You will need a template for each style of report since there is no or at best very little commonality between the two styles. The template will need to include the location of each required field (name, address etc) so formatting on print is not really an option.

    You may be better creating the documents in MS word and using mailmerge rather than using an access report.

  5. #5
    RnGWorx is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Location
    Reno, Nv
    Posts
    8
    Its funny that you say that, because that is how the company is currently doing business, and it has worked fairly well up until now. By using mailmerge, we have to create a new excel and word document for each request from our clients. Effectively slowing down our services due to how many notices we send out each month and the number of clients.

    If I understand what you are saying, I need to create a separate report formatted specifically for each township then?
    If so is it possible to write an event procedure for the "print nonpay" button on the main form that will open up both reports with one click?

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You'll need a separate report for each township. Providing the township is noted against the client? the landlord? then you can loop through the records and use a case statement to determine which report to generate. Without knowing your able structure, reportnames etc Perhaps something like where each report is named after the township and with a prefix of 'rpt'


    Code:
    dim rst as recordset
    set rst=currentdb.openrecordset("SELECT * FROM tblLandLords INNER JOIN tblTenants WHERE '''payment is late''' etc etc)
    while not rst.eof
        docmd.openreport "rpt" & rst!township,,,"TenantID"=" & rst!TenantID
        docmd.outputto.....
        docmd.close acreport  "rpt" & rst!township
    wend
    set rst=nothing
    this is just 'air code' to give you an idea - you'll need to check for grammatical and syntactical requirements

  7. #7
    RnGWorx is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Location
    Reno, Nv
    Posts
    8
    Ajax! Thank you very much for your help! I definitely have a better idea of how I'm going to make this work now.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    another option that may work a bit quicker if you have volumes to print at the same time is to run each report in turn - with the same recordsource but with criteria to filter for that particular township - so print 3 from townshipa, 10 from b, 2 for c etc

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If so is it possible to write an event procedure for the "print nonpay" button on the main form that will open up both reports with one click?
    I'm probably not interpreting this correctly (it reads like you want to 2 or more reports that are formatted differently due to the difference in townships, but are going to the same person) so I don't understand the need.
    Consider providing a thorough description of the current process, using no jargon, if you want to "pick our brains" on ways to integrate this with Access. Ajax has provided a viable approach, and I'm wondering about a possibility: if sending multiple reports (for one township) to many persons, opening the report once and having each person appear on a separate page could negate having to open and close the report for each person. Partly it depends on what the output is (paper or electronic, pdf, Word, etc.) so that's why a thorough explanation would be valuable.
    Last edited by Micron; 06-09-2017 at 07:11 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    RnGWorx is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Location
    Reno, Nv
    Posts
    8
    Hi Ajax and Micron,
    First, thank you again for you help. Micron, you're right, my initial question wasn't very clear. To clarify, the company prepares and delivers these notices to the individuals as requested by the clientele. Each client can have one or multiple requests in one or multiple townships. The company operates in multiple townships(i.e. Reno, Carson City, Sparks) but only one notice is prepared and delivered to each individual recipient.

    Currently I can (using 1 report) send out as many notices as I need with each new record showing up on a subsequent page of the same report, as long as they are all in the same township, because different townships use different formats (see different formats.zip in earlier post).

    I think I have a better idea of what I am trying to do now. You will have to forgive me if this is code is completely wrong (It has been 10 years since I've taken a VBA course, and am self taught since then), but I think from what you have shared with me, and a little more research I have figured out how to show what I would like the command button to be able to do. I have added notes describing what "I believe each line of code should be doing, but first a little contextual info.

    I receive requests for service via email with an excel spreadsheet attachment that gets imported to the main table (after some in office editing).

    I view these requests via my main form which is linked to the Query "QNotices". When I open the Form it gives me 2 prompts "Date of Service?" and Batch #?" (The batch number is there so that multiple batches of evictions can be completed in the same day if needed)
    After entering the date and batch number I'm presented with all of the Notices of every type that need to be printed.

    I have command buttons on my form that right now link to a specific report based on the Ev Type, but as you know so far I want them to open the different reports based on the required format for each Township.

    Without further adieu, here is my first attempt at actually coding this. obviously it did not work when I attempted to run it, but in my eyes its a big step closer to what I need in the long run. Hopefully, this gives you a better understanding of the desired end result, and if I am asking too much please don't hesitate to tell me. I don't want to over step the line of "free advice."


    Private Sub Command28_Click()
    dim strTownshipName as string (Identifies variable)
    dim rst as recordset ("" "")

    strTownShipName = "Township" (Defines variable) I'm not sure on the syntax, but this should link the variable to the field [Township] from Query "QNotices"
    set rst=currentdb.openrecordset("SELECT * FROM QNotices WHERE & [Ev Type] & = "NonPay") (recordset that pulls records from Query QNotices where EV Type is "NonPay")
    while not rst.eof (starts the loop)

    Select Case strTownshipName (I added this because almost always there will be multiple records that need to be part of each report, and in my logic this was the closest thing to being able to do that)


    Case "Reno, Sparks, Dayton, Fernley" (Definitely not sure of the syntax here, all of these townships use the same format though)
    DoCmd.OpenReport "HBNonPay", acViewPreview (case result)

    Case "Carson City"
    DoCmd.OpenReport "CCNonPay", acViewPreview


    Case "Minden"
    DoCmd.OpenReport "MDNonPay", acViewPreview


    Case "Gardnerville"
    DoCmd.OpenReport "GDNonPay", acViewPreview

    End Select
    wend
    set rst=nothing
    End Sub

  11. #11
    RnGWorx is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Location
    Reno, Nv
    Posts
    8
    An additional comment,
    If it was ultimately me that was going to use this system, none of this would be a problem. I would be able to use the approach that Ajax recommended, "another option that may work a bit quicker if you have volumes to print at the same time is to run each report in turn - with the same recordsource but with criteria to filter for that particular township - so print 3 from townshipa, 10 from b, 2 for c etc.

    Unfortunately, I am having to make this work for someone who has never used Access before, and barely knows how to use Excel. So everything I am trying to accomplish at this point is user friendliness, but if my very limited experience is any kind of queue, that seems to be the name of the game here.

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    users shouldn't be seeing the code - your challenge is to write the code and store the data in a way that for the user 'it just works'. All they do is effectively perhaps enter a date and click a button. They should not need to know any more. If you are concerned that the code might fail and the user is required to go into the code, then you have a problem which you cannot solve by 'keeping it simple'.

  13. #13
    RnGWorx is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Location
    Reno, Nv
    Posts
    8
    Agreed, I am doing everything I can to make this as simple as possible for the end user. Which in turn has created new challenges for me such as this one.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There is a wide margin when it comes to "free advice". I draw the line at doing someone's homework if that's what drives the request.
    OK, my two cents. I think we've established that these reports are not similar enough to manipulate, so you need 3 reports. However, I would not make choices based on code as you are doing. Should you need to add or remove a county, you are editing code. I presume you have an easy way of adding a new county, or editing the county name should they change it, without having to go into design anywhere. Design should eliminate having to add any table fields (thus likely form controls), combo list options, code edits due to option changes, etc. and forms/reports should be reused as much as is practical. Code should be kept to a minimum in your case.

    I would have
    - a reports table (rptName [uniquqe] and CountyID at least) so you can add as many reports to the list as things expand or contract. rptStatus would be one way of keeping a report in the system but not allowing users to choose it, but for me, this would require tblStatus if you want to allow for easy expansion. A simple yes/no solution may suffice, so a meaningful name for such a checkbox field might be IsCurrent, and it's either checked (True) or not.
    - a query that provides the data to each report. If need be, this is 3 queries if their criteria fields are different. If not, there is no problem in using the same query for all 3 as long as it references all the fields used across the 3. A report that doesn't need a couple of those fields just doesn't have them on the report.
    - a form would have an unbound combo whose rowsource gets the report name (1st column, invisible) and county name (2nd column visible). The open report button click would open the correct report based on the combo's county selection. The report knows which query it is bound to. If need be, the query can get other criteria from combo or textbox values, but you might want to prevent running the query if either is blank. To allow either or both to be blank and still be able to pass values to query criteria fields would require code to handle the possible combinations or a bunch of OR rows in the query design grid.
    - structure the report to support the process. If you group by recipient for example, you should be able to have all the fields repeat on each page (including the header) so that it looks like the same repeating report but each page is a different recipient. I presume there would only be one notice going to one person in arrears. If not, the 'keep together' options might split the 2 records going to the same recipient into 2 separate pages. This is based on the assumption that printed pages are being delivered to the client, or sent to the recipient (the one in arrears) from your end. If not, then the process I described might be incomplete. If the pages can't be split to suit, then I guess that report will have to open, print, close and reopen with a new recipient. That would require code, but I'd try the other way first.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Formatting Question
    By spyldbrat in forum Access
    Replies: 2
    Last Post: 06-12-2017, 01:46 AM
  2. Formatting Question
    By MTSPEER in forum Access
    Replies: 3
    Last Post: 01-01-2015, 05:24 PM
  3. Replies: 10
    Last Post: 02-14-2013, 12:12 PM
  4. Newbie VBA question (conditional formatting)
    By alhas in forum Programming
    Replies: 7
    Last Post: 01-16-2012, 08:07 AM
  5. Basic question about forum formatting
    By karmacable in forum Access
    Replies: 1
    Last Post: 09-20-2011, 02:13 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