• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

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
0
SteveL13
Asked:
SteveL13
  • 8
  • 7
1 Solution
 
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
 
SteveL13Author Commented:
This worked!  Thank you very much.
0
 
SteveL13Author Commented:
This worked!  Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now