DAP DAP DAP...Who is the expert here?

[vrk1]

I have a Data Access Page called "Page1" where the user enters the
"StartDate" and clicks on the Submit button.

I have a Query called "qry1" with the following syntax:
select * from tbl1 where date >#startdate#;

The submit button on "Page1" should launch "Page2" with the result of
"qry1". Ideally, I would like the record source of "Page2" to be "qry1".

I am using Access 2000 and I have gone through the msdn article
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_PassParam.asp)
for Access 2002 but I am unable to make sense out of this. Its not working
for me somehow.

Can someone please help me? If you can mock up a small db and send it to
me thats a huuuuge help for me, as I can further modify this to my
requirement.

 

[Ken Snell [MVP]]

Post the script that you've put in Page1 and let's see what you're doing.

 

[vrk1]

Ok here is what I have:

On Page1 I have a Text box (txtstartdate) and a Submit button.

The Onclick event for the submit button code is as follows:

<SCRIPT language=vbscript event=onclick for=btnsubmit>
<!--
document.cookie = txtstartdate
window.navigate("page2.htm")
-->
</SCRIPT>


On Page2, I have two fields - "startdate" and "items"

I have a table "tbl1" that has two fields - "startdate" and "items" where
items is a Number field.

What can I do to read the start date value from page1 and transfer that
variable into the SQL Query "select * from tbl1 where
startdate=#txtstartdate#;"

Would appreciate any help that you can provide.

P.S. This is on Access 2000.

 

[Ken Snell [MVP]]

Your Page2 DAP needs script to read and decode the cookie. The following
script is taken from the MSDN article whose hyperlink was in your original
post:

SCRIPT language=VBS>
OPTION EXPLICIT
Dim NOT_FOUND
NOT_FOUND = "NOT_FOUND"

Sub SetVariable(strVariableName, varVariableValue)
Document.Cookie = strVariableName & "=" & varVariableValue
End Sub

Sub KillVariable(strVariableName)
SetVariable strVariableName, "NULL;expires=Monday, 01-Jan-95 _
12:00:00 GMT"
End Sub

Function ReadVariable(strVariableName)
' These five variables are used in the string manipulation
' code that finds the variable in the cookie.
Dim intLocation
Dim intNameLength
Dim intValueLength
Dim intNextSemicolon
Dim strTemp

' Calculate the length and location of the variable name.
intNameLength = Len(strVariableName)
intLocation = Instr(Document.Cookie, strVariableName)

' Check for existence of the variable name.
If intLocation = 0 Then
' Variable not found, so it can't be read.
ReadVariable = NOT_FOUND
Else
' Get a smaller substring to work with.
strTemp = Right(Document.Cookie, Len(Document.Cookie) _
- intLocation + 1)

' Check to make sure we found the full string, not just a
' substring.
If Mid(strTemp, intNameLength + 1, 1) <> "=" Then
' Oops, only found substring, not good enough.
ReadVariable = NOT_FOUND

' Note that this will incorrectly give a "not found" result if
' and only if a search for a variable whose name is a substring
' of a preceding variable is undertaken. For example, this will
' fail:
' Search for: MyVar
' Cookie contains: MyVariable=2;MyVar=1

Else
' Found full string.
intNextSemicolon = Instr(strTemp, ";")

' If not found, get the last element of the cookie.
If intNextSemicolon = 0 Then intNextSemicolon = Len(strTemp) _
+ 1
' Check for empty variable (Var1=
If intNextSemicolon = (intNameLength + 2) Then
' Variable is empty.
ReadVariable = ""
Else
' Calculate value normally.
intValueLength = intNextSemicolon - intNameLength - 2
ReadVariable = Mid(strTemp, intNameLength + 2, _
intValueLength)
End If
End If
End If
End Function


Then, in Page2 DAP, in the MSODSC.BeforeInitialBind, you need to call the
script to get the cookie and then to set the filter for the DAP's recordset.
This is some generic script to do that:

<SCRIPT language=vbscript event=beforeinitialbind(dscei) for=MSODSC>
<!--

Dim NumMons
Dim errNot_Found
Dim strFilter

Const strParam = "ExpDateValue"

errNot_Found = "NOT_FOUND"
NumMons = ReadVariable(strParam)

strFilter="ExpirationDate Between Date() And
DateSerial(Year(Date()),Month(Date())+" & CLng(NumMons) & "+1,0)"

msodsc.RecordsetDefs("qry_ExpiringLeasesResults").ServerFilter=strfilter

' Delete the cookie.
Document.cookie = ""
-->
</SCRIPT>
--

Ken Snell
<MS ACCESS MVP>

 

[vrk1]

Thanks for your detailed response Ken. I followed your instructions to the
letter "T" but I am unable to get this to work. I re-read your post thrice
and verified the copied code to make sure I wasnt missing anything.

After I copied the Script code on Page2 DAP using the Script Editor and open
the Page2 DAP, I get a Run Time Error (Error: Invalid Character) that points
to the line "Sub SetVariable(strVariableName, varVariableValue)"

I dont know why I am getting this Run Time Error. Do you have any idea?

Thanks for working with me on this.

 

[Ken Snell [MVP]]

My apology... I think the copy/paste action was not quite right. Let's try
this for the cookie subroutines:
------

<SCRIPT language=VBScript>


Sub SetVariable(strVariableName, varVariableValue)
Document.Cookie = strVariableName & "=" & varVariableValue
End Sub

Sub KillVariable(strVariableName)
SetVariable strVariableName, "NULL;expires=Monday, 01-Jan-95 12:00:00
GMT"
End Sub

Function ReadVariable(strVariableName)
' These five variables are used in the string manipulation
' code that finds the variable in the cookie.
Dim intLocation
Dim intNameLength
Dim intValueLength
Dim intNextSemicolon
Dim strTemp
Dim NOT_FOUND
NOT_FOUND = "NOT_FOUND"

' Calculate the length and location of the variable name.
intNameLength = Len(strVariableName)
intLocation = Instr(Document.Cookie, strVariableName)

' Check for existence of the variable name.
If intLocation = 0 Then
' Variable not found, so it can't be read.
ReadVariable = NOT_FOUND
Else
' Get a smaller substring to work with.
strTemp = Right(Document.Cookie, Len(Document.Cookie) - intLocation + 1)

' Check to make sure we found the full string, not just a
' substring.
If Mid(strTemp, intNameLength + 1, 1) <> "=" Then
' Oops, only found substring, not good enough.
ReadVariable = NOT_FOUND

' Note that this will incorrectly give a "not found" result if
' and only if a search for a variable whose name is a substring
' of a preceding variable is undertaken. For example, this will
' fail:
' Search for: MyVar
' Cookie contains: MyVariable=2;MyVar=1

Else
' Found full string.
intNextSemicolon = Instr(strTemp, ";")

' If not found, get the last element of the cookie.
If intNextSemicolon = 0 Then intNextSemicolon = Len(strTemp) + 1
' Check for empty variable (Var1=
If intNextSemicolon = (intNameLength + 2) Then
' Variable is empty.
ReadVariable = ""
Else
' Calculate value normally.
intValueLength = intNextSemicolon - intNameLength - 2
ReadVariable = Mid(strTemp, intNameLength + 2, intValueLength)
End If
End If
End If
End Function
</SCRIPT>

--

Ken Snell
<MS ACCESS MVP>

 

[vrk1]

Ken - This is the code I have in Page 2. When I launch Page2 inside Access,
I still get the Run-Time Error : Invalid Character pointing to the line "Sub
Setvariable(strvariablename, varvariablevalue)"

and another Run-time Error: Unterminated String Constant pointing to the
line "NumMons = ReadVariable(strParam)"

Any more suggestions please?

_______________________________________________________________

Here is my code on Page2 DAP:

<SCRIPT language=VBScript>

Sub SetVariable(strVariableName, varVariableValue)
Document.Cookie = strVariableName & "=" & varVariableValue
End Sub

Sub KillVariable(strVariableName)
SetVariable strVariableName, "NULL;expires=Monday, 01-Jan-95 _
12:00:00 GMT"
End Sub

Function ReadVariable(strVariableName)
' These five variables are used in the string manipulation
' code that finds the variable in the cookie.
Dim intLocation
Dim intNameLength
Dim intValueLength
Dim intNextSemicolon
Dim strTemp
Dim NOT_FOUND
NOT_FOUND = "NOT_FOUND"

' Calculate the length and location of the variable name.
intNameLength = Len(strVariableName)
intLocation = Instr(Document.Cookie, strVariableName)

' Check for existence of the variable name.
If intLocation = 0 Then
' Variable not found, so it can't be read.
ReadVariable = NOT_FOUND
Else
' Get a smaller substring to work with.
strTemp = Right(Document.Cookie, Len(Document.Cookie) _
- intLocation + 1)

' Check to make sure we found the full string, not just a
' substring.
If Mid(strTemp, intNameLength + 1, 1) <> "=" Then
' Oops, only found substring, not good enough.
ReadVariable = NOT_FOUND

' Note that this will incorrectly give a "not found" result if
' and only if a search for a variable whose name is a substring
' of a preceding variable is undertaken. For example, this will
' fail:
' Search for: MyVar
' Cookie contains: MyVariable=2;MyVar=1

Else
' Found full string.
intNextSemicolon = Instr(strTemp, ";")

' If not found, get the last element of the cookie.
If intNextSemicolon = 0 Then intNextSemicolon = Len(strTemp) _
+ 1
' Check for empty variable (Var1=
If intNextSemicolon = (intNameLength + 2) Then
' Variable is empty.
ReadVariable = ""
Else
' Calculate value normally.
intValueLength = intNextSemicolon - intNameLength - 2
ReadVariable = Mid(strTemp, intNameLength + 2, _
intValueLength)
End If
End If
End If
End Function

</SCRIPT>

<SCRIPT language=vbscript event=BeforeInitialbind(dscei) for=MSODSC>
<!--

Dim NumMons
Dim errNot_Found
Dim strFilter

Const strParam = "ExpDateValue"

errNot_Found = "NOT_FOUND"
NumMons = ReadVariable(strParam)
strFilter="ExpirationDate Between Date() And
DateSerial(Year(Date()),Month(Date())+" & CLng(NumMons) & "+1,0)"

msodsc.RecordsetDefs("qry1").ServerFilter=strfilter

' Delete the cookie.
Document.cookie = ""

-->
</SCRIPT>

 

[Ken Snell [MVP]]

Where are you putting the scripts for the cookie stuff in Page2's module? It
must be in the "body" section. It can be a bit tricky in the script editor.

 

[vrk1]

The scripts were originally outside the "body" section but inside the "head"
section. I moved them into the "body" section. I get the same error message.

I am not able to understand what I am doing wrong here. To my knowledge,
there are very few books / online resources available on DAP that gives
step-by-step instructions....

Any other suggestions please? If sending me a mock-up db would be easier,
can you email one to (e-mail address removed) please?

Thanks for your help so far!

Regards,
Ravi

 

[Ken Snell [MVP]]

I will send you an example (text file) of the script that I use in a DAP for
reading a cookie. It will include all the HTML stuff too so that you can see
where the script is to go and how it looks.

You were correct to have the script in the Head section... my memory was
faulty when I said Body section. My brain has been working on too many
things lately < g >.

--

Ken Snell
<MS ACCESS MVP>

 

[vrk1]

Thanks for sending me the Script Ken. I think I am just so unlucky! I tried
editing information off of your text file and putting that into my
Page2.htm....somehow I get the same error mesage.

Can you recommend me any good books on DAP or any good Online resource for
me to learn this? (other than the one on msdn and the link that we discussed
about...)

Thanks for all your kind help so far.

Regards,
Ravi

 

[Ken Snell [MVP]]

John Viescas has some information in his ACCESS 2003 Inside Out book on
DAPs.... there unfortunately are not any books (to my knowledge) on DAPs
themselves.... what I've learned about them has been completely self-taught
through MSDN, Help file, and (mostly) trial-and-error..... lots of trial and
error.

You could use Google to do some searching of newsgroup archives...Microsoft
has a newsgroup called microsoft.public.access.dataaccess.pages where you
might find some information as well.

The error messages that you're getting suggest that somehow you've picked up
some "invisible" characters that are causing problems. The code that I
emailed to you is taken directly from one of my DAPs that use cookies
successfully.


--

Ken Snell
<MS ACCESS MVP>

 

[vrk1]

Thank you very much. I will continue to work more on this and see where
this goes.

You have a good day now!

Regards,
Ravi