By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,119 Members | 587 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,119 IT Pros & Developers. It's quick & easy.

can i audit my table when an excel sheet import is done?

P: 5
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private msaved As Boolean
  4.  
  5.  
  6. Private Sub Combo309_BeforeUpdate(cancel As Integer)
  7.  
  8. If Combo309.Value = "HR" Then
  9.     Call GetCount("HR")
  10.         If hrcount = 10 Then
  11.             MsgBox "you have already reached the limit, choose another department or will be assigned"
  12.             cancel = True
  13.             Me.Combo309.Undo
  14.         End If
  15. End If
  16. If Combo309.Value = "IT" Then
  17.     Call GetCount("IT")
  18.         If itcount = 10 Then
  19.             MsgBox "you have already reached the limit, choose another department or will be assigned"
  20.             cancel = True
  21.             Me.Combo309.Undo
  22.         End If
  23. End If
  24. End Sub
  25.  
  26. Private Sub Command198_Click()
  27. msaved = True
  28. MsgBox "new record added"
  29. Call AuditChangesSub("ID", "new")
  30. Me.Requery
  31. End Sub
  32.  
  33. Private Sub Command199_Click()
  34. Me.Undo
  35. DoCmd.BrowseTo acBrowseToForm, "empdetailsform", , , acFormAdd
  36. End Sub
  37.  
  38.  
  39. Private Sub browse_Click()
  40.     Dim diag As Office.FileDialog
  41.     Dim item As Variant
  42.     Set diag = Application.FileDialog(msoFileDialogFilePicker)
  43.     diag.AllowMultiSelect = False
  44.     diag.Title = "Please select an excel sheet"
  45.     diag.Filters.Clear
  46.     diag.Filters.Add "excel spreadsheet", "*.xls,*.xlsx"
  47.     If diag.Show Then
  48.         For Each item In diag.SelectedItems
  49.             Me.filename = item
  50.         Next
  51.     End If
  52. End Sub
  53.  
  54.  
  55.  
  56. Private Sub employee_id_BeforeUpdate(cancel As Integer)
  57. Dim newemployeeid As String
  58. Dim employeeidcriteria As String
  59. newemployeeid = Me.employee_id.Value
  60. employeeidcriteria = "[employee id]=" & "'" & newemployeeid & "'"
  61. If Me.employee_id = DLookup("[employee id]", "empdetails", employeeidcriteria) Then
  62.     MsgBox "This employee ID," & employee_id & " ,has already been assigned.", vbInformation, "Duplicate employee ID"
  63.     cancel = True
  64.     Me.employee_id.Undo
  65. End If
  66. End Sub
  67.  
  68. Private Sub Form_BeforeUpdate(cancel As Integer)
  69.     If msaved = False Then
  70.     cancel = True
  71.     Me.Undo
  72.     cancel = False
  73.     End If
  74.  
  75. End Sub
  76.  
  77. Private Sub Form_Current()
  78. msaved = False
  79.  
  80. End Sub
  81.  
  82.  
  83. Private Sub id_BeforeUpdate(cancel As Integer)
  84. Dim newid As Integer
  85. Dim idcriteria As String
  86. newid = Me.ID.Value
  87. idcriteria = "[ID]=" & "" & newid & ""
  88. If Me.ID = DLookup("[ID]", "empdetails", idcriteria) Then
  89.     MsgBox "This ID," & ID & " ,has already been assigned.", vbInformation, "Duplicate ID"
  90.     cancel = True
  91.     Me.ID.Undo
  92. End If
  93. End Sub
  94.  
  95. 'Public Function GetCount(department As String) As Integer
  96.  
  97. 'hrcount = DCount("[employee id]", "empdetails", "department='HR'")
  98. 'itcount = DCount("[employee id]", "empdetails", "department='IT'")
  99. 'End Function
  100.  
  101.  
  102. Private Sub import_Click()
  103.     Dim FSO As New FileSystemObject
  104.     If Nz(Me.filename, "") = "" Then
  105.         MsgBox "Please select a file."
  106.         Exit Sub
  107.     End If
  108.  
  109.     If FSO.FileExists(Nz(Me.filename, "")) Then
  110.        DoCmd.SetWarnings False
  111.        importexcelsheet.importexcelspreadsheet Me.filename, "empdetails"
  112.        DoCmd.SetWarnings True
  113.        'Call AuditChangesSub("ID", "new")
  114.     Else
  115.         MsgBox "File not found."
  116.     End If
  117. filename.Value = ""
  118. End Sub
  119.  
1 Week Ago #1
Share this Question
Share on Google+
1 Reply

cactusdata
Expert 100+
P: 122
Yes. Open the table in normal view and audit the data.
1 Week Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.