how to make main form notice changes in subform(s)? (1 Viewer)

[madEG]

Hello, I inherited an older access db (I think it is currently using 2002 container/mdb) and it is done in a style that I am not familiar. The previous developer was fond of subforms, and uses them often. I on the other hand struggled to make them work for me when first starting out, and abandoned learning how they operate years ago. Which leaves me in a bind... On the main form there are two pairs of fields which capture the date and username when a record is added/updated, which stopped working for whatever reason. My problem is I can't seem to make the main form notice when the fields are updated in the subforms. I tried in vain to use the new record and after update events on the subforms, but no joy. It's like the main form doesn't know what's happening in the subforms. How can I make the main form notice when data has been modified on the subforms? Can someone give me a push in the right direction? Thanks! -Matt G.

 

[sneuberg]

What do you need to do in the main form when there are changes in the subform?

 

[madEG]

I have some fields that track what user created the record, and when. Further, I'd like to train the form to know when any fields are updated (both main form and subforms) so I can update the two fields that catch last date and user who made the changes.

I can prepopulate the new record username and date ok, but the update user/date only fires on the main form, not when the subforms are touched.

 

[sneuberg]

I think something like:

Me.Parent.txtUserName = Environ("UserName")

in the subform afterupdate would for example update a textbox in the main form with the name txtUserName. Are you doing something like that?

 

[The_Doc_Man]

The sub-form can have events that could trigger based on AfterChange events, but note that if the sub is in datasheet view your event options are more limited. The sub-form, being just a form used in a sub-form control, CAN have the full range of options associated with forms. As a general principle in programming, if you want to record a change, let the thing that actually MAKES the change be the recorder of that change. (That advice is not limited to Access, it even works in operating system and device driver code.)

 

[madEG]

Me.Parent.txtUserName =

Going the above route worked! Before I was trying to do something like forms!frmName!txtUpdatedBy = etc on the AfterUpdate event for the subforms, and that caused the app to freeze up, which I didn't understand.

Going the me.parent is route working!

THANK YOU ALL!

 

[sneuberg]

Before I was trying to do something like forms!frmName!txtUpdatedBy = etc on the AfterUpdate event for the subforms, and that caused the app to freeze up, which I didn't understand.

I think that should have worked too unless frmName was in a subform too like for example in a NavigationSubform you get when using the navigation form.

 

[jdraw]

?? If you are recording who modifies(multiple instances) the record and when also, then the subform makes more sense to me. If you are only recording Create info --who and when, then these are single values and would fit in "parent record".

 

[Galaxiom]

The sub-form can have events that could trigger based on AfterChange events, but note that if the sub is in datasheet view your event options are more limited.

Just to clarify that. A subform in Datasheet View is still a form with a full set of Events. What look like fields from a query or table are actually controls being displayed like a field. They even support Conditional Formatting which is not available on a field.

Using a query or a table as the Source Object of a subformcontrol forgoes the facilities available for a form which is what Doc actually meant to convey.

 

[gemma-the-husky]

really in a correctly structured database, then no changes in the subform should need to affect the main form.

the point is that the subform records should be dependent only on an identifier from the main form (often the PK)

if you are say, recording who changed a subform item, and also storing this as the last change in the parent record, then although you can do this, there is no real need. You can easily determine this at runtime. As soon as you duplicate data in multiple tables, you run the risk of the data becoming inconsistent.

 

[madEG]

The application is a person/org contact/list management tool. The main form is the person/organization details, with two subforms being contact info (phone/email instances), the other is land address instances. The datasheet subform houses dropdowns that contain instances of lists that the person/org are a part of. It's strict 3NF, all hooked via fkeys. The architecture is fine, I just tend to use listboxes instead of subforms with listboxes.

I don't even see the subforms being reused, which I would think is the only reason to have them over a mere listbox on the main form - for central maintenance/re-usability.

Is there something I'm overlooking? Some benefit I'm not aware of?

Thanks for any additional input!