Hi all,
Running into a strange problem where a command button suddenly stops
working in opening a particular form in Access 2007 & 2010. Never had
a problem in 97/2002/2003. The button works in 2007/2010 over a number
of sessions and then just stops working -- it depresses when clicked,
but otherwise does nothing. Probably something wrong with my code and
hoping someone can see what. In the Select Case statement below, the
actual case in which the problem occurs is:
Case Is = "C:\Access97\fpsdata.mdb". This is where the form
"StartStopDates_TransferOpenFirst" normally opens. It will work for
several days each time the user brings their laptop into the office,
but may then not work the next time they come in and I'll have to
issue them a new FE.

The code for the command button is:

Private Sub Command94_Click()
Dim ServerRomney As String
Dim ServerMoorefield As String
Dim stDocName As String
Dim stLinkCriteria As String

ServerRomney = "P:\fpsdata.mdb"
ServerMoorefield = "P:\Petedata.mdb"
stDocName = "StartStopDates_TransferOpenFirst"

On Error GoTo Err_Command94_Click

Select Case GetLinkedDBName("Names")
Case Is = "P:\fpsdata.mdb"
MsgBox "There is nothing to do here ... you are already linked
to the server."
Exit Sub
Case Is = "P:\Petedata.mdb"
MsgBox "There is nothing to do here ... you are already linked
to the server."
Exit Sub
Case Is = "C:\Access97\fpsdata.mdb"
If Len(Dir(ServerRomney)) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "Please logon to the network first before transferring
or updating your database."
Exit Sub
End If
Case Is = "C:\Access97\Petedata.mdb"
If Len(Dir(ServerMoorefield)) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "Please logon to the network first before transferring
or updating your database."
Exit Sub
End If
End Select

Exit_Command94_Click:
Exit Sub

Err_Command94_Click:
MsgBox Err.Description
Resume Exit_Command94_Click
End Sub

'end code


The OnOpen Event for the form "StartStopDates_TransferOpenFirst" has
the following code:

Private Sub Form_Open(cancel As Integer)
Me!Label16.Visible = False 'Select your name:
Me!Text1.Visible = False 'From:
Me!Text3.Visible = False 'To:
Me!StaffSelect.Visible = False 'StaffSelect cmbobox
Me!Start.Visible = False 'StartDate box
Me!Stop.Visible = False 'StopDate box
Me!Label17.Visible = False 'Enter pay-period start and stop dates:
Me!Label92.Visible = False 'Enter dates to protect:
End Sub


Additional info:

1. Once the button ceases to work, if I go to the Navigation Pane and
try to open the form directly (i.e.,
"StartStopDates_TransferOpenFirst") a "Select Data Source" dialogue
box opens with the following description: "Select the file data source
that describes the driver that you wish to connect to. You can use a
file data source that refers to an ODBC driver which is installed on
your machine."
2. Yes, I've always used a mapped drive approach and am aware of
potential problems, but there does not appear to be an error in the
mapping in this case. Network mapping and Windows shortcuts to the
mapped drive appear fine. And, if I simply replace the FE without
rebooting or anything, the button works fine in the replaced FE.
3. In my Select Case, I don't have a Case Else statement and wonder if
that's a problem.
4. In my error handling, I just have err.Description and no
err.Number. Maybe there is an error, but one which does not have a
Description?
5. The GetLinkedDBName() function is as follows:

Option Compare Database
Option Explicit
'================================================= ==============
' The GetLinkedDBName() function requires the name of a
' linked Microsoft Access table, in quotation marks, as an
' argument. The function returns the full path of the originating
' database if successful, or returns 0 if unsuccessful.
' To test this function, type the following line in the Immediate
window,
' and then press ENTER:
' ? GetLinkedDBName("Names")
'================================================= ==============
Function GetLinkedDBName(TableName As String)
'GetLinkedDBName("Names")
Dim DB As DAO.Database, Ret
On Error GoTo DBNameErr
TableName = "Names"
Set DB = CurrentDb()
Ret = DB.TableDefs(TableName).Connect
GetLinkedDBName = Right(Ret, Len(Ret) - (InStr _
(1, Ret, "DATABASE=") + 8))
Exit Function
DBNameErr:
GetLinkedDBName = 0
End Function

'end code

6. I've tried compiling the code behind the command button once the
button stops working. The code seems to compile fine. I haven't tried
compiling the code attached to the OnOpen Event of the form that's not
opening. (I received the "Select Data Source" dialogue box when I
tried to open it directly and didn't think to try to open it in Design
View).

Any ideas?
Thanks,
Mark

On Aug 23, 12:03*pm, Mark <cmd2... (AT) frontiernet (DOT) net> wrote:
One of the users with Access 2007 just reminded me that sometimes on
her laptop when she clicks the button in question she'll get the
Windows 7 version of the hourglass which spins and spins and spins and
if she waits long enough, the form will open.

Mark <cmd2006 (AT) frontiernet (DOT) net> wrote in
news:d60624c9-fb4d-4397-b229-1007012eb541 (AT) w30g2000yqw (DOT) googlegroups.co
m:

First off, your CASE statements should be:

Case "C:\Access97\fpsdata.mdb"

The "Is =" is entirely redundant in this context.

My guess is that the slowdown is with the Dir() and the mapped drive
letter. I have found that using the File System Object's DirExists()
function is faster and less error prone. My function to check for
the availability of the network is posted after my signature.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Private Function CheckForNetwork(strFolderToCheck As String, _
Optional bolClear As Boolean = False) As Boolean
Static objFSO As Object

If bolClear Then Set objFSO = Nothing: Exit Function

If objFSO Is Nothing Then
Set objFSO = CreateObject("Scripting.FileSystemObject")
End If
CheckForNetwork = objFSO.FolderExists(strFolderToCheck)
End Function

On Aug 23, 3:06*pm, "David W. Fenton" <NoEm... (AT) SeeSignature (DOT) invalid>
wrote:
Thanks David for your response. I'll try to incorporate your
suggestions. However, any thoughts as to why I get the "Select Data
Source" dialogue box when I've bypassed all the above code and simply
try to open the form directly from the Navigation Pane?
box

On Aug 23, 3:17*pm, Mark <cmd2... (AT) frontiernet (DOT) net> wrote:
After further thought, it seems to me that something about Access 2007
and 2010 is causing the FE to become corrupt, at least in terms of
this one command button and opening of this one particular form ...
-- the command button & form work fine over numerous sessions
-- when the problem pops up, closing and restarting the FE does not
resolve the problem
-- rebooting the computer does not resolve the problem
-- opening the form's module and running compile does not resolve the
problem
-- trying to open the form directly from the Navigation Pane -- where
the only code is to set the visible property of certain fields to
false (see above) doesn't work and results in the "Select Data Source"
prompt
-- copying/replacing the FE from a backup source does fix the problem,
at least for another few sessions.

This same button, form and code has worked fine for the past 7 years
-- through versions 97 through 2002.
-- I haven't tried copying/replacing just the form in question when
the problem occurs and I get the "Select Data Source" prompt if I try
to open the form directly, so maybe I'll try that next time. If the
replacement opens, then at least I'll know that it's that one
particular form that's becoming corrupted under 2007/2010.

Mark <cmd2006 (AT) frontiernet (DOT) net> wrote in
news:537210cd-b183-4e25-9c68-3d27ce14d5b1 (AT) j8g2000yqd (DOT) googlegroups.com
:

Sounds like you've got invalid connect strings.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Mark <cmd2006 (AT) frontiernet (DOT) net> wrote in
news:413edcc6-45d3-41d0-87aa-90e348c56c09 (AT) j8g2000yqd (DOT) googlegroups.com
:

Is this a bound form? If so, the find data source dialog is popping
up because Access can't find the data source. This is not a
corruption issue, but likely a networking issue.

Or so it seems to one trying to observe from this distance.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On Aug 24, 2:41*pm, "David W. Fenton" <NoEm... (AT) SeeSignature (DOT) invalid>
wrote:
Thanks, David
No, it's an unbound form. It has a couple of option groups and a
couple of unbound text fields and it's own command button which will
process data to and from the BE, depending upon which option the user
selects. None of that code runs when the form is first opened,
however, just the setting of the visible property for the text fields.
Mark

On Aug 24, 2:40*pm, "David W. Fenton" <NoEm... (AT) SeeSignature (DOT) invalid>
wrote:
David,
Maybe, but the FE continues to work in all other aspects in terms of
reading and writing to the BE. Closing the FE and reopening produces
no error as it starts up in terms of overall connection to the BE ...
it's just that that *&!# button has stopped working.
Mark

On Aug 23, 3:06*pm, "David W. Fenton" <NoEm... (AT) SeeSignature (DOT) invalid>
wrote:
David,
Using Dir() has never seemed to slow anything down for me. Clicking
the button has always instantaneously opened the form in question.
When the problem does arise -- and it's only in Access 2007/2010/Win7
-- as soon as I copy a backup of the FE and try the button the form
once again opens immediately. My reasoning may be faulty, but I'm
thinking that rules out a network connection problem in general. (??)
Also, there's the issue that just closing the FE and reopening it
without replacing it doesn't work and rebooting the computer without
replacing the FE also doesn't work.

Having said all that, I recognize your expertise, so I would like to
replace Dir() with your function. Do I simply copy/paste your function
into my form and then replace "If Len(Dir(ServerRomney)) > 0 Then"
with:
"If CheckForNetwork(ServerRomney) Then" --?
Thanks,
Mark