Subreport needs to count and label each record it returns 1

[davidrfloyd]

Hi,

I've found tons of great help here at tek-tips by looking at FAQs and searching the other postings, but can't find one that will help me with this issue. Apologies if I'm re-posting this one!

I have a main table with customer records and a couple of other tables with data associated on the PK from the main table. I have a report with subreports that each gather data from one of the other tables. They all work fine.

My client would like each record returned within the subreports labeled like this:
First Mortgage
Second Mortgage
(etc.)

I'm not sure how to tell Access to find this out. The record number counter from the bottom of the subreport shows the info I need, btw.

TIA

 

[dhookom]

I'm not sure what you mean by "record number counter from the bottom of the subreport". Also, are you just looking to "number" your related records in your subreport?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[davidrfloyd]

Yeah, I'm really looking to number related records. So regardless of it's position in the related table, if a record is the 1st record for Client #49, it should be marked (1). The next record for that client would be (2).

Just to explain myself, what I was clumsily referring to as a "record counter" is the control group called "Navigation Buttons" that appear on my subform. The client wants the data from the related tables to be labeled on a report as "First Mortgage, Second Mortgage, First Lien, Second Lien." I'm sure that Mortgage (1), Mortgage (2) would be sufficient. There shouldn't be more than 6 records related of each of 3 related tables, if that helps.

 

[dhookom]

You can get a "counter" in a report by creating a text box:
Name: txtRunSum
Control Source: =1
Running Sum: Over Group
Visible: No

Then, add another, visible text box:
Control Source: "Mortgage (" & txtRunSum & ")"


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[davidrfloyd]

Excellent!! Worked great after a tiny change.

Control Source: ="Mortgage (" & txtRunSum & ")"

I added the "=" when it didn't work, but then it worked like a charm! Thanks for the wonderful and speedy assistance, dhookom!

 

[dhookom]

Good catch on the "=".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[davidrfloyd]

Just when I thought I was done! Now my client says that he only wants the first 3 related records where I have them in this subreport. Any overflow should go to a new page (A footer on the main report would work fine, I think, I might need to add a page break or something at the end of the report to ensure they print on a new page.

I know this complicates it a bunch, and I've spent several hours trying different options. Have any suggestions?

david

 

[dhookom]

You can add code in the subreport's On Format event of its detail section
Cancel = me.txtRunSum>3
This should only print the first three records.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[davidrfloyd]

Jeez, that was easy! Only thing that took me a minute was that I couldn't just copy/paste your code directly into the On Format field properties box - I got a missing macro error. I had to launch "Code Builder" and once in the Visual Basic window I added the code there and then it ran perfectly.

Now, to add my overflow (records after those first 3) at the end of the report, I made a copy of my subform and put it into the report footer. Then, attempting to modify your code to now OMIT the first three records, I found I had to change the numbering on my txtbox. It was starting after omitting one number, for some reason. I set code for the 2nd box like this to compensate:
="Mortgage Amount (" & [txtRunSum]-1 & "):"
I'm not sure why I had to do that but I'm pleased with the result. I tried making a new txt box name but it worked exactly the same.

Also, I as I wanted to only show records after the first three, I changed the code in the On Format of the new subform to this:
Cancel = Me.txtRunSum <= 4
I had to experiment with different values and Arithmetic operators to get this to work and I'm not sure why it needs to be <= 4 and not < 4, but I suspect it is related to the same reason I have to use the -1 on the Mortgage Amount txt box! I'm pleased with the result but confused as to why it works!! If you think I'm headed for a train wreck with any of this, please tell me!

I think I'm finished with this and again huge thanks for the help!! (I put in this lengthy followup in case it's useful to anyone with a similar problem.)

 

[dhookom]

I'm not sure why you had to do the extra math. I have used the solution of printing the top X records but had not ever had the need for the remaining records. If it works, do what I do: just walk away and be happy!

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[davidrfloyd]

I had to do the extra math because I had not added the new subforms correctly! I'll publish my error for anyone that's using this thread for their own solution.

I noticed that my new form was not linked correctly to my main form by "Link Child Fields" and "Link Master Fields," which were both used on my other subforms. They were set, maybe automatically (I don't remember putting data in them) to both show the common field in the linked table on the original subform. So, I added this info to my new subform. It cleared up the weird math I needed and I was able to make more logical-seeming code for the txt boxes. For clarity, I now can use the same code for the txt box as on the first form:
="Mortgage Amount (" & [txtRunSum]. & "):"
Along with the code following, this new field picks up numbering where it left off before, no changes necessary. I suspect another test record was being counted because the form was selecting an unrelated record due to the omission of the "Link Fields" info.
Therefore, the code for the On Format property of the detail of the new subform works more logically as this:
Cancel = Me.txtRunSum < 4
The new subform has been added into the report footer and it works just fine.

Thanks again! (whew, done!)

 

[dhookom]

David,
Glad to hear there was a logical explanation of the numbering. I should have asked about the link master/child...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Gberndt]

I have a question? did you ever do this on the Form, or did you only do it on the report. The reseaon why is because Im trying to do the same thing, just on the form and the report.

Thanks

Greg

 

[davidrfloyd]

I only used this for the report. The form uses subforms to save the numbered data in related tables, related by a customer ID. So, the person putting in the info sees the subform as part of the main form with it's own record numbering (so for each customer record showing on a form, there could be many related records input and updated from the subform).

Does that answer your question?