Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab query field order (Access 2003 SR2)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab query field order (Access 2003 SR2)

    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

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab query field order (Access 2003 SR2)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •