New Record On Top 2

[EllieFant]

I have a subform that is set in continous form mode. I was wondering if there was a way to make it so that a new/blank record would be at the top of the subform and the other records listed below it.

I was considering making two subforms...one that would be read only - no editing, no entering and another subform that would be add mode only. Then I could put the add mode only subform on top of the other form...just wasn't sure if there was an easier way or not.

Thanks in advance for your help,


Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[scriverb]

I suppose you could add an additional field to your table called SortOrder and make it numeric long integer. Give it a default value of 1.

Create a query called qrySFRecs and copy and paste this SQL:
SELECT A.*
FROM tblYourTableName as A
ORDER BY A.SortOrder DESC , A.Field1, A.Field2;

Create a query called qryUpdSortToZero and copy and paste in this SQL:
UPDATE tblYourTableName as A SET A.SortOrder = 0
WHERE (((A.SortOrder)=1));

Change the red code above to reflect your table name and field names that you want as you sort order.

Create a subform form with properties set as follows:
Default View - Continuous Forms
Scroll Bars - Vertical Only
Record Selector - Yes
Navigation Buttons - No
Control Box, Min Max buttons, Close Box - No

Place your controls on the subform as a single row horizontally as needed. Make sure that you place a control here for SortOrder. Set its visible property to No and squeeze it down horizontally and place it behind another control as it is not needed to be seen.

Put the following code in the indicated event procedures:
BEFORE UPDATE
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdSortToZero"
DoCmd.SetWarnings True
AFTER UPDATE
Me.Requery

I think I have everything included here. What happens is that all records except the last one added has a 0 in the SortOrder field. The records in the subform are sorted by SortOrder descending which puts the one record with a 1 in the SortOrder field at the top and all the rest get sorted by whatever you indicate as the rest of the sort in the query. When a new record is added the BeforeUpdate of the form executes and a query runs that clears the old NEW record by setting the SortOrder field to 0. The AfterUpdate event procedure requeries the subform which now puts the NEW NEW record at the top.

It works here in my test database so if you have problems just get back to me and I will help you setting it up.




Bob Scriver

 

[EllieFant]

Bob,

I will try this on Monday when I go back to work!

Thank you for your help, I will let you know how it goes.



Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[scriverb]

Ellie, I think we can also backshade the record for you. It would be the only one backshaded and at the top. I will work that up and if you need it will post at a later time.

Thanks for the star.

Bob Scriver

 

[EllieFant]

Bob,

I got this to work as you described it would. The error is in how I described what I was looking to be able to do.

I want the blank record to be on top - where they would enter the newest information on the suggestion.

I have a bunch of very novice people that will be using this database and I don't want them to change information they have already entering not realizing that will erase the information. Maybe if I could protect the fields that were already filled in that would work...maybe a statement like not isnull then lock it - otherwise let them use it. I am trying to get out of the Safety Committee by making this database as easy to use as possible - it is just turing out to be harder to make - but will be well worth it!

Thank you for helping me out though I really appreciate it. Why is this backshade that you spoke about in your last message? I am always eager to learn something new!

Thanks again!

Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[scriverb]

The locking proposition sounds as a viable possibility but all other records showing in your continuous form will have the same fields locked and unlocked. This kind of form is like that.

In a continuous form the properties of each column change for every record in the recordset. Because of this if you backshaded all of the fields on a particular record with code all records would then show this backshading. There is a technique that kind of fools ACCESS by making the field controls transparent all of the time which allows a large(wide) control in the back to show through in a color or white. The color is controled by whether the record is selected or not. When selected a control takes on a value of True and the shading control takes on the color designated. So, voila a colored backshaded row and ACCESS doesn't even know it is smart and cool to be able to do this.

If you want to learn to do this I can direct you to a link as well as post some fairly simple instructions to make this work in your database. Just let me know.

Thank you for star as it is appreciated.

Bob Scriver

 

[EllieFant]

Bob,

I would love to learn more about backshading. Since my job deals with making databases of various types, any and all help would be appreciated and as I said, I am eager to learn new things....please post the link and the directions. Anything is worth trying out

Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[scriverb]

Here is the link to the technique of backshading a row of a continuous form. There are a number of variations but that just requires your creativity.

http://www.mvps.org/access/forms/frm0047.htm

I will post the necessary changes to your situation in my next post.

Bob Scriver

 

[EllieFant]

Thank you for your help. Since our IT department doesn't allow us to have Winzip (or any other program) I am hoping that the database supplied on the webpage will not be needed to do this. If it is I will have to download it from home, unzip it and send it back to myself at work.


Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[scriverb]

Ellie, send me an email(see my profile) and I will send you a self-extracting file so you can get the files at your office.

Bob Scriver

 

[scriverb]

Here are the updates/changes to the previous posting if you want to backshade the top row or the last row added.

First of all change the field type of the new field SortOrder from numeric to Yes/No(Boolean). Throughout the postings the field SortOrder had a default value of 1 and was changed to zero(0). Change the Default value to -1(True) in the tables Field Description. Also, change the reference to this field in the query from 1 to -1. This can be found in the Where statement.

In the subform select all of the field controls and change their Transparent property to yes. Change the SortOrder control Visible property to No. Change the horizontal size to 0 so that it is just a thin vertical line. Move this behind another control and out of the way. You will not see this control. Create another control the full width of the subform. This control will sit behind all of the other controls. The Control Source should be SortOrder. The Font Name should be change to Terminal. Change the forecolor to whatever color you want to be displayed when the row is displayed. The Format property should be changed to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ" Include the double quotation marks. This is where the the color comes from. The second parameter which after the first semi-colon will display if this controls value is true. These Terminal Font characters show up as rectangles of color.

Well, that should just about do it for you. The record that is recently added will have a value of -1 which will sort to the top, and will be backshaded with color as the Format property of solid colored rectangles will display and be seen through the transparent controls on the subform. All other rows will have a value of zero(0) in the SortOrder field so they will have not display anything but the white color.

Good luck.

Bob Scriver