Two forms sharing one table: write conflict

[Wim]

I have two forms (single forms) that have the same table as their record
source. Form 2 is opened by pressing a command button on form 1 and it shows
the same record as form 1. In the On Deactivate event of both forms I have
If me.Dirty Then me.Dirty = False
Still, if I input some data on both forms, close form 2 and then try to
close form 1 I get an error message saying
Write conflict; this record has been changed by another user since you
started editing it...
Form 2 is pop-up, but not modal. Form 1 has several tabs. I want form 2 to
be visible from each of the tabs, that’s why I chose a pop-up form and not a
subform. The controls on both forms are linked to different fields in the
table, with the exception of one: the primary key, which is filled in
automatically. The db is single-user.
What am I doing wrong?

 

[Jeanette Cunningham]

Wim,
put some code on the command button that opens form2 to open form2 in
acDialog mode.
DoCmd.OpenForm "Form2", , , , , acDialog
While form2 is open, form1 will not respond until form2 closes.


Jeanette Cunningham

 

[Wim]

But I want form 2 to remain open while the user is filling out form 1. Isn't
that possible? Why (not)?

 

[Bob Quintal]

I have two forms (single forms) that have the same table as their
record source. Form 2 is opened by pressing a command button on
form 1 and it shows the same record as form 1. In the On
Deactivate event of both forms I have
If me.Dirty Then me.Dirty = False
Still, if I input some data on both forms, close form 2 and then
try to close form 1 I get an error message saying
Write conflict; this record has been changed by another user
since you
started editing it...
Form 2 is pop-up, but not modal. Form 1 has several tabs. I want
form 2 to be visible from each of the tabs, that’s why I chose a
pop-up form and not a subform. The controls on both forms are
linked to different fields in the table, with the exception of
one: the primary key, which is filled in automatically. The db is
single-user. What am I doing wrong?

You need to put a statement in the On_click Event of the button that
opens Form2 which sets Dirty on Form 1 to false, and prevent entry
in form one until form 2 has been saved.

I suspect you'll have nightmares if you try and implement this.

 

[Wim]

Hi Bob, thanks for answering my question.
In the On_Click event of the button that opens Form 2 from Form 1 I have:
If Me.Dirty Then Me.Dirty = False
Is that what you mean?
As for the second part of your answer: if I force Form 2 to be saved after
every change (looks like I'll have to use After_Update for that...?), can I
then keep it open, so that it is visible for the user, while (s)he is filling
out Form 1?

 

[Jeanette Cunningham]

Wim,
have you thought of using a tab control where you could put form1 on a
separate page from page2?
The separate pages of a tab control are all part of the same form.
Users could switch back and forth between the 2 pages if they like.

Jeanette Cunningham

 

[Wim]

Presently Form 1 has a tab control with several pages. What's on Form 2 is
general information, that is related to information on all of the pages in
Form 1. I would like the information on Form 2 to be visible from anywhere in
Form 1 and, if possible, I would like the user to be able to switch between
the forms and make changes on both.
I could of course place the contents of Form 2 on Form 1 (outside the tab
control, so it will always be visible), but there simply isn't enough room
for that on Form 1.
Any other suggestions?

 

[Bob Quintal]

Hi Bob, thanks for answering my question.
In the On_Click event of the button that opens Form 2 from Form 1
I have:
If Me.Dirty Then Me.Dirty = False
Is that what you mean?
As for the second part of your answer: if I force Form 2 to be
saved after every change (looks like I'll have to use After_Update
for that...?), can I then keep it open, so that it is visible for
the user, while (s)he is filling out Form 1?

yes.

Yes because any change via form 2 saves to the underlying record of
Form 1.

There are a couple of ways areound this. 1) Make form2 read only,
and add a tab on form 1 that duplicates the form 2 for data entry.

remove the recordsource from form 2, and use code to copy the data
from form 1 to form 2 when opening form 2, and copy the data back to
form 1 after every change made in form 2.

Not fun to code, but it works.

 

[Wim]

OK. I think I'll go for the second option. Let me see if I understood it well.
Form 2 would be unbound. Any change on Form 2 would be programmatically
transmitted to (invisible) controls on Form 1. How? By putting
Forms!Form1!control1 = Me.control1
in the After_Update event of each control on Form 2?
On opening Form 2 from Form 1 the controls on Form 2 should be populated
with the values on Form 1. How? By putting a series of
Me.control1 = Forms!Form1.control1
statements (one for each control) in the On_Open event of Form 2?
Is that what you are suggesting?
Thank you for your patience!

 

[Bob Quintal]

OK. I think I'll go for the second option. Let me see if I
understood it well. Form 2 would be unbound. Any change on Form 2
would be programmatically transmitted to (invisible) controls on
Form 1. How? By putting
Forms!Form1!control1 = Me.control1
in the After_Update event of each control on Form 2?
On opening Form 2 from Form 1 the controls on Form 2 should be
populated with the values on Form 1. How? By putting a series of
Me.control1 = Forms!Form1.control1
statements (one for each control) in the On_Open event of Form 2?
Is that what you are suggesting?
Thank you for your patience!

Yes, that's exactly how to do it.

 

[Wim]

OK, thank you very much, Bob

Yes, that's exactly how to do it.