Transpose results of single-column query into a row (1 Viewer)

[Ingeneeus]

Greetings, all.
I hope my subject line is at least somewhat explanatory. I have a query (Query Shipped Today) that returns a single column of data (names of items shipped in a given order). I am attaching a screencap (QueryCap.jpg) of the query.

[FONT=&quot]What I need to have happen is for that column of data to wind up as a single[FONT=&quot] line of text, which [FONT=&quot]is t[FONT=&quot]hen copied to the clipboard. I also need to a) change the name of the header [FONT=&quot]field, and b) add [/FONT]a separator between each item name.

[FONT=&quot][FONT=&quot][FONT=&quot]To illustrate, [FONT=&quot]if[/FONT] my query yields the following column[/FONT][/FONT][/FONT]:
[/FONT][/FONT][/FONT][/FONT]

[FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot] [FONT=&quot]Product (header)[/FONT][/FONT][/FONT][/FONT][/FONT]
Backtrack (item #1)
Emelie (item #2)
Krampus (item #3)
Scherzo Diabolico (item #4)
Trouble Every Day (items #5)
​

I need to get it the following configuration:

Shipped XofX: Backtrack | Emelie | Krampus | Scherzo Diabolico | Trouble Every Day |
​

[FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot]I have a workaround which involves [FONT=&quot]using [/FONT]a macro [FONT=&quot]in a MS Word docum[FONT=&quot]ent on my workstation, but [FONT=&quot]the process runs a bit slowly (and sometimes crashes my Access application), and[FONT=&quot] I [FONT=&quot]have discovered that it d[FONT=&quot]oesn't [FONT=&quot]work w[FONT=&quot]hen I am working via a certain service that [FONT=&quot]r[FONT=&quot]hymes with MoDoNyDC.

[FONT=&quot][FONT=&quot][FONT=&quot]Here's [FONT=&quot]how it works: [/FONT][/FONT][/FONT]I have a function which copies the query results[/FONT] to the clipboard[FONT=&quot]:
[/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT]

RunCommand acCmdSelectAllRecords
 RunCommand acCmdCopy
 DoCmd.Close acQuery ("Query Shipped Today")

[FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot]

[FONT=&quot]and then call[FONT=&quot]s a [FONT=&quot]another macro[FONT=&quot]:
[/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT]

Call RunCleanShipped_Word

[FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot]

[FONT=&quot]Which runs [FONT=&quot]the Word [/FONT]macro[FONT=&quot]:[/FONT][/FONT]
[/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT]

Sub RunCleanShipped_Word()
Dim myWD As Object
Dim myFile As Object
Set myWD = CreateObject("Word.Application")
Set myFile = myWD.Documents.Open("C:\Users\g.birkholz\Cleanshipped.docx")
myWD.Run "CleanShipped"
myFile.Close
myWD.Quit
Set myFile = Nothing
Set myWD = Nothing
End Sub

[FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot]

[FONT=&quot][FONT=&quot]The Word macro pastes the [FONT=&quot]contents of the clipboard in[FONT=&quot]to a document (QueryResultCap.jpg) as a tab[FONT=&quot]le. [/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot]T[/FONT]he[FONT=&quot] ma[FONT=&quot]cro then removes the first [FONT=&quot]two [FONT=&quot]rows, selects the whole column, cuts it, and pas[FONT=&quot]tes it as plain text. (PlainTextCap).
[/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT]Through [/FONT]a conv[FONT=&quot]oluted series of Find/Replace st[FONT=&quot]atements, the[FONT=&quot] ma[FONT=&quot]cro [FONT=&quot][FONT=&quot]removes the hard returns, adds "Shipped [FONT=&quot]XofX:" to the fron[FONT=&quot]t of the [FONT=&quot]line,[/FONT][/FONT][/FONT] [FONT=&quot]and insert[FONT=&quot]s " | " [FONT=&quot]after eac[FONT=&quot]h item name, [/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT]all of which give[FONT=&quot]s me t[FONT=&quot]he [FONT=&quot]result I refer[FONT=&quot]enced [/FONT]a[FONT=&quot]b[FONT=&quot]o[FONT=&quot]ve.[/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT]
[FONT=&quot]It [FONT=&quot]then copies that result to the clipboard so it can be pasted w[FONT=&quot]here I need it to go, [FONT=&quot]and closes the Word application.

[FONT=&quot]As I [FONT=&quot]said, it's a work-around.

I'd like to take the [FONT=&quot]Word macro out [FONT=&quot]of this pr[FONT=&quot]ocess[FONT=&quot], but I'm not even remo[FONT=&quot]tely sure [FONT=&quot]this can be do[FONT=&quot]ne entirely in the Access env[FONT=&quot]ironment, and if it can, I[FONT=&quot] don't know where to start. I [FONT=&quot]looked into building [/FONT]a Transpose query base[FONT=&quot]d [FONT=&quot]on my [/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot][FONT=&quot]Query Shipped Today query [/FONT][/FONT], but ev[FONT=&quot]idently you can't do one of those if you have only one column.

[FONT=&quot][FONT=&quot]I also have zero ideas abo[FONT=&quot]ut how I wo[FONT=&quot]uld get those separator[FONT=&quot]s in th[FONT=&quot]eir, even if I could have made the Transpose que[FONT=&quot]r[FONT=&quot]y work.

[FONT=&quot]Is ther[FONT=&quot]e any hope here, or is [FONT=&quot]what I [FONT=&quot]want just not po[FONT=&quot]ssible within the confines of [FONT=&quot]Access?

[FONT=&quot][FONT=&quot]As always, any light that can be shed in[FONT=&quot]to the m[FONT=&quot]urky swamp of my Access [FONT=&quot]knowledge will be appreciated![/FONT][/FONT][/FONT][/FONT][/FONT]
[/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT]

 

[JHB]

I've made an example for you, it was easier as a long description.
Open the only form in it, click the button.

 

[Ingeneeus]

I've made an example for you, it was easier as a long description.
Open the only form in it, click the button.

Oh My God. So clean, so fast! And I can even figure out how it works!

Awesome -- thank you, JHB!

~Gene

 

[Ingeneeus]

Uh, Oh.
Spoke too soon, I'm afraid.
It blew up when I ran it in my database. I'm getting Run-time error '3061': Too few parameters. Expected 1.
The Debugger kicks me to Set rst = dbs.OpenRecordset("Query Shipped Today") [The actual name of my query]

I note that you built this in an .accdb database. I perhaps should have mentioned that my database is an old .mdb. I'm not sure that has a bearing here, but then, I'm kind of a hammer-mechanic.

Again: Help! I feel like I'm so close!

 

[JHB]

.. I'm getting Run-time error '3061': Too few parameters. Expected 1.
The Debugger kicks me to Set rst = dbs.OpenRecordset("Query Shipped Today")

It looks like you've a criteria in the query, which you not have set when the query runs.
Show the SQL-String for the query, or even better, post a stripped version of your database with some sample data, zip it!

 

[Ingeneeus]

Hi again, JHB --

Sorry for the delayed reply. I have been unable to hit servers in the UK for most of the day. Thus, no Access Forum for me until just now

You are correct; I most certainly do have criteria in my query.

Here is the SQL:

SELECT [Order Details].Product
FROM [Order Details]
WHERE ((([Order Details].Product) Not Like "Marc Rec*" And ([Order Details].Product) Not Like "DVD Pro*" And ([Order Details].Product) Not Like "Shipping*" And ([Order Details].Product) Not Like "Adjustment for*" And ([Order Details].Product) Not Like "Sales Tax*") AND (([Order Details].OrderNumber)=[Forms]![View Orders]![txtOrder]) AND (([Order Details].Backordered)<1))
ORDER BY [Order Details].ItemNumber;

I wish I could include a stripped down version of my database, but I'm not sure how I'd go about it. It's staggeringly big and fiendishly complicated. I hope the code above provides a clear enough picture.

I'm using (([Order Details].OrderNumber)=[Forms]![View Orders]![txtOrder]) so I can generate the query for the order I'm currently looking at on the View Orders screen (form). the other criteria are there to filter out everything that isn't an actual product shipped (Sales Tax, DVD Processing, etc.)

I'll be out of the office all weekend, so I won't be able to reply until then. Have a good weekend,

~Gene

 

[JHB]

When you execute the code line "Set rst = dbs.OpenRecordset("Query Shipped Today")", do you remember to have the form [View Orders] open and some value in the control [txtOrder]?

 

[Ingeneeus]

Hi, JHB --

Yes, [View Orders] is open and there is a value in [txtOrder] when the line is executed. I placed a button ([Command6] for testing purposes) on the [View Orders] form which triggers the macro you wrote. That's how I launched my earlier macro that used the MS Word workaround. It was faster than having a separate form to input the order number I'm shipping and it eliminates the chance of me fat-fingering in the wrong order number.


​

 

[sneuberg]

If you are using parameters in a query that is used in a record set those parameters have to be set in the code some like shown in red below

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
    
Set db = CurrentDb
Set qdf = db.QueryDefs("qryCustomersSelect")
[COLOR="Red"]qdf.Parameters(0) = _
  Forms!frmSelectCountry!cboCountry[/COLOR]
Set rst = qdf.OpenRecordset
    
Do Until rst.EOF
  Debug.Print rst!CustomerID
  rst.MoveNext
Loop
    
rst.Close
qdf.Close

Which is an example from https://msdn.microsoft.com/en-us/library/office/aa160564(v=office.11).aspx

In your case in might be something like:

qdf.Parameters("[Forms]![View Orders]![txtOrder]") = [Forms]![View Orders]![txtOrder]

Note that you need to use a querydef in this scheme rather than opening the recordset directly.

 

[Ingeneeus]

OK, Awesome --

sneuberg, the code (and link) you posted worked -- with a few personalizations to make it fit my form and query, it eliminated the error altogether!

JHB, once sneuberb got me past the Run-time / parameter error, your code worked perfectly as well!

Here's what the final code looked like:

 Private Sub CommandShipped_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim StringPart As String
    
Set db = CurrentDb
Set qdf = db.QueryDefs("Query Shipped Today")
qdf.Parameters("[Forms]![view orders]![txtOrder]") = [Forms]![view orders]![txtOrder]
Set rst = qdf.OpenRecordset
    
     StringPart = "Shipped XofX: "
Do Until rst.EOF

      StringPart = StringPart & rst![Product] & " | "
      rst.MoveNext
 Me.HiddenControl = Left(StringPart, Len(StringPart) - 1)
  Me.HiddenControl.visible = True
  Me.HiddenControl.SetFocus
  DoCmd.RunCommand acCmdCopy
  Me.CommandShipped.SetFocus
  Me.HiddenControl.visible = False
Loop

rst.Close
qdf.Close
End Sub

Result: Shipped XofX: Backtrack | Emelie | Krampus | Scherzo Diabolico | Trouble Every Day |

As desired. Thanks to you two, I am up and running.
I will be hitting the "Thank" button for both of you!

You've both been so helpful, I almost hate to ask (but I will anyway): Do either of you have an idea of how to put a "hard return" after the last " | "? I've tried Me.HiddenControl = Left(StringPart, Len(StringPart) - 1) & vbNewLine and a number of variations: & vbCr, & Chr(10), etc. but it's not producing the return I'm looking for. What's really weird is that when I put in a Debug.Print Me.HiddenControl line right after the Me.HiddenControl = Left(StringPart, Len(StringPart) - 1) & vbNewLine line, I can see that the code is putting that hard return in every pass but the LAST one:
Shipped XofX: Backtrack |

Shipped XofX: Backtrack | Emelie |

Shipped XofX: Backtrack | Emelie | Krampus |

Shipped XofX: Backtrack | Emelie | Krampus | Scherzo Diabolico |

Shipped XofX: Backtrack | Emelie | Krampus | Scherzo Diabolico | Trouble Every Day |

I can live without the hard return, but it really would be the cherry on the sunday

 

[sneuberg]

Edit: This property I refer in this post probably isn't the problem as my test code works even with the Enter Key Behavior set to Default.


In the properties of the textbox, Other Tab make sure the Enter Key Behavior is set to New Line in Field rather than Default and of course make sure your textbox is high enough to accommodate one than one line.

With these these changes I tested the textbox with

Me.HiddenControl = "First Line" & vbNewLine & "Second Line"

and the vbNewLine worked.

As an side the reason I know about this property is because it being set "New Line in Field" in a textbox that was only one line high gave me a bug that took hours to track down. In the textbox I was seeing

Angle Eyes

A description of a product, but when I queried Angle Eyes it wouldn't show up. What the user a inadvertently entered was

Angle Eyes
Ruby Red

but I couldn't see the Ruby Red part. Arrg!!

 

[sneuberg]

The solution I proposed in my last post can't be your problem as that property just affects the entry from the keyboard and not from code.

Of course the height of the textbox still needs to accommodate the number of lines entered, but if that's ok I'm lost as to why the vbNewLine isn't working for you when it worked for my simple test case.

Could you upload your database. Maybe I can figure out what's wrong.

 

[JHB]

..
Do either of you have an idea of how to put a "hard return" after the last " | "?

The below line should do it.

  Me.HiddenControl = Left(StringPart, Len(StringPart) - 1) & Chr(13)

 

[Ingeneeus]

The below line should do it.

  Me.HiddenControl = Left(StringPart, Len(StringPart) - 1) & Chr(13)

Hi, JHB. Sadly, that doesn't do it. After a bit of research, I also tried & Chr(10) & Chr(13), since I had seen something that indicated I needed both. That didn't produce the intended results either.

I'm starting to think the issue might be occurring when we Do.Cmd.RunCommand acCmdCopy, because when I look at the Debug.Print Me.HiddenControl output, the hard return is there. I commented out the Me.HiddenControl.visible = False line just to test, and sure enough, the cursor is on the next line after the "Shipped XofX" line (see screencap).

 

[Ingeneeus]

Got it!

Evidently, DoCmd.RunCommand acCmdCopy won't copy a trailing hard return as such. I'm guessing because there's actually nothing in the next line. It just copies out to the end of the text.

So I tried

Me.HiddenControl = Left(StringPart, Len(StringPart) - 1) & vbNewLine & "."

which got me the return I wanted, but left me with a . on the next line.
So I tried

Me.HiddenControl = Left(StringPart, Len(StringPart) - 1) & vbNewLine & " "

which left me with a space on the next line.

I can live with that

Thanks again for all your help!

 

[Ingeneeus]

The solution I proposed in my last post can't be your problem as that property just affects the entry from the keyboard and not from code.

Of course the height of the textbox still needs to accommodate the number of lines entered, but if that's ok I'm lost as to why the vbNewLine isn't working for you when it worked for my simple test case.

Could you upload your database. Maybe I can figure out what's wrong.

Hi, sneuberg

Thanks for your willingness to keep helping! I'm afraid I'm not able to upload my database. Not only is it ginormous, but it also contains customer info and I don't feel comfortable putting that up here. If I could figure out how to put up a stripped down, "redacted" version, I would, but I don't know how I would go about doing that.

At any rate, thanks to both your and JHB's input, I was able to make it work (see above posts). I really appreciate the help!

 

[JHB]

Good you got it working!
Only as follow up - in a Word document the Chr(13) works, the cursor is on a new line, in Notebook it doesn't work - strange.