Crosstab query field order (Access 2003 SR2)

Topic

New Reply

I have a crosstab query which I am using as the recordsource for a datasheet form. The query summarises missing products by category, and the number of columns can vary between 2 and 20 – the exact number is only known when the query is run. I have code which then builds the required number of controls (text boxes) on the form.

My problem is this – when I run the query in the Query Window the Totals column is always the rightmost column – this is what I want. However, when I loop through the query in VBA using the following code, the Totals is always the second column from the left.

For intLoop = 0 To intNumFields – 1
strName = rst.Fields(intLoop).Name
Set txt = CreateControl(frm.Name, acTextBox, acDetail, , strName)
txt.Name = strName
Next

Is there a way to adapt the code to ensure that Totals is the last control created, and therefore appears on the right of the form ?

Nick

Reply | Quote

Replies

Handle the first and second field separately:

strName = rst.Fields(0).Name
CreateControl(frm.Name, acTextBox, acDetail, , strName).Name = strName

For intLoop = 2 To intNumFields – 1
strName = rst.Fields(intLoop).Name
CreateControl(frm.Name, acTextBox, acDetail, , strName).Name = strName
Next intLoop

strName = rst.Fields(1).Name
CreateControl(frm.Name, acTextBox, acDetail, , strName).Name = strName

Reply | Quote

Hans

Thanks for that.

I managed to find something similar in the meantime which I ran after the first loop:

For intLoop = 0 To intNumFields – 1
Set txt = frm.Controls(intLoop)
Select Case intLoop
Case 0
‘Do nothing
Case 1
txt.TabIndex = intNumFields – 1
Case Else
txt.TabIndex = intLoop – 1
End Select
‘txt.Name = strName
Next

Thanks again

Nick

Reply | Quote