Save form data on change of focus

I have a form bound to a table, say TableA. From this form, call it
FormA, I lauch another form, call it FormB, which is bound to TableB.
The primary key of TableA is linked to a field of TableB by a
one-to-many relationship with referential integrity enforced. I have
placed a toggle button on FormA that launches FormB. When FormB is
launched from FormA the FormB records are filtered such that only those
records related to the current FormA record are displayed, rather like
the case with a subform.

When I enter new data in FormA, I save it just before FormB is launched
by using the

If Me.Dirty Then Me.Dirty = False
Do.Cmd OpenForm "FormB"

technique, with this code in the Click event of the toggle button that
launches FormB. This works fine as long as FormB is not open already
and I only launch FormB after entering the primary key data into FormA.

The problem is that I like to browse through my data on FormA using the
record navigation buttons while FormB is open. As I do so, the data
displayed on FormB changes according to the filter, and everything
works splendidly. But when I decide to add a new record into FormA
while FormB is open, and then I change focus and go to FormB to add a
corresponding record, I get the following error:

"You can't add or change a record because a related record is
required in Table 'TableA'."

I recognize this as being due to the fact that the record just entered
in FormA is not yet saved. I thought I could simply add an

If Me.Dirty Then Me.Dirty = False

line to the LostFocus event of FormA, but this does not do what I
expected. I still get the same error.

I also have to enter the foreign key data manually into FormB under
this condition. Normally this field is filled in for me automatically
using a default value. The default value is

[Forms]![FormA]![SuchAndSuchaTextbox]

So I have two questions:

1) How can I make sure that the FormA record gets saved when I change
the focus to edit FormB? (and will doing so accomplish what I want it
to?)

2) In the special case I've outlined above, how can I make sure the
foreign key field on FormB is updated at the same time I change focus
from FormA to FormB so I don't have to do it manually?

Hi ben,

Sounds like you have a main form/sub form relationship... Have you
considered putting FormB in a subform in FormA. If you want separate pages,
use a Tab Control with the FormA info on the first tab and the FormB info in
a subform on the second tab. This way Access will look after the
relationship for you (assuming you set the link master field/child field
appropriately).

Hope this helps.

Damian.

" wrote:

I have a form bound to a table, say TableA. From this form, call it
FormA, I lauch another form, call it FormB, which is bound to TableB.
The primary key of TableA is linked to a field of TableB by a
one-to-many relationship with referential integrity enforced. I have
placed a toggle button on FormA that launches FormB. When FormB is
launched from FormA the FormB records are filtered such that only those
records related to the current FormA record are displayed, rather like
the case with a subform.

When I enter new data in FormA, I save it just before FormB is launched
by using the

If Me.Dirty Then Me.Dirty = False
Do.Cmd OpenForm "FormB"

technique, with this code in the Click event of the toggle button that
launches FormB. This works fine as long as FormB is not open already
and I only launch FormB after entering the primary key data into FormA.

The problem is that I like to browse through my data on FormA using the
record navigation buttons while FormB is open. As I do so, the data
displayed on FormB changes according to the filter, and everything
works splendidly. But when I decide to add a new record into FormA
while FormB is open, and then I change focus and go to FormB to add a
corresponding record, I get the following error:

"You can't add or change a record because a related record is
required in Table 'TableA'."

I recognize this as being due to the fact that the record just entered
in FormA is not yet saved. I thought I could simply add an

If Me.Dirty Then Me.Dirty = False

line to the LostFocus event of FormA, but this does not do what I
expected. I still get the same error.

I also have to enter the foreign key data manually into FormB under
this condition. Normally this field is filled in for me automatically
using a default value. The default value is

[Forms]![FormA]![SuchAndSuchaTextbox]

So I have two questions:

1) How can I make sure that the FormA record gets saved when I change
the focus to edit FormB? (and will doing so accomplish what I want it
to?)

2) In the special case I've outlined above, how can I make sure the
foreign key field on FormB is updated at the same time I change focus
from FormA to FormB so I don't have to do it manually?

Damian,

I'm sure you're right, a true subform would probably accomplish what
I'm trying to do, but I needed more flexibility in my FormB than is
afforded by a subform. I purposely avoided doing this with a subform.

On Dec 12, 5:09 pm, Damian S
wrote:
Hi ben,

Sounds like you have a main form/sub form relationship... Have you
considered putting FormB in a subform in FormA. If you want separate pages,
use a Tab Control with the FormA info on the first tab and the FormB info in
a subform on the second tab. This way Access will look after the
relationship for you (assuming you set the link master field/child field
appropriately).

Hope this helps.

Damian.



" wrote:
I have a form bound to a table, say TableA. From this form, call it
FormA, I lauch another form, call it FormB, which is bound to TableB.
The primary key of TableA is linked to a field of TableB by a
one-to-many relationship with referential integrity enforced. I have
placed a toggle button on FormA that launches FormB. When FormB is
launched from FormA the FormB records are filtered such that only those
records related to the current FormA record are displayed, rather like
the case with a subform.

When I enter new data in FormA, I save it just before FormB is launched
by using the

If Me.Dirty Then Me.Dirty = False
Do.Cmd OpenForm "FormB"

technique, with this code in the Click event of the toggle button that
launches FormB. This works fine as long as FormB is not open already
and I only launch FormB after entering the primary key data into FormA.

The problem is that I like to browse through my data on FormA using the
record navigation buttons while FormB is open. As I do so, the data
displayed on FormB changes according to the filter, and everything
works splendidly. But when I decide to add a new record into FormA
while FormB is open, and then I change focus and go to FormB to add a
corresponding record, I get the following error:

"You can't add or change a record because a related record is
required in Table 'TableA'."

I recognize this as being due to the fact that the record just entered
in FormA is not yet saved. I thought I could simply add an

If Me.Dirty Then Me.Dirty = False

line to the LostFocus event of FormA, but this does not do what I
expected. I still get the same error.

I also have to enter the foreign key data manually into FormB under
this condition. Normally this field is filled in for me automatically
using a default value. The default value is

[Forms]![FormA]![SuchAndSuchaTextbox]

So I have two questions:

1) How can I make sure that the FormA record gets saved when I change
the focus to edit FormB? (and will doing so accomplish what I want it
to?)

2) In the special case I've outlined above, how can I make sure the
foreign key field on FormB is updated at the same time I change focus
from FormA to FormB so I don't have to do it manually?- Hide quoted text -- Show quoted text -

The issue is getting the underlying table updated in FormA so FormB knows it
is there.

You can accomplish this by requerying FormA.
It is possible that FormB will also have to be requeried so the new record
from FormA will be included in its record source, but I am not sure.

The requery is the easy part. The problem is that when you requery the
form, it goes back to the first record in the record source. This means you
have to have a way to reposition the record source to the record you were on.
Here is some sample air code that shows how this can be accomplished.

Dim strKeyVal As String 'Use the data type that corresponds to the key data
type
Dim rst As Recordset

strKeyVal = Me.txtPrimaryKeyField 'Save the key value to use to search
Me.Requery 'Update the underlying table
Set rst = Me.RecordsetClone
With rst
.FindFirst "[PrimaryKeyField] = '" & strKeyVal & "'" 'Locate the
Previous Record
If Not .NoMatch Then
Me.BookMark = .Bookmark 'Move to the Previous Record
End If
End With
Set rst = Nothing

" wrote:

I have a form bound to a table, say TableA. From this form, call it
FormA, I lauch another form, call it FormB, which is bound to TableB.
The primary key of TableA is linked to a field of TableB by a
one-to-many relationship with referential integrity enforced. I have
placed a toggle button on FormA that launches FormB. When FormB is
launched from FormA the FormB records are filtered such that only those
records related to the current FormA record are displayed, rather like
the case with a subform.

When I enter new data in FormA, I save it just before FormB is launched
by using the

If Me.Dirty Then Me.Dirty = False
Do.Cmd OpenForm "FormB"

technique, with this code in the Click event of the toggle button that
launches FormB. This works fine as long as FormB is not open already
and I only launch FormB after entering the primary key data into FormA.

The problem is that I like to browse through my data on FormA using the
record navigation buttons while FormB is open. As I do so, the data
displayed on FormB changes according to the filter, and everything
works splendidly. But when I decide to add a new record into FormA
while FormB is open, and then I change focus and go to FormB to add a
corresponding record, I get the following error:

"You can't add or change a record because a related record is
required in Table 'TableA'."

I recognize this as being due to the fact that the record just entered
in FormA is not yet saved. I thought I could simply add an

If Me.Dirty Then Me.Dirty = False

line to the LostFocus event of FormA, but this does not do what I
expected. I still get the same error.

I also have to enter the foreign key data manually into FormB under
this condition. Normally this field is filled in for me automatically
using a default value. The default value is

[Forms]![FormA]![SuchAndSuchaTextbox]

So I have two questions:

1) How can I make sure that the FormA record gets saved when I change
the focus to edit FormB? (and will doing so accomplish what I want it
to?)

2) In the special case I've outlined above, how can I make sure the
foreign key field on FormB is updated at the same time I change focus
from FormA to FormB so I don't have to do it manually?

Although I've never used it, when switching back and forth between two forms
I think (notice I say THINK) that the form's Activate/Deactivate events are
triggered. Try this in Form A and see if it works.

Private Sub Form_Deactivate()
If Me.Dirty Then Me.Dirty = False
End Sub

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200612/1

Whoops. I think I've been "replying to author" instead of to the group.
Sorry. Publicly, now, I did try using the Me.Dirty method in the
Deactivate event previously, and although the Activate/Deactivate
events are triggered when switching back and forth between forms, the
record still doesn't get saved. And I can say the same of the
requerying idea that Klatuu suggested. I can't get the table to update,
even though I place requerying code in the Deactivate event of FormA.
One problem I notice with Deactivate--it gets triggered by almost
anything (pushing the navigation buttons, for instance).

On Dec 13, 9:08 am, "missinglinq via AccessMonster.com" u28780@uwe
wrote:
Although I've never used it, when switching back and forth between two forms
I think (notice I say THINK) that the form's Activate/Deactivate events are
triggered. Try this in Form A and see if it works.

Private Sub Form_Deactivate()
If Me.Dirty Then Me.Dirty = False
End Sub

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200612/1

Don't see Klatuu's post! Have you tried requrying Form B on Activate?

wrote:
Whoops. I think I've been "replying to author" instead of to the group.
Sorry. Publicly, now, I did try using the Me.Dirty method in the
Deactivate event previously, and although the Activate/Deactivate
events are triggered when switching back and forth between forms, the
record still doesn't get saved. And I can say the same of the
requerying idea that Klatuu suggested. I can't get the table to update,
even though I place requerying code in the Deactivate event of FormA.
One problem I notice with Deactivate--it gets triggered by almost
anything (pushing the navigation buttons, for instance).

On Dec 13, 9:08 am, "missinglinq via AccessMonster.com" u28780@uwe
wrote:
Although I've never used it, when switching back and forth between two forms
I think (notice I say THINK) that the form's Activate/Deactivate events are
[quoted text clipped - 10 lines]

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200612/1

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200612/1

Okay. I think I have it working. I saved the data in FormA using code
in the BeforeInsert event of FormB ( Forms![FormA].Dirty = False
method), and I copied the foreign key information into FormB at the
same time. This should do it. Thanks everyone for your help!


On Dec 13, 10:50 am, "missinglinq via AccessMonster.com" u28780@uwe
wrote:
Don't see Klatuu's post! Have you tried requrying Form B on Activate?





wrote:
Whoops. I think I've been "replying to author" instead of to the group.
Sorry. Publicly, now, I did try using the Me.Dirty method in the
Deactivate event previously, and although the Activate/Deactivate
events are triggered when switching back and forth between forms, the
record still doesn't get saved. And I can say the same of the
requerying idea that Klatuu suggested. I can't get the table to update,
even though I place requerying code in the Deactivate event of FormA.
One problem I notice with Deactivate--it gets triggered by almost
anything (pushing the navigation buttons, for instance).

On Dec 13, 9:08 am, "missinglinq via AccessMonster.com" u28780@uwe
wrote:
Although I've never used it, when switching back and forth between two forms
I think (notice I say THINK) that the form's Activate/Deactivate events are
[quoted text clipped - 10 lines]

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200612/1--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200612/1- Hide quoted text -- Show quoted text -