pskeens
asked on
MS Access - If Not Rs.EOF Issue Loop issue
Once again I need help. How is a better way to write this RS.EOF code? WHat I need is sort of a loop, but its not looping really because it applies to the next record and different txt control on the form. Basically it works now but if there is not five records then I get an error and it stops after the second instance if there are only two records in the recordset.
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
rs.MoveFirst
Me!txtBdl1 = rs("inv_tagid") & " - " & rs("inv_rec_date")
If Not rs.EOF Then
rs.MoveNext
Me!txtBdl2 = rs("inv_tagID") & " - " & rs("inv_rec_date")
If Not rs.EOF Then
rs.MoveNext
Me!txtBdl3 = rs("inv_tagID") & " - " & rs("inv_rec_date")
If Not rs.EOF Then
rs.MoveNext
Me!txtBdl4 = rs("inv_tagID") & " - " & rs("inv_rec_date")
If Not rs.EOF Then
rs.MoveNext
Me!txtBdl5 = rs("inv_tagID") & " - " & rs("inv_rec_date")
End If
End If
End If
End If
rs.Close
Set rs = Nothing
ASKER
That did not work. I did not get any errors, however nothing happened.
You need to move to the first record. I believe that is the statement that was missing.
Thus:
dim txtBdl(6), lcount
lcount = 0
rs.MoveFirst
while Not rs.EOF
txtBdl(lcount ) = rs("inv_tagid") & " - " & rs("inv_rec_date")
lcount = lcount + 1
rs.MoveNext
End While
Thus:
dim txtBdl(6), lcount
lcount = 0
rs.MoveFirst
while Not rs.EOF
txtBdl(lcount ) = rs("inv_tagid") & " - " & rs("inv_rec_date")
lcount = lcount + 1
rs.MoveNext
End While
ASKER
That did not work either. However I changed the "End While" to "Wend", but still did nothing
Dim txtBdl(6), lcount
lcount = 0
rs.MoveFirst
While Not rs.EOF
txtBdl(lcount) = rs("inv_tagid") & " - " & rs("inv_rec_date")
lcount = lcount + 1
rs.MoveNext
Wend
Dim txtBdl(6), lcount
lcount = 0
rs.MoveFirst
While Not rs.EOF
txtBdl(lcount) = rs("inv_tagid") & " - " & rs("inv_rec_date")
lcount = lcount + 1
rs.MoveNext
Wend
You could count the records before entering the loop:
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
If Not rs.EOF Then
rs.MoveLast
If rs.RecordCount >= 5 Then
rs.MoveFirst
Me!txtBdl1 = rs("inv_tagid") & " - " & rs("inv_rec_date")
rs.MoveNext
Me!txtBdl2 = rs("inv_tagID") & " - " & rs("inv_rec_date")
rs.MoveNext
Me!txtBdl3 = rs("inv_tagID") & " - " & rs("inv_rec_date")
rs.MoveNext
Me!txtBdl4 = rs("inv_tagID") & " - " & rs("inv_rec_date")
rs.MoveNext
Me!txtBdl5 = rs("inv_tagID") & " - " & rs("inv_rec_date")
End If
End If
rs.Close
Set rs = Nothing
/gustav
I modified one of my ASP pages to test this and this is what works. It also matches the code in my Access book as far as the Do While loop goes. I did the second loop at the end to show that the data was in the array.
<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<html>
<head>
<title>Dave's Simple ADO Query on Musician's 2 MDB</title>
<style type="text/css">
<!--
td { background-color: white; }
-->
</style>
</head>
<body bgcolor="White" topmargin="10" leftmargin="10">
<!-- Display Header -->
<font size="4" face="Arial, Helvetica">
<b>Simple ADO Query with ASP</b></font><br>
<hr size="1" color="#000000">
Contacts within the Musicians Database:<br><br>
<%
Dim oConn
Dim oRs
Dim filePath
Dim Index
Dim txtBdl(6), lcount
' Map authors database to physical path
filePath = Server.MapPath("Musicians2.mdb")
' Create ADO Connection Component to connect
' with sample database
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath
' Execute a SQL query and store the results
' within recordset
Set oRs = oConn.Execute("SELECT ID, FirstName, LastName, HomePhone From Addresses")
%>
<table border="0" cellpadding="1" cellspacing="1" summary="" bgcolor="#808080">
<%
lcount = 0
oRs.MoveFirst
Do while ((Not oRs.eof) AND (lcount < 6)) %>
<tr>
<% txtBdl(lcount) = oRs.Fields("FirstName").Value & " - " & oRs.Fields("LastName").Value %>
<td valign=top> <% = txtBdl(lcount)%></TD>
<%
lcount = lcount + 1 %>
</tr>
<% oRs.MoveNext
Loop
%>
</table>
<%
oRs.close
oConn.close
lcount = 0
%>
<br>
<%
Do While (txtBdl(lcount) <> "") AND (lcount < 6)
Response.write txtBdl(lcount) & " :: "
lcount = lcount + 1
Loop
%>
</body>
</html>
ASKER
Thanks. Counting the records before does not help. Sometimes all 5 text boxes will be filled but then there will be times that only one, two, or even none of them will be. This is why I was trying to use the loop through.
Dave I will give this a shot and see what happens.
Dave I will give this a shot and see what happens.
> Counting the records before does not help.
> Sometimes all 5 text boxes will be filled but then there will be times
> that only one, two, or even none of them will be.
??
What does it matter if the textboxes are filled? Your original code doesn't care.
And why does counting not help? A count of 4 or less was exactly the cause of your trouble.
Please elaborate.
/gustav
> Sometimes all 5 text boxes will be filled but then there will be times
> that only one, two, or even none of them will be.
??
What does it matter if the textboxes are filled? Your original code doesn't care.
And why does counting not help? A count of 4 or less was exactly the cause of your trouble.
Please elaborate.
/gustav
How about this?
rather than re-iterating over the different text boxes by name, use an incrementing variable
rather than re-iterating over the different text boxes by name, use an incrementing variable
rs.MoveFirst
Dim i
i = 1
Do While Not rs.EOF
Me!txtBdl & i = rs.Fields(0).Value & " - " & rs.Fields(1).Value
i = i + 1
rs.MoveNext
Loop
ASKER
gbshahaq that did not work.
Gustav: The code above on counting records means that it will only go through the movenext steps IF there are more than 5 records, right? I need it to complete the steps until it runs out of rows of data up to 5.
Gustav: The code above on counting records means that it will only go through the movenext steps IF there are more than 5 records, right? I need it to complete the steps until it runs out of rows of data up to 5.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BINGO! We have a working code.
I cant thank ALL OF YOU enough! Thanks for hanging with me on this.
I cant thank ALL OF YOU enough! Thanks for hanging with me on this.
You are welcome!
/gustav
/gustav
Open in new window