syncing up forms and sub-forms 1

[uscitizen]

a user brought this 'interesting' behavior to my plate this pm.

there is a form called 'Protocol' which has nested w/in it a sub-form called 'Tracking'. the 'Protocol' (parent) form is linked to the sub-form via a field called 'IRB#'.

the tables of each have the forms' same names and the pk of 'Protocol' is 'IRB#' and the pk of 'Tracking' table is (composite) 'IRB#' AND 'Date'.

in looking at the underlying 'Tracking' table, things are copacetic, however the user told me that when they entered a record in 'Tracking' with a date that was not in the chronological order that had preceded it, that it continued to remain in the same (last row)!

graphically, the form for IRB# ABC1 would look like this after the user entered the final record,

IRB# Date
ABC1 01/02/03
ABC1 01/02/04
ABC1 02/03/04
ABC1 03/29/04
ABC1 03/24/04 <-- why does this not show up above 03/29/04 on the form?


“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[glalsop]

Your subform has an OrderBy property that you can set to make the dates appear in chronological order, Access will not (and shouldn't) automatically store your records in chronological order.

-Gary

 

[uscitizen]

i tried that (i added Date to the 'Order by' field before raising this on the forum) but did not find it would work automatically; the help documentation says something about clicking on the sort in the 'Record' area to get it to work. it would be slicker if the records in the sub-form would get ordered using the sequence in the pk of the underlying table w/o the user needing to intervene, but hey, whatever works.


“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[glalsop]

Try setting the Order By property of the subform to:

[IRB#], [Date]

Like you have already tried. Then, in the OnExit event of the last text box on the detail row of your subform (or wherever you save the record to the table via code) do a requery:

Me.Requery

This should requery (hence, reorder) the record source.



-Gary

 

[uscitizen]

hi,

as it happens, the subform is set up in 'datasheet' view, fwiw. i don't know if that makes any difference to this process --there's no 'On Exit' property that i find to use for that matter. after entering the pair of controls in the 'Order by' field and writing a Me.Requery in the 'After Update' property, i tested this by adding a record with an intervening date (between two other dates in the sub-form) and still don't find that it's sorting 'em.

“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[glalsop]

You dont see text boxes when you look at your subform in design view?

I just re-tested with using datasheet view and it still works.

Good luck

-Gary

 

[uscitizen]

now i see what you mean by 'On Exit'....you're referring to the text control properties and not the form's. i think i tried it and it's still unchanged. can you give a narrative of what you've done in your corner. how are your forms configured. perhaps something will jump out (hopefully).

worse case, i may think about telling them to use the sort button and have a 'Date' in the Order By field.

[one other thing: since the master child fields are linked by 'IRB #' why's it necessary to have both 'IRB #' AND 'Date' in the 'Order by' field of the sub-form's?]

“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[TheAceMan1]

uscitizen . . . . .

I would add a command button and call it [blue]Serialize[/blue] or something, and put the [purple]Requery[/purple] there.

The user may want enter a group of records before hitting the button (allows them to verify data entry before execution).

Besides, [purple]Requery[/purple] always sets the focus back to the 1st record, and if your recordcount is large, jumping top to bottom to enter data can drive you mad!

See Ya! . . . . . .

 

[uscitizen]

aceman!

tried out your sugg'n and want to feedback the results and desc'n of the method i used..

Order by: [Tracking Part].[IRB #],[Tracking Part].[Date]

also turned switched from 'datasheet' to 'continuous form' view so i could add a command button with Me.Requery.

when i am on the sub-form, and click it, the resulting message stated "You can't use the apply filter action on this window"? hmmmmm...

“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[TheAceMan1]

uscitizen . . . . .

[blue]You can't use the apply filter action on this window[/blue]

I cannot readily explain that without some digging (this really should be so simple!). So instead of going of the track, lets stay on target. Do the following (don't forget to backup the DB whenever you make changes!):

1) Remove the Order By clause.

2) In query design view, make a query that returns all the fields you have in your Tracking subform. Set the Sorting in your date field. [blue]This takes care of sorting . . . . period![/blue] Note that sorting within a query is faster (the Microsoft Jet is used directly) than Order By. Also note: basing forms on queries allows for much greater versatility in handling your data. I alway use a query for Record Source, for just that reason. You may want to consider a query for your Protocol form.

3) Set the Record Source of your subform to the Query.

4) The placement of the Button is the next issue. I believe you have it on the Tracking subform. In should not be in the [blue]Details Section[/blue], but rather in the [blue] Form Footer or Header Section[/blue]. If its not, make it so.

5) Take a peek and make sure the only code for the button is [blue]Me.Requery[/blue]

Try it now!

See Ya! . . . . . .

 

[uscitizen]

looks like it might could work. i think i'll give it a test and see if it does or not. i'll get back to you just as soon as i do.

“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[TheAceMan1]

uscitizen . . . . .

By the way . . . . Open your [blue]Tracking Table[/blue], put the cursor in the [blue]Date Field[/blue], and click the [blue]Acending Sort[/blue] toolbar button. Save the table & close.

Now the table is permanently sorted by date . . .

See Ya! . . . . . .

 

[uscitizen]

let's not lose sight of the fact that 'tracking part' has been used as the record source of the 'tracking part' (child) sub-form which is linked bo the 'protocol' record source of the main 'protocol' form via their shared 'IRB #' (pk) values. also that 'Date' is the seondary pk of 'tracking part' table. change any thing?

“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[TheAceMan1]

uscitizen . . . . .

Sorting is strictly an Display Issue and nothing else. It has no effect on returned records.

See Ya! . . . . . .