1st subreport is accurate, 2nd less, all others not

[tstoneami]

If I print a single report its good every time. If I print a batch with a where in the docmd, it prints the correct records - but the logic begins to fail after the first report on the sub reports.



The random data that does print on the sub report is accurate.

If I go into debug mode, as it steps, I see that the me.field values it is checking are not accurate. Its as if the sub report on format logic is acting on the wrong record at run time.

As mentioned, a single report is fine with any of the reports that batch up. The number of records found is accurate - but the logic fails because it appears to be acting on the wrong record after the first record. Everything seems to be set up properly.

I am quite confused about this.

Thank you for any interest - I am behind the 8 ball on this one and would be willing to compensate for a solution - sorry if that is in bad form.

Regards,

t

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[tstoneami]

I will do that later today.

[tstoneami]

Thank you June, and anyone else who might want to help. More than grateful...very thankful for the community here. After my first solid week with Access, I can see the possibilities...

The upload manager states that files will be deleted after 1hr. Please let me know if I have to resend it again. Thanks!

UPDATE: The relationship field was different in structure (one was long, one was decimal). I made them both integer/decimal auto - same results. This was Record ID in the Loans table, and the Related Loan field in Loan Reverification.

[June7]

Why do you have RecordID# and LoanNumber fields in Loans? RecordID# is not autonumber so you have to manually populate both fields? The LoanNumber could be primary key. PK/FK fields must be same data type.

Advise not to use spaces and special characters/punctuation (underscore is exception) in names. Better would be RecordID or RecordIDNum or Record_ID_Num.

If RecordID# is the PK/FK then don't need LoanNumber in LoanReverification, nor should have the borrower name or other info from Loans.

Interesting use of the checkboxes. However, the code to construct the WHERE criteria for screen output doesn't look right if you want criteria built on multiple checkboxes.

With only the two tables involved in this report, quite possibly a subreport is not need. Try a query that joins the tables as the report RecordSource. Use report Grouping & Sorting.

That is a lot of code behind report, no wonder it takes so long to open.


You have a label with an ampersand (&) character. This is a special character and to have it as a printing character, need to double it in the caption text.


All for now, I will look at the report more closely this weekend.

[tstoneami]

I appreciate the best practice feedback, and I understand that my where construct is not complete/appropriate, but as you know I am trying to zero in on the reason behind the failing logic in the form code. What you see is a small part of a project of which I am only two people - so its a POC / alpha thing in actual production at this point. I don't see an & anywhere, except the expression in a field which is just part of that function. Is that what you mean?

Because of the seemingly flat nature of the data I am importing, at this time I can't see any other way to determine the highly variable results of the data of the subreport. I need to run something on the back end to figure out what to present. Depending on the category the master record falls in, I have to determine if the setting of the potential line items falls in that category and if so, print that line item. At this point I can't see any other way of doing it unless I normalize it a great deal. I am not a totally db savvy person, so that might be a stretch at this time.

Because it works well in a single print, it seems to indicate that its just being stretched performance wise by the lengthy process on the form code - which will only get more complex as more field requirements come to the data source. If there was a way to "post back" that the logic had completed before the next record was processed that would be interesting.

Can you feed back on how to join the records so as to avoid the sub report? I can't see how to represent the data on the subreport in any other way than I am doing it - so wouldnt I exceed the length of the report itself? Seem to have read about that a few times.

[tstoneami]

UPDATE: I had been getting a value from the calling form via the Form! method. I hard set this in the form event code. I also commented out all but one of the section checks (the top if / endif block) to make the sub report only run that and make it less intensive. No change, except it just batches a bit faster. This tells me that it is something OTHER than the processing in the form event is causing this.

[tstoneami]

UPDATE: I've removed all logic from the format event code and am just spitting out the data directly. It works. It seems that any logic in the format event causes the relationship between the data in the main and sub report to fail.

[tstoneami]

UPDATE and NEW INFO: I removed the sub report and put a small section of it in the details of the main report. In doing so, the record source query changed appropriately.

I placed the logic corresponding to that part into the on format event. Exact same behavior. Works perfectly for a single record, anything output with a where clause (causing multiple records) causes the logic to fail. In fact, it seems as if the format event only fires once and consistently looks at the same data - causing the results to be wrong. The attached one is the simplified one - no sub report. How can I get the logic to fire as each report in the recordset is processed? HELP

[tstoneami]

I have seen numerous places where it seems to indicate that the format even runs for each record in the record set...such as here...am I misunderstanding this?
http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

[tstoneami]

i have a post already here: [merged together with this thread - admin]

Which has a very simplified example of what I am trying to do.

Essentially it seems like the format event fires only once with a docmd that has a where clause. If I print a single report it is perfect! Example one in the above post has a subreport, example 2 its very simply incorporated into the main report with no sub report. I really need a solution!

Sorry for the double post, I just thought if I asked it a different way, someone might flash on it differently. ... doh

[June7]

I just tested a report for multiple records in my db with code in Detail section Format event to set visibility of a control and opening the report with DoCmd and WHERE clause. It works.

Your mainreporttest does not have a complete RecordSource. I replaced the incomplete SQL statement using the wildcard to pull in all fields and now all the textboxes say 'no such field in the field list'. There are too many fields for the query. I've never seen tables with so many fields. So had to be more selective on fields pulled in.

I notice that if individual items are manually selected, then click a checkbox, the manually selected items are still also selected.

Using Debug.Print to do some testing. The VBA can't find [Loan Number] field/textbox. I renamed the textbox different from the field. In the OrigCRMatchBorrowerNameRating conditional I put Debug.Print Me.tbxLoanNum & " Borrower". Opened unfiltered report which shows 136 records. The immediate window shows 24 outputs before I even navigate through the report. Navigating the report shows more Debug outputs. I set the OrigCRMatchEmployerNameComments textbox as red BackStyle so I could tell which records don't display it. It doesn't show for about 95 records.

Then tried report filtered by manually selecting items. Single works. Multi-select items from listbox does not work. Doesn't matter which items selected, seems to open filtered to the same 68 records and only the last one shows the red box.

Selecting by one checkbox does seem to work. Moderate shows the red box on/off for different records.

[tstoneami]

Your mainreporttest does not have a complete RecordSource. I replaced the incomplete SQL statement using the wildcard to pull in all fields and now all the textboxes say 'no such field in the field list'. There are too many fields for the query. I've never seen tables with so many fields. So had to be more selective on fields pulled in.

I've noticed that the record source property was truncated. I think this is because the text limit on the field itself is 255. In fact as mentioned, even with the record source looking like that, the stupidly huge tables (I pulled these in from another source) actually are accessible and the fields are all represented.

>>>>I notice that if individual items are manually selected, then click a checkbox, the manually selected items are still also selected.

I mentioned that this wasn't finished.. That's why I gave two examples of how to test - single choice with the single button, group/multi with the print...

>>>>Then tried report filtered by manually selecting items. Single works. Multi-select items from listbox does not work.
Yeah, I've always noted that a single choice works.

>>>>Using Debug.Print to do some testing. The VBA can't find [Loan Number] field/textbox. I renamed the textbox different from the field. In the OrigCRMatchBorrowerNameRating conditional I put Debug.Print Me.tbxLoanNum & " Borrower". Opened unfiltered report which shows 136 records. The immediate window shows 24 outputs before I even navigate through the report. Navigating the report shows more Debug outputs. I set the OrigCRMatchEmployerNameComments textbox as red BackStyle so I could tell which records don't display it. It doesn't show for about 95 records.

This is the behavior noted all along.

QUESTION: Do I understand that you said that if you use a much smaller record source (selective fields) you saw it work properly?

[June7]

The number of characters allowed in RecordSource: 32,750

Yes, the report code does seem to work properly.

Why do you need to manage visibility of textboxes?

I haven't yet looked at the report/subreport arrangement.

[tstoneami]

The number of characters allowed in RecordSource: 32,750 >>>>Can't see how that was exceeded.

Yes, the report code does seem to work properly. >>>> I will have to try that.

Why do you need to manage visibility of textboxes? >>>>Because if nothing shows up in the section I don't want a title with no data below it.

I haven't yet looked at the report/subreport arrangement.>>>> k