INSERT INTO not working correctly!!

[UrrrrgAccess]

Hi there,

I have a database I am working on which I am adding the ability to import an excel spreadsheet, clean the imported table then finally import that table into the main database table.

Everything was working smoothly but the import routine I have written just doesn't seem to work, strange thing is if I place a breakpoint on the For statement and loop through with F8 the code below inserts a record for every record I can be bothered to sit and step through (40 is my current record!).

But if I let the code just run from the button click my progress bar will fill like it it moving through the table and adding records but when I check my main table only about 3 - 5 records have actually been inserted.

I am going NUTS!!! I am sure it is probably something simple I have missed as I am in no way an Access expert.

Code is below for the button click that should add the table data to my main database table:

Private Sub Command65_Click()
    Dim strPrimaryKey As String, strSQL As String, strSQL2 As String, rstNationalSchedule As DAO.Recordset, db As DAO.Database, SessionName As String
    Dim AssistantDirector As String, DueDate As String, ObjectionOfficer As String, TrainerName As String, counter As Integer, recordcount As Integer
    Dim TaskType As String, Topic As String, TopicType As String, TopicSubType As String, OriginatingArea As String, Notes As String
    Dim TeamName As String, AssDirUserID As String, DirectorUserID As String
 
    strSQL = "SELECT * FROM " & Me.List59.Value & ";"
    Set db = CurrentDb
    Set rstNationalSchedule = db.OpenRecordset(strSQL, dbOpenDynaset)
    rstNationalSchedule.MoveLast
    rstNationalSchedule.MoveFirst
    recordcount = rstNationalSchedule.recordcount
 
    TaskType = "Facilitation"
    Topic = "Delivery"
    TopicType = "Training Request"
    TopicSubType = "Roster Period Training"
    OriginatingArea = "Operations"
 
     For counter = 1 To recordcount
         If rstNationalSchedule.AbsolutePosition <> -1 Then
 
            ProgressBarB.Width = (ProgressBarA.Width / rstNationalSchedule.recordcount) * rstNationalSchedule.AbsolutePosition
            Me.Repaint
 
            SessionName = rstNationalSchedule![Training Session]
            AssistantDirector = rstNationalSchedule![State ]
            DueDate = rstNationalSchedule![Date]
            TrainerName = LookupUserIDfromName(rstNationalSchedule![Trainer])
            ObjectionOfficer = TrainerName
            Notes = rstNationalSchedule![ParticipantNames]
            TeamName = rstNationalSchedule![Day]
            AssDirUserID = GetAssDirUserIDfromTeam(TeamName)
            DirectorUserID = GetDirectorUserIDfromTeam(TeamName)
 
            strPrimaryKey = Format(Now(), "yyyymmddhhmmss") & GUserId()
 
            strSQL = "INSERT INTO ObjectionData(PrimaryKey,Task,TaskType,Topic,TopicType,TopicSubType,ChangeReason,AssistantDirector,TaskStatus,Notes,ChangeRequiredYes,DateAllocated,DueDate,ObjectionOfficer,ObjectionOfficeTeam,LeadershipUserID,DirectorUserID) " & _
    "VALUES (" & Chr(34) & strPrimaryKey & Chr(34) & ", " & Chr(34) & SessionName & Chr(34) & ", " & Chr(34) & TaskType & Chr(34) & ", " & Chr(34) & Topic & Chr(34) & ", " & Chr(34) & TopicType & Chr(34) & ", " & Chr(34) & TopicSubType & Chr(34) & ", " & Chr(34) & OriginatingArea & Chr(34) & ", " & Chr(34) & AssistantDirector & Chr(34) & ", " & Chr(34) & "On Hand" & Chr(34) & ", " & Chr(34) & "Not Started" & Chr(34) & "," & Chr(34) & Notes & Chr(34) & ",True," & Chr(34) & DueDate & Chr(34) & ", " & _
    "" & Chr(34) & ObjectionOfficer & Chr(34) & ", " & Chr(34) & TeamName & Chr(34) & ", " & Chr(34) & AssDirUserID & Chr(34) & "," & Chr(34) & DirectorUserID & Chr(34) & ")"
 
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
 
        Else
            ProgressBarB.Width = rstNationalSchedule.recordcount
            Me.Repaint
        End If
 
            rstNationalSchedule.MoveNext
    Next
 
    ProgressBarB.Width = 0
    Me.Repaint
 
End Sub

Any help is greatly appreciated!!

 

[vbaInet]

So why not comment out the two DoCmd.SetWarnings lines and see what is happening. Also you are repeating this process in each loop, so take it out of the loop, disable it before entering the loop and enable it right after the loop. This you do after testing out the former.