Two forms open bound to same table

[303factory]

Hi people

I have a bit of a conundrum, my software is upgrading to dual monitors and I have to set up my client to have a different form maximised on each screen.

The problem is in some occasions the two forms will be bound to the same table. So I need to syncronise the data so when you edit a field on one form and move to the other, it updates (it is possible for both forms to have the same field displayed at once)

I assume I want to save the record and refresh the form when you move from one to the other, I tried playing with the GotFocus and LostFocus events but they dont seem to trigger when I click on the other form. I could get it to refresh AfterUpdate but I dont want that triggering every time you enter a bit of data.

Any suggstions on how this should be managed? Is it possible without further normalisation of the tables (not really an option at the moment unformtunately)

Regards

303

 

[Steve R.]

... (it is possible for both forms to have the same field displayed at once) ... I could get it to refresh AfterUpdate but I dont want that triggering every time you enter a bit of data.

It seems that you are asking a somewhat conflicting question. One approach would be to use a Boolean variable for the fields that you do want to synchronize between the two forms. Use the OnChange event to set the Boolean variable to "true" for that field and execute it in the after update event. Fields that don't matter would not trigger an update event then.

Another approach, use the OnTimer event to periodically update the second from. Of course this method would have a time delay. (You may want to use both approaches.)

 

[vbaInet]

Why don't you just requery the form after every update? Is it paramount that it must wait until it has the focus before it will refresh?

 

[dfenton]

Keep in mind that editing the same table in two different forms can lead to write conflicts.

 

[Banana]

In one scenario where the client insisted on a UI where there was a listbox with few fields -- double click a row in the listbox and the fields would then "load" with the values from that listbox for editing.

I solved this problem by using a common recordset.. that is, I opened a recordset and bound both form itself (single record view) and the listbox to the same recordset. This nicely solved the write conflict problem & focus problem and did not require me to requery or refresh since both were sharing the recordset and thus received updates consistently.

I'm sure same principle could be applied to the forms, though I am little unclear as to what two forms are actually doing, and more importantly whether two forms could be in dirty state at same time.

 

[The_Doc_Man]

Look at the "Enter" and "Exit" events on the forms. When you have two forms open and switch between them, there is a form.Exit and a form.LostFocus (not sure of the order) on the form you are leaving, then a form.GotFocus and a form.Enter on the form you are activating. For that matter, form.Activate is a valid event that you could use rather than form.Current

Look into those events as places to put a requery. It would be theoretically possible to put some logic in the forms so that as long as they are both open, they will remember that they lost focus and so need to do a requery when they regain focus. Whereas when they are initially opened, they have never yet lost focus so a requery isn't required.

In each form, you might have this arrangement:

Declarations area:

Dim ReQueryMe as Boolean

Form.Open (or Form.Load):

ReQueryMe = False

Form.Activate or Form.Enter:

IF ReQueryMe then me.Requery
ReQueryMe = False

Form.Exit:

ReQueryMe = True

That would cause the form to requery itself if it had been open, exited, and re-entered, but would do nothing as long as the form was never exited.

 

[303factory]

Thanks for the responses.
I'm having a little trouble implimenting these ideas

AfterUpdate will not trigger if I move from one form to the other that shares the same bound table. It only triggers if I move into a subform bound to a different table.

Form_Activate, Form_Deactivate, Form_Current, Form_Click, Form_LostFocus - these also are not triggering if you go from one form to the other. Only if you minimise one of the forms it would seem.

It also seems that while the second form is open, nothing I change on the first form gets saved. Even if I Me.Requiery on form_close.

 

[DCrake]

As you have 2 forms loaded at the same time why not use the IsLoaded("FormName") command to check for the sister form status and then on the after update of the control force the value in the sister form to update using the Forms("FormName")("ControlName") = Me.TextBox.

This way you can do it one the controls that you want to update only.

David

 

[vbaInet]

Thanks for the responses.
I'm having a little trouble implimenting these ideas

AfterUpdate will not trigger if I move from one form to the other that shares the same bound table. It only triggers if I move into a subform bound to a different table.

Form_Activate, Form_Deactivate, Form_Current, Form_Click, Form_LostFocus - these also are not triggering if you go from one form to the other. Only if you minimise one of the forms it would seem.

It also seems that while the second form is open, nothing I change on the first form gets saved. Even if I Me.Requiery on form_close.

Your Me.Requery will requery the form you are closing, not the form you are moving to. So it should be:

Forms!NameOfForm_ToRequery.Requery
Forms("NameOfForm_ToRequery").Requery

Whichever you prefer. I've noticed the former doesn't always work.

 

[303factory]

thanks people got it working now

 

[DCrake]

Prey tell, which method did you employ?