What Method / Properties Can I use

[CementCarver]

I'm trying to test a new value and old value in a subform contained within my main form. I have a line that is causing an error:



TRACE_MEMBER_ID_OLD = Nz(Forms("frm_EDIT_CURRENT_FULLGROWER").Controls(" EDIT_GROWER_TRACEABILITY_DATASHEET").Form.Controls ("MEMBER_ID").OldValue, "0")

The error states that the object does not support this property or method. How do I find out what properties/methods are supported with this object?

CementCarver

[amrut]

Does this

TRACE_MEMBER_ID_OLD = Nz(Forms!frm_EDIT_CURRENT_FULLGROWER!EDIT_GROWER_TRACEABILITY_DATASHEET.Form.MEMBER_ID.OldValue, "0")

helps ?
See http://access.mvps.org/access/forms/frm0031.htm for syntax to refer to subforms.

[CementCarver]

Amrut, is your link related to Access 2010 vba? Cause the line I have, which works for other controls from my subform take on this syntax:

Forms!MAIN_FORM!SUBFORM.Form.control like this:

Forms!frm_EDIT_CURRENT_FULLGROWER!EDIT_GROWER_T RACEABILITY_DATASHEET.Form.MEMBER_ID

CementCarver

[amrut]

Amrut, is your link related to Access 2010 vba?

The syntax in the link work for me for 2010.Does the code given by me works ?

For an explanation of the difference in the two syntax, you need to wait till an expert joins in as it is beyond my scope to explain.
EDIT :
I think the .Controls method is used when one has to manipulate the controls collection on a form.

[CementCarver]

Amrut, can you provide me with a line of your code that working? I can't get any line to work from the link you've provided.

CementCarver

[amrut]

The code varies depending on whether the control you are referring to is on the main form or sub form and the form from where you are calling it.
The code you are using is in the main form or sub form ?

[CementCarver]

I've selected the subform, during the main form design stage. Then I add to the subform a beforeupdate event and patch in that code. So, I think the code that i'm using is related to the sub form.

CementCarver

[amrut]

If the code is in the Before_Update event of form, Nz(Me.MEMBER_ID.OldValue,0) should solve your purpose.

[CementCarver]

Let me expand on my design alittle. In a previous form, I allow the client to create a record, which they may or may not complete all the records. And when the client creates the new record, it automatically populates two other tables and in this cause it's the traceability table. So, when the client goes into the form, using the edit form, the subform already has the MEMBER_ID and BUSINESS NAME already populated in the subform. Here, they can complete the edit on the first entry, but can also add in more entries making it a one-many relationship in the subform.

So when my code runs on the beforeupdate event for the subform, the client sees the MEMBER_ID and BUSINESS NAME there already, which they should not change. So maybe that where my code logic is incorrect.

CementCarver

[CementCarver]

Amrut,

I get an error.... Invalid use of the ME .....................for this line........TRACE_MEMBER_ID_OLD = Nz(Me.MEMBER_ID.OldValue, 0)

CementCarver

[amrut]

On which form is the control MEMBER_ID is located ? Are you referring from a different form ?

the subform already has the MEMBER_ID and BUSINESS NAME already populated in the subform.

The subform beforeupdate will not fire unless a record is edited.

[CementCarver]

The MEMBER_ID is on both forms. And that's how they're linked together. So when the new record gets created the next highest member_id is assign and also populates the subform with the same number.

CC

[amrut]

In a previous form, I allow the client to create a record, which they may or may not complete all the records. And when the client creates the new record, it automatically populates two other tables and in this cause it's the traceability table. So, when the client goes into the form, using the edit form

I think I am not following you. Are you referring to one or two forms ?
Is following correct ?
Main form : frm_EDIT_CURRENT_FULLGROWER
SubformControlName ( as viewed in the mainform): EDIT_GROWER_T RACEABILITY_DATASHEET
ControlONSubForm : MEMBER_ID

[CementCarver]

That is correct.

[amrut]

Are you referring to one or two forms ?