Need List Box to appear like TxxtBox in Report

[RobH]

I have a report that has some Listboxes - due to the nature that these can do SQL Queries within themselves but they appear with boxes around them and the content does not wrap.

How Can I make a listbox appear like a textbox.?

[Jim Doherty]

I have a report that has some Listboxes - due to the nature that these can do SQL Queries within themselves but they appear with boxes around them and the content does not wrap.

How Can I make a listbox appear like a textbox.?

Going on your brief posting you could iterate through the list provided by your listbox concatenating and assigning each data value to a string variable using ITEMDATA in a loop.

Each data value could be separated for instance with a comma and space between each value. You would then strip off the last comma and space at the end of the loop and assign the whole value of the variable to the textbox

My Assumptions
You have a single listbox called MyListBox that you requery each time in the Detail_Print event of your report and that this listbox has a single (1) column list
You have a single unbound textbox Called MyTextBox placed on the detail section which will be used to display the listbox contents.
You are not too concerned with efficiency (in effect you are handling this data twice! once to return and display in a listbox and then two to loop and display the same data in a textbox.) I assume you will hiding the listbox? given the textbox will be displaying its content.

The Code to make this work in the DETAIL_PRINT event of the report

 
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me!MyListBox.Requery
Dim i As Integer
    Dim GetList As String, strData As String
    ' Iterate through all entries.
    For i = 0 To MyListBox.ListCount - 1
        GetList = Me!MyListBox.ItemData(i)
        ' Add a comma and space to each concatenated data value.
        strData = strData & GetList & ", "
    Next i
    If Len(strData) = 0 Then
        Me!MyTextBox = ""
    Else
    'strip off the last space and comma and display in text box.
        Me!MyTextBox = Left(strData, Len(strData) - 2)
    End If
End Sub
 

Hope this helps you

Regards

Jim

[NeoPa]

Rob,
You're a full member now. It would be nice if you could phrase your question a little more clearly. It's hard enough to find the answers sometimes, but we shouldn't need to spend so much time trying to understand what the question means.

MODERATOR.

[RobH]

Wow I see what you mean about slowing it down..
If you have a bunch on the screen it will take a while.

Thanks.

[RobH]

Alternatively is there a way (in vb) to store the result of the SQL statement into the Textbox..

eg:

Me.MyTextBox = DoCmd.RunSQL "Select............"
 
 
or
 
 
Dim sqlvalue as string
sqlvaue = Docmd.RunSQL "Select............"
me.MyTextBox = sqlvalue
 

[Jim Doherty]

Alternatively is there a way (in vb) to store the result of the SQL statement into the Textbox..

eg:

Me.MyTextBox = DoCmd.RunSQL "Select............"
 
 
or
 
 
Dim sqlvalue as string
sqlvaue = Docmd.RunSQL "Select............"
me.MyTextBox = sqlvalue
 

Rob

I'm afraid you are way off the mark with that one I understand your thought process, but you are talking about the return of recordsets and how you deal with the physical data returned as a two dimensional matrix.

Docmd.RunSQL whilst incredibly useful is not the panacea to all it runs 'Action' or data definition queries
INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statements

Note.... NOT.... SELECT statements (which return datasets and from which your listboxs rely for their data)

So in short...you simply cannot throw a multi column two dimensional recordset into a string variable

When I mentioned you were handling the data twice that is correct. Your answer lies optimally in opening up a RECORDSET in Code and looping through the recordset, grabbing datavalues as you loop through the recordset (in much the same way as my earlier example to you) concatenating the results in whichever way befits your requirement ie: is it one column or two or maybe more columns that you wish to grab ..... if so how do you intend to equally space datavalues if the data is to be dumped into the textbox. IF there is more than one column and so on.

It is entirely possible for you to open a recordset loop through it and assign the result to the textbox without ever having any listbox on your report you merely have to understand the concept of recordsets generally, how they get opened in memory, how you loop through and grab their values importantly and especially within the context of what you wish to do currently how to massage and manipulate that data to FILL the textbox with it.

Unfortunately every method or property has its limitations scoped to the intention of, as in this case.......the 'control' as we appreciate that 'actually' what you are trying to do is make a textbox into a listbox.

You will probably think this is way too much work to do to achieve something which, and I would agree with you seems like a simple request.

The elegance of programming mimicry very often lies in the skilful understanding of workarounds because, as you quite rightly point out, a listbox doesnt grow and behave like a textbox by default.

Have a look at the openrecordset method in Access help

I hope this helps you a bit further

Regards

Jim