Import Data via Excel Is Updating All the Records for a Particular Employee

**Deggsy** · 06-04-2015, 08:30 AM

We have a form on our training database than can update training information via Excel sheets received from the trainers.
The Update button runs on the following code:

Code:

Private Sub Command2_Click()
Dim strSQL As String
Dim txtLst As String
Dim msgAns As Integer
DoCmd.SetWarnings False
If IsNull(Me!Table_List) = False Then
strSQL = "UPDATE [" & Me.Table_List & "] INNER JOIN [Course Completed] ON [" & Me.Table_List & "].[Employee Number] = [Course Completed].[Employee Number] SET [Course Completed].Attended = [" & Me.Table_List & "].[Attended];"
CurrentDb.QueryDefs("Register_Update_Query").Sql = strSQL
Else
    MsgBox "Please choose the Register you wish to update", vbOKOnly, "No File Select!"
    Exit Sub
End If
            DoCmd.OpenQuery "Register_Update_Query", acViewNormal, acEdit
strSQL = "INSERT INTO [Training Run] ( OT, Course, Course_Date, Start_Time, Finish_Time ) " & _
            "SELECT TOP 1 [" & Me.Table_List & "].[OT], [" & Me.Table_List & "].[Course Name], [" & Me.Table_List & "].[Course Date], [" & Me.Table_List & "].[Start Time], [" & Me.Table_List & "].[Finish Time] " & _
            "FROM [" & Me.Table_List & "];"
CurrentDb.QueryDefs("Register_Update_Query").Sql = strSQL
            DoCmd.OpenQuery "Register_Update_Query", acViewNormal, acEdit
            
            msgAns = MsgBox("Do You Wish To Remove the Register?" & vbNewLine & vbNewLine & "If you wish to remove later you will need to go through this process again.", vbYesNo, "Remove Rgister")
If msgAns = vbYes Then
            DoCmd.DeleteObject acTable, Me!Table_List
Else
            Cancel = True
End If
DoCmd.SetWarnings True
Dim intItemsInList As Integer
Dim intCounter As Integer
  
intItemsInList = Me!Table_List.ListCount
  
For intCounter = 0 To intItemsInList - 1
  Me!Table_List.RemoveItem 0
Next
Me.Table_List.SetFocus
End Sub

When imported, it creates a table based on the Excel sheet, links to other appropriate and updates the employees' training history in the table Course Completed (Design and Datasheet Views attached - See below), which among its fields is one for Attended, which will be either Y or N.
It is possible that an employee can attend a training session on one date and not attend a later one, as in the example in the image below.

What seems to be happening is that when an Excel sheet is received from a trainer, and it contains an employee with a record already in the table, that whatever Attended status is on the Excel sheet just received will override all the statuses in the table for that employee (in the example below, for instance, one employee should have had an Attended Status of Y on 27/04/2015 and N on 25/05/2015. The 27/04/2015 imported fine, but when the 25/05/2015 register was imported, where the employee Attended Status was N, the Attended status for this employee for 27/04/2015 was changed to N).

I'm assuming at this stage that this is occurring either because of something in the above code during the import code, or because a property needs to be set in the Course Completed table in order to prevent data in previous records from being changed unless the exact dates are used, or something like that. Any advice would be appreciated...

Click image for larger version.

Name: Course Completed Datasheet View.JPG
Views: 18
Size: 32.1 KB
ID: 20921

Click image for larger version.

Name: Course Completed Design View.JPG
Views: 18
Size: 52.2 KB
ID: 20920

**June7** · 06-04-2015, 10:12 AM

Yes, need more criteria in the UPDATE action to prevent other records being updated. Perhaps comparing date values will accomplish. If you need more help, post the Register_Update_Query statement for analysis.

**Deggsy** · 06-04-2015, 02:12 PM

Originally Posted by June7

Yes, need more criteria in the UPDATE action to prevent other records being updated. Perhaps comparing date values will accomplish. If you need more help, post the Register_Update_Query statement for analysis.

Thanks - here's the SQL for the Register_Update_Query, with the name of whatever imported spreadsheet taking the name of the table:

Code:

INSERT INTO [Training Run] ( OT, Course, Course_Date, Start_Time, Finish_Time )
SELECT TOP 1 [Imported Spreadsheet Table].[OT], [Imported Spreadsheet Table].[Course Name], [Imported Spreadsheet Table].[Course Date], [Imported Spreadsheet Table].[Start Time], [Imported Spreadsheet Table].[Finish Time]
FROM [Imported Spreadsheet Table];

**June7** · 06-04-2015, 02:39 PM

That is an INSERT action. It should not impact existing records, only add new records.

An UPDATE action will modify existing records.

I see now the code is using QueryDefs to modify query object. Why? Don't even need objects to execute these SQL actions. I use CurrentDb.Execute method.

**Deggsy** · 06-08-2015, 03:32 AM

Originally Posted by June7

That is an INSERT action. It should not impact existing records, only add new records.

An UPDATE action will modify existing records.

I see now the code is using QueryDefs to modify query object. Why? Don't even need objects to execute these SQL actions. I use CurrentDb.Execute method.

Thanks for responding - Based on my limited knowledge, it appears that it was set up like this in case a returned register was incomplete (for instance leaving the times off it) but that existing records would still be updated (e.g. whether or not the employee attended the particular course). It creates a temporary table for the Excel register that is chosen and imported.

I'm not that great on SQL - is there anything I can add to the existing SQL to keep all the Attended Statuses of a particular employee from being changed? (I'll add all the code below):

Code:

Private Sub Command2_Click()
Dim strSQL As String
Dim txtLst As String
Dim msgAns As Integer
DoCmd.SetWarnings False
If IsNull(Me!Table_List) = False Then
strSQL = "UPDATE [" & Me.Table_List & "] INNER JOIN [Course Completed] ON [" & Me.Table_List & "].[Employee Number] = [Course Completed].[Employee Number] SET [Course Completed].Attended = [" & Me.Table_List & "].[Attended];"
CurrentDb.QueryDefs("Register_Update_Query").Sql = strSQL
Else
    MsgBox "Please choose the Register you wish to update", vbOKOnly, "No File Select!"
    Exit Sub
End If
            DoCmd.OpenQuery "Register_Update_Query", acViewNormal, acEdit
strSQL = "INSERT INTO [Training Run] ( OT, Course, Course_Date, Start_Time, Finish_Time ) " & _
            "SELECT TOP 1 [" & Me.Table_List & "].[OT], [" & Me.Table_List & "].[Course Name], [" & Me.Table_List & "].[Course Date], [" & Me.Table_List & "].[Start Time], [" & Me.Table_List & "].[Finish Time] " & _
            "FROM [" & Me.Table_List & "];"
CurrentDb.QueryDefs("Register_Update_Query").Sql = strSQL
            DoCmd.OpenQuery "Register_Update_Query", acViewNormal, acEdit
            
            msgAns = MsgBox("Do You Wish To Remove the Register?" & vbNewLine & vbNewLine & "If you wish to remove later you will need to go through this process again.", vbYesNo, "Remove Rgister")
If msgAns = vbYes Then
            DoCmd.DeleteObject acTable, Me!Table_List
Else
            Cancel = True
End If
DoCmd.SetWarnings True
Dim intItemsInList As Integer
Dim intCounter As Integer
  
intItemsInList = Me!Table_List.ListCount
  
For intCounter = 0 To intItemsInList - 1
  Me!Table_List.RemoveItem 0
Next
Me.Table_List.SetFocus
End Sub

Private Sub Form_Load()
Dim obj As AccessObject
Dim dbs As Object
Dim ctrlListBox As ListBox
Set dbs = Application.CurrentData
Set ctrlListBox = Me.Table_List
Dim intItemsInList As Integer
Dim intCounter As Integer
  
intItemsInList = Me!Table_List.ListCount
  
For intCounter = 0 To intItemsInList - 1
  Me!Table_List.RemoveItem 0
Next
    With ctrlListBox
      For Each obj In dbs.AllTables
        If Left$(obj.Name, 8) = "Register" Then
            .AddItem Item:=obj.Name
        End If
      Next obj
    End With
End Sub

Private Sub Form_Timer()
Set dbs = Application.CurrentData
Set ctrlListBox = Me.Table_List
Dim intItemsInList As Integer
Dim intCounter As Integer
  
intItemsInList = Me!Table_List.ListCount
  
For intCounter = 0 To intItemsInList - 1
  Me!Table_List.RemoveItem 0
Next

    With ctrlListBox
      For Each obj In dbs.AllTables
        If Left$(obj.Name, 8) = "Register" Then
            .AddItem Item:=obj.Name
        End If
      Next obj
    End With
End Sub

Private Sub Table_List_GotFocus()
Dim obj As AccessObject
Dim dbs As Object
Dim ctrlListBox As ListBox
Set dbs = Application.CurrentData
Set ctrlListBox = Me.Table_List
Dim intItemsInList As Integer
Dim intCounter As Integer
  
intItemsInList = Me!Table_List.ListCount
  
For intCounter = 0 To intItemsInList - 1
  Me!Table_List.RemoveItem 0
Next

    With ctrlListBox
      For Each obj In dbs.AllTables
        If Left$(obj.Name, 8) = "Register" Then
            .AddItem Item:=obj.Name
        End If
      Next obj
    End With
End Sub

**June7** · 06-08-2015, 09:21 AM

Not sure what the code looping through listbox items is accomplishing. I think it is just building the listbox list of items. Why it is in the timer event is beyond me. I do see the same code repeated in 3 events. Really should have the repetitive code in one procedure that can be called by the 3 events.

Code:

Private Sub Form_Load()
Call ListBoxEdit
End Sub

Private Sub Form_Timer()
Call ListBoxEdit
End Sub

Private Sub Table_List_GotFocus()
Call ListBoxEdit
End Sub

Sub ListBoxEdit()
Dim obj As AccessObject
Dim dbs As Object
Dim ctrlListBox As ListBox
Set dbs = Application.CurrentData
Set ctrlListBox = Me.Table_List
Dim intItemsInList As Integer
Dim intCounter As Integer
  
intItemsInList = Me!Table_List.ListCount
  
For intCounter = 0 To intItemsInList - 1
  Me!Table_List.RemoveItem 0
Next
With ctrlListBox
  For Each obj In dbs.AllTables
    If Left$(obj.Name, 8) = "Register" Then
        .AddItem Item:=obj.Name
    End If
  Next obj
End With
End Sub

Still not seeing code or query that updates existing data.

**Deggsy** · 06-09-2015, 01:27 AM

Originally Posted by June7

Not sure what the code looping through listbox items is accomplishing. I think it is just building the listbox list of items. Why it is in the timer event is beyond me. I do see the same code repeated in 3 events. Really should have the repetitive code in one procedure that can be called by the 3 events.

Code:

Private Sub Form_Load()
Call ListBoxEdit
End Sub

Private Sub Form_Timer()
Call ListBoxEdit
End Sub

Private Sub Table_List_GotFocus()
Call ListBoxEdit
End Sub

Sub ListBoxEdit()
Dim obj As AccessObject
Dim dbs As Object
Dim ctrlListBox As ListBox
Set dbs = Application.CurrentData
Set ctrlListBox = Me.Table_List
Dim intItemsInList As Integer
Dim intCounter As Integer
  
intItemsInList = Me!Table_List.ListCount
  
For intCounter = 0 To intItemsInList - 1
  Me!Table_List.RemoveItem 0
Next
With ctrlListBox
  For Each obj In dbs.AllTables
    If Left$(obj.Name, 8) = "Register" Then
        .AddItem Item:=obj.Name
    End If
  Next obj
End With
End Sub

Still not seeing code or query that updates existing data.

Yeah, it's a mess, isn't it? This is what you get when you let one person build a database the way they want it. It's fine - so long as they don't leave :-)

Thanks for looking at it anyway, it's greatly appreciated :-) :-) :-)

Import Data via Excel Is Updating All the Records for a Particular Employee

Thread Tools

Import Data via Excel Is Updating All the Records for a Particular Employee

Similar Threads

Import data from Excel monthly - not manual input all data

Import access query data into excel file, using excel vba

import excel sheet causes autonumber beyond records

Updating Multiple Access Records From Imported Excel Spreadsheet

Updating Access Database With New Excel Data

Posting Permissions