"Close Window" closes application

[Robin Chapple]

I am using Access 2002 SP2

I have a membership databse which I have been using many times each
day and updating from eXternal data every day. It has been running
since July 2003.

The update process is performed by a macro which deletes the data from
the [tblDatabase] and appends data from the imported database. In
order to refresh the data I close and then open the form.

Today at line 5 in my macro, which is "Close the form", the
application closes. I have single stepped the macro.

I have used the on form "Close Form" button and the "Close Window"
button and many times, but not always, the application closes.

I am a beginner and I am lost.

Your ideas welcome.

Thanks,

Robin Chapple

 

[PC Datasheet]

Robin,

Rather than closing and then reopening your form, try just requerying your form
at the same point in your code where you now cose and reopen the form. The code
is:
Me.Requery

There's also the Repaint method. The code is:
Me.Repaint

 

[Robin Chapple]

I am using a macro for this.

I have changed to the repaint option and at the end of the sequence
each field shows #Deleted which was the original reason for closing
the form and then reopening.

Also my "close form" button and the Access "close window " both close
the application after I have run the macro. They behave normaly at
other times

I converted the macro to VBA in order to include it here but can't
find the VBA

Thanks,

Robin

 

[PC Datasheet]

Robin,

#Deleted shows up because your table is still in edit mode. There's something
wrong in how you have the delete and append macros setup. Try deleting and
appending in VBA:

To Delete:
DELETE TblDatabase.* FROM TblDatabase;


To Append:
INSERT INTO TblDatabase SELECT TblImported.* FROM TblImported;


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

 

[Robin Chapple]

Mr PC DataSheet,

Thanks for your persistence. I am not using close and open following
your advice to repaint.

I now have converted the macro to VBA and placed it under the command
button:

Private Sub cmdUpdateDatabase_Click()
On Error GoTo Err_cmdUpdateDatabase_Click

DoCmd.SetWarnings False
DoCmd.OpenQuery "MakeTableCountryUpdate", acViewNormal, acEdit
DoCmd.OpenQuery "yyDeleteDatabaseQuery", acViewNormal, acEdit
DoCmd.OpenQuery "yyAppendROTIDB2Database", acViewNormal, acEdit
DoCmd.OpenQuery "UpdateQueryCountriesToDatabse", acViewNormal,
acEdit
DoCmd.RepaintObject acForm, "frmMembers"
DoCmd.SetWarnings True

Exit_cmdUpdateDatabase_Click:
Exit Sub

Err_cmdUpdateDatabase_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateDatabase_Click

End Sub

I hope that the clue is there.

The names commencing "yy" are purely to make them sort together at the
end of the list.

Regards,

Robin

 

[Mark]

You can first remove acViewNormal, acEdit from all your DoCmd.OpenQuery
statements. acViewNormal, acEdit are the defaults. Delete this line:
DoCmd.RepaintObject acForm, "frmMembers". After DoCmd.SetWarnings True add this
line Me.Requery.

I would then start with your first DoCmd.OpenQuery statement and add Exit Sub
immediately after it. Run the code then open the table to see what is in the
table. Then move the Exit Sub line to after the second DoCmd.OpenQuery
statement, run the code then check the table. Do this for all four
DoCmd.OpenQuery statements.

Post back what you found.

Steve
PC Datasheet

 

[Robin Chapple]

Steve,

I was unable to apply your instructions to move the "Exit Sub" line
without an error message.

Instead I remmed the lines.

The problem arises after the line:

DoCmd.OpenQuery "yyDeleteDatabaseQuery"

The application closes.

Here is the code:

DELETE tblDatabase.ID, tblDatabase.UserName, tblDatabase.Password,
tblDatabase.Title, tblDatabase.FirstName, tblDatabase.NickName,
tblDatabase.LastName, tblDatabase.Office, tblDatabase.Club,
tblDatabase.ClubWeb, tblDatabase.District, tblDatabase.DistrictWeb,
tblDatabase.EmailConsent, tblDatabase.Email, tblDatabase.Address,
tblDatabase.City, tblDatabase.State, tblDatabase.PostCode,
tblDatabase.Country, tblDatabase.Phone, tblDatabase.Fax,
tblDatabase.ICQ, tblDatabase.PHF, tblDatabase.MPHF,
tblDatabase.Benefactor, tblDatabase.Bequest, tblDatabase.SpName,
tblDatabase.SpRotarian, tblDatabase.Vocation, tblDatabase.Hobbies,
tblDatabase.PicFile, tblDatabase.Joined, tblDatabase.BornMonth,
tblDatabase.Bornday, tblDatabase.BornYear, tblDatabase.SpBornMonth,
tblDatabase.SpBornDay, tblDatabase.SpBornYear, tblDatabase.WedMonth,
tblDatabase.WedDay, tblDatabase.WedYear, tblDatabase.ROTIORG,
tblDatabase.Serious, tblDatabase.ROTIWiz, tblDatabase.ROTIGer,
tblDatabase.RotiLat
FROM tblDatabase;

Cheers,

Robin

 

[PC Datasheet]

Robin,

I take it that you copied the SQL view and posted that. I don't readily see
anything wrong there. Are you trying to delete all data in TblDatabase with
yyDeleteDatabaseQuery? If yes, rename yyDeleteDatabaseQuery then create a new
query based on TblDatabase. Only pull down the asterisk (*). Change the query to
a delete query. The SQL view should look like:
DELETE TblDatabase.* FROM TblDatabase;
Name the new query yyDeleteDatabaseQuery.

Now run your code.

What is the name of the table you are creating with the query
MakeTableCountryUpdate?

Post back what you find.

Steve
PC Datasheet

 

[Robin Chapple]

Steve,

Thanks for your persistence.

I take it that you copied the SQL view and posted that.

That is correct

I don't readily see
anything wrong there. Are you trying to delete all data in TblDatabase with
yyDeleteDatabaseQuery?
Yes

If yes, rename yyDeleteDatabaseQuery then create a new
query based on TblDatabase. Only pull down the asterisk (*). Change the query to
a delete query. The SQL view should look like:
DELETE TblDatabase.* FROM TblDatabase;
Name the new query yyDeleteDatabaseQuery.

Here is my SQL in case there is a difference that I have missed:

DELETE tblDatabase.*
FROM tblDatabase;

Now run your code.

I did that and the first time all was OK. Every time afterwards the
application closed.

What is the name of the table you are creating with the query
MakeTableCountryUpdate?

TblCountryUpdate

Here is the SQL

SELECT tblDatabase.ID, tblDatabase.Country INTO tblCountryUpdate
FROM tblDatabase;

To fill in the background. This is a web based database and members
fill in a field for the country in which they live. It matters not
when viewed whether the use "US"; "USA"; "U.S.A." or United States"
but for my statistics I need uniformity. I change the record on my
uploaded copy.

When I am updating I take a copy of the countries against the member
ID and after update I put the countries back.

 

[PC Datasheet]

Robin,

Do you have error handling in the code that runs the four queries? If yes,
comment out the "On Error...." statement to see if you get an error message
right before the application closes.

Steve

 

[Robin Chapple]

Steve,

I hope that this is what you intended:

Private Sub cmdUpdateDatabase_Click()
' On Error GoTo Err_cmdUpdateDatabase_Click

DoCmd.SetWarnings False
DoCmd.OpenQuery "MakeTableCountryUpdate"
DoCmd.OpenQuery "yyDeleteDatabaseQuery"
DoCmd.OpenQuery "yyAppendROTIDB2Database"
DoCmd.OpenQuery "UpdateQueryCountriesToDatabse"
DoCmd.SetWarnings True
Me.Requery

Exit_cmdUpdateDatabase_Click:
Exit Sub

Err_cmdUpdateDatabase_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateDatabase_Click

End Sub

There was no error message and the application closed.

Robin

 

[PC Datasheet]

Robin,

I am willing to take a look at your actual database if you want to email it to
me. If it is split I will need both files. Compact first then zip. My email
address is (e-mail address removed). Tell me where the code with the 4 queries
is at.

Steve

 

[Robin Chapple]

Steve,

I have just compacted both files and they are 20MB., 5MB zipped.

I will attaempt to build a separate file without the irelevant data.

Robin