Moving controls in report based on the data

[Seth Schrock]

I may be trying the impossible. I'm trying to print multiple records on preprinted forms. However, each record may be on different forms. There will always be at least to different formats. So what I have done is to create a table that specifies the width, height, top and left properties for each control based on the form type needed by a specific record. At the bottom of the Detail section of the report, I have a page break so that each record will be on its own page. So I have my code that loops through the controls and sets the various size/position properties.

Dim db As DAO.Database
Dim strControls As String
Dim rstControls As DAO.Recordset
 
Set db = CurrentDb
 
strControls = "SELECT ControlName, Visible, Width, Height, Top, Left, FontSize, TextAlign " & _
              "FROM TicketSpecs WHERE TicketTypeID_fk = " & Me.txtTicketType
 
Set rstControls = db.OpenRecordset(strControls, dbOpenDynaset)
 
With rstControls
    Do While Not .EOF
        With Me.Controls(!ControlName)
            If rstControls!Visible Then
                .Visible = True
                .Width = rstControls!Width
                .Height = rstControls!Height
                .Top = rstControls!Top
                .Left = rstControls!Left
                .FontSize = rstControls!FontSize
                .TextAlign = rstControls!TextAlign
            Else
                .Visible = False
            End If
 
        End With
        .MoveNext
    Loop
End With
 
 
Set db = Nothing
rstControls.Close
Set rstControls = Nothing

Now my problem is in which event to I call this. I had put it in the report's On_Current event, but I have to actually click on each record for this to run. Even in print preview while cycling through the pages doesn't trigger it. Also, as soon as it tries to change the Width property, the control becomes invisible. I thought maybe a repaint was needed, but I can't find a command for that.

I had thought of having a different report for each form and then just looping through the records calling each report as needed, but then either I would have to use the default printer or be asked which printer I want to use for every single record. Not what I'm wanting to happen.

[jimatqsi]

Anything changing object visibility, position or size on a report must be addressed in the On Format event.

Jim

[Seth Schrock]

Okay, I have put it in the Detail On_Format event. However it is still making them invisible as soon as I try to make the size/position changes. I also tried using the Move method, but I get the same result.

[jimatqsi]

A With within a With creeps me out. So does using Access keywords/properties for field names.

Jim

[Seth Schrock]

I had tried removing the outside WITH and it doesn't change anything. Nested WITHs are fully supported. Also, Width and Height are not reserved words (Top and Left are. I didn't think of those as reserved words when I created this.) I have never heard of property names causing problems as field names. However, I have now added an f in front of each field name (for field) and no change.

[jimatqsi]

Seth,
What values are you using for top and height and width? Are you specifying units of measure? Try hard-coding the position of one object; or put an object like an empty text-box myTB at a particular place on the form and make one of your objects move by using

someobj.top = myTB.top
someobj.height=myTB.height

etc...

I've done this sort of things lots.In fact, NeoPa invited me to be a Bytes expert because he saw one of my report functions for aligning headings and data in a case where columns could be made invisible based on the data.

I'd bet it's a problem of units of measure.

Jim

[Seth Schrock]

That fixed it. I just had tried both inches and twips, but neither worked. The problem was when I used the twips, I just multiplied the inches by 1440, but the resulted in some decimal values and evidently it only works with integers. Thanks Jim.