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.
