Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

changing FP Access reports

Posted on 2004-10-22
22
Medium Priority
?
232 Views
Last Modified: 2013-12-24
I use fp2003 forms to create a database and then ASP report pages from the database results wizard.  

When i want to make any changes in the report I have to rebuild the ASP report form from scratch.  Is there any way to change the elements of the database results wizard without losing the alread formatted asp report?

0
Comment
Question by:flwebster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 7
  • 2
22 Comments
 
LVL 14

Expert Comment

by:hhammash
ID: 12384845
Hi,

Yes only if you add or remove things to the DRW.

So,  my recommendation is to make sure that this is what you really want before going ahead in making a fancy design.

hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 12384867
Hi again,

When you create the DRW and finish, it generates ASP code.  So, if you edit the DRW and add fields it will prompt you to save,  and when you accept it will disregards previous setup and it will go back to the raw appearance.

When I create a DRW I make sure that this is what I need,  I change the setup, the design,  the colors ...etc.

I know that it is annoying when you put lot of effort in chaning the design then the DRW disregards that after editing.

hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 12384897
If you change something in the SQL you won't be prompted for the change,  it is only changing the fields requested from the database.  Which is a logical thing.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 14

Expert Comment

by:hhammash
ID: 12385089
BUT,

If you did a very hard work on the report,  time consuming work and you want to change the DRW by adding or removing a field you can do that without affecting the design.  It is a bit difficult.

How can you do it:
Let's say you want to remove a field from the DRW.

1- Open the DRW in design mode
2- Highlty the heading of the fied a title for the field
3- Switch to HTML view
4- You will see the place of that word highlight in the HTML Code. Let's say you want to remove a filed called "location". The title of the column will look like this

<td><b>Location</b></td>

You will see this before the DRW code:

  <td><b>Name</b></td>
      <td><b>Salary</b></td>
      <td><b>Location</b></td>

   </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart"  ...............etc
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results .....etc
</SCRIPT>
<% end if %>

Remove that line:
<td><b>Location</b></td>

Now,
Go to the place in the DRW at the bottom where you find field names after the variables.  You will see as many lines as the number of your fields.  The code will be in grey and the field names will be in red.


The lines I am talking about will start immediately after these lines:
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="1554" --><tr>

The line will look like this:

      <td bgcolor="#006596">
      <!--webbot bot="DatabaseResultColumn" s-columnnames="Name,Salary,Location" s-column="Location" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;BC&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Location")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="62237" --></td>

Select the line of the "location" field and remove it.  Then save the DRW.   You will see that the DRW changed and the field is no longer there, BUT,  the format you made did not change.

Now,
Adding a field which is not in the DRW.  Suppose I want to add a field called "City":

1- I go in HTML view to the table heading,  I must have 3 lines because I selected 3 fields in the first place.  Here are the lines above the DRW code:

  <td><b>Name</b></td>
  <td><b>Salary</b></td>
  <td><b>Location</b></td>

What I do is copy the last one
      <td><b>Location</b></td>
Change it to:
      <td><b>City</b></td>
Now I will have 4 lines
  <td><b>Name</b></td>
  <td><b>Salary</b></td>
  <td><b>Location</b></td>
  <td><b>City</b></td>

Go to normal view and you will see a new column added with the heading City.

Now,
Go down to those lines under the variables which I mentioned above.
Copy the last one which will look like:

<td bgcolor="#006596">
      <!--webbot bot="DatabaseResultColumn" s-columnnames="Name,Salary,Location" s-column="Location" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;BC&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Location")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="62237" --></td>

Then paste is immediately after the last one taking into consideration the <td> and the </td> to be at the beginning and at the end.  Now change the occurance of location into City.  The occurance that you need to change is in two places:
s-column="Location"  change it to "City" to look: s-column="City"
and the piece of the line which is in red:

<%=FP_FieldVal(fp_rs,"Location")%>

Change it to
<%=FP_FieldVal(fp_rs,"City")%>

Save the DRW.

The field will be added without affecting your format.

hhammash


0
 
LVL 14

Expert Comment

by:hhammash
ID: 12385172
If your DRW is a "List - One Field Per item"  it is much more easier

Just in normal view highlight the line,  switch to HTM view,  Delete the section you selecter with the
<tr> before it
and
</tr> after it

To add
Highlight the last one
Switch to HTML view
Cop the selected section with the <tr> that is before it and the </tr> after it.  Then past under the last one or in the place you want it to appear.
Change the field name in the two locations I gave you above.

hhammash
0
 

Author Comment

by:flwebster
ID: 12385918
Hi,
My particular problem isn't in the formatting of the report at the moment but rather in the sort order.. I have the report sorted on one field and want to sort on another.. is there a way to edit the code directly without losing the formatting of the report?  

Thanks
0
 
LVL 12

Expert Comment

by:rcmb
ID: 12386617
To edit the code directly switch to html view and locate the red code in the DBRW region. At the top of the red code is a line of gray code that contains your SQL information. Do not edit the red code as no changes will take. In the gray line scroll your screen to the right until you find your SQL code and make your changes there. Once you save the page you will see the red code update.

You can make whatever changes you desire to the SQL in the gray region and it will not change your formatting.

RCMB
0
 

Author Comment

by:flwebster
ID: 12386986
Great.. yes, I see the gray code and the list of fields and the setup lines.. but I have not the foggiest about how to edit it to establish a sort (timestamp from most recent backward)... I don't see anyplace in the code which identifies the sort and direction or the field on which its based... Can you be more specific as to code change?

Thanks!!!!!
0
 
LVL 12

Assisted Solution

by:rcmb
rcmb earned 600 total points
ID: 12387012
Find the area that has:

SELECT * FROM TableName WHERE fieldname='::fieldname::'

or something to that, and make it read like:

SELECT * FROM TableName WHERE fieldname='::fieldname::' ORDER BY timestamp ASC, lastname ASC, firstname ASC

the words timestamp, lastname, firstname will equal fields in your database table.

You may or may not have the WHERE clause in your SQL statement so if not just ignore that part.

RCMB
0
 
LVL 14

Accepted Solution

by:
hhammash earned 1400 total points
ID: 12387030
Hi flwebster,

You should've said something about SQL my friend,  you could've spared me lot of work.  When you Say "change the elements of the database results wizard without losing the alread formatted asp report?"  means,  physical elements.

Any way,  here is the grey line, it is afer <tbody> and before <%if 0 then%>

  </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart" s-columnnames="CatID,SubCat,Body,Name" s-columntypes="202,202,203,202" s-dataconnection="MemberCategory" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="Categories" s-displaycolumns="CatID,SubCat,Body,Name" s-criteria="[CatID] EQ {CatID} +" s-order s-sql="SELECT * FROM Categories WHERE (CatID =  '::CatID::')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="CatID=" s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
   
Look in the first line where it says sql=Select * from Categories Where(CatID='::CatID::')

This is what you chagne in the code and save the DRW.  When you do that you will see that the red line of the :

fp_sQry="SELECT * FROM Categories WHERE (CatID =  '::CatID::')"

Has changes without changing the format.

Here is the line after modification,  I will add and AND statement and added another field which is Subcat:

s-sql="SELECT * FROM Categories WHERE (CatID =  '::CatID::' AND SubCat =  '::SubCat::')

hhammash
0
 

Author Comment

by:flwebster
ID: 12387235
I made the changes as follows, and now my asp won't execute. Obviously, I've entered the code change incorrectly somehow. I've tried every variation I can think of without fixing it... The field name I'm trying to sort from most recent to oldest is named "timestamp"  


<!--webbot bot="DatabaseRegionStart" s-columnnames="ID,first,lastname,hsgrade,street,town,st,zip,hmphone,email,highschool,hstown,hsst,hszip,yrsvar,hscoahc,coachphone,coachemail,D1,laxhonors,baseball,basketball,track,xcountry,soccer,football,hockey,wrestling,academicprogram,gpa,sat,psat,ACT,c9a,c9al,c9ag,c9b,c9bl,c9bg,c9c,c9cl,c9cg,c9d,c9dl,c9dg,c9e,c9el,c9eg,c9f,c9fl,c9fg,c9g,c9gl,c9gg,c10a,c10al,c10ag,c10b,c10bl,c10bg,c10c,c10cl,c10cg,c10d,c10dl,c10dg,c10e,c10el,c10eg,c10f,c10fl,c10fg,c10g,c10gl,c10gg,c11a,c11al,c11ag,c11b,c11bl,c11bg,c11c,c11cl,c11cg,c11d,c11dl,c11dg,c11e,c11el,c11eg,c11f,c11fl,c11fg,c11g,c11gl,c11gg,c12a,c12al,c12ag,c12b,c12bl,c12bg,c12c,c12cl,c12cg,c12d,c12dl,c12dg,c12e,c12el,c12eg,c12f,c12fl,c12fg,div,T1,T2,Remote_computer_name,User_name,Browser_type,Timestamp" s-columntypes="3,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,203,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,135" s-dataconnection="recruitersdb" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="Results" s-displaycolumns="ID,first,lastname,hsgrade,street,town,st,zip,hmphone,email,highschool,hstown,hsst,hszip,yrsvar,hscoahc,coachphone,coachemail,D1,laxhonors,baseball,basketball,track,xcountry,soccer,football,hockey,wrestling,academicprogram,gpa,sat,psat,ACT,c9a,c9al,c9ag,c9b,c9bl,c9bg,c9c,c9cl,c9cg,c9d,c9dl,c9dg,c9e,c9el,c9eg,c9f,c9fl,c9fg,c9g,c9gl,c9gg,c10a,c10al,c10ag,c10b,c10bl,c10bg,c10c,c10cl,c10cg,c10d,c10dl,c10dg,c10e,c10el,c10eg,c10f,c10fl,c10fg,c10g,c10gl,c10gg,c11a,c11al,c11ag,c11b,c11bl,c11bg,c11c,c11cl,c11cg,c11d,c11dl,c11dg,c11e,c11el,c11eg,c11f,c11fl,c11fg,c11g,c11gl,c11gg,c12a,c12al,c12ag,c12b,c12bl,c12bg,c12c,c12cl,c12cg,c12d,c12dl,c12dg,c12e,c12el,c12eg,c12f,c12fl,c12fg,div,T1,T2,Remote_computer_name,User_name,Browser_type,Timestamp" s-criteria s-order s-sql="SELECT * FROM Results WHERE(timestamp='::timestamp::')ORDER by timestamp ASC)" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="BODY" preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" startspan --><!--#include file="../_fpclass/fpdblib.inc"-->
0
 
LVL 14

Expert Comment

by:hhammash
ID: 12387258
Are you getting an error or just "No records returned"?

If you are sure that the table has information.  Make sure:

1- The database has write permissions
2- The database is verified: Tools > Websettings >Databases  then click on the database and click verify.

let me know please.

hhammash
0
 

Author Comment

by:flwebster
ID: 12387270
no, cannot be returned.. when I loaded my backup copy, identical except for these changes.. it works, so clearly I'm formatting the code incorrectly,
and I'm certain that it's in the quotation marks, etc for the SELECT * FROM results WHERE (timestamp='::timestamp::' ORDER by timestamp DSC"
(or whatever the code is for descending...

by the way, my apologies for stating the problem so poorly.. in fact I have all sorts of prolblems with DRW rewriting... I try to plan carefully but
it almost never fails that someone thinks of some reason why it should be different, and generaly I've just rewritten them but
this one is a huge report and would take forever to rewrite.. so thank you for your patience wtih me, and my sincere apologies for my
poor use of the correct terminology.
0
 
LVL 14

Expert Comment

by:hhammash
ID: 12387276
Hi,

Timestamp is a binary field,  your line should be
Where(Timestamp=Timestamp)
0
 

Author Comment

by:flwebster
ID: 12387283
now works fine when i do ASC for ascend, what is the code for descending order (newest to oldest)?
0
 

Author Comment

by:flwebster
ID: 12387294
Thank you.. i figured out the desc code and it works beautifully. Thank you for your patience and excellent help!!!
0
 
LVL 14

Expert Comment

by:hhammash
ID: 12387341
Desc
0
 
LVL 14

Expert Comment

by:hhammash
ID: 12387347
You are welcom
0
 
LVL 14

Expert Comment

by:hhammash
ID: 12387349
No problem flwebster,  you are welcome, no harm done.  But I hope that you might benefit from the explanation on how to change Physical Formatting lateron.

Regards
hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 12387380
Hi flwebster,

A piece of advice for the future.

Don't rely on timestamp fields that comes with Frontpage.  Create your own stamp.

In Access table create a date field and a time field and make them default =Date() =Now() or =Time().

Whenever a new record is added these two fileds will take the current date and current time.

Timestamp fileds get tricky sometimes and create problems with your SQL without knowing.

Regards
hhammash
0
 

Author Comment

by:flwebster
ID: 12394067
Thank you for the advice. I'll use it for my projects from now on!
0
 
LVL 14

Expert Comment

by:hhammash
ID: 12394216
You are welcome flwebster
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
Suggested Courses

636 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