I have frmProductInfo which contains a subform, frmProdContractInfo Subform. The frmProdContract subform automatically loads when frmProductInfo loads. The subform is opening to the first record of the data set, and I need it to open to the latest one. I've tried creating Macros in the On Load and Current events, in both the main form and sub form, but I get errors:

The object 'frmProdContract subform' isn't open.

How can I go to the last record when the main form opens?

This is Access 2010, by the way...

This post has been edited by BillR: May 5 2012, 10:01 AM

" I need it to open to the latest one"
So does this imply that there is a date-time of entry on the record?
If not , how do you work out what the latest one is?

The latest one will be the last record in the data set (e.g. Record 2 of 2).

Access tables do not have a 'position' concept. unless the data is sorted into a sequence.

You should imagine records are in a bucket, not in an organised drawer. Without a sort sequence Access may pick them out of the bucket in whatever sequence it finds them.

So there needs to be a field that provides the required sequence.

Unless, of course, you just want any record from the table.

Hi Bill

To go to the "last" record, try the following in the main form's Open or Load event:

Me.[frmProdContractInfo Subform].SetFocus
DoCmd.GoToRecord , , acLast

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

That did the trick. Thanks a lot!

Hi Bill,

(IMG:style_emoticons/default/yw.gif)

Peter and I are happy to help. Good luck with your project.

Working with the same database, I wanted to take some data from the sub form to flag a notice that the customer call is billable. I was working with this code, but it isn't flagging "Billable" (Billable is a check box that I expect should be checked by VBA if the WarrantyExpiration or ContractExpiration date is earlier than today's date).


This code appears in both the OnLoad and Current Events in the form which is loaded from frmProductInfo.

Any help is appreciated!

Hello

How would you get this to work on a Navigation Form? I tried it and it worked while I was not opening it in the Navigation Form?

Thanks
Larry

Hi Bill,


In the Current event of the subform and in the AfterUpdate events of WarrantyExpiration and ContractExpiration:

If Me.WarrantyExpiration < Date() OR Me.ContractExpiration < Date() Then
Me.Billable = True
Else
Me.Billable = False
End If

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Hi Larry,


You might have to use something like this from the Open or Load event of the Navigation Form:

Me.NameOfYourSubform.SetFocus
Me.NameOfYourSubform.Form!NameOfYourSubSubform.SetFocus
DoCmd.GoToRecord , , acLast

(untested)
Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

This post has been edited by theDBguy: May 5 2012, 09:59 PM

Thanks DB

Couldn't get it to work...will just leave it as it is now...it works anyways...LOL

Larry

Hi Larry,


Did it not work because I used acNext instead of acLast? Sorry about that... I edited my post above to fix it.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Thanks DB

I had already changed it and and the subform names...It would only give me a blank screen...and no errors.... (IMG:style_emoticons/default/iconfused.gif)

I am not going to worry about it right now...can use the navigation buttons to go where I need to...but tanks for your time and trouble.

Larry

Thanks DB. However, the form which I wanted to populate is not the same form which the nested subforms appear, therefore the "Me." line couldn't apply, and the code you so kindly supplied is not working. The database structure is:

frmProductInfo contains nested subforms, frmProdWarranty and frmProdContract. There is control which opens up frmServiceInfo, which contains certain fields passed through from frmProductInfo (model and serial number etc). This is where the Billable checkbox appears (frmServiceInfo, that is). Therefore the checkbox Billable needs to be populated from a nested form, in a different form which has lost its focus.

Hi Bill,

Are you able to post a zip copy of your db with test data? My first thought is that if you can pass info from the other forms to the form you want to update, then maybe you can also pass the conditions you want to check to update the Billable field.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

It's easier said than done. I need to get my exclusive rights back, I have an errant user who didn't close the application and I can't find him! I need to merge the FE and BE again, after which I'll send it off. thanks.

I finally got it, please see attached (read Database Notes first!).

Hi Bill,

You're right, I'm not sure I understand your table relationships. Give this one a try and let us know...

In the Current Event of frmServiceInfo:


Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

you're right, the db's a mess. I learned a lot between when I started with the project and now, mostly through UtterAccess. If I had to do it over, it'd look completely different. Oh well.
the code doesn't seem to work. In any case it was a "nice to have" rather than a "must" in the project. I'll leave it as is. Thanks for your efforts.