New record input NOT updated!

[Guest]

I have a form with two subforms. Both subforms are based on the same table,
but the 1st subform is in form view and is used for data entry (with
DataEntry property set to Yes), the 2nd one is in table view displaying a
list of all records.

The problem is after I enter a new record in the 1st subform and click my
designed Save button (with SaveRecord action using wizard), the record is
saved but NOT updated in the 2nd subform. Can anyone please kindly show me
the code that does the job? - because I'm not code literate at all

 

[Guest]

Also, can I disable the auto save of data so that records are only saved if
the designed Save button is clicked? (Because Access by default auto save the
data after the form is closed)

 

[Dirk Goldgar]

I have a form with two subforms. Both subforms are based on the same
table, but the 1st subform is in form view and is used for data entry
(with DataEntry property set to Yes), the 2nd one is in table view
displaying a list of all records.

The problem is after I enter a new record in the 1st subform and
click my designed Save button (with SaveRecord action using wizard),
the record is saved but NOT updated in the 2nd subform. Can anyone
please kindly show me the code that does the job? - because I'm not
code literate at all

Your 2nd subform must be requeried when the record is saved in the 1st
subform. I would create an event procedure for the AfterUpdate event of
the first subform, which would look something like this:

Private Sub Form_AfterUpdate()

Me.Parent!NameOfSecondSubform.Requery

End Sub

Note that "NameOfSecondSubform" in the above must be the name of the
subform *control* (on the main form) that displays the subform. This
name may or may not be the same as the name of the form object that
control displays.

 

[Dirk Goldgar]

Also, can I disable the auto save of data so that records are only
saved if the designed Save button is clicked? (Because Access by
default auto save the data after the form is closed)

It is possible to do this, but (a) it requires a bit of code, and (b)
for most purposes it is counterproductive. When people are entering
data, they expect that data to be saved -- that's why they're entering
it. Why should they have to take an extra step to save it, instead of
just moving on to the next record? If they weren't happy with what
they'd entered, they would either correct it or undo it. If *you* (the
developer) might not be happy with what they'd entered, you'd validate
it in the form's BeforeUpdate event and cancel that event to prevent
them from saving it if it didn't meet your standards.

So yes, with code you can keep records from being saved unless the user
clicks your button. Are you sure you want to do it?

 

[Guest]

It is possible to do this, but (a) it requires a bit of code, and (b)
for most purposes it is counterproductive. When people are entering
data, they expect that data to be saved -- that's why they're entering
it. Why should they have to take an extra step to save it, instead of
just moving on to the next record? If they weren't happy with what
they'd entered, they would either correct it or undo it. If *you* (the
developer) might not be happy with what they'd entered, you'd validate
it in the form's BeforeUpdate event and cancel that event to prevent
them from saving it if it didn't meet your standards.

So yes, with code you can keep records from being saved unless the user
clicks your button. Are you sure you want to do it?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks for your generosity, Dirk
Yes. I'd like to make use of the SaveRecord button because that way the new
input 'displays in 2nd subform' only when the user confirms it by clicking
the button -this is more of a procedural step (Also, because there's no
particular entry criteria, BeforeUpdate event might not be needed for the
purpose of saving new record in this case)

However, your suggestion of undo/delete the new entry is exactly what's
needed for another problem that follows. That is after requering 2nd subform
to display the new entry (as you suggested, which works perfect), 1st
data-entry-subform became bound to the 2nd subform. Hence, any change to the
new record in 2nd subform reflects immediately in 1st subform. e.g. if I
delete the new record in 2nd subform, all fields in 1st subform display as
"#Deleted".

Any idea how to cater for 1) undo/delete actions; 2) keeping fields in 1st
subform blank (as it should, being a data entry form) once the SaveRecord
button is clicked?
I was thinking of a "undo" button that clears the previous new entry in 2nd
subform and a "delete" button that deletes any record in 2nd subform. But I
don't know what code would do these jobs. Any suggestion is much appreciated

 

[Dirk Goldgar]

Yes. I'd like to make use of the SaveRecord button because that way
the new input 'displays in 2nd subform' only when the user confirms
it by clicking the button -this is more of a procedural step (Also,
because there's no particular entry criteria, BeforeUpdate event
might not be needed for the purpose of saving new record in this case)

Here's example code. This would be in the module of the data-entry
subform -- the "top" subform". There's a command button named "cmdSave"
on that subform. The second subform -- the one that displays all the
records that have already been entered -- is named "sfSubList".

'----- start of subform module code -----
Option Compare Database
Option Explicit

Dim mfSave As Boolean

Private Sub cmdSave_Click()

If Me.Dirty Then
mfSave = True
Me.Dirty = False
RunCommand acCmdRecordsGoToNew
End If

End Sub


Private Sub Form_AfterUpdate()
Me.Parent!sfSubList.Requery
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = Not mfSave
mfSave = False

End Sub
'----- end of module code -----

However, your suggestion of undo/delete the new entry is exactly
what's needed for another problem that follows. That is after
requering 2nd subform to display the new entry (as you suggested,
which works perfect), 1st data-entry-subform became bound to the 2nd
subform. Hence, any change to the new record in 2nd subform reflects
immediately in 1st subform. e.g. if I delete the new record in 2nd
subform, all fields in 1st subform display as "#Deleted".

I'm not sure I follow, but the it seems to me that the desired behavior
of the data-entry subform would be to move to a new (blank) record as
soon as the record is saved. The code I posted above does that. So
that would eliminate this problem.

Any idea how to cater for 1) undo/delete actions; 2) keeping fields
in 1st subform blank (as it should, being a data entry form) once the
SaveRecord button is clicked?
I was thinking of a "undo" button that clears the previous new entry
in 2nd subform and a "delete" button that deletes any record in 2nd
subform. But I don't know what code would do these jobs. Any
suggestion is much appreciated

Tell you what -- first try out the new code and observe the behavior,
and then come back and tell me what you'd like to add. There are lots
of ways this could work, but let's make sure we're starting from the
same place.

 

[Guest]

Yes. I'd like to make use of the SaveRecord button because that way

Here's example code. This would be in the module of the data-entry
subform -- the "top" subform". There's a command button named "cmdSave"
on that subform. The second subform -- the one that displays all the
records that have already been entered -- is named "sfSubList".

'----- start of subform module code -----
Option Compare Database
Option Explicit

Dim mfSave As Boolean

Private Sub cmdSave_Click()

If Me.Dirty Then
mfSave = True
Me.Dirty = False
RunCommand acCmdRecordsGoToNew
End If

End Sub


Private Sub Form_AfterUpdate()
Me.Parent!sfSubList.Requery
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = Not mfSave
mfSave = False

End Sub
'----- end of module code -----

Please excuse my limited knowledge with Access. I've created a module (named
Module1 as default) with the code you supply. Because the cmbSave button is
placed on the main form, how do I assign the module to the cmdSave button?

 

[Dirk Goldgar]

Please excuse my limited knowledge with Access. I've created a module
(named Module1 as default) with the code you supply.

No, it was a mistake to create a standard module for this. The code I
supplied was supposed to go in the module of the first, data-entry,
subform.

Because the
cmbSave button is placed on the main form, how do I assign the module
to the cmdSave button?

This is another, more severe problem. The Save button *cannot* be on
the main form. That is because, once you modify the current record on
the subform, you can't set the focus back to the main form (to click the
Save button, for example) without saving the subform record. Access
just won't do it. But our code is not letting Access save the record
unless the button is clicked, so Access won't let the focus go to the
main form, so you can't click the button, so the record can't be saved,
so the focus can't go to the main form ...

The cmdSave command button must be on the subform.

 

[Guest]

Here's example code. This would be in the module of the data-entry

subform -- the "top" subform". There's a command button named "cmdSave"
on that subform. The second subform -- the one that displays all the
records that have already been entered -- is named "sfSubList".

'----- start of subform module code -----
Option Compare Database
Option Explicit
Dim mfSave As Boolean

Private Sub cmdSave_Click()
If Me.Dirty Then
mfSave = True
Me.Dirty = False
RunCommand acCmdRecordsGoToNew
End If
End Sub

Private Sub Form_AfterUpdate()
Me.Parent!sfSubList.Requery
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = Not mfSave
mfSave = False
End Sub
'----- end of module code -----

The cmdSave command button must be on the subform.

Exactly Thanks

Tell you what -- first try out the new code and observe the behavior,
and then come back and tell me what you'd like to add. There are lots
of ways this could work, but let's make sure we're starting from the
same place.

The data-entry subform now works great. The 2nd subform (sfSubList) is in
datasheet view --with AllowAdditions = No, so user is forced to use the
data-entry subform for adding record.

Because I'd like all toolbars to be hidden (and have done so). I'm hoping to
have a cmdUndo that undo the last input (just like the default undo) and a
"delete" button that deletes any selected record in 2nd subform. How code
does the jobs? Can buttons be placed in main form (because they won't display
in datasheet view)?

 

[Dirk Goldgar]

The data-entry subform now works great.
Excellent!

The 2nd subform (sfSubList)
is in datasheet view --with AllowAdditions = No, so user is forced to
use the data-entry subform for adding record.

Because I'd like all toolbars to be hidden (and have done so). I'm
hoping to have a cmdUndo that undo the last input (just like the
default undo) and a "delete" button that deletes any selected record
in 2nd subform. How code does the jobs? Can buttons be placed in main
form (because they won't display in datasheet view)?

A Delete button shouldn't be a problem, but I'm not sure about the Undo
button. I can think of several different circumstances in which the
user might want to "undo":

1. After beginning to enter a record on the data-entry subform, but
before saving it, the user might decide to discard this unsaved record.
In this case, at least, the Undo button would have to be on the subform,
not the main form. Note that the user can press the {Esc} key to
undo -- first the current control, then the form.

2. After saving a record from the data-entry subform, the user might
want to "undo" it. In this case, undoing the record would require
deleting the most recently saved record. The command button to
accomplish this could be on the main form or the subform; it doesn't
matter.

3. While editing a record on the "list" subform (since it allows edits),
the user might want to undo the changes made, before the record is
saved. This is a true "undo" operation, but there's a problem with
using a button for it. The button can't be on the subform, if the
subform is in datasheet view, because the button won't be visible. But
the button can't be on the main form, because transferring the focus to
the main form will force the subform to save its record, and so it won't
be "undoable" any more. Working around this obstacle will be
complicated. Again, note that the user can press the {Esc} key once or
twice to undo the changes made to the current record.

4. After editing a record on the "list" subform and saving it, either
explicitly or by sending the focus to the main form or to the data-entry
subform, the user might want to undo the changes that he made (and
saved) and restore the record to the values it had before it was edited.
The only way to do this would be to record the pre-edit field values of
the most recently edited record, saving them in a collection perhaps,
before the record is saved. Then it would be possible for code behind a
button to update that record and set it back to its original values. As
you can see, this is not a trivial business.

5. After deleting a record on the "list" subform, the user might want to
restore it. Handling this would be similar to restoring a saved record
to its pre-edit values.

So, seeing what's involved here, it's up to you to decide which, if any,
of these various "undo" features you want to provide. The code could
get pretty elaborate for some of them, and I don't think I have the free
time to write it all for you.

 

[Guest]

The 2nd subform (sfSubList)

A Delete button shouldn't be a problem

I had the Delete button working now, thanks

but I'm not sure about the Undo button.
I can think of several different circumstances in which the
user might want to "undo":

1. After beginning to enter a record on the data-entry subform, but
before saving it, the user might decide to discard this unsaved record.
In this case, at least, the Undo button would have to be on the subform,
not the main form. Note that the user can press the {Esc} key to
undo -- first the current control, then the form.

2. After saving a record from the data-entry subform, the user might
want to "undo" it. In this case, undoing the record would require
deleting the most recently saved record. The command button to
accomplish this could be on the main form or the subform; it doesn't
matter.

3. While editing a record on the "list" subform (since it allows edits),
the user might want to undo the changes made, before the record is
saved. This is a true "undo" operation, but there's a problem with
using a button for it. The button can't be on the subform, if the
subform is in datasheet view, because the button won't be visible. But
the button can't be on the main form, because transferring the focus to
the main form will force the subform to save its record, and so it won't
be "undoable" any more. Working around this obstacle will be
complicated. Again, note that the user can press the {Esc} key once or
twice to undo the changes made to the current record.

4. After editing a record on the "list" subform and saving it, either
explicitly or by sending the focus to the main form or to the data-entry
subform, the user might want to undo the changes that he made (and
saved) and restore the record to the values it had before it was edited.
The only way to do this would be to record the pre-edit field values of
the most recently edited record, saving them in a collection perhaps,
before the record is saved. Then it would be possible for code behind a
button to update that record and set it back to its original values. As
you can see, this is not a trivial business.

5. After deleting a record on the "list" subform, the user might want to
restore it. Handling this would be similar to restoring a saved record
to its pre-edit values.

Wow, you're right! I never thought there would be this many undo actions
required. I think I'd need to either have them all or not to have any at all.

So, seeing what's involved here, it's up to you to decide which, if any,
of these various "undo" features you want to provide. The code could
get pretty elaborate for some of them, and I don't think I have the free
time to write it all for you.

Thanks for your help so far. It's been extremely valuable. Much appreciated

 

[Guest]

I had a second thought about the undo situations you pointed out and decide
to provide the users with undo option 1 and 2 only, and simply inform them to
amend the "list" subform right first time -- because options 3-5 are just too
far beyond my competence at this stage

I have managed to make option 1 working. Just wonder if I could please get
some help with option 2? Thanks

2. After saving a record from the data-entry subform, the user might
want to "undo" it. In this case, undoing the record would require
deleting the most recently saved record. The command button to
accomplish this could be on the main form or the subform; it doesn't
matter.

--- The followings are from last post ---