VBA to update field contents...Error 91

[jpkeller55]

I know this is probably something basic but my VBA knowlege is pretty limited. I am trying to update a field After Update in a form using the following code but getting an error message - "Run-time error '91': Object variable or With block variable not set"



I guess I am missing something. Thanks for any help.

Private Sub Form_AfterUpdate()
    Dim MyForm As Form
        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
        "***Response Updated***"
        MyForm.Lastupdated = Now()
        MyForm.LastUpdatedBy = Environ("USERNAME")
        Exit Sub
End Sub

[pbaldy]

Hint: what form do you intend this code to work on?

[jpkeller55]

I was thinking I have to declare the form name somehow but not sure. The form is frmQandRsubform. Dim frmQandRsubform as form?

[pbaldy]

I don't typically use the Form type (I'd be more likely to use a string variable, but that's just me). You'd need a new line like:

Set MyForm = Forms!frmQandRsubform

[ajetrumpet]

JIm,

I agree with Paul on this. using the FORM type in visual basic is cumbersome because the syntaxes related to it and its members have changed from version to version.

is this code behind the actual form that you're updating? what control are you changing? one on a subform, or one on the main form? for instance, to change a control on a subform, this would be needed:

me.subformcontrol.form.controls("updates") = value

there is really no need to declare anything.

and...staying with your code, yes, it will error on this line:

MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _

because the MyForm object is not associated with anything by using the SET statement.

[jpkeller55]

Thanks Adam. I am trying to update a value in a table associated with the main form when the subform is udpated. I have data fields in the subform that point to the table. So, I have the code on the After Update event of the subform to update the value in the table via the field on the subform. Does this make sense?

[ajetrumpet]

I am trying to update a value in a table associated with the main form when the subform is udpated. I have data fields in the subform that point to the table. So, I have the code on the After Update event of the subform to update the value in the table via the field on the subform.

Does this make sense?

No. How about answering these...

*whats the source of the subform (record source, that is)
*whats the source of the main form
*whats the control name thats providing the update to the table
*where is that control? on the main or the sub?
*do you actually know that the sub's afterupdate event is firing? if so, how do you know?

[jpkeller55]

Adam, see my responses in blue...

*whats the source of the subform (record source, that is) - qryQandR

*whats the source of the main form - qryQuestion
*whats the control name thats providing the update to the table - Updates
*where is that control? on the main or the sub? - sub
*do you actually know that the sub's afterupdate event is firing? I assume so but don't know for sure. if so, how do you know?

[ajetrumpet]

let's say that on the AFTER UPDATE of the sub, you want to popuplate 2 controls on the main with NOW() and USERNAME.

if that is so, you will need:

sub_afterupdate

me.parent.form.controls("NOWcontrol") = NOW()
me.parent.form.controls("USERNAMEcontrol") = environ("username")

just trying to get you one step closer to your own solution here. you can use the 'parent' property on any subform, anywhere. it is much much easier than messing with objects and form names. e.g. - these two do the same thing:

me.parent

forms("mainform")

does that help at all?

<edit>
to update a control on the main from UPDATES on the sub:

me.parent.control = me.updates

[jpkeller55]

Thanks Adam...clearly I need to do some homework. Will let you know if I get this resolved.