Subform Madness. Please help!

[MarkWaddington]

I have added a main "client details" form on my database and on the form I have put an "Updated By" subform which automatically adds the username of the person who has inputted data on the form.

Basically I have attached a macro to the Beforeupdate event handler which fills in the values on the subform (i.e. username).

I refer to the subform in my macro as follows:

(Condition):

IsNull([Forms]![Client]![Client_Update].[Form]![InputtedBy])

(Action):

SetValue with CurrentUser()



Now when I go to my form and try to enter a record I get the following error message:

You can't assign a value to this object

* The object may be a control on a read only form (WHICH IT ISN'T)

* The object may be on a form that is open in design view (WHICH IT ISN'T)

* The value may be too large for this field (NOPE, IT ISN'T).

Please can anyone help?

Thanks in advance!

Mark Waddington.

 

[JeremyNYC]

Try calling this macro from the afterUpdate event of the main form, instead of the beforeUpdate.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[MarkWaddington]

Thanks for the reply!

No that doesn't work, i get the same error!

Am I referring to the subform controls properly?

Do I need to "tell" it to make a record in my subform that matches the autonumber of the main form? If so, how?

I made a one-to-one relationship between the underlying tables and I set a child-field in my subform linked to the parent-field (the autonumber) in the main form.

Anymore ideas..?

Thanks.

 

[JeremyNYC]

Can you convert the macro to code and paste the code here?

Thanks.

jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[MarkWaddington]

Sure, here's the code (note I have 4 fields, not 1 as my question originally stated):

Function UserUpdates()
On Error GoTo UserUpdates_Err

If (IsNull(Forms!Client!Client_Update.Form!InputtedBy)) Then
IsNull(Forms!Client!Client_Update.Form!InputtedBy) = CurrentUser()
End If
If (IsNull(Forms!Client!Client_Update.Form!InputtedByDate)) Then
IsNull(Forms!Client!Client_Update.Form!InputtedByDate) = Now()
End If
If (IsNull(Forms!Client!Client_Update.Form!UpdatedBy)) Then
IsNull(Forms!Client!Client_Update.Form!UpdatedBy) = CurrentUser()
End If
If (IsNull(Forms!Client!Client_Update.Form!UpdatedByDate)) Then
IsNull(Forms!Client!Client_Update.Form!UpdatedByDate) = Now()
End If
If (Not IsNull(Forms!Client!Client_Update.Form!UpdatedBy)) Then
IsNull(Forms!Client!Client_Update.Form!UpdatedBy) = CurrentUser()
End If
If (Not IsNull(Forms!Client!Client_Update.Form!UpdatedByDate)) Then
IsNull(Forms!Client!Client_Update.Form!UpdatedByDate) = Now()
End If
Exit Function


UserUpdates_Exit:
Exit Function

UserUpdates_Err:
MsgBox Error$
Resume UserUpdates_Exit

End Function


Thanks a lot for your help.

 

[JeremyNYC]

I can't see anything wrong with your code. Seems like there has to be something else causing the problem. First thing is to recheck each of those three possibilities. Next, what is the allowEdits property of the form and the subform? I'm not sure where else to look.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[MarkWaddington]

Thanks for trying!

AllowEdits is set to yes on the MainForm and sub-form.

At first i had all these update fields as part of my main table and form.. and the code worked fine.

But then I realised i'd need to have separate tables for the client details and user update because otherwise it would cause errors when i had a user who had only "read-only" access.

So I made a separate table.. added a one-to-one relationship, added the subform to my main form and changed the macro so it would refer to the controls correctly.. that's all i've changed.

Ah well, guess i'll have to have a rethink! Is there anyway you can stop a macro from being carried out if the user who is running it only has read-only access?