Results 1 to 10 of 10
  1. #1
    hehrerh is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    35

    How can I make this marketing map database VBA more efficient?

    Hey everyone,



    This is a project I put together to print live marketing maps. I'm pretty happy with how it's working, although I would like to find a better way to run the vba print to folders.

    Right now I have an individual vba code for each marketing map that prints the map to a .pdf and saves the map to a specific folder.

    Is there a better way to code this which would consolidate the code? Any way to reduce the number of vba modules and functions would be great. Reducing the number will help from a maintenance perspective.

    I cut out a lot of the maps - there are about 60 total.

    Thanks!

    Howie
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your database is dependent on your .png files which have not been included in your zip.

  3. #3
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    You have 6 identical functions with various parameters hardcoded. You need to have one function and pass the arguments to it.
    also you dont need a function as your not returning a value, so a sub should do.


    Code:
    Public Sub MySub (vProjectname as string, SelectedReport as string, MyPath as string,etc. etc....)

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I'd like to have the image files you're using to see what this actually does without errors as well. You also have linked tables to a back end that wasn't included in your sample database which is causing other errors. You also have code for dozens of command buttons all having the same code, only 6 of which appear to be connected to buttons is there a reason for that? Lastly, moke123 has pointed out something very relevant, the point of having functions is to repeat common things with minimal effort. While don't necessarily agree a function needs to return a value you can generalize your modules into a single function probably by passing the report name and maybe some of the other items hard coded into your application. For instance you can make the export locations dependent on where your database is and use the currentproject.path to return the current location of your database and figure out your subfolder structure from there. Maybe even have a table storing relevant report specific details that you've got hard coded and just looking them up when it's time to export.

  5. #5
    hehrerh is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    35

    Revised Files

    I tried to clean some things up for you guys - here's the link as the file is too large to be hosted on the thread:
    https://www.filehosting.org/file/det...%20Example.zip

    ..That includes the db and the image files - in the reports you may need to re-link these to get them to work.

    I guess I'm not clear on the difference between a function and a sub.

    The way I see this working is that there is some common bit of code that can be recycled for any report. The three variables that are specifically related to each report are
    1. ProjectName "Cook's Crossing North ph. 1"
    2. The report "rpt_MM_ALDN01"
    3. The output path folder file path "\\CAPPUCCINO\SalesResources\Promotional Materials\Communities\Cook's Crossing\Live Marketing Maps\North ph. 1

    Usually I would pass this data from a table or query into the function, but because this information is not stored in a table I don't know how to get it there. Should I create a table that includes these three data points for each map?

    I can't wrap my brain around how the code should work. I feel like there should be one function/sub (still foggy on the difference) that does the bulk of the work, which uses the aforementioned three variables.

    Feel free to make edits to the db if you have a clever idea.

    rpeare - I removed a bunch of maps and buttons to simplify the application for your reviews. However, I didn't go back and strip down the vba code on the switchboard form. Basically, picture the same 3 buttons for 50 more projects, and that's what all of that code references. These buttons 1. print the report to a folder (by running the relevant function each saved as a module), 2. open the folder, and 3. view the report in print preview.

    ...also, I have four queries for each report. If you have any thoughts on how to consolidate these so that they are recyclable for each report that would be wonderful.

    Thanks!

    Howie

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Both subs and functions are procedures. Both can accept parameters (arguments) as inputs. Only a function can return a value to whatever called it.
    Regardless of whether or not it returns a value, only a function can be called by certain things. Examples would be a toolbar, menu or ribbon button. Also, to substitute a custom procedure for a built in event (e.g. command button click) only a function will work.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Sorry I'm not going to give out my email etc to download your file. Try uploading the image files one at a time.

  8. #8
    hehrerh is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    35
    Any clever ducks think they can help me out here??

    Thanks,
    Howie

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you tried compact and repair on your database, then create a zip file to attach to a post?
    Many will not go to an outside link to get your file --especially if you have to supply info to download.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It seems you have coding ability and several good suggestion. If access to db remains an issue, don't those suggestions provide a path forward?
    I have email accounts that I use for such things but am temporarily hampered (dominant hand in a cast) and typing is a real PITA) presently. Too much so for a lot of code work.

    Consolodation is KEY - e.g. not 3 buttons for 1 process. 1 for all 3 etc.

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

Similar Threads

  1. Replies: 4
    Last Post: 04-26-2019, 10:57 AM
  2. Replies: 4
    Last Post: 09-19-2017, 12:28 AM
  3. Replies: 5
    Last Post: 02-02-2017, 08:07 PM
  4. looking for guidance on designing efficient database
    By mai0f in forum Database Design
    Replies: 3
    Last Post: 12-10-2015, 02:36 PM
  5. Which would be more efficient
    By BRZ-Ryan in forum Forms
    Replies: 5
    Last Post: 12-28-2013, 03:41 PM

Tags for this Thread

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