AddNew button on the Record selector

[Guest]

Hi,
On my form, I had to create a personalized AddNew button because I need some
special calculations to be done when that event happens.
I need the record selector to be displayed for the user to navigate between
records (First, Previous, Next and Last).
But the record selector has an AddNew button also that does not do the same
thing as my personalized one. And that causes me a problem.
I would like to keep the record selector but disable the AddNew button or
get the button to run the procedure I wrote for the AddNew record.
Is either solution possible?
Many thanks

 

[Marshall Barton]

On my form, I had to create a personalized AddNew button because I need some
special calculations to be done when that event happens.
I need the record selector to be displayed for the user to navigate between
records (First, Previous, Next and Last).
But the record selector has an AddNew button also that does not do the same
thing as my personalized one. And that causes me a problem.
I would like to keep the record selector but disable the AddNew button or
get the button to run the procedure I wrote for the AddNew record.

It sounds like you are talking about the Navigation buttons
at the bottom of the form, not the Record Selector button at
the left of each record.

The New Record navigation button just takes you to a new
record, it doesn't create a new record. That happens when
the record is saved by navigating to a different record or
several other actions.

You can run your own procedure to set values in a new record
by putting your code in the Form's Current event procedure:

If Me.NewRecord Then
' your code
End If

without creating your own button or trying to fudge the
navigation buttons.

Note that if a user accidently navigates to a new record and
your code sets the value of some bound controls, the user
needs to hit the Esc key twice (to back out of the changes
you made) before navigating to a different record.

 

[Guest]

Hi Marsh,
You are right about the Navigation button instead of the Record Selector
button.
I will try your "If Me.NewRecord Then" solution and see if it works. It
should.
But the thing is that the addNew applies to the children on the subMain
form. I guess it will work as well if I put the code in the right place.
I will let you know if I can't figure it out.
Thank you again. I really appreciate.

 

[Guest]

Hi again Marsh,
I tried to try your solution, but cannot even figure out where to put the
code you're suggesting:
' ****************
If Me.NewRecord Then
' your code
End If
' ****************
The problem is that I have a parent form with a child form in it. For the
parent, it is impossible to insert a new record except through the special
home built insert procedure I wrote. That part works fine.
The problem happens when the user wants to insert a child record. For that
part, which uses the personalized procedure, everything works fine except for
a couple of details. The first one is that if the user clicks on the AddNew
button on the navigation bar on the child form, my procedure does not execute
and trouble starts.
The second (and last) problem is that when the user uses my personalized
button on the main form, I cannot get the display to show the last child
record (see the next post on this newsgroup), actually the new one that has
just been inserted even though I can create a new child record, calculate
some value based on the value the last child record has, and activate the
next field on the new record.
You suggest that I put my code in the "Form's Current event procedure" and
use the "Me." construct. That is not possible because the Me refers to the
parent and I am inserting into the child table. And also, I cannot find any
Current event procedure on the main form. I maybe wrong but I can't see how
to solve these problems.
Hope this will clarify the problem I have and eventually help in finding a
proper solution.
Thank you again for your concern.

 

[Guest]

Hi again, Marsh,
I found out something interesting. I tried this code from the help file to
list all open forms and, for each one, all the controls they hold:
' ***********************************
Dim frm As Form, ctl As Control
Dim intNbFrm As Integer
intNbFrm = Forms.Count
MsgBox "There is actually " & intNbFrm & " from(s) open."
' List the Forms collection.
For Each frm In Forms
' Print the name of the form.
Debug.Print "Form name: "; frm.Name
' List the Controls collection for each form.
For Each ctl In frm.Controls
' Print the name of each control.
Debug.Print "Control name >>> "; ctl.Name
Next ctl
Next frm
' ***********************************
That code shows that there is only one form open and that the subform it
holds is considered to be a control. That is why I cannot access it as a from.
What can I do now with that control? Can I still go to it's last record or
use methods or properties that apply to forms?
Thanks again.

 

[Marshall Barton]

Hi again, Marsh,
I found out something interesting. I tried this code from the help file to
list all open forms and, for each one, all the controls they hold:
' ***********************************
Dim frm As Form, ctl As Control
Dim intNbFrm As Integer
intNbFrm = Forms.Count
MsgBox "There is actually " & intNbFrm & " from(s) open."
' List the Forms collection.
For Each frm In Forms
' Print the name of the form.
Debug.Print "Form name: "; frm.Name
' List the Controls collection for each form.
For Each ctl In frm.Controls
' Print the name of each control.
Debug.Print "Control name >>> "; ctl.Name
Next ctl
Next frm
' ***********************************
That code shows that there is only one form open and that the subform it
holds is considered to be a control. That is why I cannot access it as a from.
What can I do now with that control? Can I still go to it's last record or
use methods or properties that apply to forms?

You're losing me here. Are you saying that you have to run
a special procedure for a new record on the main form and
another procedure for a new record on the subform? Since
this is a fairly unusual thing to do. I am having trouble
figuring out why and what this is all about.

As far as adressing the subform. As you have seen, a
subform is not in the list of forms that are open in the
Access window. A form object that is displayed in a subform
control is associated with the subform control. From code
in the main form's module, you can address the subform
object's properties through the subform control:
Me.subformcontrol.Form.propertyname

You would get to the subform's last record using the same
kind of reference along with the same logic you used on the
main form:
With Me.subformcontrol.Form.RecordsetClone
If .RecordCount > 0 Then
.MoveLast
Me.subformcontrol.Form.Bookmark = .Bookmark
End If
End With

The subform's Current event is specified in the form used in
the subform control's SourceObject property. In other
words, just open that form in design view and work on it
like any other form.

 

[Guest]

Hi again Marshall,
Your answer made me very happy because it answered two questions I posted at
the same time.
You say I've lost you. Well, I didn't mean it. I will explain the situation
to make you feel better... And if you read over the first question and the
other ones I posted afterwards, you will realize that I was never out of
track nor mixed up.
My system is exactly the same as a Client-Order system with a main form
displaying the client and a subform displaying the orders and their line
orders (in a subsubform).
My clients must be imported from a DB2 database via ODBC (if they are not
already in the local database). That is why I built a special procedure for
them.
As for the orders, I have to calculate and display the order number
according to the number of orders that client already has and some other
information.
That is why I put a special button on the Header's form to add a new order
so that the order number would be automatically calculated and displayed.
I did not do that for fun but because the client asked for it.

Now, with your answer, very pertinent I have to admit, I could figure out
how to display the new order I calculated the number for. That makes my day.
And I really thank you for it.
For that answer, you introduced two important properties: the
".RecordsetClone" and the ".Bookmark". Those are not evident, you must admit.
Now I have one more question if you don't mind:
If I want to place the cursor in a specific field (the EvalDate field) and I
write:
Me.frmSubMain.Form.Controls("EvalDate").SetFocus
Why doesn't it go there? What's wrong with that code?
Thanks you again, a thousand times, and a have a nice night.

 

[Marshall Barton]

See comments inline below.

Your answer made me very happy because it answered two questions I posted at
the same time.
You say I've lost you. Well, I didn't mean it. I will explain the situation
to make you feel better... And if you read over the first question and the
other ones I posted afterwards, you will realize that I was never out of
track nor mixed up.
My system is exactly the same as a Client-Order system with a main form
displaying the client and a subform displaying the orders and their line
orders (in a subsubform).
My clients must be imported from a DB2 database via ODBC (if they are not
already in the local database). That is why I built a special procedure for
them.

OK, this makes sense to me now. You are essentially doing a
search for the client and possible copying the client's
data. You can control the user's ability to get to a new
record by manipulating the form's AllowAdditions property or
by an elaborate set of flags to coordinate the button and
the Current event. I think I saw another thread where you
have this part working.

As for the orders, I have to calculate and display the order number
according to the number of orders that client already has and some other
information.
That is why I put a special button on the Header's form to add a new order
so that the order number would be automatically calculated and displayed.
I did not do that for fun but because the client asked for it.

This is different from the new client situation you
described above. Here, all you want to do is calculate the
appropriate order number and it must be done for every new
order whether you have a button or not. The common approach
for this kind of thing is to use the form's BeforeUpdate
event. This minimizes the time between calculating the
number and saving it to the table down to a few
milliseconds. If that time window is open too long, it's at
least conceivable that another user would also be
calculating an order number and both getting the same
number. The code in the BeforeUpdate event would be much
the same as I posted earlier:
If Me.NewRecord Then
' calculate new order number
End If

If there is no chance that another user could clash, then
you may prefer to use the BeforeInsert event. This has the
somewhat dubious benefit of displaying the order number as
soon as the user types the first character in to the new
record. However, if the user stars to create a new order,
but then goes off to lunch, the time window could be
measured in hours. The code in the BeforeInsert event would
not need to test for a new record because the event only
happens for new records.

Having a button to trigger this activity, could just provide
more opportunities for users to make mistakes and leaves the
time window open even longer.

Now, with your answer, very pertinent I have to admit, I could figure out
how to display the new order I calculated the number for. That makes my day.
And I really thank you for it.
For that answer, you introduced two important properties: the
".RecordsetClone" and the ".Bookmark". Those are not evident, you must admit.
Now I have one more question if you don't mind:
If I want to place the cursor in a specific field (the EvalDate field) and I
write:
Me.frmSubMain.Form.Controls("EvalDate").SetFocus
Why doesn't it go there? What's wrong with that code?

Don't ask me to explain why Access does it that way ;-)
For whatever reason, you need to set the focus to the
subform control before setting it to a control in the
subform.

Me.frmSubMain.SetFocus
Me.frmSubMain.Form.EvalDate.SetFocus

 

[Guest]

Hi again Marshall,
You make my day again. I take precious note of your comments. I can't change
too many things because the time allowed to finish the job is running out and
I must deliver.
Your comments have been very helpful and I thank you a thousand times.

 

[Guest]

Hi Marshall,
I tried your suggestion to add some code to the Form_Current event procedure
and it works fine. If the user clicks on the New record button, an
information message is displayed and the last record is displayed.
Now, on my form, I have a subform and I want the same kind of action to be
done when the user clicks on the child's Add New Record button but it does
not work. The procedure does not execute at all.
Would you, or anyone, know what could be the problem?
Thanks

 

[Guest]

Hi me,
I thought I would help the community in commenting my first post.
The easiest solution to this problem is to set the form's Allow additions
property to No. That way, navigation is possible but the Add new record
button is disabled.
I found that idea from different comments from Marshall Barton among others.
Thanks to all.