Switchboard Form

[pfriedmann]

Let me say this as a preface, this database(s) and everything in them was here before I started to work here 2 months ago. The people that did all of the work have been gone for several years. I have correct tons of issues with all of the access databases people use here, this is one issue I can not resolve so I am reaching out for help. There is a switchboard form that is used to jump back and forth between databases such as when in a productivity database and want to go to a employee database you can use the form instead of closing one and opening another one. On 64bit Windows with 32bit office installed a 'runtime error 5 invalid call or argument' error comes up with switching between databases. This only happens with 64 bit, everyone else that uses 32 bit OS receives no errors and everything works fine. When I go to debug the following code is highlighted:

Private Sub OpenDatabaseFile(strDatabaseName As String, strDatabasePath As String)
Shell Chr(34) & FindAccessFileLocation & Chr(34) _
& " " & Chr(34) & strDatabasePath & strDatabaseName & Chr(34), vbMaximizedFocus
DoCmd.Quit acQuitSaveNone
End Sub



Does anyone have any ideas? Thanks

[June7]

Post the code for FindAccessFileLocation procedure.

The following works for me in 32- and 64- bit.
Shell SysCmd(acSysCmdAccessDir) & "MSAccess.exe " & """" & strFilePath & """", vbNormalFocus

[pfriedmann]

So do I place the code in the Private Function FindAccessFileLocation() As String or in the procedure that is above? I am kind of a newbie when it comes to VB, I had it in school years ago but the place I worked before this did not use a lot of applications that was coded in VB so I am trying to relearn everything all over again, thanks

[June7]

I don't know what that function does so can't be sure. I asked you to post the code. Maybe it is just running SysCmd(acSysCmdAccessDir).

[pfriedmann]

Here is the FindAccess procedure, sorry I misunderstood you:

Private Function FindAccessFileLocation() As String
On Error Resume Next
'this is to handle server utilization, as well as client machines
Dim varReturnValue As Variant
varReturnValue = FileDateTime("C:\Program Files\Microsoft Office\Office14\MSACCESS.exe")
'test 1
If IsNull(varReturnValue) Then
GoTo NOT_FOUND
ElseIf (varReturnValue) = "" Then
GoTo NOT_FOUND
Else
FindAccessFileLocation = "C:\Program Files\Microsoft Office\Office14\MSACCESS.exe"
GoTo EXIT_CALLER
End If
NOT_FOUND:
'test 2
varReturnValue = FileDateTime("E:\Program Files\Microsoft Office\Office14\MSACCESS.exe")
If IsNull(varReturnValue) Then
GoTo EXIT_CALLER
ElseIf (varReturnValue) = "" Then
GoTo EXIT_CALLER
Else
FindAccessFileLocation = "E:\Program Files\Microsoft Office\Office14\MSACCESS.exe"
End If
EXIT_CALLER:
Set varReturnValue = Nothing
End Function

[June7]

Windows 7 64-bit machines have two Program Files folders.

Program Files
Program Files (x86)

The first is for 64-bit apps.
The second is for 32-bit apps.

(Don't shoot me, just the messenger.)

Are you running 32-bit Access on all machines? Your db is designed with 32-bit Access?

Even on the 64-bit machines the code is looking for Access app in the Program Files folder.

I think you can and should eliminate this function and use the code I suggested.

[pfriedmann]

Yes 32 bit access is running on all machines, and the db was created on a 32 bit version of access. I will try the code you suggested under the findaccessfile procedure, will let you know what happens, thanks

[June7]

My suggestion is to eliminate the FindAccessFileLocation function.

Replace your Shell statement with mine, except fix to use your path and file name variables.

[pfriedmann]

Now I get the "error 53 file not found"

[June7]

Post your exact attempted code for analysis.

[pfriedmann]

When I hit debug there is a section highlighted in red. Here is the entire code for that module on the switchboard:

Option Compare Database
Option Explicit
'load the user name of the network login
Public g_strOSUserName As String
'system errors
Public Enum SystemError
SimpleError = 1
ExtendedError = 2
ValidNameEntry = 3
End Enum
Public Enum ImportTimeOption
Minutes = 1
Hours = 2
End Enum
'callers to API
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
Public Sub LoadOSUser()
Dim strReturnValue As String * 20
Dim intReturnLength As Long
intReturnLength = GetUserName(strReturnValue, 20)
If intReturnLength > 0 Then
g_strOSUserName = UCase(Left(strReturnValue, InStr(strReturnValue, Chr(0)) - 1))
Else
g_strOSUserName = "NA"
End If
End Sub
Public Sub OpenEmployeeSystem()
OpenDatabaseFile "Employee System.accdb", "\\w2lefs\Software\Warehouse Databases\Employee System\"
End Sub
Private Sub OpenDatabaseFile(strDatabaseName As String, strDatabasePath As String)
Shell SysCmd(acSysCmdAccessDir) & "C:\Program Files(x86)\Microsoft Office\Office14\MSACCESS.exe" & """ & strFilePath & """, vbNormalFocus
DoCmd.Quit acQuitSaveNone
End Sub

Public Sub ProcessProductivityImport(strLinkTableName As String)
'This block is the parse portion of the productivty import. This will breack down the data
'into attributes to be used in the tables and queries.
Dim lngLoopIndex As Long
Dim lngActivityID As Long
Dim bolAddNewRow As Boolean
Dim strUnitsOfMeasure As String
Dim dteProductivityDate As Date
Dim strRowHolder As String
Dim bolStandardTriggered As Boolean
Dim s_recDataSource As Recordset
Dim s_recDataStore As Recordset
Set s_recDataSource = CurrentDb.OpenRecordset(strLinkTableName, dbOpenSnapshot)
Set s_recDataStore = CurrentDb.OpenRecordset("tblProductivity", dbOpenDynaset)
With s_recDataSource
Do While .EOF = False
'///////////////////////////////////////////
'get the report date
If (![DATA_SOURCE]) Like "*Reporting Date Run:*" Then
dteProductivityDate = CDate(Trim(Mid(![DATA_SOURCE], InStr(1, ![DATA_SOURCE], ":") + 1, 11)))
If (ProductivityDateExists(dteProductivityDate)) = True Then
MsgBox "The productivity date you have specified already exists.", vbExclamation, ReturnSystemTitle
GoTo EXIT_CALLER
End If
.MoveNext
End If
'///////////////////////////////////////////
'check and get the activity id
If (![DATA_SOURCE]) Like "*UNIT OF MEASURE:*" Then
lngActivityID = ValidateActivity(Trim(Mid$(![DATA_SOURCE], 14, 14)), _
Trim(Mid$(![DATA_SOURCE], 45)))
.MoveNext
End If
'///////////////////////////////////////////
Do While ![INDICATOR] <> 0
s_recDataStore.AddNew
strRowHolder = Trim(![DATA_SOURCE])
s_recDataStore![PRODUCTIVITY_DATE] = dteProductivityDate
s_recDataStore![Employee_Name] = Trim(Left$(strRowHolder, 22))
s_recDataStore![TIME_IN_ACTUAL] = Trim(Mid(strRowHolder, 23, 5))
s_recDataStore![WORK_TIME] = IIf(Trim(Mid(strRowHolder, 28, 10)) = "", "", _
Trim(Mid(strRowHolder, 28, 10)))
s_recDataStore![TOTAL_COUNT] = IIf(Trim(Mid(strRowHolder, 38, 6)) = "", 0, _
Trim(Mid(strRowHolder, 38, 6)))
s_recDataStore![COUNT_HOURS] = Trim(Mid(strRowHolder, 44, 7))
If (bolStandardTriggered) = False Then
If (Trim(Mid(strRowHolder, 51, 8))) <> "" Then
UpdateActivityStandard lngActivityID, Trim(Mid(strRowHolder, 51, 8))
bolStandardTriggered = True
End If
End If
s_recDataStore![ACTIVITY_ID] = lngActivityID
s_recDataStore![VARIANCE] = IIf(Trim(Mid(strRowHolder, 59, 11)) = "", 0, _
Replace(Trim(Mid(strRowHolder, 59, 11)), "*", ""))
s_recDataStore![LOCATION_VISITED] = IIf(Trim(Mid(strRowHolder, 71, 8)) = "", 0, _
Trim(Mid(strRowHolder, 71, 8)))
s_recDataStore![PALLETS] = IIf(Trim(Mid(strRowHolder, 79, 7)) = "", 0, _
Trim(Mid(strRowHolder, 79, 7)))
s_recDataStore![PA_CARTONS_BUNDLES] = IIf(Trim(Mid(strRowHolder, 86, 8)) = "", 0, _
Trim(Mid(strRowHolder, 86, 8)))
s_recDataStore![GOOD_TOTE] = IIf(Trim(Mid(strRowHolder, 93, 4)) = "", 0, _
Trim(Mid(strRowHolder, 93, 4)))
s_recDataStore![GOOD_SCAN] = IIf(Trim(Mid(strRowHolder, 97, 5)) = "", 0, _
Trim(Mid(strRowHolder, 97, 5)))
s_recDataStore![BAD_TOTE] = IIf(Trim(Mid(strRowHolder, 102, 4)) = "", 0, _
Trim(Mid(strRowHolder, 102, 4)))
s_recDataStore![BAD_SCAN] = IIf(Trim(Mid(strRowHolder, 106, 5)) = "", 0, _
Trim(Mid(strRowHolder, 106, 5)))
s_recDataStore![DIRECT_TO_TRUCK] = IIf(Trim(Mid(strRowHolder, 111, 5)) = "", 0, _
Trim(Mid(strRowHolder, 111, 5)))
s_recDataStore.Update
.MoveNext
If (.EOF) = True Then GoTo EXIT_CALLER
Loop
bolStandardTriggered = False
'///////////////////////////////////////////
.MoveNext
Loop
End With
EXIT_CALLER:
s_recDataSource.Close
s_recDataStore.Close
Set s_recDataSource = Nothing
Set s_recDataStore = Nothing
End Sub
Private Sub UpdateActivityStandard(lngActivityID As Long, strStandard As String)
Dim s_recActivityStandard As Recordset
Set s_recActivityStandard = CurrentDb.OpenRecordset("SELECT * FROM " & _
"[tblActivity] WHERE [ACTIVITY_ID] = " & lngActivityID & ";", dbOpenDynaset)
With s_recActivityStandard
If (.EOF) = False Then
.Edit
![STANDARD] = CLng(Trim(strStandard))
.Update
End If
End With
s_recActivityStandard.Close
Set s_recActivityStandard = Nothing
End Sub
Public Function ProductivityImportLink(strFileNamePath As String) As String
'The strLinkTable is where the raw data will be stored. The filter query is run off of the
'data in the strLinkTable and then the parse is enacted upon the query breaking the data
'down into the corresponding attributes to be stored in tblProductivity.
Dim strLinkTable As String
Dim varObjectList As Variant
strLinkTable = "lntblPRODIMPORT"
For Each varObjectList In Application.CurrentData.AllTables
If (varObjectList.Name) = strLinkTable Then
DoCmd.DeleteObject acTable, varObjectList.Name
End If
Next varObjectList

DoCmd.TransferText acLinkDelim, "Productivity Link Specification", strLinkTable, _
strFileNamePath, True
'send the output filtered link to the query
ProductivityImportLink = "qryProductivityFilter"

Set varObjectList = Nothing
End Function
Public Function ValidateActivity(strActivity As String, strMeasure As String) As Long
Dim s_recDataActivity As Recordset
Set s_recDataActivity = CurrentDb.OpenRecordset("SELECT [ACTIVITY_ID], [ACTIVITY], [MEASURE] " & _
"FROM [tblActivity] WHERE [ACTIVITY] = '" & strActivity & "' AND [MEASURE] = '" & _
strMeasure & "';", dbOpenDynaset)
With s_recDataActivity
If (.EOF) = True Then
.AddNew
![ACTIVITY] = strActivity
![MEASURE] = strMeasure
ValidateActivity = ![ACTIVITY_ID]
.Update
Else
ValidateActivity = ![ACTIVITY_ID]
End If
End With
s_recDataActivity.Close
Set s_recDataActivity = Nothing
End Function
Public Function ReturnSystemTitle() As String
ReturnSystemTitle = "Productivity"
End Function
Public Function ReturnSystemError(enumErrorCaller As SystemError) As String
Select Case enumErrorCaller
Case 1:
ReturnSystemError = "An error occured while processing the command."
Case 2:
ReturnSystemError = "An error occured while processing the command." & _
vbNewLine & "System{" & Err.Description & "}"
Case 3:
ReturnSystemError = "You must specify a valid name to print this report."
Case Else:
ReturnSystemError = "An error occured while processing the command."
End Select
End Function
Public Function ProductivityDateExists(dteDateCriteria As Date) As Boolean
Dim s_recProductivityDate As Recordset
Set s_recProductivityDate = CurrentDb.OpenRecordset("SELECT TOP 1 [PRODUCTIVITY_ID] " & _
"FROM [tblProductivity] WHERE [PRODUCTIVITY_DATE] = #" & dteDateCriteria & _
"#;", dbOpenSnapshot)
With s_recProductivityDate
If (.EOF) = True Then
ProductivityDateExists = False
Else
ProductivityDateExists = True
End If
End With
s_recProductivityDate.Close
Set s_recProductivityDate = Nothing
End Function
Public Function ImportReturnValue(strArg As String) As Integer
Dim dteTimeHolder As Date
Dim intMinutes As Integer
If (strArg) = "" Then
intMinutes = 0
ElseIf InStr(1, strArg, ":") < 1 Then
intMinutes = CInt(strArg)
ElseIf InStr(1, strArg, ":") > 0 Then
intMinutes = DatePart("n", CDate(strArg))
intMinutes = intMinutes + (DatePart("h", CDate(strArg)) * 60)
End If
ImportReturnValue = intMinutes
End Function

[June7]

Can hardly read that blue, changed to red. The one line of code was all I needed to see.

Did not use the code I provided. Did not use your variables.

Try:

Shell SysCmd(acSysCmdAccessDir) & "\MSACCESS.exe " & """" & strDatabasePath & strDatabaseName & """", vbNormalFocus

[pfriedmann]

That worked, you sir a godsend. If I could upload a case of beer to you I would, thanks!