Looping through Table and Show each Entry

[bbrendan]

Hi All,

Im sort of new to VBA but need some help.

What I would like to do is loop through a table and grab the results and populate them into my text string like

Available in the Following Colours:

White
Mist Grey
Ivory
Peach

In my code, I link to a colour table and grab the colour and link this with my product description table and then wrap it in html tags. Then I save this using the open file.
Then I can view this html file in a broswer window on my form the check the html code.

**** but when I do the loop it goes straight to the last one!, and misses out the first 3 colurs.

Can anyone point me in the right direct PLEASE!!!!!

-----------------------------------
Dim strColour As string
Dim mydb2 As ADODB.Recordset
Dim intFile As Integer

Set mydb2 = New ADODB.Recordset
intFile = FreeFile

mydb2.ActiveConnection = CurrentProject.Connection
mydb2.Open "select Colour_Text from Colours where proddesc_id =" & Me.PRODDESCID

While Not mydb2.EOF
strColour = mydb2.Fields("colour_text&quot

Me.html_text = &quot;<font face='Verdana' size='2'><b>&quot; & Me.SHORTTEXT & &quot;</b></font>&quot; _
& &quot;<font face='Verdana' size='2'>&quot; & &quot;<p>&quot; _
& &quot;<b>Features</b><hr>&quot; _
& &quot;<b>::</b> &quot; & strFeatures & &quot;</b>&quot; _
& &quot;<p>&quot; & Me.PRODDESCR _
& &quot;<p><i>Available in the Following Colours:</i><p>&quot; _
& &quot;<b>::</b> &quot; & strColour _ '*** See here I need this to show all colours!!!
& &quot;</font>&quot;

Open &quot;E:\SBT\Supplier_Information\Website Connection\html\test.html&quot; For Output As intFile

Print #intFile, _
Me.html_text
Close intFile

mydb2.MoveNext
Wend
mydb2.Close

Set mydb2 = Nothing

Me.WebBrowser1.Navigate &quot;E:\SBT\Supplier_Information\Website Connection\html\test.html&quot;
-------

thanks
brendan

 

[hwkranger]

edit with the following:

dim mydb as database
dim myrecord as recordset
dim my strnig as string

set db = currentdb()
set myrecord =db.openrecordset(&quot;TABLE WHERE DATA COMES FROM&quot
If myrecord.BOF = True Then Exit Sub
With myrecord
If .RecordCount Then
.MoveFirst
Do Until myrecordset.EOF
mystring = mystring & &quot; &quot; & ![YOUR FIELD NAME]
end if
end with

Me.html_text = &quot;<font face='Verdana' size='2'><b>&quot; & Me.SHORTTEXT & &quot;</b></font>&quot; _
& &quot;<font face='Verdana' size='2'>&quot; & &quot;<p>&quot; _
& &quot;<b>Features</b><hr>&quot; _
& &quot;<b>::</b> &quot; & strFeatures & &quot;</b>&quot; _
& &quot;<p>&quot; & Me.PRODDESCR _
& &quot;<p><i>Available in the Following Colours:</i><p>&quot; _
& &quot;<b>::</b> &quot; & MYSTRING'*** I FIXED IT FOR YOU
& &quot;</font>&quot;

Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[bbrendan]

hi hwkranger,

thanks for you reply!. I have one small problem

when I try and run I get the error
End if without block if

If myrecord.BOF = True Then 'Exit Sub
With myrecord
If .RecordCount Then
.MoveFirst
Do Until myrecordset.EOF
mystring = mystring & &quot; &quot; & ![Colour_Text]

End If ***** Error Here!!
End With

Any Ideas?

Thanks

 

[PaulBricker]

MS errors messages aren't always clear. What it's saying is you have a Do Until without the Loop. Try this.
If .RecordCount Then
.MoveFirst
Do Until myrecordset.EOF
mystring = mystring & &quot; &quot; & ![Colour_Text]
.MoveNext
Loop

End If

Paul

 

[hwkranger]

sorry about that.

as you can see in the dim statements I made typo's, i forgot to end the loop


thanks Paulbricker - I should slow down when trying to help ppl Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[bbrendan]

hi paul,

thanks for your help on this. I figured it out with your help..

Just one other thing that got me stuck with this is. Some of the products we have dont have a &quot;colour&quot;, and I need a way to say something like
if colour = null then
dont show the html text tags.. for this part.

& &quot;<p><i>Available in the Following Colours:</i><p>&quot; _

any ideas?

cheers

 

[PaulBricker]

Try this on the HTML tag line for colour. I don't use this much so it's really just a guess.

Paul


IIf(IsNull(sttColour),&quot;&quot;,& &quot;<p><i>Available in the Following Colours:</i><p>&quot; _)

 

[PaulBricker]

Sorry, the other way to look at this would be to build two HTML tags.

If IsNull(strColour) Then
Me.html_text = &quot;<font face='Verdana' size='2'><b>&quot; & Me.SHORTTEXT & &quot;</b></font>&quot; _
& &quot;<font face='Verdana' size='2'>&quot; & &quot;<p>&quot; _
& &quot;<b>Features</b><hr>&quot; _
& &quot;<b>::</b> &quot; & strFeatures & &quot;</b>&quot; _
& &quot;<p>&quot; & Me.PRODDESCR _
& &quot;<b>::</b> &quot; & MYSTRING'*** I FIXED IT FOR YOU
& &quot;</font>&quot;

Else
Me.html_text = &quot;<font face='Verdana' size='2'><b>&quot; & Me.SHORTTEXT & &quot;</b></font>&quot; _
& &quot;<font face='Verdana' size='2'>&quot; & &quot;<p>&quot; _
& &quot;<b>Features</b><hr>&quot; _
& &quot;<b>::</b> &quot; & strFeatures & &quot;</b>&quot; _
& &quot;<p>&quot; & Me.PRODDESCR _
& &quot;<p><i>Available in the Following Colours:</i><p>&quot; _
& &quot;<b>::</b> &quot; & MYSTRING'*** I FIXED IT FOR YOU
& &quot;</font>&quot;
End If


Paul

 

[bbrendan]

Hi,

Sounds like it could do thr trick. Ill give it a try tommorrow

thanks
brendan