Add items to drop down list on user form 2

[dgillz]

I have a user form with a combobox and I want to add items to the combo box from a SQL statement. Here is my code:

[COLOR=#4E9A06]'Drop Down List SQL statement[/color]
mstSql = "SELECT DISTINCT user_def_fld_3 FROM imitmidx_sql WHERE user_def_fld_3 IS NOT NULL ORDER BY user_def_fld_3"
    cmbColor.Clear [COLOR=#4E9A06]'clears the drop down list[/color]
    
    rsData.Open mstSql, conData, adOpenDynamic, adLockOptimistic [COLOR=#4E9A06]'executes the SQL statement[/color]
    
    With rsData
        cmbColor.AddItem rsData.Fields("user_def_fld_3") [COLOR=#4E9A06]'adds the item to the drop down list[/color]
        .MoveNext [COLOR=#4E9A06]'? was hoping this would loop through the record set, but it doesn't[/color]
    End With    
    rsData.Close

This does add the first item to the drop down list, but I need it to loop through ALL the records in rsData, and add them all. Any ideas on how to do this?


Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[PHV]

You need a loop, eg:

With rsData
    .Open mstSql, conData, adOpenDynamic, adLockOptimistic 'executes the SQL statement
    While NOT (.EOF Or .BOF)
        cmbColor.AddItem .Fields("user_def_fld_3") 'adds the item to the drop down list
        .MoveNext
    WEnd
    .Close
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dgillz]

Doesn't there need to be a Loop in there somewhere?

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[PHV]

Doesn't there need to be a Loop in there somewhere?
Did you even try my sugested code ?
Did you even know that a While ... Wend statement is a LOOP ?

 

[RonRepp]

dgillz:

PHV is correct. The .bof means "beginning of file"; the .eof means "end of file". The .MoveNext moves the recordset to the next qualifying record.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[dgillz]

PHV,

No, I did not know wend was/is a loop. I thought a loop was explicitly required. In fact I've never heard of wend until now.

I understand .bof and .eof no problem, but wend is new to me.

I appreciate straight forward answers to my questions, so I can learn. An explanation of wend and how it solves my issue would've been greatly appreciated.

A star nonetheless for solving my problem.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[RonRepp]

From Dictionary.com:

wend [wend] verb, wend·ed or ( Archaic ) went; wend·ing.
verb (used with object)
1. to pursue or direct (one's way).
verb (used without object)
2. to proceed or go.

Thus, you are "going" "while" something is happening. You could also have used something like this: (old procedure that creates an HTML e-mail)

Dim Con As ADODB.Connection
    Dim SQL As String
    Dim RS As ADODB.Recordset
    Dim hTable As String
    Dim Q, ST, S As String
       
    Set RS = New ADODB.Recordset
    Set Con = New ADODB.Connection
    
    SQL = "EM_GetOrderedTotals " & OrderID
            
    Con.ConnectionString = GetConstring("3Source")
    
    Con.Open
    
    RS.Open SQL, Con, adOpenDynamic, adLockBatchOptimistic, adCmdText
    
    Do While Not RS.EOF
        
        Q = RS("QTY").Value
        S = Format(RS("Shipping").Value, "###,###.00")
        ST = Format(RS("SubTotal").Value, "###,###.00")
        
    hTable = hTable & "<tr>"
    hTable = hTable & "<td style=""padding: 1.5pt; noWrap width=""139"" align=""left"">"
    hTable = hTable & "<p class=""MsoNormal"" style=""TEXT-ALIGN: left"" align=""left""><b>"
    hTable = hTable & "<font face=""Times New Roman"" size=""2"">"
    hTable = hTable & "<span style=""FONT-WEIGHT: bold; FONT-SIZE: 10.5pt"">Totals:</span></font></b></td>"
    hTable = hTable & "<td style=""padding: 1.5pt; noWrap width=""272"" align=""left"">"
    hTable = hTable & "<p class=""MsoNormal"" style=""TEXT-ALIGN: left"" align=""left""><b>"
    hTable = hTable & "<font face=""Times New Roman"" size=""2"">"
    hTable = hTable & "<span style=""FONT-WEIGHT: bold; FONT-SIZE: 10.5pt"">&nbsp;&nbsp;&nbsp</span></font></b></td>"
    hTable = hTable & "<td style=""padding: 1.5pt; noWrap width=""41"" align=""center"">"
    hTable = hTable & "<p class=""MsoNormal""><b>"
    hTable = hTable & "<font face=""Times New Roman"" size=""2"">"
    hTable = hTable & "<span style=""FONT-WEIGHT: bold; FONT-SIZE: 10.5pt"">" & Q & "</span></font></b></td>"
    hTable = hTable & "<td style=""padding: 1.5pt; noWrap width=""56"" align=""center"">"
    hTable = hTable & "<p class=""MsoNormal""><b>"
    hTable = hTable & "<font face=""Times New Roman"" size=""2"">"
    hTable = hTable & "<span style=""FONT-WEIGHT: bold; FONT-SIZE: 10.5pt"">&nbsp;&nbsp;&nbsp</span></font></b></td>"
    hTable = hTable & "<td style=""padding: 1.5pt; noWrap width=""144"" align=""right"">"
    hTable = hTable & "<p class=""MsoNormal""><b>"
    hTable = hTable & "<font face=""Times New Roman"" size=""2"">"
    hTable = hTable & "<span style=""FONT-WEIGHT: bold; FONT-SIZE: 10.5pt"">$ " & S & "</span></font></b></td>"
    hTable = hTable & "<td style=""padding: 1.5pt; noWrap width=""194"" align=""right"">"
    hTable = hTable & "<p class=""MsoNormal""><b>"
    hTable = hTable & "<font face=""Times New Roman"" size=""2"">"
    hTable = hTable & "<span style=""FONT-WEIGHT: bold; FONT-SIZE: 10.5pt"">$ " & ST & "</span></font></b></td>"
    hTable = hTable & "</tr>"
    hTable = hTable & "</TBODY></TABLE>"
        
        
        RS.MoveNext
    Loop
    
    RS.Close
    Set RS = Nothing

I hope this helps

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net