Fixed column name in crosstab query (1 Viewer)

[selvsagt]

Hi.

My application is generating a lot of PDFs from template files, and get data from an msaccess form. This is done with VBA.
This works quite nicely, but I get into trouble when I want to get data from a continous form. Only the first value end up in the PDF. This makes perfect sence due to the fact that the code gets the value from a control, and continous forms only has "one" control, but I haven't been able to solve this in code, and therefore I am trying an alternate solution with a crosstab query.

(If anyone know an answer to this in code, then give it a shot
I have included the code at the bottom of the post).

To solve the problem I was thinking I could use a crosstab query.
This would give each row a column instead, but I need the crosstab to have specific names that do not change.

This is the format I would like to have in such a crosstab query, but I do not know how to accomplish this structure;

(Cross structure):
Product1	Prod1Perc	Coca Cola	Product2	Prod2Perc	Fanta Orange	Product3	Prod3Perc	7-up
Coca Cola	20 %	       15.000	      Fanta Orange	 40 %              12.350	7-up Light	50 %	        17.365
								
								
								
I would then name the fields something like this
prod1	prod1perc	value1	prod2	prod2perc	value2	prod3	prod3perc	value3

The underlying query is pretty basic;
("bel" is the value, "vekt" is the percentage and "produkt" is the productname.)

SELECT tblProd.Produkt, tblFondsOrdreInnh.vekt, tblFondsOrdreInnh.Bel, tblFondsOrdreInnh.FondsOrdreID
FROM tblFondsOrdreInnh INNER JOIN tblProd ON tblFondsOrdreInnh.ProdID = tblProd.prodID
WHERE (((tblFondsOrdreInnh.FondsOrdreID)=[Forms]![frmKundekort]![frmFondsOrdre].[Form]![fondsordreID]));

(This is the vba code for inserting values into the pdf. If anyone know how to fix this by code, that would really be great, but I guess a crosstab also would work):

    For Each ctl_input In Me.account.Controls
        If TypeOf ctl_input Is TextBox Then
            If IsNull(ctl_input) = False Then   'remove all IsNull's
                    Print #intfile, "<field name=" & "'" & ctl_input.Name & "'" & ">"
                    Print #intfile, "<value>" & ctl_input.Value & "</value>"
                    Print #intfile, "</field>"
                    Debug.Print ctl_input.Name & ": " & ctl_input.Value
            End If
        End If
    Next ctl_input

 

[sneuberg]

I don't know if a crosstab query will solve this for you but if you are going to try it that way I suggest reviewing these techniques on them.

Maybe you could do this in code by looping though the recordset of the continuous form. That would overcome this continous forms only has "one" control problem' This video will get you started on looping through recordset.