Re: AddNew button on the Record selector

See comments inline below.


Jac Tremblay wrote:
>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

--
Marsh
MVP [MS Access]
.