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!

Register Log in
  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

populate list/menu minus records already assigned ??

Status
Not open for further replies.
danblack30

danblack30

MIS
Aug 1, 2003
85
US
To start off, I'm not even sure If that's the correct question.

Anywho, I've built a detail page that gets the userid from a querystring. That string populates a text field (username) and list menu (pagelinks) by referencing the recordset.

Recordset =
SELECT useraccess.UserID, useraccess.Username, linkdetail.pagelinkIDdetail, pagelinks.pagelinks
FROM useraccess INNER JOIN (pagelinks INNER JOIN linkdetail ON pagelinks.pagelinkID = linkdetail.pagelinkIDdetail) ON useraccess.UserID = linkdetail.userID
WHERE useraccess.UserID<>MMColParam

I have three tables USERACCESS (userid, username, userpassword, userlevel) , LINKDETAIL (id, userid, pagelinkdetail) , and PAGELINKS (pagelinkid, pagelinks).

USERACCESS userid(1) inner join LINKDETAIL userid(many)
PAGELINK pagelinkid(1) inner join LINKDETAIL pagelinkiddetail(many)

There will be several different userid's, with access to many pagelinks.

What I'm trying do is populate the drop down list with pagelinks that aren't already assigned to that user. Then use an insert behavior to add userid and pagelinkiddetail to the linkdetail table. Which will in turn remove that pagelink from the menu.

here's the code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/conntesttsrserv.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("UserID") <> "") Then
Recordset1__MMColParam = Request.QueryString("UserID")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_conntesttsrserv_STRING
Recordset1.Source = "SELECT useraccess.UserID, useraccess.Username, linkdetail.pagelinkIDdetail, pagelinks.pagelinks FROM useraccess INNER JOIN (pagelinks INNER JOIN linkdetail ON pagelinks.pagelinkID = linkdetail.pagelinkIDdetail) ON useraccess.UserID = linkdetail.userID WHERE useraccess.UserID<>" + Replace(Recordset1__MMColParam, "'", "''") + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<table width="100%" height="82" border="0">
<tr>
<td><form name="form1" method="post" action="">
<p>
Username
<%=(Recordset1.Fields.Item("Username").Value)%>
</p>
<p> pagelinks
<select name="pagelinkid" id="pagelinkid">
<%
While (NOT Recordset1.EOF)
%>
<option value="<%=(Recordset1.Fields.Item("pagelinkIDdetail").Value)%>"><%=(Recordset1.Fields.Item("pagelinks").Value)%></option>
<%
Recordset1.MoveNext()
Wend
If (Recordset1.CursorType > 0) Then
Recordset1.MoveFirst
Else
Recordset1.Requery
End If
%>
</select>
</p>
<p>
<input type="submit" name="Submit" value="Add pagelink">
</p>
</form></td>
</tr>
</table>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Any help greatly appreciated.
Thanks,
Dan
 
Sort by date Sort by votes
As this is more of an ASP question than an Access Forms question, you may have better results in the Microsoft: Active Server Pages (ASP) forum.

I see a number of issues with the ASP code that may or may not be producing bad results. First, nothing can preceed a DOCTYPE declairation, not even ASP code. Secondly, VB uses the "&" to concatenate values in a string, not "+". Also, (please take this as constructive criticism) your code is very disjointed. Instead of using response.write, you stop code, write a small piece of html, then start the code again. Oh, and the "while... wend" statement has been depricated. You should use "Do While... Loop" statements instead.

If it were me, I would write the code like this:
Code: 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/html4/loose.dtd">[/URL]
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
  <title>Untitled Document</title>
</head>

<body>
<!--#include file="connections/conntesttsrserv.asp" -->
<%
  Dim Recordset1_MMColParam
  Dim Recordset1
  Dim Recordset1_numRows

  Recordset1_MMColParam = Request.Querystring("UserID")
  If Recordset_MMColParam = "" Then
    RecordSetMMColParam = "1"
  End If

  Set Recordset1 = Server.CreateObject("ADODB.Recordset")
  Recordset1.ActiveConnection = MM_Conntesttserv_STRING
  Recordset1.Source = "SELECT useraccess.UserID, useraccess.Username," & _ 
                      "linkdetail.pagelinkIDdetail, pagelinks.pagelinks" & _
                      "FROM useraccess INNER JOIN (pagelinks INNER JOIN" & _
                      "linkdetail ON pagelinks.pagelinkID =" & _
                      "linkdetail.pagelinkIDdetail) ON useraccess.UserID = " & _
                      "linkdetail.userID  WHERE useraccess.UserID<> " & _
                      Replace(Recordset1__MMColParam, "'", "''") & ";"
  Recordset1.CursorType = 0
  Recordset1.CursorLocation = 2
  Recordset1.LockType = 1
%>

  <table width="100%" height="82"  border="0">
    <tr>
      <td>
        <form name="form1" method="post" action="">
<%
  Response.Write("<p>Username " & Recordset1("Username") & "</p>")
  Response.Write("<p>PageLinks <select name='pagelinkID' id='pagelinkID'>")
  Do While NOT Recordset1.EOF
    Response.Write("<option value='" & Recordset1("pagelinkIDdetail") & _
                   "'>" & Recordset1("pagelinks") & "</option>")
    Recordset1.MoveNext
  Loop
  Response.Write("</select></p>")
  Response.Write("<p><input type='submit' name='submit' value='Add PageLink'>")
  Response.Write("</p>")
%>
        </form>
      </td>
    </tr>
  </table>
</body>
</html>

Without knowing more about the database and the fields there in, it is difficult to know for sure where the problem really lies.

-Brian-
I'm not an actor, but I play one on TV.
 
Upvote 0 Downvote
hehe disjointed ... I feel like the scarecrow in the Wizard of Oz.

Problem is, I'm using dreamweaver and that's how it writes the code by default. I even put that on a new page so there would be less code to worry about =).
I took your suggestion and reposted in the vbscript section.

Thanks for the response,
Dan
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

caykamanj1966
  • Locked
  • Question
Duplicate Records in DataSheet View
Replies
5
Views
771
Andrzejek
Andrzejek
spartansFC
  • Locked
  • Question
Combo Box (Don't show value in selection if already in use)
Replies
3
Views
299
Andrzejek
Andrzejek
JayRo71
  • Locked
  • Question
Select records on subform using a combo box with a twist 1
2 3
Replies
56
Views
2K
JayRo71
JayRo71
dhookom
  • Locked
  • Question
Make second combo box display records dependant on selection from first combo box 1
Replies
2
Views
416
dhookom
dhookom
nikademous
  • Locked
  • Question
List procedures in a module
Replies
2
Views
246
nikademous
nikademous

Part and Inventory Search

Sponsor

Back
Top