none
Access 2007 Update command's path VBA code not working RRS feed

  • Question

  • I have created nine (9) District Databases and one (1) Master District databases. Data collected is entered into the District Databases and the Master District database will update its data from them.

    the VBA code for the Update command for the District master looks something like this:

    Private Sub Update_Button_Click()
    On Error GoTo Err_Update_Button_Click

        msg = "This will first remove, and then update ALL information for " & [District] & " District from the " & [District] & " District Database.  Are you sure you want to do this?"
        resp = MsgBox(msg, vbYesNo + vbQuestion)
        If resp <> vbYes Then
        
        path = FindDatabase([District])
       
            If Nz(path) <> "" Then 
            
                DoCmd.OpenForm ("Update Database Progress")
                Forms![Update Database Progress]![District] = [District]
                Forms![Update Database Progress].Repaint
                
                On Error Resume Next
                
                DoCmd.DeleteObject acTable, "Imported Ward List"   
                DoCmd.DeleteObject acTable, "Imported Village List"  
                DoCmd.DeleteObject acTable, "Imported Infrastructure"    
                DoCmd.DeleteObject acTable, "Imported Population District"    
                DoCmd.DeleteObject acTable, "Imported Travel Times"  
                
                On Error GoTo Err_Update_Button_Click
                
                DoCmd.TransferDatabase acImport, "Microsoft Access", _
                path, acTable, "Ward List", _
                "Imported Ward List"
                
                DoCmd.TransferDatabase acImport, "Microsoft Access", _
                path, acTable, "Village List", _
                "Imported Village List"
                
                DoCmd.TransferDatabase acImport, "Microsoft Access", _
                path, acTable, "Infrastructure", _
                "Imported Infrastructure"
                
                DoCmd.TransferDatabase acImport, "Microsoft Access", _
                path, acTable, "Population District", _
                "Imported Population District"

                DoCmd.TransferDatabase acImport, "Microsoft Access", _
                path, acTable, "Travel Times", _
                "Imported Travel Times"
                
                Forms![Update Database Progress]![Import].Visible = True
                Forms![Update Database Progress].Repaint

                DoCmd.SetWarnings False
                     
                DoCmd.RunSQL ("DELETE [Ward List].LLG FROM [Ward List] WHERE ((([Ward List].LLG) In (SELECT DISTINCT [Imported Ward List].LLG FROM [Imported Ward List];)));")

                     DoCmd.RunSQL ("INSERT INTO [Ward List] ( LLG, [Ward Number], [Ward Name], Councillor, Notes ) SELECT [Imported Ward List].LLG, [Imported Ward List].[Ward Number], [Imported Ward List].[Ward Name], [Imported Ward List].Councillor, [Imported Ward List].Notes FROM [Imported Ward List];")
                
                 Forms![Update Database Progress]![Ward].Visible = True
                Forms![Update Database Progress].Repaint


                  DoCmd.RunSQL ("DELETE [Village List].[LLG Name] FROM [Village List] WHERE ((([Village List].[LLG Name]) In (SELECT DISTINCT [Imported Ward List].LLG FROM [Imported Ward List];)));")

               
                DoCmd.RunSQL ("INSERT INTO [Village List] SELECT [Imported Village List].* FROM [Imported Village List];")
                
                       Forms![Update Database Progress]![Village].Visible = True
                Forms![Update Database Progress].Repaint


                   DoCmd.RunSQL ("DELETE [Infrastructure].LLG FROM [Infrastructure] WHERE ([Infrastructure].[District] = Forms![Update Database]![District]);")

               
                DoCmd.RunSQL ("INSERT INTO [Infrastructure] SELECT [Imported Infrastructure].* FROM [Imported Infrastructure];")
                
                Forms![Update Database Progress]![Infrastructure].Visible = True
                Forms![Update Database Progress].Repaint


                  DoCmd.RunSQL ("DELETE [Population District].[District] FROM [Population District] WHERE ([Population District].[District] = Forms![Update Database]![District]);")

                        DoCmd.RunSQL ("INSERT INTO [Population District] SELECT [Imported Population District].* FROM [Imported Population District];")
                
                Forms![Update Database Progress]![Population].Visible = True
                Forms![Update Database Progress].Repaint


                DoCmd.RunSQL ("DELETE [Travel Times].LLG FROM [Travel Times] WHERE ([Travel Times].District = Forms![Update Database]![District]);")

                DoCmd.RunSQL ("INSERT INTO [Travel Times] SELECT [Imported Travel Times].* FROM [Imported Travel Times];")
                
                Forms![Update Database Progress]![Accessibility].Visible = True
                Forms![Update Database Progress].Repaint

                
                DoCmd.DeleteObject acTable, "Imported Ward List"   
                DoCmd.DeleteObject acTable, "Imported Village List"    
                DoCmd.DeleteObject acTable, "Imported Infrastructure"   
                DoCmd.DeleteObject acTable, "Imported Population District"    
                DoCmd.DeleteObject acTable, "Imported Travel Times"   
                
                Forms![Update Database Progress]![Delete].Visible = True
                Forms![Update Database Progress].Repaint

                DoCmd.SetWarnings True
                
                 [Last Updated] = Date
                
                DoCmd.Close acForm, "Update Database Progress"
                
                resp = MsgBox([District] & " District Information successfully refreshed from District Database.  The database will now be compressed.", vbInformation)
                
                SendKeys "%TDC"

            End If
        End If
       
    Exit_Update_Button_Click:
        Exit Sub

    Err_Update_Button_Click:
        If Err.Number = 3024 Then    
            resp = MsgBox("Error 3024 - cannot find the " & [District] & " Database you specified.", vbExclamation)
        Else
            MsgBox Err.Description
        End If
        Resume Exit_Update_Button_Click
       
    End Sub

    ______________________________

    However the path to the District database is not shown, therefore I cannot choose which District to update from and thus I cannot update my Master District Database.

    pls help.



    • Edited by Mark Gedisa Friday, February 15, 2013 2:46 AM
    Thursday, February 7, 2013 11:09 PM