Get ready for BLACK FRIDAY shopping starting in

Forums

This topic is locked

Repeat Region/Show If Empty

Posted 10 Mar 2002 09:15:20
1
has voted
10 Mar 2002 09:15:20 frog jumps posted:
<font face='Tahoma'>
Here goes:

I have a Access db w/ 3 tables
1. Customers - Info pertaining to customer is listed here
2. Notes - Notes pertaining to customers in Customers Table, updated
constantly--sales info, general notes, invoicing info etc....
3. Employees - employee info, name, user id, password etc...

My problem lies within a REPEATED REGION....
When displaying the Customers Record I get ALL the NOTES
on ALL customers, not just notes pertaining to the current record
being displayed. I think my problem might be in the SQL, not sure.

But the Repeat Region is tweaked regardless...

here's the code, can someone HELP:

&lt;%@LANGUAGE="VBSCRIPT"%&gt;
&lt;!--#include file="Connections/cnDH.asp" --&gt;
&lt;%
set rsCust = Server.CreateObject("ADODB.Recordset"
rsCust.ActiveConnection = MM_cnDH_STRING
rsCust.Source = "SELECT Customers.CustID, Customers.CompanyName,
Customers.ContactFirstName, Customers.ContactLastName,
Customers.Address, Customers.Address2, Customers.Address3,
Customers.City, Customers.State, Customers.ZipCode,
Customers.PhoneNumber, Customers.FaxNumber, Customers.AltPhone,
Notes.TaskID, Notes.nCustID, Notes.Employee, Notes.Time, Notes.Date,
Notes.Notes FROM Customers LEFT OUTER JOIN Notes ON (Notes.nCustID =
Customers.CustID)"
rsCust.CursorType = 3
rsCust.CursorLocation = 2
rsCust.LockType = 3
rsCust.Open()
rsCust_numRows = 0
%&gt;
&lt;%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsCust_numRows = rsCust_numRows + Repeat1__numRows
%&gt;
&lt;%
' *** Recordset Stats, Move To Record, and Go To Record: declare
stats variables

' set the record count
rsCust_total = rsCust.RecordCount

' set the number of rows displayed on this page
If (rsCust_numRows &lt; 0) Then
rsCust_numRows = rsCust_total
Elseif (rsCust_numRows = 0) Then
rsCust_numRows = 1
End If

' set the first and last displayed record
rsCust_first = 1
rsCust_last = rsCust_first + rsCust_numRows - 1

' if we have the correct record count, check the other stats
If (rsCust_total &lt;&gt; -1) Then
If (rsCust_first &gt; rsCust_total) Then rsCust_first = rsCust_total
If (rsCust_last &gt; rsCust_total) Then rsCust_last = rsCust_total
If (rsCust_numRows &gt; rsCust_total) Then rsCust_numRows =
rsCust_total
End If
%&gt;
&lt;%
' *** Move To Record and Go To Record: declare variables

Set MM_rs = rsCust
MM_rsCount = rsCust_total
MM_size = rsCust_numRows
MM_uniqueCol = ""
MM_paramName = ""
MM_offset = 0
MM_atTotal = false
MM_paramIsDefined = false
If (MM_paramName &lt;&gt; "" Then
MM_paramIsDefined = (Request.QueryString(MM_paramName) &lt;&gt; ""
End If
%&gt;
&lt;%
' *** Move To Record: handle 'index' or 'offset' parameter

if (Not MM_paramIsDefined And MM_rsCount &lt;&gt; 0) then

' use index parameter if defined, otherwise use offset parameter
r = Request.QueryString("index"
If r = "" Then r = Request.QueryString("offset"
If r &lt;&gt; "" Then MM_offset = Int(r)

' if we have a record count, check if we are past the end of the
recordset
If (MM_rsCount &lt;&gt; -1) Then
If (MM_offset &gt;= MM_rsCount Or MM_offset = -1) Then ' past end or
move last
If ((MM_rsCount Mod MM_size) &gt; 0) Then ' last page not a
full repeat region
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If

' move the cursor to the selected record
i = 0
While ((Not MM_rs.EOF) And (i &lt; MM_offset Or MM_offset = -1))
MM_rs.MoveNext
i = i + 1
Wend
If (MM_rs.EOF) Then MM_offset = i ' set MM_offset to the last
possible record

End If
%&gt;
&lt;%
' *** Move To Record: if we dont know the record count, check the
display range

If (MM_rsCount = -1) Then

' walk to the end of the display range for this page
i = MM_offset
While (Not MM_rs.EOF And (MM_size &lt; 0 Or i &lt; MM_offset + MM_size))
MM_rs.MoveNext
i = i + 1
Wend

' if we walked off the end of the recordset, set MM_rsCount and
MM_size
If (MM_rs.EOF) Then
MM_rsCount = i
If (MM_size &lt; 0 Or MM_size &gt; MM_rsCount) Then MM_size = MM_rsCount
End If

' if we walked off the end, set the offset based on page size
If (MM_rs.EOF And Not MM_paramIsDefined) Then
If (MM_offset &gt; MM_rsCount - MM_size Or MM_offset = -1) Then
If ((MM_rsCount Mod MM_size) &gt; 0) Then
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If

' reset the cursor to the beginning
If (MM_rs.CursorType &gt; 0) Then
MM_rs.MoveFirst
Else
MM_rs.Requery
End If

' move the cursor to the selected record
i = 0
While (Not MM_rs.EOF And i &lt; MM_offset)
MM_rs.MoveNext
i = i + 1
Wend
End If
%&gt;
&lt;%
' *** Move To Record: update recordset stats

' set the first and last displayed record
rsCust_first = MM_offset + 1
rsCust_last = MM_offset + MM_size
If (MM_rsCount &lt;&gt; -1) Then
If (rsCust_first &gt; MM_rsCount) Then rsCust_first = MM_rsCount
If (rsCust_last &gt; MM_rsCount) Then rsCust_last = MM_rsCount
End If

' set the boolean used by hide region to check if we are on the last
record
MM_atTotal = (MM_rsCount &lt;&gt; -1 And MM_offset + MM_size &gt;= MM_rsCount)
%&gt;
&lt;%
' *** Go To Record and Move To Record: create strings for maintaining
URL and Form parameters

' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName &lt;&gt; "" Then MM_removeList = MM_removeList & "&" &
MM_paramName & "="
MM_keepURL="":MM_keepForm="":MM_keepBoth="":MM_keepNone=""

' add the URL parameters to the MM_keepURL string
For Each Item In Request.QueryString
NextItem = "&" & Item & "="
If (InStr(1,MM_removeList,NextItem,1) = 0) Then
MM_keepURL = MM_keepURL & NextItem &
Server.URLencode(Request.QueryString(Item))
End If
Next

' add the Form variables to the MM_keepForm string
For Each Item In Request.Form
NextItem = "&" & Item & "="
If (InStr(1,MM_removeList,NextItem,1) = 0) Then
MM_keepForm = MM_keepForm & NextItem &
Server.URLencode(Request.Form(Item))
End If
Next

' create the Form + URL string and remove the intial '&' from each of
the strings
MM_keepBoth = MM_keepURL & MM_keepForm
if (MM_keepBoth &lt;&gt; "" Then MM_keepBoth = Right(MM_keepBoth,
Len(MM_keepBoth) - 1)
if (MM_keepURL &lt;&gt; "" Then MM_keepURL = Right(MM_keepURL,
Len(MM_keepURL) - 1)
if (MM_keepForm &lt;&gt; "" Then MM_keepForm = Right(MM_keepForm,
Len(MM_keepForm) - 1)

' a utility function used for adding additional parameters to these
strings
Function MM_joinChar(firstItem)
If (firstItem &lt;&gt; "" Then
MM_joinChar = "&"
Else
MM_joinChar = ""
End If
End Function
%&gt;
&lt;%
' *** Move To Record: set the strings for the first, last, next, and
previous links

MM_keepMove = MM_keepBoth
MM_moveParam = "index"

' if the page has a repeated region, remove 'offset' from the
maintained parameters
If (MM_size &gt; 0) Then
MM_moveParam = "offset"
If (MM_keepMove &lt;&gt; "" Then
params = Split(MM_keepMove, "&"
MM_keepMove = ""
For i = 0 To UBound(params)
nextItem = Left(params(i), InStr(params(i),"=" - 1)
If (StrComp(nextItem,MM_moveParam,1) &lt;&gt; 0) Then
MM_keepMove = MM_keepMove & "&" & params(i)
End If
Next
If (MM_keepMove &lt;&gt; "" Then
MM_keepMove = Right(MM_keepMove, Len(MM_keepMove) - 1)
End If
End If
End If

' set the strings for the move to links
If (MM_keepMove &lt;&gt; "" Then MM_keepMove = MM_keepMove & "&"
urlStr = Request.ServerVariables("URL" & "?" & MM_keepMove &
MM_moveParam & "="
MM_moveFirst = urlStr & "0"
MM_moveLast = urlStr & "-1"
MM_moveNext = urlStr & Cstr(MM_offset + MM_size)
prev = MM_offset - MM_size
If (prev &lt; 0) Then prev = 0
MM_movePrev = urlStr & Cstr(prev)
%&gt;
&lt;html&gt;
&lt;head&gt;
&lt;title&gt;The Direct Hit&lt;/title&gt;
&lt;meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1"&gt;
&lt;script language="JavaScript"&gt;
&lt;!--
function
GP_AdvOpenWindow(theURL,winName,features,popWidth,popHeight,winAlign,ignorelink,alwaysOnTop,autoCloseTime,borderless)
{ //v2.0
var leftPos=0,topPos=0,autoCloseTimeoutHandle, ontopIntervalHandle,
w = 480, h = 340;
if (popWidth &gt; 0) features += (features.length &gt; 0 ? ',' : '') +
'width=' + popWidth;
if (popHeight &gt; 0) features += (features.length &gt; 0 ? ',' : '') +
'height=' + popHeight;
if (winAlign && winAlign != "" && popWidth &gt; 0 && popHeight &gt; 0) {
if (document.all || document.layers || document.getElementById) {w
= screen.availWidth; h = screen.availHeight;}
if (winAlign.indexOf("center" != -1) {topPos =
(h-popHeight)/2;leftPos = (w-popWidth)/2;}
if (winAlign.indexOf("bottom" != -1) topPos =
h-popHeight; if (winAlign.indexOf("right" != -1) leftPos =
w-popWidth;
if (winAlign.indexOf("left" != -1) leftPos = 0; if
(winAlign.indexOf("top" != -1) topPos = 0;
features += (features.length &gt; 0 ? ',' : '') + 'top=' +
topPos+',left='+leftPos;}
if (document.all && borderless && borderless != "" &&
features.indexOf("fullscreen" != -1) features+=",fullscreen=1";
if (window["popupWindow"] == null) window["popupWindow"] = new
Array();
var wp = popupWindow.length;
popupWindow[wp] = window.open(theURL,winName,features);
if (popupWindow[wp].opener == null) popupWindow[wp].opener = self;
if (document.all || document.layers || document.getElementById) {
if (borderless && borderless != ""
{popupWindow[wp].resizeTo(popWidth,popHeight);
popupWindow[wp].moveTo(leftPos, topPos);}
if (alwaysOnTop && alwaysOnTop != "" {
ontopIntervalHandle =
popupWindow[wp].setInterval("window.focus();", 50);
popupWindow[wp].document.body.onload = function()
{window.setInterval("window.focus();", 50);}; }
if (autoCloseTime && autoCloseTime &gt; 0) {
popupWindow[wp].document.body.onbeforeunload = function() {
if (autoCloseTimeoutHandle)
window.clearInterval(autoCloseTimeoutHandle);
window.onbeforeunload = null; }
autoCloseTimeoutHandle =
window.setTimeout("popupWindow["+wp+"].close()", autoCloseTime *
1000); }
window.onbeforeunload = function() {for (var
i=0;i&lt;popupWindow.length;i++) popupWindow[i].close();}; }
document.MM_returnValue = (ignorelink && ignorelink != "" ? false :
true;
}
//--&gt;
&lt;/script&gt;
&lt;/head&gt;
&lt;body text="#000000" link="#0000FF" vlink="#FF0000" alink="#FFFF00"
bgcolor="#FFFFFF"&gt;
&lt;font face="Tahoma"&gt;&lt;font face="Tahoma"&gt;&lt;font size="2"&gt; &lt;font
color="#333333"&gt;&lt;img src="/fireworks/directhit.png" width="160"
height="20"&gt;&lt;br&gt;
&lt;br&gt;
&lt;/font&gt;&lt;/font&gt;&lt;font size="2" color="#333333"&gt; &lt;A
HREF="&lt;%=MM_moveFirst%&gt;"&gt;First&lt;/A&gt;
| &lt;A HREF="&lt;%=MM_moveNext%&gt;"&gt;Next&lt;/A&gt;
| &lt;A HREF="&lt;%=MM_movePrev%&gt;"&gt;Previous&lt;/A&gt;
| &lt;A HREF="&lt;%=MM_moveLast%&gt;"&gt;Last&lt;/A&gt;&lt;br&gt;
&lt;/font&gt;&lt;font face="Tahoma" color="#333333"&gt;&lt;/font&gt;&lt;/font&gt;&lt;/font&gt;
&lt;form name="Form1" action="/modify.asp" method="POST"&gt;
&lt;font face="Tahoma"&gt;&lt;font face="Tahoma"&gt;&lt;font face="Tahoma"
color="#333333"&gt;&lt;font size="2"&gt;&lt;A HREF="/modify.asp?&lt;%= MM_keepURL &
MM_joinChar(MM_keepURL) & "CustID=" &
rsCust.Fields.Item("CustID".Value %&gt;"&gt;Modify
Record&lt;/A&gt;&lt;/font&gt; &lt;font size="2"&gt;| &lt;A HREF="/insert.asp?&lt;%=
MM_keepURL & MM_joinChar(MM_keepURL) & "CustID=" &
rsCust.Fields.Item("CustID".Value %&gt;"&gt;Insert
New Record&lt;/A&gt; | &lt;A HREF="/delete.asp?&lt;%= MM_keepURL &
MM_joinChar(MM_keepURL) & "CustID=" &
rsCust.Fields.Item("CustID".Value %&gt;"&gt;Delete
Current Record&lt;/A&gt; |&lt;/font&gt; &lt;font size="2"&gt;Schedule Call | Invoice |
&lt;/font&gt;&lt;/font&gt;&lt;/font&gt;&lt;/font&gt;
&lt;br&gt;
&lt;br&gt;
&lt;table border="0" width="797"&gt;
&lt;tr&gt;
&lt;td align="right" width="113"&gt;
&lt;div align="left"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;Customer
#&lt;/font&gt;&lt;/div&gt;
&lt;/td&gt;
&lt;td colspan="2"&gt;&lt;font size="2.5" color="#000000"
face="Tahoma"&gt;&lt;%=(rsCust.Fields.Item("CustID".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="99"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;Address&lt;/font&gt;&lt;/td&gt;
&lt;td width="356"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;&lt;%=(rsCust.Fields.Item("Address".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td align="right" width="113"&gt;
&lt;div align="left"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;Customer's
Name&lt;/font&gt;&lt;/div&gt;
&lt;/td&gt;
&lt;td colspan="2"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;&lt;%=(rsCust.Fields.Item("CompanyName".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="99"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;Address line
2&lt;/font&gt;&lt;/td&gt;
&lt;td width="356"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;&lt;%=(rsCust.Fields.Item("Address2".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td align="right" width="113"&gt;
&lt;div align="left"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;Contact&lt;/font&gt;&lt;/div&gt;
&lt;/td&gt;
&lt;td colspan="2"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;&lt;%=(rsCust.Fields.Item("ContactFirstName".Value)%&gt;&lt;%=(rsCust.Fields.Item("ContactLastName".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="99"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;Address line
3&lt;/font&gt;&lt;/td&gt;
&lt;td width="356"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;&lt;%=(rsCust.Fields.Item("Address3".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td align="right" width="113"&gt;
&lt;div align="left"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;Alternate
Contact&lt;/font&gt;&lt;/div&gt;
&lt;/td&gt;
&lt;td colspan="2"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="99"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;City&lt;/font&gt;&lt;/td&gt;
&lt;td width="356"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;&lt;%=(rsCust.Fields.Item("City".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td align="right" width="113"&gt;
&lt;div align="left"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;Phone
#&lt;/font&gt;&lt;/div&gt;
&lt;/td&gt;
&lt;td colspan="2"&gt; &lt;font face="Tahoma" color="#000000"
size="2.5"&gt;&lt;%=(rsCust.Fields.Item("PhoneNumber".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="99"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;State&lt;/font&gt;&lt;/td&gt;
&lt;td width="356"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;&lt;%=(rsCust.Fields.Item("State".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td align="right" width="113"&gt;
&lt;div align="left"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;Alternate
Phone #&lt;/font&gt;&lt;/div&gt;
&lt;/td&gt;
&lt;td colspan="2"&gt; &lt;font face="Tahoma" color="#000000"
size="2.5"&gt;&lt;%=(rsCust.Fields.Item("AltPhone".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="99"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;Zip Code&lt;/font&gt;&lt;/td&gt;
&lt;td width="356"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;&lt;%=(rsCust.Fields.Item("ZipCode".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td align="right" width="113"&gt;
&lt;div align="left"&gt;&lt;font face="Tahoma" color="#000000"
size="2.5"&gt;Fax #&lt;/font&gt;&lt;/div&gt;
&lt;/td&gt;
&lt;td colspan="2"&gt; &lt;font face="Tahoma" color="#000000"
size="2.5"&gt;&lt;%=(rsCust.Fields.Item("FaxNumber".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="99"&gt;&lt;font color="#000000" face="Tahoma"
size="2.5"&gt;eMail&lt;/font&gt;&lt;/td&gt;
&lt;td width="356"&gt;&lt;font color="#000000" size="2.5"
face="Tahoma"&gt;&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;font face="Tahoma" color="#333333"&gt;&lt;br&gt;
&lt;/font&gt; &lt;font face="Tahoma" size="2"&gt;&lt;font color="#333333"&gt;| &lt;font
color="#0000CC" onClick="GP_AdvOpenWindow('/ins_note.asp?&lt;%=
MM_keepURL & MM_joinChar(MM_keepURL) & "CustID=" &
rsCust.Fields.Item("CustID".Value
%&gt;','InsertNote','fullscreen=no,toolbar=no,location=no,status=no,menubar=yes,scrollbars=no,resizable=yes',400,300,'center','','',0,'');return
document.MM_returnValue"&gt;Insert
Note&lt;/font&gt; |&lt;/font&gt; Activities | Quote | Invoice |&lt;/font&gt;&lt;font
face="Tahoma" size="2"&gt;&lt;/font&gt;&lt;font face="Tahoma" size="2"&gt;&lt;/font&gt;
&lt;/form&gt;
&lt;font face="Tahoma" size="4"&gt; &lt;font size="2"&gt;Account
History&lt;/font&gt;&lt;/font&gt;&lt;font face="Tahoma" size="2"&gt;
&lt;/font&gt;&lt;font face="Tahoma" size="2"&gt;&lt;br&gt;
&lt;/font&gt;
&lt;table width="73%" border="0"&gt;
&lt;tr bgcolor="#66CCFF"&gt;
&lt;td width="10%"&gt;&lt;i&gt;&lt;font face="Tahoma"
size="2.5"&gt;Date&lt;/font&gt;&lt;/i&gt;&lt;/td&gt;
&lt;td width="10%"&gt;&lt;i&gt;&lt;font face="Tahoma"
size="2.5"&gt;Time&lt;/font&gt;&lt;/i&gt;&lt;/td&gt;
&lt;td width="52%"&gt;&lt;i&gt;&lt;font face="Tahoma"
size="2.5"&gt;Notes&lt;/font&gt;&lt;/i&gt;&lt;/td&gt;
&lt;td width="14%"&gt;&lt;i&gt;&lt;font face="Tahoma"
size="2.5"&gt;Salesman&lt;/font&gt;&lt;/i&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;%
While ((Repeat1__numRows &lt;&gt; 0) AND (NOT rsCust.EOF))
%&gt;
&lt;%If (Repeat1__numRows Mod 2) Then%&gt;
&lt;tr bgcolor="#CCCCCC"&gt;
&lt;%Else%&gt;
&lt;tr bgcolor="#FFFFFF"&gt;
&lt;%End If%&gt;
&lt;td width="10%"&gt;&lt;font face="Tahoma" size="2"
color="#000000"&gt;&lt;%=(rsCust.Fields.Item("Date".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="10%"&gt;&lt;font face="Tahoma" size="2"
color="#000000"&gt;&lt;%=(rsCust.Fields.Item("Time".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="52%"&gt;&lt;font face="Tahoma" size="2"
color="#000000"&gt;&lt;%=(rsCust.Fields.Item("Notes".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="14%"&gt;&lt;font face="Tahoma" size="2"
color="#000000"&gt;&lt;%=(rsCust.Fields.Item("Employee".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCust.MoveNext()
Wend
%&gt;
&lt;/table&gt;
&lt;/body&gt;
&lt;/html&gt;
&lt;%
rsCust.Close()
%&gt;
</font id='Tahoma'>

Replies

Replied 10 Mar 2002 11:05:04
10 Mar 2002 11:05:04 Owen Eastwick replied:
The problem is definately with the SQL statement, there is no WHERE clause to relate what you are selecting to the current customer.

I would expect something like:

rsCust.Source = "SELECT Customers.CustID, Customers.CompanyName,
Customers.ContactFirstName, Customers.ContactLastName,
Customers.Address, Customers.Address2, Customers.Address3,
Customers.City, Customers.State, Customers.ZipCode,
Customers.PhoneNumber, Customers.FaxNumber, Customers.AltPhone,
Notes.TaskID, Notes.nCustID, Notes.Employee, Notes.Time, Notes.Date,
Notes.Notes FROM Customers LEFT OUTER JOIN Notes ON (Notes.nCustID =
Customers.CustID) WHERE Customers.CustID = " & varCustID

Where varCustID is determined when the user logs in, perhaps you could retreive this and hold it in a session variable, in which case you could do something like:

...WHERE Customers.CustID = " & Session("CustID"

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Reply to this topic