Opened form disappears, Database freezes

[Andy]

Hi All, (Access 2002, Windows XP Pro)



I have an Access form which opens another form whilst passing a key value in
the open args. The key value is used in the Form_Load event of the second
form to generate its recordsource. The form opened fine for a dozen or so
times but then stopped opening. All I saw was the hour glass momentarily and
nothing else. Now the whole database freezes and I have to close it with
task manager. The form calling code follows:





Private Sub lblSendEmail_Click()



On Error GoTo ErrorHandler



Dim lngCandidateID As Long



'lngCandidateID = Nz(Me![CandidateID])

lngCandidateID = Me.CandidateID



If lngCandidateID <> 0 Then

'DoCmd.OpenForm FormName:="frmCandidateEmail", View:=acNormal,
OpenArgs:=lngCandidateID

DoCmd.OpenForm "frmCandidateEmail", acNormal, , , , acDialog,
lngCandidateID

End If



exitSendEmail:

Exit Sub



ErrorHandler:

MsgBox Err.Description '

MsgBox Err.Number & ": " & Err.Description

Resume exitSendEmail



End Sub





And here is the Form_Load code from the form I want to open:



Private Sub Form_Load()



On Error GoTo ErrorHandler



'Me.RecordSource = "SELECT * FROM tblCandidates WHERE CandidateID = " &
Me.OpenArgs



'Me.Caption = " Email - Candidate"



exitForm_Load:

Exit Sub



ErrorHandler:

MsgBox Err.Description '

MsgBox Err.Number & ": " & Err.Description

Resume exitForm_Load



End Sub





I'm stumped. Any ideas at all about whats the cause of this behaviour?



Any ideas greatly appreciated.



Andy.

 

[Andy]

Tom,

Thanks for your two tips. I have tried both and no luck. I've been working
on this access database for 3 years now and never seen this disappearing
form thing before. Bummer that it now freezes the database too. There are 70
forms in the database including about 15 Pop up forms and its just never
happened before. Every day there are 5 users pounding away at it and its
behaved very well till now.

Thanks for you help anyway

Regards,

Andy

Hi Andy,

If it worked fine at one point, and now has stopped working, then it has
likely become corrupted. Try using the undocumented /decompile switch:

Start > Run | msaccess.exe /decompile

The next database that you open will have any compiled "P" code discarded.
Note: This method works as long as you only have one version of Access
installed. If you have multiple versions of Access installed, then create
a
shortcut that points to the version of msaccess.exe that you want to run.
Add
the /decompile switch to the target of the shortcut.

Open the database with the shift key fully depressed the entire time, to
prevent an Autoexec or startup form from opening. Then do a compact and
repair: Tools > Database Utilities > Compact and repair database. Now, try
re-compiling your code. Open any code module and click on Debug > Compile
ProjectName.

If that does not help, then try the following steps to cure minor
corruption:

Create a brand new database and immediately disable the NameAutocorrupt
feature (see: http://allenbrowne.com/bug-03.html for reasons why you want
to
do this). Then import all objects from the suspect database into the new
database, one group at a time. In other words, import all tables (but not
linked tables), then import all queries, then all forms, etc. While Access
will allow you to import all objects in one operation, the experts at FMS,
Inc. (a Microsoft Partner), have stated that it is best to import objects
one
group at a time (Reference:
http://www.fmsinc.com/ubb/Forum12/HTML/000285.html).

Recreate any linked tables from scratch. Access can cache a lot of
information about linked tables, which may no longer be valid, so it's
always
best to recreate the linked tables from scratch. When importing local
tables,
make sure to check the option to import relationships, menus and toolbars,
and import/export specs. If any of the local tables in the source DB are
hidden, you'll need to first unhide them. You will need to set the checked
references to match the source database, along with any startup options
set
under Tools > Startup. Going through this process often times solves
corruption problems, because you get a new set of the hidden system tables
(the tables whose names start with "MSYS"). These system tables are
updated
appropriately as you import objects.

This may sound like a lot of work, but it really isn't. Creating a new
container DB, disabling NameAutocorrect, importing all objects one group
at a
time, re-establishing any linked tables, setting startup options, and
setting
references to match the source DB is usually a fairly quick procedure.
When
you are in the Visual Basic Editor, in order to check that the references
match the source DB, you should do a Debug > Compile ProjectName as well.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi All, (Access 2002, Windows XP Pro)



I have an Access form which opens another form whilst passing a key value
in
the open args. The key value is used in the Form_Load event of the second
form to generate its recordsource. The form opened fine for a dozen or
so
times but then stopped opening. All I saw was the hour glass momentarily
and
nothing else. Now the whole database freezes and I have to close it with
task manager. The form calling code follows:





Private Sub lblSendEmail_Click()



On Error GoTo ErrorHandler



Dim lngCandidateID As Long



'lngCandidateID = Nz(Me![CandidateID])

lngCandidateID = Me.CandidateID



If lngCandidateID <> 0 Then

'DoCmd.OpenForm FormName:="frmCandidateEmail", View:=acNormal,
OpenArgs:=lngCandidateID

DoCmd.OpenForm "frmCandidateEmail", acNormal, , , , acDialog,
lngCandidateID

End If



exitSendEmail:

Exit Sub



ErrorHandler:

MsgBox Err.Description '

MsgBox Err.Number & ": " & Err.Description

Resume exitSendEmail



End Sub





And here is the Form_Load code from the form I want to open:



Private Sub Form_Load()



On Error GoTo ErrorHandler



'Me.RecordSource = "SELECT * FROM tblCandidates WHERE CandidateID = " &
Me.OpenArgs



'Me.Caption = " Email - Candidate"



exitForm_Load:

Exit Sub



ErrorHandler:

MsgBox Err.Description '

MsgBox Err.Number & ": " & Err.Description

Resume exitForm_Load



End Sub





I'm stumped. Any ideas at all about whats the cause of this behaviour?



Any ideas greatly appreciated.



Andy.

 

[Andy]

Tom,

Thanks so much for your help.

It turns out that the 'auto center' property of the form on the Format tab
of the forms properties needed to be set to Yes otherwise all hell broke
loose. Very strange. Any ideas what was going on?

Thanks again.

Andy

Do you have a known good back-up copy of this database, which may have
older
data, but has a current copy of the form in question? If so, try deleting
the
form, compacting the database, and importing the same form from the known
good copy of your database.

If you don't have such a back-up, try using the new database that you
created in step 2 for the form that is not opening properly. First, I
assume
you can open this form in design view. Is this correct? If your answer is
yes, try the following:

1.) Open NotePad

2.) Open the form's code module. Copy the code associated with this form
(Ctrl C) and paste it (Ctrl V) into NotePad. Save the text file.

3.) Set the Has Module property for the form to No. Save the form.

4.) Open the Immediate Window (Ctrl G) and issue the following command. I
have used C:\Temp as the example folder. Use a valid path for your PC.

Application.SaveAsText acForm, "NameOfForm", "C:\Temp\NameOfForm.txt"

5.) Delete the form. Compact the database.

6.) Open the Immediate Window (Ctrl G) and issue the following command:

Application.LoadFromText acForm, "NameOfForm", "C:\Temp\NameOfForm.txt"

7.) Create a new code module for this form. Copy the code from the text
file
you previously saved, and paste it into the recovered form's code module.
Remove any duplicate declarations that you may have at the very top.

Note: You should see Option Explicit as the second line of code in your
new
module. If not, add this line manually. Then set your VBA options so that
you
will always get this line in all new modules that you create. See the
following Gem Tip if you are not sure what this is all about:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

8.) Click on Debug > Compile ProjectName. Test the functionality.


Good Luck.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tom,

Thanks for your two tips. I have tried both and no luck. I've been
working
on this access database for 3 years now and never seen this disappearing
form thing before. Bummer that it now freezes the database too. There are
70
forms in the database including about 15 Pop up forms and its just never
happened before. Every day there are 5 users pounding away at it and its
behaved very well till now.

Thanks for you help anyway

Regards,

Andy

Hi Andy,

If it worked fine at one point, and now has stopped working, then it
has
likely become corrupted. Try using the undocumented /decompile switch:

Start > Run | msaccess.exe /decompile

The next database that you open will have any compiled "P" code
discarded.
Note: This method works as long as you only have one version of Access
installed. If you have multiple versions of Access installed, then
create
a
shortcut that points to the version of msaccess.exe that you want to
run.
Add
the /decompile switch to the target of the shortcut.

Open the database with the shift key fully depressed the entire time,
to
prevent an Autoexec or startup form from opening. Then do a compact and
repair: Tools > Database Utilities > Compact and repair database. Now,
try
re-compiling your code. Open any code module and click on Debug >
Compile
ProjectName.

If that does not help, then try the following steps to cure minor
corruption:

Create a brand new database and immediately disable the NameAutocorrupt
feature (see: http://allenbrowne.com/bug-03.html for reasons why you
want
to
do this). Then import all objects from the suspect database into the
new
database, one group at a time. In other words, import all tables (but
not
linked tables), then import all queries, then all forms, etc. While
Access
will allow you to import all objects in one operation, the experts at
FMS,
Inc. (a Microsoft Partner), have stated that it is best to import
objects
one
group at a time (Reference:
http://www.fmsinc.com/ubb/Forum12/HTML/000285.html).

Recreate any linked tables from scratch. Access can cache a lot of
information about linked tables, which may no longer be valid, so it's
always
best to recreate the linked tables from scratch. When importing local
tables,
make sure to check the option to import relationships, menus and
toolbars,
and import/export specs. If any of the local tables in the source DB
are
hidden, you'll need to first unhide them. You will need to set the
checked
references to match the source database, along with any startup options
set
under Tools > Startup. Going through this process often times solves
corruption problems, because you get a new set of the hidden system
tables
(the tables whose names start with "MSYS"). These system tables are
updated
appropriately as you import objects.

This may sound like a lot of work, but it really isn't. Creating a new
container DB, disabling NameAutocorrect, importing all objects one
group
at a
time, re-establishing any linked tables, setting startup options, and
setting
references to match the source DB is usually a fairly quick procedure.
When
you are in the Visual Basic Editor, in order to check that the
references
match the source DB, you should do a Debug > Compile ProjectName as
well.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

Hi All, (Access 2002, Windows XP Pro)



I have an Access form which opens another form whilst passing a key
value
in
the open args. The key value is used in the Form_Load event of the
second
form to generate its recordsource. The form opened fine for a dozen
or
so
times but then stopped opening. All I saw was the hour glass
momentarily
and
nothing else. Now the whole database freezes and I have to close it
with
task manager. The form calling code follows:





Private Sub lblSendEmail_Click()



On Error GoTo ErrorHandler



Dim lngCandidateID As Long



'lngCandidateID = Nz(Me![CandidateID])

lngCandidateID = Me.CandidateID



If lngCandidateID <> 0 Then

'DoCmd.OpenForm FormName:="frmCandidateEmail", View:=acNormal,
OpenArgs:=lngCandidateID

DoCmd.OpenForm "frmCandidateEmail", acNormal, , , , acDialog,
lngCandidateID

End If



exitSendEmail:

Exit Sub



ErrorHandler:

MsgBox Err.Description '

MsgBox Err.Number & ": " & Err.Description

Resume exitSendEmail



End Sub





And here is the Form_Load code from the form I want to open:



Private Sub Form_Load()



On Error GoTo ErrorHandler



'Me.RecordSource = "SELECT * FROM tblCandidates WHERE CandidateID = "
&
Me.OpenArgs



'Me.Caption = " Email - Candidate"



exitForm_Load:

Exit Sub



ErrorHandler:

MsgBox Err.Description '

MsgBox Err.Number & ": " & Err.Description

Resume exitForm_Load



End Sub





I'm stumped. Any ideas at all about whats the cause of this behaviour?



Any ideas greatly appreciated.



Andy.