Form_Current event and Me.NewRecord

[Jac Tremblay]

Hi,
I coded the Form_Current event procedure so that when the user clicks on the
NewRecord button on the navigation bar, an information message is displayed.
Everything works fine so far.
My parent form contains a child subform that also displays a navigation bar.
Now, I want the same kind of code to be executed when the user clicks on the
New record button on that child navigation bar. Naturally, I tried the
subform's current event procedure but the code doesn't execute as expected.
Where should I put the code so that it executes properly?
Thanks.

 

[Marshall Barton]

I coded the Form_Current event procedure so that when the user clicks on the
NewRecord button on the navigation bar, an information message is displayed.
Everything works fine so far.
My parent form contains a child subform that also displays a navigation bar.
Now, I want the same kind of code to be executed when the user clicks on the
New record button on that child navigation bar. Naturally, I tried the
subform's current event procedure but the code doesn't execute as expected.
Where should I put the code so that it executes properly?

The Current event is the right place. Maybe there's
something else wrong with your code??

 

[Jac Tremblay]

Hi Marshall,
That's exactly what I thought but it doesn't work when I put the code in the
subform's current event. This subform contains another subform, a subsubform,
where I tried the current event and, surprise, it worked. I do not understand
something: the subsubform where I put the code does not have the navigation
buttons displayed while the subform does.
What's wrong?

 

[Marshall Barton]

That's exactly what I thought but it doesn't work when I put the code in the
subform's current event. This subform contains another subform, a subsubform,
where I tried the current event and, surprise, it worked. I do not understand
something: the subsubform where I put the code does not have the navigation
buttons displayed while the subform does.

I can't tell what might be wrong without at least seeing the
code you are trying to use. It seems to work correctly in
some places, so maybe there's something different about the
form where it doesn't work.

Actually, I don't see where you explained what it is doing.
All you've said is that "it doesn't work". The navigation
buttons don't have anything to do with the code working or
not, they just provide a convenient way to move around the
records. Are you sure the subform is bound to a record
source so there is a record to trigger the Current event?
Are you sure that you are actually getting to the new
record?

 

[Jac Tremblay]

Hi Marshall,
What I am doing with the code is as follows (see code below).
I have a parent form with the navigation bar displayed. The records in that
system can only be inserted from occurrences in a DB2 system. The user cannot
type in some new record. So there is a special button (Search) that allows
this operation.
But when the user clicks on the New record button on the navigation bar, I
want to display a message and return to the last record. My code works fine
in the current event of thay main form.
Now, on that main form, there is a subform with the children displayed.
Those children must also be inserted via a special procedure. So I added a
button (New follow up) to do that job. So when the user clicks on the new
record button on the child form, I also want to display a message and display
the last record. This operation works fine now but it didn't work when the
code was in its current event procedure.
Since this subform contains another level of child (grand-children), I tried
to put the code in that (sub)subform and it worked (with minor adjustments).
On the subform, the navigation buttons are displayed, but on the
(sub)subform, they are not.
Here is the code for both procedures:
' **********************************************************
Private Sub Form_Current()
' The main form, the parent (actually the grand-parent).
Dim rst As Recordset
If Me.NewRecord Then
strMsg = "No insert possible with this button." & _
vbCrLf & "Use the Search button in the top of the form."
intStyle = vbExclamation + vbOKOnly
intRéponse = MsgBox(strMsg, intStyle, strcTitre)
Set rst = Me.RecordsetClone
With rst
..MoveLast
Me.Bookmark = .Bookmark
..Close
End With
Set rst = Nothing
End If
End Sub
' **********************************************************
Private Sub Form_Current()
' The child form inserted in the child (actually the grand-child).
Dim rst As Recordset
If Me.Parent.NewRecord Then
strMsg = "No insert possible with this button." & _
vbCrLf & "Use the New follow up button in the top of the form."
intStyle = vbExclamation + vbOKOnly
intRéponse = MsgBox(strMsg, intStyle, strcTitre)
Set rst = Me.Parent.RecordsetClone
With rst
..MoveLast
Me.Parent.Bookmark = .Bookmark
..Close
End With
Set rst = Nothing
End If
End Sub
' **********************************************************
Hope this clarifies the situation for you. Right now, it works so I do not
need to correct anything.
The code is actually written in french. I changed some messages to make it
clearer.
Thanks for your concern and comments.

 

[Marshall Barton]

The first thing that jumps out at me is that you are trying
to close the RecordsetClone. I don't know what this may or
may not do, but it is certainly wrong. Since you did not
open that recordset, you can not close it.

The subform code is trying to manipulating its parent's
records, which doesn't make sense to me. Actually, I would
think it would be better all the way around if you just set
all these form's AllowAdditions property to No. This will
disable the new record button on the navigation bar and you
can do away with all these complications.

If the data is read only, you may want to set the AllowEdits
and and AllowDeletes properties to No as well.

 

[Jac Tremblay]

Hi Marshall,
I have not been working on that project for a while. Now, I get back to it.
You are right about the closing of the RecordsetClone which is useless. But
that is certainly not the problem.

Actually, I would think it would be better all the way around if you just set
all these form's AllowAdditions property to No. This will disable the new record >button on the navigation bar and you can do away with all these complications.

That seems to me like a very good idea.

If the data is read only, you may want to set the AllowEdits and and AllowDeletes >properties to No as well.

That as well is good thinking. I will try those options as soon as I can.
Thank you for your comment; I appreciate.
--
Jac Tremblay

The first thing that jumps out at me is that you are trying
to close the RecordsetClone. I don't know what this may or
may not do, but it is certainly wrong. Since you did not
open that recordset, you can not close it.

The subform code is trying to manipulating its parent's
records, which doesn't make sense to me. Actually, I would
think it would be better all the way around if you just set
all these form's AllowAdditions property to No. This will
disable the new record button on the navigation bar and you
can do away with all these complications.

If the data is read only, you may want to set the AllowEdits
and and AllowDeletes properties to No as well.
--
Marsh
MVP [MS Access]

What I am doing with the code is as follows (see code below).
I have a parent form with the navigation bar displayed. The records in that
system can only be inserted from occurrences in a DB2 system. The user cannot
type in some new record. So there is a special button (Search) that allows
this operation.
But when the user clicks on the New record button on the navigation bar, I
want to display a message and return to the last record. My code works fine
in the current event of thay main form.
Now, on that main form, there is a subform with the children displayed.
Those children must also be inserted via a special procedure. So I added a
button (New follow up) to do that job. So when the user clicks on the new
record button on the child form, I also want to display a message and display
the last record. This operation works fine now but it didn't work when the
code was in its current event procedure.
Since this subform contains another level of child (grand-children), I tried
to put the code in that (sub)subform and it worked (with minor adjustments).
On the subform, the navigation buttons are displayed, but on the
(sub)subform, they are not.
Here is the code for both procedures:
' **********************************************************
Private Sub Form_Current()
' The main form, the parent (actually the grand-parent).
Dim rst As Recordset
If Me.NewRecord Then
strMsg = "No insert possible with this button." & _
vbCrLf & "Use the Search button in the top of the form."
intStyle = vbExclamation + vbOKOnly
intRéponse = MsgBox(strMsg, intStyle, strcTitre)
Set rst = Me.RecordsetClone
With rst
.MoveLast
Me.Bookmark = .Bookmark
.Close
End With
Set rst = Nothing
End If
End Sub
' **********************************************************
Private Sub Form_Current()
' The child form inserted in the child (actually the grand-child).
Dim rst As Recordset
If Me.Parent.NewRecord Then
strMsg = "No insert possible with this button." & _
vbCrLf & "Use the New follow up button in the top of the form."
intStyle = vbExclamation + vbOKOnly
intRéponse = MsgBox(strMsg, intStyle, strcTitre)
Set rst = Me.Parent.RecordsetClone
With rst
.MoveLast
Me.Parent.Bookmark = .Bookmark
.Close
End With
Set rst = Nothing
End If
End Sub
' **********************************************************
Hope this clarifies the situation for you. Right now, it works so I do not
need to correct anything.
The code is actually written in french. I changed some messages to make it
clearer.
Thanks for your concern and comments.