Combine listbox data into one text box field

I have a form which contains a listbox.  The listbox displays data containing a qty field and a description field.  For example...

Qty      Description
2      123
1      456
1      123-A

On a report that the user views while the form is open I need to combine this data into one text box like:

Inventory: (2) - 123, (1) - 123-A, (1) - 456

How can I accomplish this?

--Steve
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
create a global variable in a regular module

option compare database
option explicit
public strQD as string
----------------------------------------------


in the form's module

dim j
with ListboxName
    for j=0 to .listcount-1
         strQD = strQD & "(" & .Column(0, j) & ") - " & .Column(1, j) & ", "

    next
end with
strQD = Left(strQD, Len(strQD) - 2)

'in the report
in the print event or format event of the section where the textbox is

me.textboxname=strqd


0
SteveL13Author Commented:
When you say "in the forms module" where do you mean the core should go?

This code:

dim j
with ListboxName
    for j=0 to .listcount-1
         strQD = strQD & "(" & .Column(0, j) & ") - " & .Column(1, j) & ", "

    next
end with
strQD = Left(strQD, Len(strQD) - 2)


--Steve
0
Rey Obrero (Capricorn1)Commented:
you could place the codes in the click event of a button, perhaps in the button that opens your report
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SteveL13Author Commented:
Hmmm, I put it into the onclick event of the command button that opens the report and then the button does nothing.   Here the onclick code with your code inserted...

Private Sub Command294_Click()
On Error GoTo Err_Command294_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Dim stDocName As String
   
    'start of new code
    stDocName = "rptProcessCard"
    DoCmd.OpenReport stDocName, acPreview
   
    Dim j
    With ListboxName
        For j = 0 To .ListCount - 1
            strQD = strQD & "(" & .Column(0, j) & ") - " & .Column(1, j) & ", "

        Next
    End With
    strQD = Left(strQD, Len(strQD) - 2)
    'end of new code

Exit_Command294_Click:
    Exit Sub

Err_Command294_Click:
    MsgBox Err.Description
    Resume Exit_Command294_Click
   
End Sub
0
Rey Obrero (Capricorn1)Commented:
you have to get the values first before opening the report

Private Sub Command294_Click()
On Error GoTo Err_Command294_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Dim stDocName As String
   
    'start of new code
    Dim j
    With ListboxName
        For j = 0 To .ListCount - 1
            strQD = strQD & "(" & .Column(0, j) & ") - " & .Column(1, j) & ", "

        Next
    End With
    strQD = Left(strQD, Len(strQD) - 2)
    'end of new code




   
    stDocName = "rptProcessCard"
    DoCmd.OpenReport stDocName, acPreview




Exit_Command294_Click:
    Exit Sub

Err_Command294_Click:
    MsgBox Err.Description
    Resume Exit_Command294_Click
   
End Sub
0
SteveL13Author Commented:
Ok.  The command button now works.  I had to replace listboxname with the real name of the listbox.  But here is what I'm getting in the resulting report text box...

      (PartN) - Qty, (2) - 2, (2) - 1, (2) - 1(PartN) - Qty, (2) - 2,
      (2) - 1, (2) - 1(PartN) - Qty, (2) - 2, (2) - 1, (2) - 1(PartN) -
      Qty, (2) - 2, (2) - 1, (2) - 1(PartN) - Qty, (2) - 2, (2) - 1, (2)
      - 1(PartN) - Qty, (2) - 2, (2) - 1, (2) - 1

What I need to see using thie above example, which is what I have in the listbox I'm using as an example is...

(2) - 123, (1) - 123-A, (1) - 456
0
Rey Obrero (Capricorn1)Commented:
you have the header columns in your listbox?

change this

   For j = 0 To .ListCount - 1

to

   For j = 1 To .ListCount - 1
0
SteveL13Author Commented:
That didn't work.  I got the same data in the text box instead of..

(2) - 123, (1) - 123-A, (1) - 456

Then I removed the header columns in the listbox and still got the same result.
0
Rey Obrero (Capricorn1)Commented:
use this codes

Private Sub Command294_Click()
On Error GoTo Err_Command294_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Dim stDocName As String
   
strQD =""  '< clear the variable first

    'start of new code
    Dim j
    With ListboxName
        For j = 1 To .ListCount - 1
            strQD = strQD & "(" & .Column(0, j) & ") - " & .Column(1, j) & ", "

        Next
    End With
    strQD = Left(strQD, Len(strQD) - 2)
    'end of new code




   
    stDocName = "rptProcessCard"
    DoCmd.OpenReport stDocName, acPreview




Exit_Command294_Click:
    Exit Sub

Err_Command294_Click:
    MsgBox Err.Description
    Resume Exit_Command294_Click
   
End Sub
0
SteveL13Author Commented:
Not quite.  Here is what I get now...

(2) - 2, (2) - 1, (2) -1

(I have turned on the header columns again and this is what I got.)

Again.. this is what I need to get...

(2) - 123, (1) - 123-A, (1) - 456
0
Rey Obrero (Capricorn1)Commented:
can you upload your db
0
SteveL13Author Commented:
Attached.  I had to clean some of it up because of the confidential nature.  Please do the following:

1)  Launch the form.
2)  Ignore any run time errors.  Just click [End} each time one appears.
3)  When the form opens, select part #2 from the drop-down and then [Tab]
4)  Click the [Process Card] button
5)  The report will open.  Approx. 1/3 of the way down on page 1 is the "Chills" text box that isn't displaying the correct information.

For reference, the correct information should be coming from the form tab labeled "SI" (Chills listbox).

Thanks for all your help so far.  I have to run out for about an hour.  Please stay with it.  I really appreciate your help.
Sample.mdb
0
Rey Obrero (Capricorn1)Commented:
you did not specify that your list box have three columns



Private Sub Command294_Click()
On Error GoTo Err_Command294_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim stDocName As String

strQD = "" '< clear the variable first

'start of new code
Dim j
With lstPartNChills
For j = 1 To .ListCount - 1
strQD = strQD & "(" & .Column(1, j) & ") - " & .Column(2, j) & ", "
Next
End With
strQD = Left(strQD, Len(strQD) - 2)
'end of new code

stDocName = "rptProcessCard"
DoCmd.OpenReport stDocName, acPreview


Exit_Command294_Click:
Exit Sub

Err_Command294_Click:
MsgBox Err.Description
Resume Exit_Command294_Click

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
This worked!  Thank you very much.
0
SteveL13Author Commented:
This worked!  Thank you very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.