Solved

Is the rs.filter property exclusively for ASP use, or can I also using in a standard Data Access Page using VBscript?

Posted on 2004-04-27
18
281 Views
Last Modified: 2010-04-06
Hi folks,

I have an MS Access Data Access Page displaying a report.  I wish to change the criteria of the report query by using the rs.filter property in conjunction with a combo box.  I just need to check with you guys that this particular property will work with VBscript as opposed to ASP (I currently suspect this is exclusively an ASP property).  If anyone can point me in the direction of more info on this rs.filter property, I'd be grateful!!

Cheers,

Luke
0
Comment
Question by:Lukasx
  • 9
  • 7
  • 2
18 Comments
 

Author Comment

by:Lukasx
ID: 10929093
Here is the code I'm trying to apply it to:

<SCRIPT language=VBSCRIPT event=onload>
function DefaultDate()
{
Dim DefDate as variant

DefDate = format(dateadd("m",-4,now()), "YYYY/MM")

rs.filter = Defdate
 
}
</SCRIPT>

If the code is flawed, please mention it and I'll set up another question specifically for a functional alternative.  At this time, I'd be grateful just to know if the rs.filter property can work in a Data Access Page type webpage (VBscript is generally supposed to work with them).
0
 
LVL 1

Expert Comment

by:dcrysler
ID: 10940086
Is rs defined as a recordset?  I have not worked with ASP but I have worked with recordsets through mfc and vc++.  There the filter property can be used to set the WHERE, ORDER BY, and GROUP BY clauses of the sql statement.  So in your example you could try something like:

rs.filter = "report_date = #" + Defdate + "# ORDER BY report_id"

where report_date is the name of the msaccess date field you are trying to filter by.  obviously you don't need the order by clause,  I just through it in for effect.
0
 

Author Comment

by:Lukasx
ID: 10940151
Hi dchrysler!

My biggest limitation here is that I cannot use ASP as it is not supported by our system.

I'm not really sure if 'rs.filter' will work in the Data Access Page I have made.  The only method I have found so far to filter the records is using the GROUP LEVEL FILTER built into Access itself.  This is not a code solution, it is just accomplished by selecting various properties in Access.  It is also horribly limited.

So, I'm just looking for a code-based filter property I can manipulate using VBscript, which Data Access Pages (and our system) do support.

I'll give your code a shot, so far all I get is the raw, unfiltered mass of all the records like the filter isn't even there!

Cheers,

Luke
0
 
LVL 9

Expert Comment

by:mrGreen
ID: 10987245
the rs.filter is a method of ADO, so provided you've created a recordset object you should be able to use it outside of ASP, it's certainly not exclusive to ASP.

is that all of the code you are using above?
0
 
LVL 9

Expert Comment

by:mrGreen
ID: 10987323
you don't have to use filter though, you can just specify some SQL

you also need a connection object which you haven't got above (sorry if you just haven't included it)

DefDate = format(dateadd("m",-4,now()), "YYYY/MM")

set conn = createObject("ADODB.Connection")
set rs = createObject("ADODB.Recordset")

conn.open("your connection string")

rs.ActiveConnection = conn
rs.Source = "SELECT * FROM yourTable WHERE fldDate = #" & DefDate& "#
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()



something like that anyway


cheers

Matt
0
 

Author Comment

by:Lukasx
ID: 11004299
Hi guys!

The code doesn't seem to be working!  I don't think it's even querying the DB.  Is there a way of testing whether our intranet supports ADO?

I'll make a seperate question for this if you guys feel it is digressing too far from the original question.


Cheers,

Luke
0
 
LVL 1

Expert Comment

by:dcrysler
ID: 11005652

1) Create a file called test.vbs and add the following code :

set conn = createObject("ADODB.Connection")
set rs = createObject("ADODB.Recordset")
conn.open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Projects\TestDB.mdb;Persist Security Info=False")
rs.ActiveConnection = conn
rs.Source = "SELECT * FROM TestTable"
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()
msgbox(rs.EOF)
rs.Close()


2) change the path to your access database
3) fix the SQL statement
4) save the file, then double-click on it

If this script works and there are records in the selected table you will see a messagebox with the value "FALSE"  if it can't connect to the database you will see an error messagebase with a description of the error.

If it errors out try the following.   Create a test file called x.udl,  the name and/or contents are not important, only the extension.  Double-clicking on it will open a dialog box that will allow you to create and test a database connection.  Click through the tabs and make your desired settings,  after you can get the connection to succeed click "ok" and the contents of your x.udl file will contain the connection string you need to use in your vbs file.

0
 

Author Comment

by:Lukasx
ID: 11005818
test.vbs seems to work fine!

I got the Msgbox with FALSE on it.

So, does that confirm that I have ADO?

That'll be great, so then at least I know what I'm trying to do is possible! :-)
0
 

Author Comment

by:Lukasx
ID: 11005953
I've checked the code in the Microsoft Script Editor (at the moment, it is located in the BODY section of the code, it is in

<%

%>

tags.  In the Script Editor, the tags are highlighted Yellow.  I believe that means something is wrong with them.  Do you know why this might be?

Cheers,

Luke
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:dcrysler
ID: 11005967

Yes ADO is working and you are connecting to the database,  you can test it further by replacing the msgbox line with the following :

while( rs.EOF = false )
  msgbox("First Field : " & rs.Fields(0))
  rs.MoveNext
wend

This will display the first field in each record returned by the SQL statement.
0
 
LVL 1

Expert Comment

by:dcrysler
ID: 11006046

<% %> are ASP tags.  One of the confusing parts about old ASP is that the html, server-side scripting, and client-side scripting are all mixed together on the same page.  I think that coloring the ASP open/close tags yellow is just a convenience thing so you can pick out the blocks of ASP.

So you are using ASP then?
0
 

Author Comment

by:Lukasx
ID: 11006075
I shouldn't be!!

I thought it was VBscript tags!

Here is the code I have right now at this point in time:

<BODY style="OVERFLOW: auto" vLink=#800080 link=#0000ff>
<%
function DefaultDate(rs)
Global DefDate  '<--------------no AS necessary, all variables are variant in VBScript
DefDate = dateadd("m",-4,now()), "YYYY/MM") '<-------format was not valid VBScript function

set conn = createObject("ADODB.Connection")
set rs = createObject("ADODB.Recordset")
conn.open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=T:\e-Finance\Web Based Reports\MonthlyReports.mdb;Persist Security Info=False")
rs.ActiveConnection = conn
rs.Source = "SELECT * FROM qryEE500 WHERE F11 = DefDate"
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()

end function
%>


By the way, your modified code worked fine too!

I think I just clicked OK to about 500 Msgboxes!  :0)

0
 
LVL 1

Expert Comment

by:dcrysler
ID: 11006361

I'm certainly no expert on ASP so I could be wrong,  but I've never understood your application without ASP.  The example I gave you is a stand-alone vbs script which you can run by double-clicking on it.  Similarly you could create a stand-alone html file which can be excecuted by double-clicking.  The VBScript code then executes locally which is contrary to the normal web design since in your example every computer would need to have a "T" drive mapped and be able to find the Access Database.  Normally database web apps have the querys run on the server.  

Webservers do not process html files so if you are not usng ASP and you are saving your html pages on your website as html files then you are effectively doing the same thing as double-clicking on a stand-alone html file.  The only way to have the webserver process the file is to save the file with an extension other than html and then map that extension to an ISAPI filter.  That's really all that ASP is,  IIS automatically installs the ASP.dll and maps the .asp extension to it.  Cold Fusion has it's own dll and maps the .cfm extension, etc.

0
 

Author Comment

by:Lukasx
ID: 11006582
You are quite right in your assumption!  All our computers have access to a shared T: drive, so this method does lend itself to our 'unusual' setup.

The problem I have now is getting the HTML page to resemble the format I had got just about perfect in Access.  Do you know if the Data Access Page will accept this coded recordset as opposed to the Recordset Access usually provides?

If not, I guess I'll check out some tutorials on how to format the results of an ADO database query!
0
 
LVL 1

Expert Comment

by:dcrysler
ID: 11006843


What do you mean by coded recordset?  The fields returned by the recordset will have no concept of font, color, placement on the screen, etc.  You will need to use HTML and CSS to control the display of the data.
0
 

Author Comment

by:Lukasx
ID: 11007067
Cool!

If (or if not, you've already earned the points!) you can just post a link to a tutorial on how to format a recordset using HTML/CSS, the points are yours with my utmost gratitude!!!  :-)

If it is possible, I'll be very grateful (I tried searching myself, but keep getting ASP code solutions), if not, no probs at all and I'll award you the points for a job well done!
0
 
LVL 1

Accepted Solution

by:
dcrysler earned 500 total points
ID: 11007387


This site has a good online reference for both HTML and CSS:

http://www.w3schools.com/default.asp


Below is a simple page that displays the first two fields in the recordset.  Note that you can also use the Field Names instead of numeric indexes when calling the recordset.Fields list.  I've included some common style properties so you can see how they play together.

The results are displayed in a <table> tag.  There is 1 row <tr> per record and 1 cell <td> per field.  I used CSS to set the font for all <td> tags and created subclasses "a" and "b" for the first and second cells.  This should get you started....


<html><head><title>Test Page</title>
<style type="text/css">
body { background-color : white; }
table { background-color : white; border : 1px; }
td { font-size:8pt; font-family:sans-serif; }
td.a { text-align:center; color:navy; font-weight:bold; }
td.b { text-align:left; color:#ff0000; padding-left : 10px; }
</style></head><body>
<table border="1">
<tr><th width="50px">Field 1</th><th width="100px">Field 2</th></tr>
<script language="vbscript">
      set conn = createObject("ADODB.Connection")
      set rs = createObject("ADODB.Recordset")
      conn.open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Projects\TestDB.mdb;Persist Security Info=False")
      rs.ActiveConnection = conn
      rs.Source = "SELECT * FROM TestTable"
      rs.CursorLocation = 2
      rs.LockType = 3
      rs.Open()
      while( rs.EOF = false )
            document.write("<tr><td class='a'>" & rs.Fields("FieldA") & "</td><td class='b'>" & rs.Fields("FieldB") & "</td></tr>")
            rs.MoveNext()
      wend
      rs.Close()
</script></table></body></html>
0
 

Author Comment

by:Lukasx
ID: 11007437
Great stuff!!

Thanks very much for all your help on this!!!  I'd say you really earned yourself these points!!!

Cheers,

Luke
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now