Running events on other forms. (1 Viewer)

[hully333]

Hey,

I hope one of you wizards out there can help.

I have a main form and subform with a one to many relationship.

Some main records contain no subform records and others can contain 20+.
My goal is when the main form changes record i want the subform to always go to a new record regardless of how many records are in that subform record set.

I can use the main form's PK change as the event to trigger it off, im just unsure how to do it.

And Vice versa when an event happens (like a change to a field value) on the sub form i want to be able to change the value of a field on the main form to reflect this change on the lost focus event of the sub form field, is that possible?

If anyone can give some examples i would be a happy chappy.

Thanks.

 

[DCrake]

Simple Software Solutions

When using main and sub forms you set the relationship between them on the forms properties page. You need to identigy which PK and FK hold the same data. So as you change the PK on your main form the subform will only display children of that parent.

In respect to your other question I can't why you would want to have the same data on both the main and the sub form, unless it is a calculation or the like. If this is so then these fields must be unbound and hodl the reference to the data in the child form.


CodeMaster:

 

[hully333]

Thanks for the reply, but the Child form does only show records that are related to it, the PK / FK relationships are fine, thats not the issue. I want however, when scrolling through the main form records, the sub form and only the sub form to go to a new record automatically, but it has to happen when the main form changes record.

Secondly, The sub form in question is an actions table, the main form being an event table, When the Action is "close" and saved i want the main form to reflect this "closure" and hold a "CLOSED" status, this is why i want the main form to hold the same information as the child form, only for the "closure" action being taken that is.

I can do this in VB and SQL with a stored proceedure updating the main record, but am struggling with the same way of thinking in access.


Regards

Simon

 

[DCrake]

Simple Software Solutions

Ok,

So when you close a child is the parent closed?
Or is parent closed when only all of the children are closed?

If the former are you running any code once the child is closed to update the parent as being closed, them repainting the screen with the on current event?

Because you, I assume, are using bound controls dynamically changing a value for the child, in this case as closed status, does not autiomatically reflect in the parent unless you set a trigger.

David

 

[hully333]

Ok,

So when you close a child is the parent closed?
Or is parent closed when only all of the children are closed?

If the former are you running any code once the child is closed to update the parent as being closed, them repainting the screen with the on current event?

Because you, I assume, are using bound controls dynamically changing a value for the child, in this case as closed status, does not autiomatically reflect in the parent unless you set a trigger.

David

The child form is embedded into the main form and stays open permenantly with the main form until the main form is closed and both are then closed.

No code runs at closure.

Yes, i am using bound controls on both forms, and dlookups. I am looking for a little guidance on how to update the information on 1 form from a trigger on the other. Its how i switch i am having difficulty with.


Longing for.....
[Using the Child Form] upon the save button event click trigger;
If action_type.value = "closure"....then....

[Update the main form]
event_current_status.value = "CLOSED"

etc


Regards

 

[DCrake]

Simple Software Solutions

I was aware that you had a main form with an embedded subform. However you have not stated how you are displaying records in your subform? Is it a datasheet? form view? Continuious?

Lets says it is a single view - one child record at a time, and on the child form you have a tick box that has a label called 'status'. Then if you tick the box you are stating that the child record is "CLOSED". Does this mean that only that one particular child record is CLOSED? or does it mean all of them? Also does it mean that the Parent record is also CLOSED?

Lets work on the fact that its "one out all out"

When you tick on that box (which is a bound control to a field in the underlying table) the record status changes to CLOSED. What you need to do then is to run the following code on the OnClick Property of the tick box.

If Me.TickBox = True Then

DoCmd.RunSQL "UPDATE MyTable Set MyField = 'Closed' Where MyKey = " & Me.UniqueID 
Me.Repaint
End If

Code has been simplified for brevity.

David

 

[hully333]

Yes the child form is in form view showing one record at a time, the action form (child) is merely a recording form of all actions possible throughout the life cycle of the event (main form), ie adding a note, Update, closure, re-open etc for an audit trail of all actions taken against the event for viewing and future reporting. There's no reason to closure any records on the child form, its the "closure" action on the child form that will "close" the main event record.

I will have a go at that method you mention, it seems to make perfect sense.

Thanks for your help.

 

[hully333]

Works like a dream, thanks for helping.

 

[DCrake]

Simple Software Solutions

We got there in the end, didn't we.