Can't see error in my code

[galorin]

Here is a block of code that gets re-used with changes throughout my project. It takes various numeric values stored in a remote MySQL DB, munges them together to create drawing identifiers,

Private Sub poplist()
Dim cn As ADODB.Connection
Dim cmd As ADODB.command
Dim rst As ADODB.Recordset
Dim i As Integer
Dim f(9) As String
Dim Field0 As String
Dim Field1 As String
Dim Field2 As String
Dim rev As Integer

Set cn = New ADODB.Connection

With cn
  .ConnectionString = DB_CONNECT
  .Open
End With

Set cmd = New ADODB.command
With cmd
 .CommandType = adCmdUnknown
End With

[COLOR=green]'get fields to build drawing number[/color]
sqlstr = "SELECT h.ht_id, h.softver, h.House_type, "
sqlstr = sqlstr & "b.ShortCode AS builder_ShortCode, h.level, h.Floor, h.Revision, "
sqlstr = sqlstr & "d.ShortCode AS dist_ShortCode, e.ShortCode AS enq_ShortCode,"
sqlstr = sqlstr & " h.Hours"
sqlstr = sqlstr & " FROM `contacts`.`HouseType` AS h INNER JOIN CompShort as b"
sqlstr = sqlstr & " ON b.CompID = h.builder_id INNER JOIN CompShort as d"
sqlstr = sqlstr & " ON d.CompID = h.dist_id INNER JOIN CompShort as e"
sqlstr = sqlstr & " ON e.CompID = h.enq_id WHERE h.quote_id =" & QuoteID
sqlstr = sqlstr & " ORDER BY House_Type"

With cmd
  .ActiveConnection = cn
  .CommandText = sqlstr
  Set rst = .Execute
End With

[COLOR=green]'clear out listbox for new items[/color]
For i = Me.HTlist.ListCount - 1 To 0 Step -1
    Me.HTlist.RemoveItem i
Next i

If Not rst.EOF Then
        rst.MoveFirst
        Do
[COLOR=green]'loop through resultset[/color]
        f(0) = rst.Fields(0) [COLOR=green]'Housetype ID for operatons[/color]
        f(1) = rst.Fields(1) [COLOR=green]'Software version[/color]
        For i = rst.Fields.Count - 1 To 2 Step -1
         If IsNull(rst.Fields(i)) Then
          f(i) = ""
         ElseIf rst.Fields(i) = "non" Then
          f(i) = ""
         ElseIf i = 6 Then
           If rst.Fields(i) = 0 Then
            f(i) = ""
           Else
            rev = rst.Fields(i)
            f(i) = "=rev-" & Chr(96 + rev)
           End If
        Else
        f(i) = "=" & rst.Fields(i)
        End If
        Next i
            Field0 = rst.Fields(2) 'House name
            Field1 = f(1) & f(3) & f(4) & f(2) & f(5) & f(6) & f(7) & f(8) [COLOR=green]'Drawing number identifier[/color]
            Field2 = Nz(rst.Fields(9), "0") [COLOR=green]'Hours assigned to project[/color]
            Me.HTlist.AddItem Item:="" + f(0) + ";" + Field0 + ";" + Field1 + ";" + Field2 + ""
        rst.MoveNext
        Loop Until rst.EOF
    End If

cn.CLose
End Sub

The above code works everywhere else, but in this iteration the last line for the htlist is present, but blank. Is this a code error, or could it be a corruption in the file itself?

 

[missinglinq]

Me.HTlist.AddItem Item:="" + f(0) + ";" + Field0 + ";" + Field1 + ";" + Field2 + ""

That's because you have it adding your fields togather! Try

Me.HTlist.AddItem Item:="" & f(0) & ";" & Field0 & ";" & Field1 & ";" & Field2 & ""


The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[galorin]

I see, I've changed the code, but not before doing a /decompile and compact&repair DB recovery thingy. the code started working properly before I made the changes, but I have changed the + to & for each time I use similar code.

I'm still not sure what the difference is exactly, but it seems to work the same, and is perhaps a better coding practise. Can you see anything else wrong, or that I could change to make my code more manageable?

 

[ClulessChris]

+ is a mathamatical calculation
& is used to concatonate string values.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[ck1999]

You could use a case statement to better visually understand your if statements

and won't htlist.Clear clear your list box so you do not need the loop

ck1999

 

[galorin]

I'll try the htlist.clear, not a good enough coder to know all them nifty tricks, just enough to trip over VBA bugs.

I tried doing a case but ran into problems because of the completely random data that gets fed into it.

 

[ClulessChris]

you can use case else for unexpected data passing through a select case

Never knock on Death's door: ring the bell and run away! Death really hates that!