Export Form With 11 Sub-forms Not Working With docmd.outputto0

[Tlo]

Hello,

I have a form that has 11 sub-forms that are all based on the same query. The query is a complex one, it has the maximum number of fields allowed (I believe 255). The form in question (frmMaintProposal) is a proposal, and the sub-forms that comprise it really only gather a few pieces of information from the large query (qryMaintEstimate), the rest of the information in the sub-forms is text that is standard for every proposal (company history, terms and conditions, etc.). I have a seperate form (frmMaintSummary) that shows an overview of the estimate, and contains a button with an on click event to create a PDF version of the proposal. When I click this button one of a few things happen:

[Tlo]

Sorry accidentally hit enter.
These are the things that happen:
a) I get an error message (in a loop it seems) that says cannot open any more databases
b) The form exports and opens, but only 2 pages are created and they are both blank
c) The form exports and opens and 3 pages are populated and two of them blank.

Here is the code behind the button:

Private Sub Command276_Click()


If IsNull(Me.cboLength) Then
MsgBox "Please Select Contract Length", vbInformation, "Error"
Exit Sub
Else
Me.RFPstatus.Value = 4
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "frmMaintProposalComm", acNormal, , "[ID] = " & [ID], , acWindowNormal
DoCmd.OutputTo acOutputForm, "frmMaintProposalComm", acFormatPDF, Me.RFPName & ".pdf", True
DoCmd.Close acForm, "frmMaintProposalComm", acSaveYes
DoCmd.Close acForm, "frmMaintProposalDates", acSaveYes
End If


End Sub

Can someone please offer some advice on what might be going wrong? The export process takes around 45 seconds, so something is inefficient somewhere. If i had to guess it would be the massive query that I've built, but I'm not sure. I'm going to try building another query and extracting only the information needed on the proposal from qryMaintEstimate and basing the forms and sub forms on that query to see if that helps with speed.

I'm using Access 2016, and the DB is split into fe and be.

Thanks in advance.

[aytee111]

Forms are not designed to be printed. My suggestion is to make it into a report.

[Tlo]

Thanks for the reply. Unfortunately I don't think I can use a report to do this, at least the way I've got several fields set up now. There are several text boxes on the form that contain iif statements, and unless I'm mistaken reports don't support that kind of dynamic display (which I think is a shortcoming of Access, but I'm a novice so what do I know)
UPDATE: I created a smaller query (about 20-30 fields) now that the proposal formed is based on, and I am no longer getting the "Cannot open any more databases" error message, and the load time has gone from 45 seconds to 10 seconds, so it would seem that the smaller query has addressed at least part of my problem.
The problem that still remains is that only part of the form (5 or 6 of the sub-forms) are loading into the pdf document - does anyone have any ideas on this?

In my code example above I open the form frmMaintProposal before the docmd.outto action and then close it immediately after. I thought that maybe the form was closing before it was entirely "output" (for lack of a better word), so I commented out the line that closes frmMaintProposal, but that didn't fix the issue.

[Micron]

For me, you definitely raise concerns about how many fields you have (255), suggesting your tables may not be normalized. Also, if there are only a few fields from the main query showing up on a subform, that also suggests a design problem. Sub forms are typically used as a many display for the one (main form details) and have their own record sources. The fact that you are getting these sub form details from the main form query also suggests normalization/design issues. Put all this together, along with several calculated fields (the ones with IIF statements) means that Access is going through a huge amount of fields and applying calculations to get data and doing this for every record in some master table instead of ignoring data that it doesn't need when it has been normalized. I would not have been surprised if you said it took 45 minutes, not 45 seconds. Maybe your record limit hasn't reached a critical point yet. I presume these sub forms are not nested, but you don't say much about them. There is a limit as to how many controls you can have on a form regardless of nesting which I believe is 754. With 255 in the main form, you might have exceeded the limit. One more thing - if there is a bunch of information that shows up on every version of the form, why query it each time? It ought to be a label or some other method that is more efficient.

[Tlo]

Thanks for the reply Micron. The massive query I'm referring to - I see what you are saying with labels instead of fields in a table, but the rest of it I'm not sure I know how to get around. The query takes approximately 40 "tasks" (each task will have a value for each record); each task has a measurement, frequency, production standard, resulting time per occurence (calculation), and resulting total extended time associated to it (calculation). That's 240 fields (including the description of the task) alone. Then there are calculations for contract pricing, gross profit, rates/hour, etc, say 10 fields. Then add in all of the customer info, address, etc and I'm at 255. Believe me I'm not arguing that this is ideal or even proper design, just explaining how it is set up now.

How would you suggest breaking that query up?

The subforms are not nested, and really are pretty simple. Like I said a lot of standard info, then price, contract term, customer name, salesperson, etc. And as I mentioned above I created a query and extracted from the large query only the fields that the subforms need to make the proposal - this query is closer to 30 fields.

Question - if query B is based on query A, and I run query B (or a form based on it) does not query A have to run as well as query B?

The end goal I'm trying to achieve is for a salesperson to click a button that says "Create Contract" without them having to fumble in word and adobe.

[Micron]

Question - if query B is based on query A, and I run query B (or a form based on it) does not query A have to run as well as query B?
yes
It sounds like you have a normalization issue. I don't relate to your description of what's going on but I can envision at least 3 separate tables based on my interpretation. I'll bet you allowed a spreadsheet mentality to govern your design. Basically, any THING deserves its own table, and each record in it is for an attribute of that thing. That's putting it very simply. I think you have a situation where to find any ONE value requires a query to look in over 200 fields in every record - very very inefficient. If you took this query back to Pentium II days, you'd start your query and go on vacation. Maybe it would be done when you got back! Plus, it looks like you are storing calculations - generally a no-no. If all of these suppositions are true, then you should read all of these links (or some if you already know the topics).

Normalization is paramount (read as much as you need). Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/index.php/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp

[Tlo]

Thank you for the resources Micron, I'll definitely read them. I think I did have a spreadsheet mentality when building this particular query. Two final questions:
1) Suppose that a contractor has 40 line items on every estimate that he builds. If I'm understanding your statement of every THING needs it's own table, are you suggesting that there should be 40 tables, one for each item on the estimate? I.e. a flooring table, a sheet rock table, a cabinets table, a hardware table, etc?
2) RE: Storing calculations. All of the calculations are being done in the query. Where else would I do them, and how would I not store them?

[Micron]

The general rule is 'normalize until it hurts' but you have to make the decision on how to classify entities, which will be much easier if you work through the normalization part. The goal is not to have a huge number of tables any more than it is to have a huge number of fields in one table. You'll need to absorb the information and use it to compartmentalize things in a way that makes sense for the business at hand. Re your first question, are these not all 'supplies' of some sort? That might be one table. Would that table have an associated table that holds the supply category (hardware, sheet rock, flooring, plumbing...)? Yes if it makes sense. In that case tblSupplies would not usually repeat the supply type for each item (when you have a category table) like this

tblSupplies
CatType	ItemName
plumbing	sink
plumbing	faucet
plumbing	pipe
plumbing	clamp

but you might have

tblSupplyCat
SupplyID	SupplyName
1	plumbing
2	electrical
3	flooring

tblSupplies
CatType	ItemName
1	sink
1	faucet
1	pipe
1	clamp

This is a very simplified explanation that likely doesn't reflect your business needs, but should get my point across.
Last question - generally speaking, calculations are never stored, regardless of how or where you arrive at them. The exception would be where one or more values used in the calculation is subject to change (such as an item price) and you cannot avoid affecting historical data without storing the result. In the past, I have used an invoice as an example (past invoices would be affected if you only used a current price x quantity calculation in the invoice). Not being an expert in financial database design, I'd say one might store the calculation in that case, or at least the price at the time of purchase. Other than that, it is not advised.