UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dynamical Resize Of Subreport, Access 2016    
 
   
mattmurphy16
post Jan 14 2020, 05:04 PM
Post#1



Posts: 6
Joined: 27-February 19



Hello All,

This is my first post here. Seems like a lively community with very knowledgeable and experienced members, so I'm hoping you can help me.

First, a little background about my project. My company ships food products to different countries around the world. We apply dual language labels to products in our warehouse prior to exporting for some customers. I’ve created a DB to better control the information we put on these items. I’m very close to being able to use this, but my main hurdle right now is addressing the size of the labels we’d like to use.

I have a main report (8.x5” x 11”) with the product label with text boxes for all the various fields that might need to be included, with the “can shrink” property set to yes for everything. That way, the field disappears if there’s no data and everything else gets moved up. I’ve also got the “can grow” property set to yes. This allows things like manufacturer’s address, ingredients, etc. to adjust for each item. This is used as a subreport on a form that prompts the user for the item number, production/expiration dates, etc.

I had been trying to manually create individual reports with the page size setup to accommodate each of the label sizes we use (3” x 4”, 4” x 6”, etc.) and adjusting the text boxes and text size down accordingly. This has been pretty painstaking though, given the large number of fields and very small sizes of the fields. And even then, I’m not sure if it would work 100% of the time. What if the information is too much for our largest size label?

Ideally, I’d like to have a catch-all solution to ensure the data fits on a particular label size. I’m wondering if I could hard code a “fit-to-page” function or a “dynamically resize” type of thing. I’m wondering if it’s possible to programmatically adjust the dimensions of each text box and text size if certain conditions are met. Or even have various blank reports with the label sizes and my main label report shrunken down as a sub report to fit on one "page".

Any thoughts, guidance, advice, etc. from you lovely people on how I might achieve this would be much appreciated.


Thanks!

Go to the top of the page
 
June7
post Jan 14 2020, 05:14 PM
Post#2



Posts: 1,257
Joined: 25-January 16
From: The Great Land


Changing textbox properties in a report requires VBA code in section Format or Print event. I've never tried setting control and font sizes but suppose can be done. What criteria would determine setting?



--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
projecttoday
post Jan 14 2020, 09:42 PM
Post#3


UtterAccess VIP
Posts: 11,638
Joined: 10-February 04
From: South Charleston, WV


welcome2UA.gif

What kind of labels are these? Why can't you use the largest size for all of them?

--------------------
Robert Crouser
Go to the top of the page
 
mattmurphy16
post Jan 14 2020, 10:26 PM
Post#4



Posts: 6
Joined: 27-February 19



Very astute question @projecttoday. Perhaps I should have explained a bit more in my original post. These are basic white sticker labels that are printed by heat transfer on labels of various sizes. Our largest labels are 4" x 6". These would work find if we're just slapping them on the outside of a box. However, we have to apply the labels to the inner units inside each master case as well. The products we are dealing with are foodservice items you'd see in a restaurant. Some items can be smaller. We also have to be careful not to give the impression that we are deliberately covering up information on the manufacturer's label. Think of a bottle of ketchup that sits on the table, for example. A 4x6 label would be too large.
This post has been edited by mattmurphy16: Jan 14 2020, 10:28 PM
Go to the top of the page
 
mattmurphy16
post Jan 14 2020, 10:47 PM
Post#5



Posts: 6
Joined: 27-February 19



@June7, I was thinking the criteria would first be number of fields present. One of the things I need to include is nutrition facts. The FDA lists several things that could be included in the nutrition facts, but aren't always present. You don't see magnesium in the nutrition facts for everything. I purposefully included a field for anything that could show up that we would have to include, with the expectation that several of them would be blank and thus disappear on the report.

The next criteria would be length of the ingredients. Some things only have a few ingredients. Like salt. This section would be one line. Another item, like a salad dressing, cake, jalapeno popper, etc., may have an entire paragraph of ingredients. This would cause the height of the ingredients field to grow.

I've been pondering a way to identify the total height for all the fields using VBA, then performing some calculations to adjust accordingly. For example, if I'm trying to squeeze everything on a 3" x 4" label, but I've got 5" in height forth of information, have VBA squeeze it down to 2.5" (accounting for quarter inch margins). I'd assume this would be more of a text size adjustment than a control heigh adjustment, given the "can shrink" function is enabled. Thoughts?
Go to the top of the page
 
June7
post Jan 15 2020, 12:38 AM
Post#6



Posts: 1,257
Joined: 25-January 16
From: The Great Land


Sorry, I am just lost.

Why would you put nutrition label on product container that already has nutrition label?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
projecttoday
post Jan 15 2020, 03:46 AM
Post#7


UtterAccess VIP
Posts: 11,638
Joined: 10-February 04
From: South Charleston, WV


So you're printing some labels and some of the labels are going to be 3 X 4 and some of them will be 5 X 6. Or whatever. That means you will have to divide the printout since label forms only have one size on them. Where are you stuck on this? (What little label printing experience I have was years ago.)

--------------------
Robert Crouser
Go to the top of the page
 
mattmurphy16
post Jan 15 2020, 06:13 AM
Post#8



Posts: 6
Joined: 27-February 19



@June7, no worries. I appreciate the interest. Our labels are mostly English/Arabic for shipping to the Middle East. Including nutrition facts in Arabic is one of the regulatory requirements. We include English as well to help our staff catch any changes on the manufacturer level over time, which flags our translator to update the Arabic portion.
This post has been edited by mattmurphy16: Jan 15 2020, 06:13 AM
Go to the top of the page
 
mattmurphy16
post Jan 15 2020, 01:55 PM
Post#9



Posts: 6
Joined: 27-February 19



@projecttoday, I'm stuck on deciding the best approach to take. Yes, I could develop different reports and adjust the page size of each to a particular size of a label (i.e. 3 x 5), but I'm worried this won't account for every possible situation. If there's a product with a very long list of ingredients, will it be too big for our largest label size?

Right now, I have a form where the user enters the item number to pull up the information for the required label. I have a sub report on the form that shows the label information. The sub report data source is a query, where the query parameter is set to the text box I have on the form. I have some VBA setup to rerun the query on exit of the item number text box, which updates the label.

I'm wondering if I might be able to total up the heights of each text boxes after the re-query to determine the total height of data that I need. Then if it exceeds the height of the label, reduce the text size by some amount and re-run the test. Now that I think about it, this would probably have to be a loop.

Which leads me to another question. Are text sizes only available as integer values? I've tried adjusting the height to a decimal value on the format tab and it always rounds. Perhaps I could override this in VBA?
Go to the top of the page
 
projecttoday
post Jan 15 2020, 03:30 PM
Post#10


UtterAccess VIP
Posts: 11,638
Joined: 10-February 04
From: South Charleston, WV


As far as I know font sizes are whole numbers.

--------------------
Robert Crouser
Go to the top of the page
 
mattmurphy16
post Feb 6 2020, 03:03 PM
Post#11



Posts: 6
Joined: 27-February 19



Hello All,

I just wanted to share my progress in case someone stumbles upon this same issue later on.

I ended up starting from scratch, almost. I made a new form with an unbound text box for my item number. On this form a I have a subreport setup to the size of my label (3" x 4" right now) and two text boxes, one of the left for English and another on the right for Arabic.

I have some VBA running that takes the item number from the form, builds a SQL string to select all of the required data points from a table where Item Number = the item number my table. I then opened a recordset using the SQL string.

From there I have the code building a long string of text by concatenating all of the various fields from the recordset, adding in label names (i.e. "Ingredients: " & rs!ingredients), and a line break. I then set the textbox on the sub report to equal the text string.

For the resizing, programatically set the text size to something to big for my entire textbox. I converted the size of the label print area (label size - margins) into TWIPs. I have a DO WHILE loop that basically says something like this:

CODE

Do While Me.SubreportName.Report.TextBoxName.Height > LabelHeightTWIP
    
     Me.SubReportName.Report.requery
     Me.SubReportName.Report.TextBoxName.FontSize = Me.SubReportName.Report.TextBoxName.FontSize - 1`
     Me.SubReportName.Report.AutoResize = True
     Me.SubReportName.Report.TextBoxName.FontSize = LabelHeightTWIP

Loop



It's got a bit more to it but that's the gist of it. Hope it helps someone later!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th February 2020 - 10:35 AM