I have form which has a subform that serves as like a check register, and has
one control for entering deposits and another for entering withdrawals. The
main form had three controls for calculating the totals of each accounts
deposits and withdrawals entered into the subform, and calculating the
balance of each trasaction. I have a macro that "runs" each time an account
transaction is made on the subform that will "check" the balance. This macro
is set up with the following: 1) a GoToControl to get the focus on the
control on the main form where the balance is calculated, 2) SendKeys with
the KeyStrokes {F9} and Wait as Yes to give the calculation time to happen,
3) GoToControl to return focus back to the subform.
What would serve as a better alternative to that SendKeys action?
Thanks,
RC

On Mon, 16 Jul 2007 08:38:07 -0700, RC <(E-Mail Removed)> wrote:

>What would serve as a better alternative to that SendKeys action?

Me.Recalc


John W. Vinson [MVP]

Thanks John that looks like it will work as an alternative to that SendKeys
action I had been using, that is, if I can now get an issue resolved here
where to focus is now always returning to the first Account record in the
subform instead of the Account record I'm working with. This issue is
something new that has shown up in the "revision" I've made to the little
accounting application, and, from what I had read, I thought that the
SendKeys action I had been using in the "original" application was now
finally showing up as being unstable in the "revised" application.
In the "original" application, in the subform, where the account activity
takes place, the AfterUpdate for the Debit field or for the Credit each runs
a macro that used the GoToControl only to send the focus to the calculated
Balance control on the main form. The SendKeys was used only to allow the
calculation to take place before the macro executed a second GoToControl
action to return the focus back to the subform, and then a third GoToControl
action to set the focus in the next field in the same record to where the
last data entry was made. When on Lost Focus for the Balance field another
macro ran with conditions set to evaluate whether the calculation was less
than or equal to zero, and returned a message by a message box if so.
Using the code you provided appears to work fine as a replacement to that
SendKeys action, yet the focus is still being returned to the first account
record instead of the record where the entries are being made. All else
appears to be the same as in the "original" application that is working ---
any ideas of what could be the cause?
Thanks again,
RC

"John W. Vinson" wrote:

> On Mon, 16 Jul 2007 08:38:07 -0700, RC <(E-Mail Removed)> wrote:
>
> >What would serve as a better alternative to that SendKeys action?
>
> Me.Recalc
>
>
> John W. Vinson [MVP]
>

To return to the 'working' record.....
I don't have the code details at hand, but in general, before the .recalc,
save the primary key to a variable. After the .recalc, do a
me.recordsetclone.findfirst with the primary key = variable as the
criterium.

HTH, UpRider

"RC" <(E-Mail Removed)> wrote in message
news:5183BB70-9A2B-4611-B36B-(E-Mail Removed)...
> Thanks John that looks like it will work as an alternative to that
> SendKeys
> action I had been using, that is, if I can now get an issue resolved here
> where to focus is now always returning to the first Account record in the
> subform instead of the Account record I'm working with. This issue is
> something new that has shown up in the "revision" I've made to the little
> accounting application, and, from what I had read, I thought that the
> SendKeys action I had been using in the "original" application was now
> finally showing up as being unstable in the "revised" application.
> In the "original" application, in the subform, where the account activity
> takes place, the AfterUpdate for the Debit field or for the Credit each
> runs
> a macro that used the GoToControl only to send the focus to the calculated
> Balance control on the main form. The SendKeys was used only to allow the
> calculation to take place before the macro executed a second GoToControl
> action to return the focus back to the subform, and then a third
> GoToControl
> action to set the focus in the next field in the same record to where the
> last data entry was made. When on Lost Focus for the Balance field another
> macro ran with conditions set to evaluate whether the calculation was less
> than or equal to zero, and returned a message by a message box if so.
> Using the code you provided appears to work fine as a replacement to that
> SendKeys action, yet the focus is still being returned to the first
> account
> record instead of the record where the entries are being made. All else
> appears to be the same as in the "original" application that is
> working ---
> any ideas of what could be the cause?
> Thanks again,
> RC
>
> "John W. Vinson" wrote:
>
>> On Mon, 16 Jul 2007 08:38:07 -0700, RC <(E-Mail Removed)>
>> wrote:
>>
>> >What would serve as a better alternative to that SendKeys action?
>>
>> Me.Recalc
>>
>>
>> John W. Vinson [MVP]
>>

On Mon, 16 Jul 2007 13:08:01 -0700, RC <(E-Mail Removed)> wrote:

>Thanks John that looks like it will work as an alternative to that SendKeys
>action I had been using, that is, if I can now get an issue resolved here
>where to focus is now always returning to the first Account record in the
>subform instead of the Account record I'm working with.

That sounds like you're Requerying the form (rather than, or instead of,
recalculating it). Could you post your code?

John W. Vinson [MVP]

Here's what I had.
The After Update event for the Debit field in the subform, Check_Register
Subform, executes a macro with the following actions:
1.) GoToControl --- Control - Balance (a calculated control in the footer of
the main form, Accounts)
2.) SendKeys --- KeyStrokes: {F9} and Wait; Yes
3.) GoToControl --- Control - Check_Register Subform
4.) GoToControl --- Control - CheckNum

The same was set up for the Credit field in that subform.
This worked fine in the "original" application, but in the "revised"
application, which is almost a "carbon copy" of the "original", the focus
always winds up in the CheckNum field in the first record.

Here's what I've attempted with the Me.Recalc code.
1.) The AfterUpdate event in the Debit field executes the same macro, except
the SendKeys action has been removed.
2. When the GoToControl --- Control - Balance executes the OnGotFocus event
of the Balance field runs the code below:

Private Sub Balance_GotFocus()
Me.Recalc
End Sub

3.) The second GoToControl --- Control - Check_Register Subform executes

4.) The third GoToControl --- Control - CheckNum executes

With the actions and codes set up in either of the two ways listed above
inside the "revised" application , the focus is still being returned to the
CheckNum field in the first record. When the SendKeys action is removed from
the first "configuration" listed above, or the Me.Recalc code is removed from
the second "configuration" listed above next, then with either configuration
the focus will return to the CheckNum field in the last record.

I just tested the Me.Recalc code in the "original" application, and it works
fine there too, configured in the same way as that second configuration
listed above. Now to just figure out why the "revised" application is
behaving differently from the "original" and causing this particular issue.


"John W. Vinson" wrote:

> On Mon, 16 Jul 2007 13:08:01 -0700, RC <(E-Mail Removed)> wrote:
>
> >Thanks John that looks like it will work as an alternative to that SendKeys
> >action I had been using, that is, if I can now get an issue resolved here
> >where to focus is now always returning to the first Account record in the
> >subform instead of the Account record I'm working with.
>
> That sounds like you're Requerying the form (rather than, or instead of,
> recalculating it). Could you post your code?
>
> John W. Vinson [MVP]
>

Whew! I think I've got something that appears to be working now! I replaced
the macros in the AfterUpdate event for the Debit and Credit fields with the
following code, which runs as an Event Procedure.
Here is the code for the Debit field:
Private Sub Debit_AfterUpdate()
DoCmd.GoToControl "Balance"
Me.Recalc
DoCmd.GoToControl "Check_Register Subform"
DoCmd.GoToControl "CheckNum"
End Sub

And, here is the code for the credit field:

Private Sub Credit_AfterUpdate()
DoCmd.GoToControl "Balance"
Me.Recalc
DoCmd.GoToControl "Check_Register Subform"
DoCmd.GoToControl "Date"
End Sub

The On Lost Focus event for the cntrol named Balance runs a macro with a
MsgBox Action that has a condition that "checks" for balance less than or
equal to zero, and if that condition is true the message diplays. Should I
replace this by adding an If...Then statement into each of the codes above,
or leave well enough alone now that the "revised" app is working like it
should?


"RC" wrote:

> Here's what I had.
> The After Update event for the Debit field in the subform, Check_Register
> Subform, executes a macro with the following actions:
> 1.) GoToControl --- Control - Balance (a calculated control in the footer of
> the main form, Accounts)
> 2.) SendKeys --- KeyStrokes: {F9} and Wait; Yes
> 3.) GoToControl --- Control - Check_Register Subform
> 4.) GoToControl --- Control - CheckNum
>
> The same was set up for the Credit field in that subform.
> This worked fine in the "original" application, but in the "revised"
> application, which is almost a "carbon copy" of the "original", the focus
> always winds up in the CheckNum field in the first record.
>
> Here's what I've attempted with the Me.Recalc code.
> 1.) The AfterUpdate event in the Debit field executes the same macro, except
> the SendKeys action has been removed.
> 2. When the GoToControl --- Control - Balance executes the OnGotFocus event
> of the Balance field runs the code below:
>
> Private Sub Balance_GotFocus()
> Me.Recalc
> End Sub
>
> 3.) The second GoToControl --- Control - Check_Register Subform executes
>
> 4.) The third GoToControl --- Control - CheckNum executes
>
> With the actions and codes set up in either of the two ways listed above
> inside the "revised" application , the focus is still being returned to the
> CheckNum field in the first record. When the SendKeys action is removed from
> the first "configuration" listed above, or the Me.Recalc code is removed from
> the second "configuration" listed above next, then with either configuration
> the focus will return to the CheckNum field in the last record.
>
> I just tested the Me.Recalc code in the "original" application, and it works
> fine there too, configured in the same way as that second configuration
> listed above. Now to just figure out why the "revised" application is
> behaving differently from the "original" and causing this particular issue.
>
>
> "John W. Vinson" wrote:
>
> > On Mon, 16 Jul 2007 13:08:01 -0700, RC <(E-Mail Removed)> wrote:
> >
> > >Thanks John that looks like it will work as an alternative to that SendKeys
> > >action I had been using, that is, if I can now get an issue resolved here
> > >where to focus is now always returning to the first Account record in the
> > >subform instead of the Account record I'm working with.
> >
> > That sounds like you're Requerying the form (rather than, or instead of,
> > recalculating it). Could you post your code?
> >
> > John W. Vinson [MVP]
> >

On Tue, 17 Jul 2007 12:44:01 -0700, RC <(E-Mail Removed)> wrote:

>Whew! I think I've got something that appears to be working now! I replaced
>the macros in the AfterUpdate event for the Debit and Credit fields with the
>following code, which runs as an Event Procedure.
>Here is the code for the Debit field:
>Private Sub Debit_AfterUpdate()
> DoCmd.GoToControl "Balance"
> Me.Recalc
> DoCmd.GoToControl "Check_Register Subform"
> DoCmd.GoToControl "CheckNum"
>End Sub
>
>And, here is the code for the credit field:
>
>Private Sub Credit_AfterUpdate()
> DoCmd.GoToControl "Balance"
> Me.Recalc
> DoCmd.GoToControl "Check_Register Subform"
> DoCmd.GoToControl "Date"
>End Sub
>
>The On Lost Focus event for the cntrol named Balance runs a macro with a
>MsgBox Action that has a condition that "checks" for balance less than or
>equal to zero, and if that condition is true the message diplays. Should I
>replace this by adding an If...Then statement into each of the codes above,
>or leave well enough alone now that the "revised" app is working like it
>should?

Well, don't fix what ain't broke, I guess. If you're setting focus to a
subform control, the mainform record will be saved at that point; you could do
the balance checking in the main Form's BeforeUpdate event, and cancel the
event or just display a message if the balance is negative.

John W. Vinson [MVP]

Thanks John, I'll give your BeforeUpdate Event recommendation a try. Thanks
for all your help. All your recommendations helped in getting me on track to
get this app back to working again, and it now works in Windows Vista Home
Premium Edition too.

Thanks again,
RC

"John W. Vinson" wrote:

> On Tue, 17 Jul 2007 12:44:01 -0700, RC <(E-Mail Removed)> wrote:
>
> >Whew! I think I've got something that appears to be working now! I replaced
> >the macros in the AfterUpdate event for the Debit and Credit fields with the
> >following code, which runs as an Event Procedure.
> >Here is the code for the Debit field:
> >Private Sub Debit_AfterUpdate()
> > DoCmd.GoToControl "Balance"
> > Me.Recalc
> > DoCmd.GoToControl "Check_Register Subform"
> > DoCmd.GoToControl "CheckNum"
> >End Sub
> >
> >And, here is the code for the credit field:
> >
> >Private Sub Credit_AfterUpdate()
> > DoCmd.GoToControl "Balance"
> > Me.Recalc
> > DoCmd.GoToControl "Check_Register Subform"
> > DoCmd.GoToControl "Date"
> >End Sub
> >
> >The On Lost Focus event for the cntrol named Balance runs a macro with a
> >MsgBox Action that has a condition that "checks" for balance less than or
> >equal to zero, and if that condition is true the message diplays. Should I
> >replace this by adding an If...Then statement into each of the codes above,
> >or leave well enough alone now that the "revised" app is working like it
> >should?
>
> Well, don't fix what ain't broke, I guess. If you're setting focus to a
> subform control, the mainform record will be saved at that point; you could do
> the balance checking in the main Form's BeforeUpdate event, and cancel the
> event or just display a message if the balance is negative.
>
> John W. Vinson [MVP]
>