Labels and text boxes how can I 'not' show em on a report

[srmillward]

Here is my problem,
I am designing a database which the users enter data on a form, some times multiple forms. One section allows for up to 10 entries. Now I didn't know how to do this other than create 10 entries and the unused ones are left blank. I gave them static names ie DETAILS_1 to DETAILS_10
Now when I come to create a report for the data on that form, here is my problem. If the user only used DETAILS_1 to DETAILS_4, how can I just show the ones that have data??

THanks

 

[LonnieJohnson]

Why not have a subform that stores the details in a separate table by a unique id that ties to the main form?

Then for main record #1 there would be four detail records each with an id of 1. For main record #2 there may be 8 detail records each with an id of 2.

Now you create your report for the main records and add a sub report tieing the two tables by the unique id of the main table and you will see each main record displaying all it's sub records.



ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[JamieDeVry]

Lonnie gives a best practise recommendation. He is totally correct. The way you are collecting the data is problematic. This would require that you first change the structure of your database. In the meantime, you will want to create a recordset loop to determine the number of records that you have to display in the report. then you will need to use that value to hide each label and textbox control that exceeds that value.

Sub mcrRemoveLabelAndTextboxes()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intRec As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM tblTest&quot
intRec = rs.RecordCount
'lets assume that your record count is at 4
For intRec = intRec To 10
With Reports(0)
.Controls("Label" & intRec).Visible = False
.Controls("TextBox" & intRec).Visible = False
End With
Next intRec


End Sub

Lonnie however is correct in the poor structure of your database.

Jamie

http://nj.devry.edu/~jbates