Go to last record in subform based on main form selection. 3

[KenGoonan]

I have a simple access db, one main form with basic client demographic data. The main form has 4 subforms that contain multiple records of different client events. I would like to have the subforms default to the last record so as to make the information on the client selected be the most current. I have tried macros with no luck. Does anyone have a VBA script or code that I could try or any other suggestion? Main form has primary key and is one to many to the tables behind the subforms by Client ID#. Thanks in advance.

 

[GKChesterton]

Is there any chance this is handled well by merely sorting each subform in descending chronological order?

 

[TheAceMan1]

How are ya KenGoonan . . .

In the [blue]On Load[/blue] event of the mainform, try the following:

[blue]   [[purple][B][I]subFormName[/I][/B][/purple]].Form.Recordset.MoveLast[/blue]

Repeat the line for the other forms.

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[KenGoonan]

AceMan1,

Your suggestion worked perfectly on all subforms. Thanks Much.

Ken Goonan

 

[KenGoonan]

AceMan1,

Your suggestion works fine on the first load for the first client. As you cycle through the clients it reverts back to the first record in each subform. Any other ideas?

 

[GKChesterton]

Switch from OnLoad to OnCurrent. Watch out for over-using OnCurrent, as it can be a bit hard to control. But I think this is just the occasion.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[KenGoonan]

Thanks... Works like a charm. I was just thinking the same thing, but I forgot to cut and past the code in the code builder window under OnCurrent Sub, Doh!

Thanks GK,

Ken

ps. I'll have another question with a simple search and Go To Record issue a little later.

 

[TheAceMan1]

[blue] ... Watch out for over-using OnCurrent, [purple]as it can be a bit hard to control[/purple] ...[/blue]

Special Note: The only thing you have to watchout for with the [blue]On Current[/blue] event is [red]recursion![/red] That is ... code in the event that causes the event to retrigger. Like moving to another record within the code. Other than that ... the event can be as hefty as required.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[KenGoonan]

Thank you both for your help. I will post my second problem in a new thread. I'm hoping you can help me with that one too.

 

[GKChesterton]

Thanks AceMan for the insight on recursion. I never understood it; very simple. (Not me. The insight.)

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​