Changing the order records are displayed in a subform (1 Viewer)

mafhobb · Feb 28, 2006

Hi everyone. I need help on this one

I have a form with a subform. The subform is basically the sale history of the item on the main form (The main form shows a record and then the subform reports its history). This all works fantastic, except that on the subform, every time a new record is entered, it goes to the bottom of the previous one, so after a while, if I want to enter more history into the subform, I have to scroll down through all the older ones to get to the more recent ones.

How can I do it so that in the subform the most recent entry is always at the top and each time a new record is entered, the older ones move down one. This way the most recent records are always on display and older ones move down as they become less important. Likewise, the empty record used to enter data is also at the top!

Thanks!!

Laurentech · Feb 28, 2006

In the properties for the subform, find the Order By and put

Fieldname DESC

where Fieldname is whatever field in the subform's recordsource you want to sort on.
The DESC tells it to sort descending, or newest records first.

Larry

mafhobb · Feb 28, 2006

I have tried that, and that does organize the fileds the way I want, except for the new, empty record, which is still all the way to the bottom of the list. Do you know how to fix that?

Thanks for your help!

neileg · Feb 28, 2006

Your subform should be based on a query. Apply the sort order in the query. In the After Update event of the text box in the subform, set it to reqery the subform. This will resort the data and put the new entry at the top.

MrAustin · Feb 28, 2006

I don't think that is quite what mafhobb is looking for. The OP wants the "new entry" portion of a datasheet view to appear at the top of the list of entered records. To my knowledge this portion is always at the bottom, no matter what. You could have text boxes which the user fills out to enter new records, and just write an append query + VBA code to requery the list.

Pat Hartman · Mar 1, 2006

It is not possible to move the "new record" by changing a property. You can fake it by using two subforms, properly sized. The first subform can be sized to show only a single record and its DataEntry property should be set to Yes.
The second form can be sized to show multiple records and its DataEntry property should be left at the default No but its AllowAdditions property should be set to No.

This gives you the look you want without any coding. You'll have to adjust the subform borders and some other properties to make the whole thing appear seamless.

mafhobb · Mar 2, 2006

So..

would this be the same subform inserted twice as a subform in the main form, just with a different size and shape to hide what I do not want??

mafhobb

Pat Hartman · Mar 2, 2006

No. Two separate subforms because they need different properties.

mafhobb · Mar 10, 2006

Pat, I am trying your suggestion and I got the two different subforms in place. I understand your explanation but I am getting stuck in the details...

The first subform is right above the second one and it looks exactly as I wanted it. However, three things are happening that I do not know how to fix

1- I cannot get the second subform (the one with the project history) to change to Order By DESC.
2- I can enter the data on the first subform and press tab to get out of the fields, which effectively creates the record, but the second subform does not update automatically. I have to get out of the current record to a second one in the main form for the subform to update. Is there any way to make the second subform to update automatically right after I enter the record in the first subform?
3- No matter what properties I set in the second subform, I can still edit and delete data in the records...why?

Hope you can help!

mafhobb

Pat Hartman · Mar 13, 2006

1. Create a query that orders the records in descending order. Use the query as the subform's RecordSource.
2. The record isn't saved until the record pointer moves to a new record or moves out of the subform. In the AfterUpdate event of the first subform, you need to requery the second subform.
Me.secondsubform.Requery
3. On the data tab are the properties - AllowAdditions, AllowEdits, and AllowDeletions - set them to No if that's what you want. You can also turn off the record selectors to get a cleaner look for the subforms.

CrystalSurfer · Mar 13, 2006

Im doing the same here and having problems...

Can the first subform (for new records) be linked to the main form? (via Link Child/Master Fields)
Or do those properties need to be blank?

thanks..

Pat Hartman · Mar 13, 2006

Both subforms should be linked to the main form with the master/child links.

CrystalSurfer · Mar 14, 2006

Im so very close now to having this done, except my first subform (for new record entry) has ALL the existing child records in it.
Properties set for this subform are:

Record Source: tblActivity
Filter: -
Order By: DateComplete DESC
Allow Filters: No
Allow Edits: No
Allow Deletions: No
Allow Additions: No
Data Entry: Yes
Recordset Type: Dynaset
Record Locks: No Locks
Fetch Defaults: Yes

How do I get this subform to NOT display the child records, leaving only the blank new record entry?
thanks..

BlazingSun · Mar 28, 2006

Access 2003 on XP SP2:

I too have a subform that does not sort records correctly. The records need to be displayed in date order. I tried the Order By: Item DESC for the subform and this does not help. I created a query that finds the data for the subform and sorts by date. This displays the data correctly however new records cannot be added. Even through the subform is not read-only, it acts read-only. If I remove the query from the subform definition, then new data can be added and existing data can be changed.

Why does adding the query make the subform change so it is cannot be changed?

Thank you
BlazingSun

ansentry · Mar 28, 2006

Have a look at the attached sample, I think that it does what you want.

The subform is locked and cannot be altered.

When you add a new record to the input subform and hit tab you will see that the subform requeries and the entry is at the top.

BlazingSun · Mar 31, 2006

Thank you John A. There are a lot of interesting aspects to your example.

My subform basically combines your input subform and the locked subform. Normally my subform shows the records I expect, just not always in date order. I created a query to use as the control for the subform and it shows the subform information in date order but the subform does not allow modifications.

I'm trying to understand why adding a query as the subform control source makes the subform fields so they cannot be modified. All I change is the source control for the subform. I do not change settings for any fields.

Thank you for the example. I'm learning from it...

ansentry · Mar 31, 2006

BlazingSun,

The example I posted has all the text boxes on sfrmVehicles locked by code.
You can remove it if you wish, I will not effect the many in which the form displays.

Open sfrmVehicles in design view. open the properties of the form look at the on current code;

Change this:

Code:

       ctl.Locked = True

To this:

Code:

       ctl.Locked = False

or you could just remove the code.

All I change is the source control for the subform. I do not change settings for any fields.

I can't see how this would change the editing mode of the form.

Post a copy of you mainform subform (source tables and queries) and I or someone will have a look at it.

I wonder what happened to mafhobb, he/she was the original poster, must have lost interest or got it all to work.

Keith Nichols · Apr 25, 2006

How do I get the Requery to work?

Hi Guys,

I have messed about with this and looked at the sample database provided by Ansentry. I did not understand what Ansentry's code was trying to do and so was unable to crib and adapt it to my db.

See attached db: I am trying to reference the second subform on the AfterUpdate event of the first, but the code below returns and error. I also tried to use "Forms!" and got the same result.

Where am I going wrong?

Code:

Private Sub Form_AfterUpdate()

On Error GoTo afterUpdate_Err
       
      Me![fsub_car_info].Requery
   
afterUpdate_Exit:
   Exit Sub

afterUpdate_Err:
    MsgBox Error$
    Resume afterUpdate_Exit
End Sub

Incidentally, is there any way to make the data entry subform look more like a combo box than a subform, i.e. no column headings etc?

Regards,

Changing the order records are displayed in a subform (1 Viewer)

Registered User.

Registered User.

Registered User.

AWF VIP

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Matrix activist

Super Moderator

Matrix activist

BlazingSun

Guest

Access amateur

Attachments

BlazingSun

Guest

Access amateur

Registered User.

Attachments

Similar threads

Users who are viewing this thread