Access VB Code not working correctly unless stepped through

[Guest]

Access 97
This code works correctly under most conditions. It does not work when in a
subform, a field is modified by one number, the Tab or Enter keys are not
used, and the subform is exited using Ctl-Tab.

Using an on-exit routine for the subform, the Visual Basic code does not
complete calculations. If a breakpoint is set and the code is stepped
through, everything works correctly.

 

[Ken Snell [MVP]]

You give us absolutely no information about what the code is or does, nor
what the meaning of "does not work" is, so what you describe can be answered
generically by "it's probably a timing issue" -- meaning that something that
the code initiates is not "completed" by the time you want to use the
results of it.

 

[Guest]

Sorry about that. Let me try to explain since I can duplicate this on any
form with a subform.

Trying to obtain a Bank Deposit total based upon a cash amount and the total
of personal checks received. The form contains box for todays date, cash
amount received, and bank deposit. It also contains a subform in datasheet
view based upon another form. This other form contains a description,
amount, and a subtotal in the footer.

Simply put when you enter the cash amount and all ther personal checks I
need a total in the Bank Deposit field.

This works fine 99% of the time.

Enter the Cash - OK.
Now in subform. Enter descript, tab to amount, enter amount, tab to next
descript, exit subform using Ctl-Tab - all OK! Bank deposit is correct amount.

Code(only code in this test database) from subform exit:

[BankDeposit] = Text16

Now for the real problem. Say an amount in the subform is $10.00 and you
want to change it to $20.00 quickly. Using the mouse, you highlight the 1
and replace it with a 2. Now without hitting enter or tab exit the subform
using Ctl-Tab and the Bank Deposit remains unchanged.

For a real kicker, I put a breakpoint at the one and online of code(above).
Using the same method to change the amount and exit the subform I stepped
into the line of code and now the Bank Deposit amount is correct!

I've yet to find out how to correct this. Thanks for any help.

 

[Guest]

Sorry again. Text12 is on the mainform. It is the subtotal from the
subform.

Sorry about that. Let me try to explain since I can duplicate this on any
form with a subform.

Trying to obtain a Bank Deposit total based upon a cash amount and the total
of personal checks received. The form contains box for todays date, cash
amount received, and bank deposit. It also contains a subform in datasheet
view based upon another form. This other form contains a description,
amount, and a subtotal in the footer.

Simply put when you enter the cash amount and all ther personal checks I
need a total in the Bank Deposit field.

This works fine 99% of the time.

Enter the Cash - OK.
Now in subform. Enter descript, tab to amount, enter amount, tab to next
descript, exit subform using Ctl-Tab - all OK! Bank deposit is correct amount.

Code(only code in this test database) from subform exit:

[BankDeposit] = Text16

Now for the real problem. Say an amount in the subform is $10.00 and you
want to change it to $20.00 quickly. Using the mouse, you highlight the 1
and replace it with a 2. Now without hitting enter or tab exit the subform
using Ctl-Tab and the Bank Deposit remains unchanged.

For a real kicker, I put a breakpoint at the one and online of code(above).
Using the same method to change the amount and exit the subform I stepped
into the line of code and now the Bank Deposit amount is correct!

I've yet to find out how to correct this. Thanks for any help.

You give us absolutely no information about what the code is or does, nor
what the meaning of "does not work" is, so what you describe can be answered
generically by "it's probably a timing issue" -- meaning that something that
the code initiates is not "completed" by the time you want to use the
results of it.

 

[Ken Snell [MVP]]

Yes, this is a code timing issue. When you move the focus from the subform
to the main form, the subform should save the record that you were editing
just before. However, this takes just a little bit of time, and it's very
possible that it hasn't saved the record and updated the subform's sum
textbox before the subform control's Exit event occurs.

I would not use the subform control's Exit event to run that code. Delete
the code from that event.

Instead, I would make the [BankDeposit] textbox in the main form be a
calculated control (not bound to a field; I am assuming that you don't need
to save this actual number from the summing textbox, as you can calculate it
again at any time). Change the ControlSource of this textbox to this:
=[NameOfSubformControl]![Text16]

This way, the textbox [BankDeposit] will automatically update with the
correct value when the summing textbox does finish its calculation. No code
needed at all.

--

Ken Snell
<MS ACCESS MVP>

Sorry about that. Let me try to explain since I can duplicate this on any
form with a subform.

Trying to obtain a Bank Deposit total based upon a cash amount and the
total
of personal checks received. The form contains box for todays date, cash
amount received, and bank deposit. It also contains a subform in
datasheet
view based upon another form. This other form contains a description,
amount, and a subtotal in the footer.

Simply put when you enter the cash amount and all ther personal checks I
need a total in the Bank Deposit field.

This works fine 99% of the time.

Enter the Cash - OK.
Now in subform. Enter descript, tab to amount, enter amount, tab to next
descript, exit subform using Ctl-Tab - all OK! Bank deposit is correct
amount.

Code(only code in this test database) from subform exit:

[BankDeposit] = Text16

Now for the real problem. Say an amount in the subform is $10.00 and you
want to change it to $20.00 quickly. Using the mouse, you highlight the 1
and replace it with a 2. Now without hitting enter or tab exit the
subform
using Ctl-Tab and the Bank Deposit remains unchanged.

For a real kicker, I put a breakpoint at the one and online of
code(above).
Using the same method to change the amount and exit the subform I stepped
into the line of code and now the Bank Deposit amount is correct!

I've yet to find out how to correct this. Thanks for any help.

You give us absolutely no information about what the code is or does, nor
what the meaning of "does not work" is, so what you describe can be
answered
generically by "it's probably a timing issue" -- meaning that something
that
the code initiates is not "completed" by the time you want to use the
results of it.

 

[Ken Snell [MVP]]

Text12? Your message wrote about Text16?

Is [BankDeposit] a field in the main form's recordsource query? Are you
wanting to store the value of the sum from the subform? If yes, then I would
use the form's BeforeUpdate event to write the value of the calculated
control (see my just sent message) into the [BankDeposit] field.
--

Ken Snell
<MS ACCESS MVP>

Sorry again. Text12 is on the mainform. It is the subtotal from the
subform.

Sorry about that. Let me try to explain since I can duplicate this on
any
form with a subform.

Trying to obtain a Bank Deposit total based upon a cash amount and the
total
of personal checks received. The form contains box for todays date, cash
amount received, and bank deposit. It also contains a subform in
datasheet
view based upon another form. This other form contains a description,
amount, and a subtotal in the footer.

Simply put when you enter the cash amount and all ther personal checks I
need a total in the Bank Deposit field.

This works fine 99% of the time.

Enter the Cash - OK.
Now in subform. Enter descript, tab to amount, enter amount, tab to next
descript, exit subform using Ctl-Tab - all OK! Bank deposit is correct
amount.

Code(only code in this test database) from subform exit:

[BankDeposit] = Text16

Now for the real problem. Say an amount in the subform is $10.00 and you
want to change it to $20.00 quickly. Using the mouse, you highlight the
1
and replace it with a 2. Now without hitting enter or tab exit the
subform
using Ctl-Tab and the Bank Deposit remains unchanged.

For a real kicker, I put a breakpoint at the one and online of
code(above).
Using the same method to change the amount and exit the subform I stepped
into the line of code and now the Bank Deposit amount is correct!

I've yet to find out how to correct this. Thanks for any help.

You give us absolutely no information about what the code is or does,
nor
what the meaning of "does not work" is, so what you describe can be
answered
generically by "it's probably a timing issue" -- meaning that something
that
the code initiates is not "completed" by the time you want to use the
results of it.

--

Ken Snell
<MS ACCESS MVP>


Access 97
This code works correctly under most conditions. It does not work
when in
a
subform, a field is modified by one number, the Tab or Enter keys are
not
used, and the subform is exited using Ctl-Tab.

Using an on-exit routine for the subform, the Visual Basic code does
not
complete calculations. If a breakpoint is set and the code is
stepped
through, everything works correctly.

 

[Guest]

Thanks, and sorry for the confusion between Text12 & Text16. I am using a
test database and trying different options.

I have previously tried calculated controls in this application and they
work fine. However, the actual application has four subforms, thousands of
records and many more subrecords. I am trying to store [BankDeposit] in the
main table (along with many other figures) for analysis purposes. To
recalculate creates a lot of overhead.

What I don't comprehend is why the code works correctly when I insert a
breakpoint and step through it.

Yes, this is a code timing issue. When you move the focus from the subform
to the main form, the subform should save the record that you were editing
just before. However, this takes just a little bit of time, and it's very
possible that it hasn't saved the record and updated the subform's sum
textbox before the subform control's Exit event occurs.

I would not use the subform control's Exit event to run that code. Delete
the code from that event.

Instead, I would make the [BankDeposit] textbox in the main form be a
calculated control (not bound to a field; I am assuming that you don't need
to save this actual number from the summing textbox, as you can calculate it
again at any time). Change the ControlSource of this textbox to this:
=[NameOfSubformControl]![Text16]

This way, the textbox [BankDeposit] will automatically update with the
correct value when the summing textbox does finish its calculation. No code
needed at all.

--

Ken Snell
<MS ACCESS MVP>

Sorry about that. Let me try to explain since I can duplicate this on any
form with a subform.

Trying to obtain a Bank Deposit total based upon a cash amount and the
total
of personal checks received. The form contains box for todays date, cash
amount received, and bank deposit. It also contains a subform in
datasheet
view based upon another form. This other form contains a description,
amount, and a subtotal in the footer.

Simply put when you enter the cash amount and all ther personal checks I
need a total in the Bank Deposit field.

This works fine 99% of the time.

Enter the Cash - OK.
Now in subform. Enter descript, tab to amount, enter amount, tab to next
descript, exit subform using Ctl-Tab - all OK! Bank deposit is correct
amount.

Code(only code in this test database) from subform exit:

[BankDeposit] = Text16

Now for the real problem. Say an amount in the subform is $10.00 and you
want to change it to $20.00 quickly. Using the mouse, you highlight the 1
and replace it with a 2. Now without hitting enter or tab exit the
subform
using Ctl-Tab and the Bank Deposit remains unchanged.

For a real kicker, I put a breakpoint at the one and online of
code(above).
Using the same method to change the amount and exit the subform I stepped
into the line of code and now the Bank Deposit amount is correct!

I've yet to find out how to correct this. Thanks for any help.

You give us absolutely no information about what the code is or does, nor
what the meaning of "does not work" is, so what you describe can be
answered
generically by "it's probably a timing issue" -- meaning that something
that
the code initiates is not "completed" by the time you want to use the
results of it.

--

Ken Snell
<MS ACCESS MVP>


Access 97
This code works correctly under most conditions. It does not work when
in
a
subform, a field is modified by one number, the Tab or Enter keys are
not
used, and the subform is exited using Ctl-Tab.

Using an on-exit routine for the subform, the Visual Basic code does
not
complete calculations. If a breakpoint is set and the code is stepped
through, everything works correctly.

 

[Ken Snell [MVP]]

The reason it works when you use a breakpoint and step through it is because
your "human, manual" stepping through of the code allows the record saving
operation to occur before you move to the next step. No human I know can
step through the code manually in the microseconds or milliseconds that it
takes VBA to do it on its own.

As I said, what you're seeing is a code timing issue. They are not uncommon,
and can be very vexing. If you don't want to use calculated controls, then
either rethink your setup/design to eliminate the possibility of the timing
issue occurring, or change the code so that the code step that absolutely
must wait until the subform record is saved "cannot" be run until something
happens that is triggered by the saving of the subform record, or write your
main form's code that is trying to use the value from the subform so that
the code "forces" a save of the subform record before it gets the value from
the subform's control, etc.


--

Ken Snell
<MS ACCESS MVP>

Thanks, and sorry for the confusion between Text12 & Text16. I am using a
test database and trying different options.

I have previously tried calculated controls in this application and they
work fine. However, the actual application has four subforms, thousands of
records and many more subrecords. I am trying to store [BankDeposit] in
the
main table (along with many other figures) for analysis purposes. To
recalculate creates a lot of overhead.

What I don't comprehend is why the code works correctly when I insert a
breakpoint and step through it.

Yes, this is a code timing issue. When you move the focus from the
subform
to the main form, the subform should save the record that you were
editing
just before. However, this takes just a little bit of time, and it's very
possible that it hasn't saved the record and updated the subform's sum
textbox before the subform control's Exit event occurs.

I would not use the subform control's Exit event to run that code. Delete
the code from that event.

Instead, I would make the [BankDeposit] textbox in the main form be a
calculated control (not bound to a field; I am assuming that you don't
need
to save this actual number from the summing textbox, as you can calculate
it
again at any time). Change the ControlSource of this textbox to this:
=[NameOfSubformControl]![Text16]

This way, the textbox [BankDeposit] will automatically update with the
correct value when the summing textbox does finish its calculation. No
code
needed at all.

--

Ken Snell
<MS ACCESS MVP>

Sorry about that. Let me try to explain since I can duplicate this on
any
form with a subform.

Trying to obtain a Bank Deposit total based upon a cash amount and the
total
of personal checks received. The form contains box for todays date,
cash
amount received, and bank deposit. It also contains a subform in
datasheet
view based upon another form. This other form contains a description,
amount, and a subtotal in the footer.

Simply put when you enter the cash amount and all ther personal checks
I
need a total in the Bank Deposit field.

This works fine 99% of the time.

Enter the Cash - OK.
Now in subform. Enter descript, tab to amount, enter amount, tab to
next
descript, exit subform using Ctl-Tab - all OK! Bank deposit is correct
amount.

Code(only code in this test database) from subform exit:

[BankDeposit] = Text16

Now for the real problem. Say an amount in the subform is $10.00 and
you
want to change it to $20.00 quickly. Using the mouse, you highlight
the 1
and replace it with a 2. Now without hitting enter or tab exit the
subform
using Ctl-Tab and the Bank Deposit remains unchanged.

For a real kicker, I put a breakpoint at the one and online of
code(above).
Using the same method to change the amount and exit the subform I
stepped
into the line of code and now the Bank Deposit amount is correct!

I've yet to find out how to correct this. Thanks for any help.

:

You give us absolutely no information about what the code is or does,
nor
what the meaning of "does not work" is, so what you describe can be
answered
generically by "it's probably a timing issue" -- meaning that
something
that
the code initiates is not "completed" by the time you want to use the
results of it.

--

Ken Snell
<MS ACCESS MVP>


Access 97
This code works correctly under most conditions. It does not work
when
in
a
subform, a field is modified by one number, the Tab or Enter keys
are
not
used, and the subform is exited using Ctl-Tab.

Using an on-exit routine for the subform, the Visual Basic code does
not
complete calculations. If a breakpoint is set and the code is
stepped
through, everything works correctly.

 

[Guest]

First Ken, if you do see this response, thanks much for your insight and
quick responses.

I wish I didn't have to save calculated fields, but even after long
discussions with the company who hired me to write the application, they
wanted it their way.

I didn't take your approach but instead kept researching on my own and
through other posts.

What finally resolved my problem was dSum(). It allowed me to save the
totals I needed in onExit coding. It also reduced other code I had and other
than the syntax it was quite easy to use.

Thanks again.