Problems moving a database

[RichardMGreen]

Hi all

I've got a database I've built (after a fashion) which works perfectly on my machine.

However, when I transferred it to another machine for someone else to use, I get errors.

The first one being an error in the "on click" event of a button. This one comes up as an automation error.

These are the relevant pieces of code:-

Private Sub Browse_Click()
    Me.txtExtractsLocation.SetFocus
    Me.txtExtractsLocation = GetFolderName([DefaultFileLocation])
End Sub
 
Function GetFolderName(Optional OpenAt As String) As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = OpenAt
        .Show
            For lCount = 1 To .SelectedItems.Count
                sSelectedFolder = .SelectedItems(lCount)
            Next lCount
    End With
    If Len(sSelectedFolder) = 0 Then
        GetFolderName = DLookup("DefaultFileLocation", "C4CDate")
    Else
        GetFolderName = sSelectedFolder
    End If
End Function

This is also happening for this piece of code:-

Private Sub cmdPerformanceRep_Click()
    Me.txtExtractsLocation.SetFocus
    file_location = txtExtractsLocation.Text
    If Right(file_location, 1) <> "\" Then file_location = file_location & "\"
    UserForm1.ProgressBar1.Max = 100
    UserForm1.ProgressBar1.Value = 0
    UserForm1.Show
    continue = False
    Call importer(file_location)
    If continue Then
        On Error GoTo 0
        GobleC4CDataExtDt = FileDateTime(file_location & "ASMT.csv") & " " & "AM"
        DoCmd.SetWarnings False
        UserForm1.Label1.Caption = "Building temporary tables ...."
        UserForm1.Label2.Caption = "Making Member Demographics"
        UserForm1.Repaint
        DoCmd.OpenQuery ("make_member_demographics")
        DoCmd.RunSQL ("CREATE INDEX Member on [Care Plan Report - Member Demographics] (MEMBER_C4C_ID);")
        Call file_count
        UserForm1.Label2.Caption = "Making Stage of Change"
        UserForm1.Repaint
        DoCmd.OpenQuery ("make_stage_of_change")
        DoCmd.RunSQL ("CREATE INDEX Member on [Care Plan Report - Stages Of Change] (C4C_ID);")
        Call file_count
        UserForm1.Label2.Caption = "Making 8 Care Plan"
        UserForm1.Repaint
        DoCmd.OpenQuery ("make_basic8_care_plan")
        DoCmd.RunSQL ("CREATE INDEX Member on [Care Plan Report - Basic 8 Care Plan] (C4C_ID);")
        Call file_count
        UserForm1.Label2.Caption = "Making Clinical Data"
        UserForm1.Repaint
        DoCmd.OpenQuery ("make_clinical_data")
        DoCmd.RunSQL ("CREATE INDEX Member on [Care Plan Report - Clinical Data] (C4C_ID);")
        Call file_count
        UserForm1.Label2.Caption = "Making Taken Assessments"
        UserForm1.Repaint
        DoCmd.OpenQuery ("make_taken_assessments")
        DoCmd.RunSQL ("CREATE INDEX Member on [Care Plan Report - Taken Assessments] (C4C_ID);")
        Call file_count
        DoCmd.RunSQL ("update C4CDate set c4cextractiondate = '" & GobleC4CDataExtDt & _
            "',defaultfilelocation= '" & file_location & "'")
        DoCmd.SetWarnings True
        MsgBox "Database Built"
    End If
    Unload UserForm1
    Exit Sub
dropout:
    MsgBox "Unable to build database" & vbCrLf & "Please ensure linked tables have required data and files exist"
    Unload UserForm1
End Sub

I'd be grateful for error pointers to solve this one.

I've just noticed I've put this in the Excel section, would one of the mods please move it to the Access section?

 

[RoryA]

Moved.

PS might help if you said what error you get.

 

[jackd]

My first guess would be missing references.
You're referencing msoFileDialogFolderPicker and will need a reference to Office...if you don't have it referenced already.

I'm using Acc2003. Here's some code that might help finding broken references.

'---------------------------------------------------------------------------------------
' Procedure : Get_References_In_This_Project
' Author : user
' Date : 2/18/2009
' Purpose : List the references in the VBA Project and indicate where
'the reference is OK or Is Broken (Missing)
' Get the name, description, full path and "Missing/Broken" for each reference
'---------------------------------------------------------------------------------------
'
Sub Get_References_In_This_Project()
Dim refIsBroken As String

'Loop thru each reference in this VBA Project
For Each ref In Application.VBE.ActiveVBProject.References
refIsBroken = "OK"
' Get the Reference Name
refname = ref.name
'If ref.FullPath Like "*excel*" Then
' MsgBox "Excel ref is " & ref.FullPath
'End If
' Get the Reference Description
refDesc = ref.Description
refPath = ref.FullPath
'Return True/False indicating if Reference link is broken: True(Ref is missing) or False (Ref OK)
If ref.IsBroken = True Then
refIsBroken = "***Missing/Broken***"
Else
refIsBroken = "OK"
End If
Debug.Print refname & ": " & refDesc & " - " & refPath & " -> " & refIsBroken
Next ref

End Sub

 

[RichardMGreen]

Thanks rorya

I've checked all the references and they all seem to be in place.
The error just says:-
"The expression On Click you enetered as the event property setting produced the following error: Automation Error

The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
There may have been an error evaluating the function, event or macro"

I think it may be due to different settings on different machines as I've tried a known good copy (my original) on this machine and it's giving me the same error.

 

[RichardMGreen]

Solved it.
It's a problem with other software on certain machines and some settings in the user profiles.

Off to IT goes this one.