For loop with Access

Flipster · Mar 17, 2005

Total Novice.

I have a database that contains , amongst many others, three fields: "userid", "name", and "contact". I have a list of 176 userids that I need to procure the name and contact for. Please tell me that there is an easy way to write a "for loop" type query solution within Access. And please tell me that there is an obvious place to do this within the app. Normally I would heavily research before coming here but I am under the gun.

Thanks in advance.

steven290 · Mar 17, 2005

try this
just update your db info

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Contact List</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<style>
#gridlist tr.heads td{
background-color:#666666;
color:#FFFFFF;
}

#gridlist td{
padding-left:10px;
}

</style>
<body>
<%
function emptyrecordfix(strtext)
if isempty(strtext) or isnull(strtext) or strtext="" then
temp=" "
else
temp=strtext
end if
emptyrecordfix=temp
end function

strDBPath = Server.MapPath("yourdb.mdb")

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

Set rs = conn.Execute("SELECT userid,name,contact FROM tblname")

rows = rs.GetRows()

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

FRec = LBound(rows, 2)
LRec = UBound(rows, 2)

%>
<table border="0" cellspacing="0" cellpadding="0" id="gridlist">
<tr class="heads">
<td>User ID</td>
<td>Name</td>
<td>Contact</td>
</tr>
<%

For r = FRec To LRec
if rc="" Or rc="#D8E1E9" Then rc="#ffffff" Else rc="#D8E1E9"

Response.Write "<tr bgcolor="""&rc& """ >" & vbCrLf

Response.Write vbTab & "<td>" & emptyrecordfix(rows(0, r)) & "</td>" & vbCrLf

Response.Write vbTab & "<td>" & emptyrecordfix(rows(1, r)) & "</td>" & vbCrLf

Response.Write vbTab & "<td>" & emptyrecordfix(rows(2, r)) & "</td>" & vbCrLf
Response.Write "</tr>" & vbCrLf
Next
%>
</table>
</body>
</html>

Flipster · Mar 17, 2005

Thanks but your idea seems a little involved. Is there nothing within Access itself that would run a a query for 176 of 800+ userids and return the name and contact associated?

lespaul · Mar 17, 2005

you mean you want to write a query to return these names and contacts and once you have the result set you want to loop through it and do something with each of the names in the result set? What 176 users do you want to retrieve? Is there some specific about these users that can be used as criteria?

Can you provide some sample data and your expected results? And what you want to do with the data once you get it?

Leslie

steven290 · Mar 17, 2005

whats makes these 176 different from the rest of the list? Are they the first 176?

gol4 · Mar 17, 2005

take your list of 176 user ids get them into excel
highlight them and copy them
open access and goto the tables tab
right click and select paste
this will create a table of the user ids
click on the queries tab
select new
add the table you just created and the table that has the information you want
join them on userid
add the name and contact fields to the query
run the query it will have the information for each of the users whos id you had in your list

BNPMike · Mar 17, 2005

Flipster

You don't seem to have the faintest idea about Access or relational databases. You really need to get a book and spend a couple of weeks working through it.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

For loop with Access

Flipster

MIS

steven290

Programmer

Flipster

MIS

lespaul

Programmer

steven290

Programmer

gol4

Technical User

BNPMike

Technical User

Similar threads

Part and Inventory Search

Sponsor