Help: How to set value of sub-form's recordset programatically

[ZRexRider]

Hi,


Logically I believe this should work and actually, sometimes it does
but there must be something "illegal" about what I'm doing because it
eventually crashes MS-Access (2003 SP1)


In a nutshell - I have a control on a line item of a sub-form that is a
continuous form. If there are 15 line items then there will be 15
controls. If user clicks one of these controls - I popup modal form to
request additional information. After user enters additional info on
this sub-form, I want update a field in the recordset at the row where
the control was clicked.


Seems straightforward and by golly it actually works sometimes (just to
tease me). However very soon I get the ugly "Access is crashing please
let me tell Bill Gates what went wrong message". This happens
executing the exact same line of code:


Forms!frmMainForm.frmSubForm.Form.Recordset"txtFieldToUpdate").Value =
strDataToUpdateFieldWith

Logically that line updates a the current record set / line item on the
form that called it and when it works it does exactly the way I would
expect. Once it fails, it fails catastrophically every time it hits
that line - if i comment out the line, Access stops failing.

Soo... is it a bad thing to update a record set on a sub-form from
another modal form?

This is what bugs me about developing in Access. There are errors that
get presented through the error object and then there are those errors
that shut down Access and have no logic.


Any ideas - I'm stumped?

 

[Graham Mandeno]

I understand your frustration. This is something that *should* work, but
either a corruption in your database or a bug in Access (or both!) is
causing it to fail intermittently.

However, you are using a very unconventional method to update a field in the
current record. The usual method is to update the field directly from the
form's collection, not via the form's Recordset property. Try this instead:

Forms!frmMainForm.frmSubForm.Form("txtFieldToUpdate") _
= strDataToUpdateFieldWith

 

[ZRexRider]

And that's why you're and MVP! My pain has ended thanks to Graham
Mandeno! Go New Zealand!

Going from:
Forms!frmMainForm.frmSubForm.Form.Recordset("txtFieldToUpdate").Value =

strDataToUpdateFieldWith

To:
Forms!frmMainForm.frmSubForm.Form("txtFieldToUpdate") _
= strDataToUpdateFieldWith

Stopped my tiny little Access .ADP application from crashing Access.

Good on you mate!