Populate ListBox 2

[InsaneProgrammer]

I am trying to populate a list box with records from a database but am having some trouble. There are 4 fields that I would like to put into the list box (lstProducts). In the code below am am able to put in the data from the field ProductNumber but I don't know how to include the other fields (Description, Price, QtyOnHand). Can anyone help?

Private Sub Form_Load()
    Dim MyCN As New ADODB.Connection
    Dim MyRS As New ADODB.Recordset
    Dim strConnection As String
    Dim strSQL As String
    
On Error GoTo Load_Error
    
    'Define the Connection
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    strConnection = strConnection & App.Path & "\Pos.mdb;"
    strConnection = strConnection & "Persist Security Info=False"
    
    'Open the connection
    With MyCN
        .ConnectionString = strConnection
        .Open
    End With
    
    'Define the Query
    strSQL = "Select PartNumber,Description,Price,QtyOnHand "
    strSQL = strSQL & "From tblParts Order By PartNumber"
    
    'Run the Query
    Set MyRS = MyCN.Execute(strSQL)

'Populate the list box with the recordset
If Not MyRS.EOF Then MyRS.MoveFirst
Do Until MyRS.EOF
lstProducts.AddItem MyRS("PartNumber&quot
MyRS.MoveNext
Loop

    MyRS.Close  'Close the recordset
    MyCN.Close  'Close the connection
    Exit Sub

Load_Error:
    'Message box with Error Number and Description
    Beep
    MsgBox Err.Number & vbCrLf & Err.Description
    
    MyRS.Close 'Close the recordset
    MyCN.Close 'Close the connection
End Sub

 

[GTO]

'after opening recordset

Do Until MyRS.EOF
with lstProducts
.AddItem MyRS(0)
.AddItem MyRS(1)
.AddItem MyRS(2)
.AddItem MyRS(3)
MyRS.MoveNext
loop

 

[GTO]

It sounds like you are trying to build a Pos system. Will/does it work with a scanner for barcodes. I'm working on one now. I'd be interested in how it's going. What is being incorporated into prog, etc.

 

[InsaneProgrammer]

Good guess, it is a POS program. It won't work with a bar code scanner. It's a pretty simple program I'm working on for a class assignment.

 

[InsaneProgrammer]

The program works but it doesn't display in the list box correctly. All of the data is displayed in one long column. How do I get it to display with 4 columns? I want it to look like this:
PartNumber Description Price Qty
101 Car Hood $175 3
102 Car Door $225 6

I have tried changing the number of columns in the list box but that hasn't worked.

 

[GTO]

I used 4 textboxes to display the data because of ease of positioning the text. Maybe this code might help.
Vs is a vertical scroll bar.


Option Explicit
Dim i As Integer
Dim selFile As String
Dim sScroll As String
Dim pth As String
Dim iSelect As Integer
Dim DeptMax As Integer

Private Sub cbo1_Click()
Dim count As Integer
Dim lstIdx As Integer
Dim savepth As String
Dim DT As String
lstIdx = cbo1.ListIndex
Select Case lstIdx
Case 0
cmdSave.Visible = True
List1.Visible = False
txt(0).Visible = True
txt(1).Visible = True
txt(2).Visible = True
txt(3).Visible = True
lbl.Visible = True
lbl3.Visible = True
cmdSaveChange.Visible = True
VS.Visible = True
Case 1
txt(0).Visible = False
txt(1).Visible = False
txt(2).Visible = False
txt(3).Visible = False
lbl.Visible = False
lbl3.Visible = False
cmdSaveChange.Visible = False
VS.Visible = False
List1.Clear
cmdSave.Visible = True
List1.Visible = True
selFile = "\Functkey.txt"
Open pth & selFile For Input As #1
'get Function programming
Do While Not EOF(1)
Input #1, z
List1.AddItem z
Loop
Close #1
End Select
End Sub

Private Sub cmdSave_Click()
For i = 0 To List1.ListCount - 1
If List1.Selected(i) = True Then
cmdSave.Caption = CStr(List1.ListIndex)
End If
Next
End Sub

Private Sub cmdSaveChange_Click()
rs.Open ("SELECT DeptDescription, TaxRate, Sign from DeptNum where Numdept = '" & VS.Value & "'&quot
For i = 1 To 3
rs(i - 1) = txt(i)
Next
rs.Update
rs.Close
End Sub

Private Sub Form_Load()
pth = App.Path
With cbo1
.AddItem "Departments"
.AddItem "Select Function Keys"
End With
selFile = "\DeptTotals.txt"
Open pth & selFile For Input As #1



'get tax rates
Input #1, z
Text1 = z
Input #1, z
Text2 = z
Input #1, z
Close #1


rs.Open ("SELECT NumDept, DeptDescription, TaxRate, Sign from DeptNum;&quot
txt(0) = rs(0)
txt(1) = rs(1)
txt(2) = rs(2)
txt(3) = rs(3)
Text3 = rs.RecordCount
VS.Max = rs.RecordCount
rs.Close
End Sub

Private Sub VS_Change()
If VS.Value > Text3 Then Exit Sub
rs.Open ("SELECT NumDept, DeptDescription, TaxRate, Sign from DeptNum where NumDept = '" & VS.Value & "'&quot
txt(0) = rs(0)
txt(1) = rs(1)
txt(2) = rs(2)
txt(3) = rs(3)
rs.Close
End Sub