Solved

changing FP Access reports

Posted on 2004-10-22
228 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
Question by:flwebster
    22 Comments
     
    LVL 14

    Expert Comment

    by:hhammash
    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
    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
    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
     
    LVL 14

    Expert Comment

    by:hhammash
    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
    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
    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
    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
    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
    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:
    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
    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
    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
    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
    Hi,

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

    Author Comment

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

    Author Comment

    by:flwebster
    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
    Desc
    0
     
    LVL 14

    Expert Comment

    by:hhammash
    You are welcom
    0
     
    LVL 14

    Expert Comment

    by:hhammash
    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
    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
    Thank you for the advice. I'll use it for my projects from now on!
    0
     
    LVL 14

    Expert Comment

    by:hhammash
    You are welcome flwebster
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    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…
    Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
    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 set up the permalinks on a WordPress Website. 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 : Go t…

    884 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

    18 Experts available now in Live!

    Get 1:1 Help Now