Marsh,
I'm haunted by the behavior of this bug.
The main form here has two subforms.
The purpose of the form is for the user
to enter donations for a particular individual,
the list of which is displayed in subform#1.
The YTD donations for the individual are
listed in subform#2.
The behavior is simply this: an individual
is selected from subform#1. The filters are
then set for that individual in the controls
for subform#2 and subform#2 is queried
accordingly. Now, because this is the first
of the year, the first query yields an empty
subform#2. Next, the user enters a donation
value into a control on the main form and
presses enter on the keyboard. The main
form enter event for the control makes
a command button visible prompting the
user to press if everything is in order. The
event code I posted initially is given control
and the newly entered value is inserted into
the table and subform#2 is Required to
reflect the new addition. Simple eh?
As I posted initially, the Requery of subform#2
fails to reflect the new record addition. So,
another name is selected from subform#1 and
then again the name of the individual
associated with the newly entered donation.
With that, the individual's new donation is
properly reflected AND ANY NEW donations
that are subsequently added are reflected
properly when entered via the command
button mentioned above. So, having said all
of that, what haunts me is the fact that I see
the failure when I've started with an empty
subform#2 but subsequent actions function
properly.
Is there condition I can check to see if the
Requery failed to find the newly entered
record. (I don't know what the syntax would
be here to check the Recordcount for subform#2
from the main code.
Thanks for hanging in there with me on this one.
By the way, I did go through the decompile
scenario earlier.
Bill
Marshall Barton said:
I can see that you have replied to some other folks in this
thread, but I can't see what they said. I hope someone else
has a better idea than I do.
Since logic seems to have flown out the window, I think it's
time to take the usual copout and blame it on some kind of
corruption. The fact that you said somewhere that the
identical code in an older version of your app runs fine
also lends credence to corruption being the culprit.
There are a few things you can do to try to clear it, but no
guarantees. First make sure you have a backup in case
things get worse. I think(?) the first thing to try is to
decompile. Use the Start button's Run command:
"path\MSACCESS.EXE" "path\yourapp.mdb" /Decompile
Be sure to hold down the shift key to prevent any startup
procedure from running. Then use Tools - Compact (holding
the shift key down) and close Access. While holding the
shift key down, open Access with your database, then hit
Ctrl+g to get to the VB window and use Debug - Compile and
close Access. Finally, try the problem to see if all that
made a difference.
A different thing to try is to create a new, blank mdb, set
all the options the way you want, especially make sure Name
AutoCorrect is OFF. Then import everything from the problem
mdb.
Note that a common cause of front end corruption is editing
a form/report's module while the form/report is open in any
view except design view.
--
Marsh
MVP [MS Access]
Three DoEvents didn't have any affect. However, if I
insert MsgBox "Hi" and wait 3 or 4 seconds before
I click Okay, then the Requery will return the just
inserted record. If I click Okay as fast as the message
MsgBox appears, I get an empty subform, i.e., I do
have to wait a few seconds before clicking Okay.
I'm out of ideas at the moment.
Bill said:
I put a breakpoint at the statement before the Execute
and slowwwwwwwly stepped through the Execute
and Requery. The inserted record then appeared in
the Requery. I'll try inserting a DoEvents or two.
I've never experienced the problem. You say that it works
if you do something innocuous, but doesn't that mean that
the Requery was removed or that you put a break point on
it? If so, I think that's an invalid test. Maybe adding a
DoEvents (or two) before the Requery might be effective??
Bill wrote:
That's what I believed when I wrote the code. However,
I have to perform a couple of incidental screen actions
before the data inserted displays in the subform. I added
the dbFailOnError to the Execute, but that didn't produce
any errors nor solve the problem.
One interesting observation is that it only fails when the
record inserted is THE FIRST record that would appear
in the Recordsource of the subform. Like the Recordsource
is "no records found" in the initial opening of the subform
and the record inserted is the FIRST.
What else might it be?
Bill wrote:
In the following segment of code, a record has been
inserted into the RecordSource of a subform. I'm
missing a very fundamental, I think, statement that
insures that the insert has completed before the
Requery is issued. What might that be?
tmpSQL = "INSERT INTO [DonRegFam](FundID,DOE,FamilyID,Amount,Type)"
tmpSQL = tmpSQL & " VALUES(" & cboFunds.Column(0) & ", " & DVal & ",
"
tmpSQL = tmpSQL & FamID & ", " & Me.AmtBox & ", " & TypeVal & ");"
CurrentDb.Execute tmpSQL
Me.DonationsSubform.Form.Requery
Me.CashBox = False
Me.AmtBox = 0
Me.AmtBox.SetFocus
Me.Record.Visible = False
Me.PrtStmtCmd.Visible = True
The Execute method runs synchronously, so you should not
have to worry about it. You might want to use dbFailOnError
just in case the query can not do what you told it to do.
