Add values in listbox in different columns

[knowledge76]

Option Compare Database

Private Sub Combo3_AfterUpdate()


Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim vl As String
Dim strsql As String

Dim totalengines As Integer

Dim usedengines As Integer
Dim notusedengines As Integer
Dim noinfos As Integer

List6.RowSource = ""
vl = Combo3.Value

Text13.Value = vbNullString
Text15.Value = vbNullString
Text17.Value = vbNullString

usedengines = 0
notusedengines = 0
noinfos = 0

strsql = "SELECT TBL_VP_" & Forms!Form1!Combo3.Value & ".VEHICLE_LINE_WERS AS VLWERS" '& vbNewLine
  strsql = strsql & ", TBL_VP_" & Forms!Form1!Combo3.Value & ".ENGINE AS ENGINE" '& vbNewLine
  strsql = strsql & ", TBL_AWS_ENGINE.Engine_Description as ENG_DESC" '& vbNewLine
  strsql = strsql & ", TBL_AWS_ENGINE.Used as USED" '& vbNewLine
  strsql = strsql & " FROM TBL_VP_" & Forms!Form1!Combo3.Value & vbNewLine
  strsql = strsql & " LEFT JOIN TBL_AWS_ENGINE " '& vbNewLine
  strsql = strsql & " ON TBL_VP_" & Forms!Form1!Combo3.Value & ".ENGINE = TBL_AWS_ENGINE.Engine_Code" & vbNewLine
  strsql = strsql & " GROUP BY  TBL_VP_" & Forms!Form1!Combo3.Value & ".VEHICLE_LINE_WERS"  '& vbNewLine
  strsql = strsql & ", TBL_VP_" & Forms!Form1!Combo3.Value & ".ENGINE"
  strsql = strsql & ", TBL_AWS_ENGINE.Engine_Description" '& vbNewLine
  strsql = strsql & ", TBL_AWS_ENGINE.Used;"

Debug.Print strsql

Set db = CurrentDb
Set rs = db.OpenRecordset(strsql, dbOpenDynaset)



rs.MoveFirst
rs.MoveLast


totalrecords = rs.RecordCount

rs.MoveFirst

[Forms!Form1!List6.AddItem Item:="VLWERS" & "    Engine" & "               Description               " & "                     Used"
Forms!Form1!List6.AddItem Item:=".................................................................................."
'Debug.Print ".................."
'Debug.Print ".................."
'Debug.Print "VLWERS" & "    Engine" & "               Description" & "                     Used"
'Debug.Print "....................................................................................."

Do While rs.EOF = False
    Debug.Print rs!VLWERS & "             " & rs!Engine & "          " & rs!ENG_DESC & "             " & rs!used
        If rs!used = "Y" Then
            usedengines = usedengines + 1
                ElseIf rs!used = "N" Then
                    notusedengines = notusedengines + 1
                        Else
                            noinfos = noinfos + 1
        End If

Debug.Print "VLWERS" & "    Engine" & "               Description          " & "                     Used"
Forms!Form1!List6.AddItem Item:=rs!VLWERS & "           " & rs!Engine & "                " & rs!ENG_DESC & "             " & rs!used

    rs.MoveNext


Loop


Label8.Caption = "Total no. of Engines in " & vl
Label9.Caption = "Total no. of used Engines in"
Label12.Caption = "Total no. of not used Engines"
Label19.Caption = "Used / not Used with no Information"

Text13.Value = totalrecords
Text15.Value = usedengines
Text17.Value = notusedengines
Text20.Value = noinfos

Set rs = Nothing
Set db = Nothing

End Sub

with the above code I am putting the values of recordset in one column of listbox. How can put each value of recordset in different column.

Thanks for your feedback...

 

[DCrake]

Try removing all the .values from the syntax and retry.

 

[knowledge76]

tried as you told but the code is doing nothing new...

 

[DCrake]

If you post a copy of the strsql it may lead to a solution

 

[knowledge76]

strsql = "SELECT TBL_VP_" & Forms!Form1!Combo3 & ".VEHICLE_LINE_WERS AS VLWERS" '& vbNewLine 
  strsql = strsql & ", TBL_VP_" & Forms!Form1!Combo3 & ".ENGINE AS ENGINE" '& vbNewLine 
  strsql = strsql & ", TBL_AWS_ENGINE.Engine_Description as ENG_DESC" '& vbNewLine 
  strsql = strsql & ", TBL_AWS_ENGINE.Used as USED" '& vbNewLine 
  strsql = strsql & " FROM TBL_VP_" & Forms!Form1!Combo3 & vbNewLine 
  strsql = strsql & " LEFT JOIN TBL_AWS_ENGINE " '& vbNewLine 
  strsql = strsql & " ON TBL_VP_" & Forms!Form1!Combo3 & ".ENGINE = TBL_AWS_ENGINE.Engine_Code" & vbNewLine 
  strsql = strsql & " GROUP BY  TBL_VP_" & Forms!Form1!Combo3 & ".VEHICLE_LINE_WERS"  '& vbNewLine 
  strsql = strsql & ", TBL_VP_" & Forms!Form1!Combo3 & ".ENGINE" 
  strsql = strsql & ", TBL_AWS_ENGINE.Engine_Description" '& vbNewLine 
  strsql = strsql & ", TBL_AWS_ENGINE.Used;"

Here is the new strsql statement but the question is how by just removing .value from syntax the code puts the recordset value in new column?

 

[DCrake]

When I said Post your strsql what I meant was following the line in your code

Debug.Print strsql it echoes the strsql string to the immediate window into one long string. That is what I was expecting. If you place a breakpoint on this command line and run the code it will stop at this line. Press F8 to advance to the next line. Thne press ctrl+G if the immediate window is closed you should now be able to view this translated code in full. Copy this code into a thread and post it.

In the mean time you could also create a new query, do not pick any tables or queries, go the SQL view and post the strsql string there. Change view to design mode. If Access detects any errors it will attempt to show you where they are.

David

 

[knowledge76]

Thanks for the suggestions. I will give it a try.

 

[knowledge76]

Got it...

Created a dynamic table which is bound to list and then created query over it as a source and by creating the list no. of columns selected as wished.

Thanks for your help.