Am I missing something?

  • Thread starter Thread starter .Len B
  • Start date Start date
L

.Len B

I have a form with a subform.
The form is based on a query (single table, subset of fields).
The subform is based on a related table (the many side).
All standard stuff really.

The subform is a single form not continuous.
It has a cmdSave button whose Click event will save the detail record,
then clear the form and be ready to enter another detail record for
the same parent.

Here is the code I have -
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.GoToRecord , , acNewRec
Me.Parent.Requery 'do I really need this? Does it hurt?

I have a nagging feeling that I am overlooking something but I can't
put my finger on anything.

What have I missed? Am I worrying about nothing?
TIA
 
Looks okay, except that requerying the parent form will cause it to reload
(to its first record), so the next row you enter in the subform won't be for
the same record you were on. You probably want to drop that line.

Personally, I'd probably code the button like this:
If Me.Dirty Then Me.Dirty = False 'Save
If Not Me.NewRecord Then RunCommand acCmdRecordsGotoNew
 
DoCmd.GoToRecord , , acNewRec
Click to expand...

You only need the above one line of code, since access saves for you when
you navigate....


And, if you enable the navigation buttons, the you don't even need the
above....
 
Thanks Allen,
I couldn't get rid of that 'did I leave the iron on' feeling but I
couldn't figure what was missing either.

Does setting Dirty to False actually do a save? In my mind, Dirty
was a property rather than a method. Forever learning.

I'll get rid of the Requery. Maybe that's the iron I left on. Neither
the form or the subform has navigation buttons visible. The form is
launched from another form using your FindAsYouType technique so its
filter should ensure that it is the first(only) record anyway. Users
mustn't see other parent records and previous subform records must
be unalterable.

Thanks Allen for all you do. I love your site. I use your techniques
regularly. e.g. In this project I also use Highlight and the Splash
screen Version tool. Love 'em.

--
Len
______________________________________________________
remove nothing for valid email address.
| Looks okay, except that requerying the parent form will cause it to
reload
| (to its first record), so the next row you enter in the subform won't
be for
| the same record you were on. You probably want to drop that line.
|
| Personally, I'd probably code the button like this:
| If Me.Dirty Then Me.Dirty = False 'Save
| If Not Me.NewRecord Then RunCommand acCmdRecordsGotoNew
|
| --
| Allen Browne - Microsoft MVP. Perth, Western Australia
|
| Reply to group, rather than allenbrowne at mvps dot org.
|
|
| | > I have a form with a subform.
| > The form is based on a query (single table, subset of fields).
| > The subform is based on a related table (the many side).
| > All standard stuff really.
| >
| > The subform is a single form not continuous.
| > It has a cmdSave button whose Click event will save the detail
record,
| > then clear the form and be ready to enter another detail record for
| > the same parent.
| >
| > Here is the code I have -
| > DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
| > DoCmd.GoToRecord , , acNewRec
| > Me.Parent.Requery 'do I really need this? Does it hurt?
| >
| > I have a nagging feeling that I am overlooking something but I can't
| > put my finger on anything.
| >
| > What have I missed? Am I worrying about nothing?
| > TIA
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
|
 
Thanks Albert.
I have deliberately removed the navigation buttons from both form and
subform. Users mustn't see other parent records and previous subform
records, once saved, must be unalterable so I have a chkOk2Save box
on the subform. The Enter event of every txt... and cbo... on the
subform (re)sets it to False and cmdSave checks it is True before it
will do anything. That's why I thought I had to GoTo acNewRec to clear
the saved data to prevent editing once saved and, more importantly, to
offer an 'empty' subform to allow new detail data to be entered.

--
Len
______________________________________________________
remove nothing for valid email address.
| |
| > DoCmd.GoToRecord , , acNewRec
|
| You only need the above one line of code, since access saves for you
when
| you navigate....
|
|
| And, if you enable the navigation buttons, the you don't even need the
| above....
|
|
| --
| Albert D. Kallal (Access MVP)
| Edmonton, Alberta Canada
| (e-mail address removed)
|
|
 
Yes: setting Dirty to False does save the record, and generates a trappable
error if it can't be saved for any reason (e.g. a required field left
blank.)

The main reason I like that approach is that you can specify which form's
record gets saved -- even if it happens to be in the background. (The
RunCommand acCmdSaveRecord command and others like it save whatever is the
active form.)

As Albert says, moving record causes an implicit save, so it's not strictly
necessary. However, you can get some really weird errors if the save fails
and so the move record cannot take place. I find it easier and safer to
explicitly save, as it forces the event queue before you try to
move/filter/sort/close/whatever.

[The queue of events that need to be cleared include the events of the
active control (key strokes, Change. BeforeUpdate, AfterUpdate, Exit,
LostFocus...), the control that next gets focus (Got Focus, Enter) as well
as the form (BeforeUpdate, AfterUpdate, AfterInsert, ...) Forcing the save
clears these pending events.]
 
I like explicit. No error, job done, next!

I knew events fired in a known order but I never cottoned to there
being a queue. Learned some more. I'll have to think on that since
there must be implications to abandoning what the events would have
done. Or have I misunderstood you? Does your 'clear' mean abandon or
does the explicit save force the pending events to fire now rather
than later so that the save can occur?

--
Len
______________________________________________________
remove nothing for valid email address.
| Yes: setting Dirty to False does save the record, and generates a
trappable
| error if it can't be saved for any reason (e.g. a required field left
| blank.)
|
| The main reason I like that approach is that you can specify which
form's
| record gets saved -- even if it happens to be in the background. (The
| RunCommand acCmdSaveRecord command and others like it save whatever is
the
| active form.)
|
| As Albert says, moving record causes an implicit save, so it's not
strictly
| necessary. However, you can get some really weird errors if the save
fails
| and so the move record cannot take place. I find it easier and safer to
| explicitly save, as it forces the event queue before you try to
| move/filter/sort/close/whatever.
|
| [The queue of events that need to be cleared include the events of the
| active control (key strokes, Change. BeforeUpdate, AfterUpdate, Exit,
| LostFocus...), the control that next gets focus (Got Focus, Enter) as
well
| as the form (BeforeUpdate, AfterUpdate, AfterInsert, ...) Forcing the
save
| clears these pending events.]
|
| --
| Allen Browne - Microsoft MVP. Perth, Western Australia
|
| Reply to group, rather than allenbrowne at mvps dot org.
 
By clearing the queue, I meant processing (not abandoning) the events there.

Of course if one event does fail (e.g. the Validation Rule for the text box
fails), then the subsequent events won't be triggered.
 
Phew! I'm glad I've got that straight now. It just didn't sit right
to abandon.

This prompts another question (or 2).
Are there any other 'automatic' save triggers?
If there are, I'll need to trap them because I do not want a record
saved until the user sets chkOk2Save and clicks on cmdSave.
I did have a trap in the subform's BeforeUpdate but it would fire
if the memo was edited so I had to take it out. Is there a better
place to out it?

--
Len
______________________________________________________
remove nothing for valid email address.
| By clearing the queue, I meant processing (not abandoning) the events
there.
|
| Of course if one event does fail (e.g. the Validation Rule for the text
box
| fails), then the subsequent events won't be triggered.
|
| --
| Allen Browne - Microsoft MVP. Perth, Western Australia
|
| Reply to group, rather than allenbrowne at mvps dot org.
|
|
| | > I like explicit. No error, job done, next!
| >
| > I knew events fired in a known order but I never cottoned to there
| > being a queue. Learned some more. I'll have to think on that since
| > there must be implications to abandoning what the events would have
| > done. Or have I misunderstood you? Does your 'clear' mean abandon or
| > does the explicit save force the pending events to fire now rather
| > than later so that the save can occur?
| >
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
| > | > | Yes: setting Dirty to False does save the record, and generates a
| > trappable
| > | error if it can't be saved for any reason (e.g. a required field
left
| > | blank.)
| > |
| > | The main reason I like that approach is that you can specify which
| > form's
| > | record gets saved -- even if it happens to be in the background.
(The
| > | RunCommand acCmdSaveRecord command and others like it save whatever
is
| > the
| > | active form.)
| > |
| > | As Albert says, moving record causes an implicit save, so it's not
| > strictly
| > | necessary. However, you can get some really weird errors if the
save
| > fails
| > | and so the move record cannot take place. I find it easier and
safer to
| > | explicitly save, as it forces the event queue before you try to
| > | move/filter/sort/close/whatever.
| > |
| > | [The queue of events that need to be cleared include the events of
the
| > | active control (key strokes, Change. BeforeUpdate, AfterUpdate,
Exit,
| > | LostFocus...), the control that next gets focus (Got Focus, Enter)
as
| > well
| > | as the form (BeforeUpdate, AfterUpdate, AfterInsert, ...) Forcing
the
| > save
| > | clears these pending events.]
| > |
| > | --
| > | Allen Browne - Microsoft MVP. Perth, Western Australia
| > |
| > | Reply to group, rather than allenbrowne at mvps dot org.
| >
| >
| >
 
Form_BeforeUpate is the *only* way to catch all the possible ways a record
could be saved (e.g. moving to another record, closing the form, applying a
filter/sort, pressing Shift+Enter, using the toolbar/meny/ribbon, closing
Access, ...)

You should be able to code the event so it only triggers the warnings if
something was changed, e.g.:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
dim bWarn As Boolean
'Handle the required things first.
If IsNull(Me.Surname) Then
Cancel = True
strMsg =strMsg & "Surname required." & vbCrLf
End If
'and similarly for other required things.

If Not Cancel Then
With Me.SomeField
If (.Value = .OldValue) Or (IsNull(.Value) And
IsNull(.OldValue)) Then
'do nothing: it didn't change
Else
bWarn = True
strMsg = strMsg & "Confirm change to SomeField." & vbCrLf
End If
End With
'etc for other warnings

End If

If Cancel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc> to
undo."
MsgBox strMsg
ElseIf bWarn Then
strMsg = strMsg & vbCrLf & "REALLY?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Are you sure?") <>
vbYes Then
Cancel = True
End If
End If
End Sub
 
Thanks very much Allen. That's clear now. Wow, there are so many ways
a save can be triggered. But one final (I hope) point. Does the
Form_BeforeUpdate *only* fire when the record is about to be saved?
I thought not, so, is there a way to tell 'saving' apart?

I have another related issue.
In some circumstances after saving a detail record using
If Me.Dirty Then Me.Dirty = False 'Save
If Not Me.NewRecord Then RunCommand acCmdRecordsGotoNew
(the code you showed me), the subform reverts to showing the first
detail record. I don't want this behaviour. It should only show data
entry in progress or a blank ready for data.
Should I post this question as a new thread?

--
Len
______________________________________________________
remove nothing for valid email address.
| Form_BeforeUpate is the *only* way to catch all the possible ways a
record
| could be saved (e.g. moving to another record, closing the form,
applying a
| filter/sort, pressing Shift+Enter, using the toolbar/meny/ribbon,
closing
| Access, ...)
|
| You should be able to code the event so it only triggers the warnings
if
| something was changed, e.g.:ww
|
| Private Sub Form_BeforeUpdate(Cancel As Integer)
| Dim strMsg As String
| dim bWarn As Boolean
| 'Handle the required things first.
| If IsNull(Me.Surname) Then
| Cancel = True
| strMsg =strMsg & "Surname required." & vbCrLf
| End If
| 'and similarly for other required things.
|
| If Not Cancel Then
| With Me.SomeField
| If (.Value = .OldValue) Or (IsNull(.Value) And
| IsNull(.OldValue)) Then
| 'do nothing: it didn't change
| Else
| bWarn = True
| strMsg = strMsg & "Confirm change to SomeField." &
vbCrLf
| End If
| End With
| 'etc for other warnings
|
| End If
|
| If Cancel Then
| strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc>
to
| undo."
| MsgBox strMsg
| ElseIf bWarn Then
| strMsg = strMsg & vbCrLf & "REALLY?"
| If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Are you sure?")
<>
| vbYes Then
| Cancel = True
| End If
| End If
| End Sub
|
| --
| Allen Browne - Microsoft MVP. Perth, Western Australia
|
| Reply to group, rather than allenbrowne at mvps dot org.
 
a) Form_BeforeUpdate only fires if a save is about to take place.

b) Something else must be causing the subform to reload. That could be code
in its events (e.g. AfterUpdate, AfterInsert, Current), or the control's
events (e.g. GotFocus, Enter.) Or, it could be caused by requerying or
moving record on the main form.
 
þþßÊÈ ".Len B said:
Thanks very much Allen. That's clear now. Wow, there are so many ways
a save can be triggered. But one final (I hope) point. Does the
Form_BeforeUpdate *only* fire when the record is about to be saved?
I thought not, so, is there a way to tell 'saving' apart?

I have another related issue.
In some circumstances after saving a detail record using
If Me.Dirty Then Me.Dirty = False 'Save
If Not Me.NewRecord Then RunCommand acCmdRecordsGotoNew
(the code you showed me), the subform reverts to showing the first
detail record. I don't want this behaviour. It should only show data
entry in progress or a blank ready for data.
Should I post this question as a new thread?

--
Len
______________________________________________________
remove nothing for valid email address.
| Form_BeforeUpate is the *only* way to catch all the possible ways a
record
| could be saved (e.g. moving to another record, closing the form,
applying a
| filter/sort, pressing Shift+Enter, using the toolbar/meny/ribbon,
closing
| Access, ...)
|
| You should be able to code the event so it only triggers the warnings
if
| something was changed, e.g.:ww
|
| Private Sub Form_BeforeUpdate(Cancel As Integer)
| Dim strMsg As String
| dim bWarn As Boolean
| 'Handle the required things first.
| If IsNull(Me.Surname) Then
| Cancel = True
| strMsg =strMsg & "Surname required." & vbCrLf
| End If
| 'and similarly for other required things.
|
| If Not Cancel Then
| With Me.SomeField
| If (.Value = .OldValue) Or (IsNull(.Value) And
| IsNull(.OldValue)) Then
| 'do nothing: it didn't change
| Else
| bWarn = True
| strMsg = strMsg & "Confirm change to SomeField." &
vbCrLf
| End If
| End With
| 'etc for other warnings
|
| End If
|
| If Cancel Then
| strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc>
to
| undo."
| MsgBox strMsg
| ElseIf bWarn Then
| strMsg = strMsg & vbCrLf & "REALLY?"
| If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Are you sure?")
<>
| vbYes Then
| Cancel = True
| End If
| End If
| End Sub
|
| --
| Allen Browne - Microsoft MVP. Perth, Western Australia
|
| Reply to group, rather than allenbrowne at mvps dot org.
Click to expand...
 
Back
Top