Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Anomoly entering data into table from DO Loop (Access XP running as 2K)

    I was given a file that had a list of events and times all in one column. The events were pared in separate records. So if the event name was My Event, the first record would read SE My Event and the next column would be the time stamp for the Start Event, and the very next record should be EE My Event, with the next column being the end time.

    I created a loop, with some logic thrown in to discard inconsistent records and stuff I didn't want included, that spun through the table and entered the data into new table with the event listed once and the times side by side. This was done so I could do math on the times and ultimately create a graph.

    I spent a good portion of the weekend troubleshooting some weird outcomes. I noticed that the End Event time was not being recorded in the new table. After verifying that the time did in fact exist in the source table, I trapped for the time in the loop and then stepped through the code. The time existed, it simply didn't enter it into the table when checking it after the .Update statement at runtime.

    I ran several tests of this, running the code to parse the data then ran a query looking for any Nulls in the End Time field. Each time it ran it was a different outcome, where some End Times were recorded or missing where in the previous pass it was the opposite.

    I ended up creating a subroutine that encapsulated this sub in a loop - where it would run the code, then ran the query for nulls, if the count was greater than 1, it would clear all the data and run it again. Kind-a like one of those slot machines in Vegas where you spin and get cherry, and it keeps spinning for you until you win something (okay, maybe I'll just leave the analogies out of this from now on).

    Anyhow, I put a counter on it and it said it completed successfully in the first pass. To my surprise, it did!

    Anyhow, I will need to run this again in the next few days and want to figure out what the heck is wrong. I posted the code below with several comments. I'd appreciate any input or suggestions that anyone can offer regarding this very strange behavior.


    ************************************************** ***************














    Public Sub LoadDataTable(sSourceTable As String)

    ' Comments : Takes source table, strips superfluous events and identifies start and end times for each event
    ' This is the first step in the process
    ' Parameters: sSourceTable - Table holding data to be parsed to tblData
    ' Returns : Nothing
    ' Source : M. Shea
    ' Created : M. Shea 5/8/2002

    Dim rs As DAO.Recordset
    Dim rsSource As DAO.Recordset
    Dim rsProcBasic As DAO.Recordset

    Dim x As Integer

    Dim iEventRecCount As Integer
    Dim iEventGroupCount As Integer

    Dim sEventType As String
    Dim sEventName As String
    Dim sCheckNextEvent As String
    Dim sSql As String
    Dim sHold As String

    'sSourceTable = "Copy of ctrain11"

    Set rs = CurrentDb.OpenRecordset("tblData")
    Set rsSource = CurrentDb.OpenRecordset(sSourceTable)
    Set rsProcBasic = CurrentDb.OpenRecordset("tblProcBasic")


    x = 1



    'sSql = "Update [" & sSourceTable & "] SET [" & sSourceTable & "].EventTime = #5/6/2002# & ' ' & Mid([EventTime],12)"
    '
    'DoCmd.SetWarnings False
    '
    'DoCmd.RunSQL (sSql)
    '
    'DoCmd.SetWarnings True

    Do While Not rsSource.EOF 'This is to clean up the date format, but still stored as text at this point

    rsSource.Edit

    rsSource!EventTime.Value = CDate("5/6/2002 " & Mid(rsSource![EventTime], 12) & " PM")

    rsSource.Update
    rsSource.MoveNext


    Loop

    ' Add data to tblProcBasic showing absolute start and end time of script

    rsSource.Close

    sSql = "SELECT * FROM " & sSourceTable & " ORDER BY EventTime"

    Set rsSource = CurrentDb.OpenRecordset(sSql)

    rsSource.MoveFirst

    rsProcBasic.AddNew

    rsProcBasic(cmTableName) = rsSource(cmTableName)
    rsProcBasic(cmStart) = rsSource(cmEventTime)

    rsSource.MoveLast

    rsProcBasic(cmEnd) = rsSource(cmEventTime)

    rsProcBasic.Update


    rsSource.MoveFirst 'Move cursor to beginning of rs for next routine

    With rsSource

    Do While Not .EOF

    sEventType = Left$(!Event, 2) 'Grabs for the Event code
    sEventName = Mid$(!Event, 3) 'Grabs the Event Name

    ' If !eventtime = #5/6/2002 9:23:20 PM# And !termid = "ctrain21" Then
    ' MsgBox "Debug"
    ' End If


    If Left$(sEventName, 17) = "Login to PowerCha" Or Left$(sEventName, 16) = "Script for userid" Then
    sCheckNextEvent = "SE" 'Flag to look for Start Event in the next pass
    GoTo EndLoop 'Bypass the rest of the logic in this loop
    End If


    If sEventType <> sCheckNextEvent Then 'Expected next event did not occur

    If sCheckNextEvent = "EE" And sEventType <> "EE" Then

    ' If sEventName = "ort" Or sEventName = "ript Start" Then ' Abort occurred

    ' rs.Edit
    ' rs.MovePrevious
    rs.MoveLast 'Delete previous event
    Debug.Print "Deleting Event: " & rs(2)

    rs.Delete
    ' rs.Update
    ' rs.MoveNext
    sCheckNextEvent = "SE"

    GoTo EndLoop
    '' Else
    ''
    '' Debug.Print "End Event not found: " & sEventType & sEventName
    '' sCheckNextEvent = "SE"
    '' GoTo EndLoop
    ''
    '' End If
    Else 'Otherwise, just take out the trash

    Debug.Print "Excluded: " & sEventType & sEventName
    sCheckNextEvent = "SE"
    GoTo EndLoop
    End If

    End If


    ' If ![EventTime] = #5/6/2002 9:18:56 PM# Then
    ' MsgBox "Debug"
    ' End If


    If sEventType = "SE" Then 'Handle action appropriate to event type
    rs.AddNew
    rs!termid = !termid.Value
    rs![Event Name] = sEventName
    rs![Event Start] = !EventTime.Value
    rs.Update
    Else
    If sEventType = "EE" Then

    rs.MoveLast
    rs.Edit


    Debug.Print "Source Time: " & !EventTime.Value

    rs![Event End] = CDate(!EventTime.Value) '****** Potential point of failure

    Debug.Print "Recorded Time: " & rs![Event End]

    rs.Update

    rs.MoveLast
    If rs![Event End] = "" Or IsNull(rs![Event End]) Then 'Trying to trap for End Event not recording
    MsgBox "Debug" 'which never worked
    End If

    Else
    MsgBox "Event Sequence Error"
    End If
    End If


    '
    '


    ' Debug.Print "********** " & sEventType
    ' Debug.Print "********** " & sEventName

    'Flip the flag for next expected event type

    If sEventType = "SE" Then
    sCheckNextEvent = "EE"
    Else
    If sEventType = "EE" Then
    sCheckNextEvent = "SE"
    Else
    sCheckNextEvent = ""
    End If
    End If

    ' If sCheckNextEvent = "" Then
    ' MsgBox sEventType & sEventName
    ' End If


    EndLoop:


    .MoveNext 'Move to next record in source data



    If sCheckNextEvent = "EE" And (.EOF) Then 'If EOF and still expecting an End Event, delete the SE
    rs.MoveLast
    Debug.Print "Deleting Event: " & rs(2)

    rs.Delete
    ' rs.Update
    rs.MoveNext

    sCheckNextEvent = "SE"
    End If

    ''' If .EOF Then
    ''' rs.MoveLast
    '''
    ''' If rs![Event End] = Null Then
    ''' rs.MovePrevious


    Loop

    End With
    'This was an attempt to get around the End Time not registering
    DoCmd.SetWarnings False

    DoCmd.RunSQL "INSERT INTO tblDataMaster ( TermID, [Event Start], [Event Name], [Event End] ) " _
    & "SELECT tblData.TermID, tblData.[Event Start], tblData.[Event Name], tblData.[Event End] " _
    & "FROM tblData;"

    DoCmd.RunSQL "DELETE * FROM tblData"

    DoCmd.SetWarnings True

    'Clean up

    Set rs = Nothing
    Set rsSource = Nothing
    Set rsProcBasic = Nothing

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Anomoly entering data into table from DO Loop (Access XP running as 2K

    OK, let me see if I have this straight.

    First you're opening 3 recordsets:

    rs based on tblData
    rsSource based on sSourceTable
    rsProcBasic based on tblProcBasic

    For each record in rsSource, you want to set a particular Event date and time in that recordset. Is there a reason you didn't just use an update query and execute it to update the source table?

    Then you want to open another rsSource recordset on the same sSourceTable table, but ordered by EventTime, and you want to add a new record to rsProcBasic that will contain the first (earliest) EventTime and the last (latest) eventtime. Is that part working OK?

    Then you want to move back to the first record in the new rsSource recordset and loop through it doing the following:

    Get the EventType (Event Code) and Event Name for each record
    Test the Event Name for some particular strings, set a flag to "SE" if they're found and jump to a label called EndLoop. (I'll bite my tongue over that <img src=/S/hushmouth.gif border=0 alt=hushmouth width=16 height=16> because I hate jumps)
    If you didn't jump to EndLoop, you test the EventCode to make sure it doesn't contain "SE"
    If it did NOT contain "SE", see if the flag contained "EE" and the code did not. If so, delete the record and set the flag to "SE", then jump to EndLoop.

    If the flag did not contain "EE" or the Code was "EE" then you set the flag to "SE" and jump to EndLoop.

    If the EventType (code) is equal to the sCheckNextEvent flag, you skip over the above stuff and the jumps to EndLoop and test to see if the EventType is "SE".

    If it is, you start working with the recordset based on tblData:
    Add a new record
    set the TermID and EventStart values to the values in the current rsSource record.
    Set the EventName value to the current value of sEventName
    Update the recordset
    If it is equal to "EE" instead of "SE", you move to the last record in the tblData recordset and edit it.
    Set the EventEnd value to the rsSource!EventTime value
    Upate the recordset
    Then MoveLast again (aren't you already there?)
    Check to see if EventEnd has been set to a nullstring or is null

    Use the sEventType to toggle the sCheckNextEvent flag between SE and EE

    Move to the EndLoop label.
    Do a MoveNext on rsSource
    see if the sCheckNextEvent flag is EE and rsSource is EOF
    If it is, MoveLast on the recordset based on tblData and delete the last record.
    Loop to the next record (if any) in rsSource.


    Even if I have it straight, I'm not sure I understand what you're doing. You're populating a string variable with the Event contents starting at position 3, but the way you described it looks like position 3 would be a space, or was that just for clarity in your description? From the description it sounds like you have two "event columns" and two date/time columns, but the only place I can see where you might be referencing the different columns uses variables that don't appear to be declared in your code anywhere, so it's a little hard to tell exactly what's going on.

    Which table is not getting the EventTime put into it consistently, sSourceTable or tblData?
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anomoly entering data into table from DO Loop (Access XP running as 2K

    Charlotte,

    First of all, you clearly took a lot of time analyzing the code in this subroutine and I wanted to thank you for spending so much time with it.


    >> For each record in rsSource, you want to set a particular Event date and time in that recordset. Is there a reason you didn't just use an update query and execute it to update the source table? <<

    I did use a query initially. I changed it to a looping structure to see if that would make any difference with the problem, which it didn't.

    This sub was called from another subroutine that looped through 90 tables that were created by an import from CSV files. These CSV files had a funky date format that isn't recognized as a valid date by Access. The first part (this part) was to correct the date format, but in the source table, it is still stored as text. The query I was using initially is commented out above the loop we are discussing now. I was depending on this to convert to date format when placed into the Start and End fields in master table, called tblData, which is a date formatted field.

    >> Then you want to open another rsSource recordset on the same sSourceTable table, but ordered by EventTime, and you want to add a new record to rsProcBasic that will contain the first (earliest) EventTime and the last (latest) eventtime. Is that part working OK? <<

    Both of the above appear to be working just fine, I've never seen any null date values in this table. This table is used later to determine how many computers are running at the start time of a recorded event.

    >> Get the EventType (Event Code) and Event Name for each record
    Test the Event Name for some particular strings, set a flag to "SE" if they're found and jump to a label called EndLoop. (I'll bite my tongue over that because I hate jumps)
    If you didn't jump to EndLoop, you test the EventCode to make sure it doesn't contain "SE"
    If it did NOT contain "SE", see if the flag contained "EE" and the code did not. If so, delete the record and set the flag to "SE", then jump to EndLoop. <<

    You have the jist of it. The source table has TermID, Event Name, and Event Time. So ideally, the first record will have the start time of an event, and the very next record will have the end time, identified by the first two characters being "SE" and "EE" to identify this. So, in this new table, tblData, I'm adding anything beginning with an SE and plugging the Event Time into the Start time of tblData, then looking to make sure that the next record starts with an EE (a little crude). If it does, I grab the Event Time from that and plug it into the End field of tblData. The rest of the logic that you referenced is what to do if it doesn't find the End Event - where I want to throw out the corresponding Start Event. That flag is used to tell the subroutine what event type to look for on the next pass. Again, if it doesn't find the event type it is expecting, I'm throwing it out as an incomplete event.

    I also figured you would comment on the jump and agree with you that it is sloppy.

    >> Then MoveLast again (aren't you already there?) <<

    I should be. This was another attempt to see if it would make a difference with the problem. I was running out of things to try.

    >> You're populating a string variable with the Event contents starting at position 3, but the way you described it looks like position 3 would be a space <<

    Good point, it should start at position 4.

    >> From the description it sounds like you have two "event columns" and two date/time columns, but the only place I can see where you might be referencing the different columns uses variables that don't appear to be declared in your code anywhere, so it's a little hard to tell exactly what's going on.<<

    Not sure I'm following exactly. The idea was to take the event name (minus the SE and EE) from sSourceTable and place it into the Event Name field of tblData. In the source table and destination table there is only one Event Name field. There are two string vars associated with the source table to grab the event name starting at position 4, and the first two characters to grab the event type, but I don't think this is what you are referring to. Those variables are declared, I might have missed that in the copy/paste.

    >> Which table is not getting the EventTime put into it consistently, sSourceTable or tblData? <<

    It is the End Time field only of tblData.

    I think I covered everything...let me know if I didn't.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Anomoly entering data into table from DO Loop (Access XP running as 2K

    I misunderstood part of your explanation. I read it as being two sets of fields, when you clearly referred to the next *record*.

    Where are cmTableName and cmStart and cmEventTime coming from? Those were the variables (module level constants, maybe? I'm not used to variables without a type indicator, even for constants.) that confused me.
    <hr>rsProcBasic(cmTableName) = rsSource(cmTableName)
    rsProcBasic(cmStart) = rsSource(cmEventTime)<hr>
    You may find that just changing the Left() expression fixes the routine. As it stands, you're unlikely to ever execute this portion of the code ... at least it won't execute if there's a space at the start of sEventName:
    <hr>If Left$(sEventName, 17) = "Login to PowerCha" Or Left$(sEventName, 16) = "Script for userid" Then
    sCheckNextEvent = "SE" 'Flag to look for Start Event in the next pass
    GoTo EndLoop 'Bypass the rest of the logic in this loop
    End If<hr>
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anomoly entering data into table from DO Loop (Access XP running as 2K

    Charlotte, just glanced at this and will dive into it more later, but to answer your initial question, yes, those were module constants (which is what the cm was to identify). This was what I was attempting to get the enum to work on but never did, so I declared them as constants individually.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anomoly entering data into table from DO Loop (Access XP running as 2K

    Charlotte,

    I altered the Left$() to start at position 4. This still didn't work.

    I thought I remembered reading somewhere that DAO 3.6 had some problems so I changed it back to 3.51 (I figured what the hell). I also converted the database to Access 2002 (again, what the hell). I don't know if this is a coincidence but the number of null records returning in the EndTime is a lot less, like 50% less the two times I ran it.

    Another thing that prompted me, since you didn't find anything else in the code I posted, is to looking in some of the calling subs. I noticed when I reviewed it I had the same names for my recordset objects (when I'm in a hurry on something I tend to use the name rs). I went through EVERYTHING regardless of if it was playing a part in this routine or not and gave them unique names. I have had some weird things happen in the past with improperly declared rs objects. This all still didn't work.

    I have another procedure that will just run it individually per table, where I import the table myself and hardcode in the table name rather than letting it loop thorugh all the tables. Seems to work everytime I do that. The only think I can think of is that this thing is screaming by so fast it is missing some entries. Not really sold on that theory but it is the only thing I could come up with.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Anomoly entering data into table from DO Loop (Access XP running as 2K

    I thought you were using Access XP. Why would you use DAO 3.51? That's Access 97. I've never had problems with DAO 3.6 in Access 2k.

    The only other thing I can think of is to lose the With-End With and specifically reference the recordset object in all cases. I have seen code in Access using custom types where the With-End With simply didn't work ... of course if I remember correclty, I was also doing some interesting stuff with classes in the referenced mdb, so that may have been a combined problem.

    If I were troubleshooting it, I would start by breaking out pieces of it into separate, loosely coupled functions and calling them from within the main routine, passing any needed values and/or objects in and getting some kind of return value to insure they did actually run. I've found that to be extremely useful in both figuring out where something is failing and also in cleaning up my code! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    So for instance, you might take this piece out and put it into a separate routine:

    <pre>Do While Not rsSource.EOF 'This is to clean up the date format, but still stored as text at this point

    rsSource.Edit

    rsSource!EventTime.Value = CDate("5/6/2002 " & Mid(rsSource![EventTime], 12) & " PM")

    rsSource.Update
    rsSource.MoveNext

    Loop</pre>


    You could pass the populated recordset object into the called function by reference and pass the date string as another argument. You just call the function and pass it rsSource as a recordset object and strDate as a string and get back a value to tell you it completed. I'd suggest a long integer so that it could return a counter for the number of records updated or something.

    That's the only way I've ever succeeded in untangling code that looked like it was running properly but actually wasn't. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> Eventually, you should have a simple shell that clearly shows the logic of what's being done but offloads the details to individual procedures that are easier to debug.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anomoly entering data into table from DO Loop (Access XP running as 2K

    I will switch back to DAO 3.6. I forget where I heard that 3.6 had problems, but I was grasping at straws anyway trying anything.

    I will also try the Charlotte Troublshooting Technique [img]/forums/images/smilies/smile.gif[/img] and break everything down into smaller functions and take out the With-End's. I like that suggestion.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Anomoly entering data into table from DO Loop (Access XP running as 2K

    Sometimes your just have to clear away the undergrowth so you can see the problem. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anomoly entering data into table from DO Loop (Access XP running as 2K

    A modern day "you've got to see the forrest through the trees" . . .you're so profound Charlotte [img]/forums/images/smilies/smile.gif[/img]

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Anomoly entering data into table from DO Loop (Access XP running as 2K

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> Humble, too! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •