B
Bill
Here's the sub that is causing database problems
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.
I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)
Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)
=============================================
Private Sub InitVer7pt1()
'====================================================
' TMS Version 7.1 is the first version of TMS where TableDef's are upgraded
' in the field. If the current DB is found to be without the InstDBVersion
' field within the Installation Properties, then DB needs to be upgraded to
' compatibility with at least version 7.1. THEN, after the Installation
' Properties are loaded in the main code (above), there will be checks to
' determine if further upgrades are required.
'====================================================
Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
IPDatabase = DLookup("InstDatabase", "InstProperties")
Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True
For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr
If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr
Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr
Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr
IPAddress = InputBox("Your database has been updated to include two
new" & vbNewLine _
& "fields that are required for donation
statements" & vbNewLine _
& "suitable for submission to the IRS for tax
purposes." & vbNewLine & vbNewLine _
& "Please enter the mailing address of your
installation." & vbNewLine _
& "[We'll prompt for city, state and zip
momentarily.]")
IPCityState = InputBox("And now, the city, state zip of your
installation")
End If
Set tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing
End Sub
=============================================
elsewhere (notably in the donations form) in the
application. All I have to do is comment out the
call to this sub and ALL the offending symptoms
disappear. At first, I thought it was the absence
of the close (dbCurr.Close), but adding that line
of code didn't have any noticeable affect.
I don't know what the problem is. (Note that when
this code runs, the value of booNotFound is False,
as the current mdb has been updated to version
7.1.)
Do you see anything that would leave the backend
mdb in some sort of precarious state? (I've verified
that the correct database is returned from the
DLookUp.)
=============================================
Private Sub InitVer7pt1()
'====================================================
' TMS Version 7.1 is the first version of TMS where TableDef's are upgraded
' in the field. If the current DB is found to be without the InstDBVersion
' field within the Installation Properties, then DB needs to be upgraded to
' compatibility with at least version 7.1. THEN, after the Installation
' Properties are loaded in the main code (above), there will be checks to
' determine if further upgrades are required.
'====================================================
Dim booNotFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
IPDatabase = DLookup("InstDatabase", "InstProperties")
Set dbCurr = OpenDatabase(IPDatabase)
Set tdfCurr = dbCurr.TableDefs("InstProperties")
booNotFound = True
For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "InstDBVersion" Then
booNotFound = False
Exit For
End If
Next fldCurr
If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("InstDBVersion", dbSingle)
tdfCurr.Fields.Append fldCurr
Set fldCurr = tdfCurr.CreateField("InstAddress", dbText)
tdfCurr.Fields.Append fldCurr
Set fldCurr = tdfCurr.CreateField("InstCityState", dbText)
tdfCurr.Fields.Append fldCurr
IPAddress = InputBox("Your database has been updated to include two
new" & vbNewLine _
& "fields that are required for donation
statements" & vbNewLine _
& "suitable for submission to the IRS for tax
purposes." & vbNewLine & vbNewLine _
& "Please enter the mailing address of your
installation." & vbNewLine _
& "[We'll prompt for city, state and zip
momentarily.]")
IPCityState = InputBox("And now, the city, state zip of your
installation")
End If
Set tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing
End Sub
=============================================
Marshall Barton said:It worked once, then doesn't work again??? Totally weird!
You don't have an incomplete transaction going on, a machine
that's in a cache thrashing state, or a horribly slow
server, do you?
Starting over is really tedious, but I don't have a better
idea.
Good luck,
--
Marsh
MVP [MS Access]
RATS! I thought we'd hit "pay-dirt" when I first tested
the newly created mdb with all the imports. However,
after I did some additional regression testing for many
of the other functions, I returned to the offending function
only to find that it was once again failing.
I don't know what else to do other than to re-build V7.2
starting from the V7.1 source adding the changes one at
a time and testing the offending form each time anything
is added.
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.
Bill wrote:
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.
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.