flwebster
asked on
changing FP Access reports
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?
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?
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
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
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.
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.in
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="1554" --><tr>
The line will look like this:
<td bgcolor="#006596">
<!--webbot bot="DatabaseResultColumn"
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"
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,"Loca
Change it to
<%=FP_FieldVal(fp_rs,"City
Save the DRW.
The field will be added without affecting your format.
hhammash
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.in
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="1554" --><tr>
The line will look like this:
<td bgcolor="#006596">
<!--webbot bot="DatabaseResultColumn"
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"
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,"Loca
Change it to
<%=FP_FieldVal(fp_rs,"City
Save the DRW.
The field will be added without affecting your format.
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
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
ASKER
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
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
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
You can make whatever changes you desire to the SQL in the gray region and it will not change your formatting.
RCMB
ASKER
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!!!!!
Thanks!!!!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,la
<!--webbot bot="DatabaseRegionStart" s-columnnames="ID,first,la
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
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
ASKER
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::'
(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.
and I'm certain that it's in the quotation marks, etc for the SELECT * FROM results WHERE (timestamp='::timestamp::'
(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.
Hi,
Timestamp is a binary field, your line should be
Where(Timestamp=Timestamp)
Timestamp is a binary field, your line should be
Where(Timestamp=Timestamp)
ASKER
now works fine when i do ASC for ascend, what is the code for descending order (newest to oldest)?
ASKER
Thank you.. i figured out the desc code and it works beautifully. Thank you for your patience and excellent help!!!
Desc
You are welcom
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
Regards
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
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
ASKER
Thank you for the advice. I'll use it for my projects from now on!
You are welcome flwebster
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