adp quitting and other strangeness

  • Thread starter Thread starter Rob Oldfield
  • Start date Start date
R

Rob Oldfield

Hi all,

I have a personnel database that is an adp connected up to SQL 2000 SP3.
Basic layout is a list of staff down the left with subforms to show details
of employees' information. I have a combo which allows the user to decide
whether they want to view current staff or staff who have left. If they go
for current staff then the list of staff is a treeview (to show
organisational structure), if it's ex-staff then I make the treeview
invisible and display a listbox instead. Clicking on a name in either style
puts that person's ID number into a text box (i.e. for the listbox I just
have Me.txtCurrent = Me.lstExStaff - where txtCurrent is the textbox and
lstExStaff is the listbox - in the AfterUpdate of the listbox) which is then
used as the parent for the subforms.

With me so far? Good. This is where it starts getting fun.

When the user is looking at current staff then everything is fine, but I'm
getting problems when they're using the ex-staff list. Almost never on the
first person that they click in the list but generally the 6th or 7th. The
problem can appear in various different ways...

Puts a null value into Me.txtCurrent - then all code stops working.
Puts the text txtCurrent into Me.txtCurrent (????????) - then all code stops
working.
Click on a person and I get an hourglass for maybe 5 seconds... then Access
just shuts down with no error message.
Comes up with an error saying that a table doesn't exist (e.g. the table
containing data for the subform). It's not always the same table and please
bear in mind that the connection to the table worked for the first 5 or 6
people clicked on.

There's no consistency and it's certainly not data related - I can choose to
view ex staff, click on one user and then another and they both work. Go
back and forward on those two for a while and then I get the same thing
happening.

I've removed all error handling from the db to see if I can catch where it's
going bang. And nothing. The only error I ever see is the one about not
being able to find a table - which was there 10 seconds earlier.

It's also not server related. I've just moved the back end data onto a
different SQL server (from a 2000 server to 2003) and I'm getting the same
things happening.

And not machine related. It does this on all client machines.

Any solutions out there? And, if not, then ideas, vague thoughts or just
straws for me to grasp at will also be gratefully received.
 
Which version of Access are you using?

Since WinXP SP2, I don't use Access 2002 anymore - to many GPFs - only
Access 2003 (much better).

It also possible that the Active-X used for your treeview is making some
trouble. Maybe you should recompile the whole ADP application. (By the
way, often a corrupted compilation won't show up in a error handler or even
in the debugger.).

To recompile your application, use the /decompile switch when starting
Access or even better, go to the References dialog window in the VBA Editor,
add a dummy reference, close the dialog window so that Access notice the
change, open it again, remove the dummy reference just added before and
choose Recompile All from the Debug tab.

S. L.
 
Thanks for the idea, but no success I'm afraid. A decompile left me with
the same error. I even took it one step further and created a new adp -
just copying everything across. That also gives me the same. It's Access
2000 btw.
 
Hum, Access 2000, I wouldn't bet too much on it.

After re-reading your message, I found interesting the fact that the problem
occurs with ex-staff only; so it's seems to rule out the possibility of a
problem with the Treeview control. However, even if it's hidden, there are
still the possibility of incorrect code beetween the treeview and the rest
of the form in case of an ex-staff choice.

You should make some tests by removing the treeview completely from your
solution; only to make sure that the culprit is not this active-x control.

S. L.
 
Good thinking. But still no solution. I removed the treeview and the
references it required, but am still getting the same errors.
 
Try converting the value from the listbox
Me.txtCurrent = Clng(Nz(Me.lstExStaff,0))
The values in the listbox are stringvalues.


Anders Dæmroen
 
Give that person 17,000 gold stars! That seems to have done the job.

Beats me why I wasn't getting more sensible error messages though....

Many thanks to both of you.
 
Back
Top