Full Version: Cycle Through Rs And Update From Previous Row If Blank
UtterAccess Discussion Forums > Microsoft® Access > Access Modules
killerofgiants
Ok, I have a code that doesn't work. I am fairly sure I know why it doesn't work, the problem is, I don't know how to fix it.

The table that it is modifying is a temprorary table, tbl_ImportProductivityB. This table has a total of 9 fields, one of which is just an autonumber. The fields are: CountID, ProdDate, ProdZID, ProdForm, ProdBatch, ProdDoc, ProdICN, ProdKeys, and ProdTime. Five of these fields only have a value if is different from the value of the previous record. These values are not necessarily dependent upon each other, and so therefore don't all change at the same time. In order for me to crunch these records down to totals, I need to fill those blanks with whatever was in the previous row. The five fields are: ProdDate, ProdZID, ProdFrom, ProdBatch, and ProdDoc. The other 4 fields always will have a value, which will be unique to that record. My current function, while it works, is extremely slow, and it balloons the database to about 100x the size (which of course, makes the DB very slow until it can get compacted).

I got the following code, mostly, from someone that I can no longer find. I am really just pretty new to the VBA stuff anyway, and I tried to make it similar to the the other code I have, but of course, I have failed.

The lines that I am quite sure are incorrect are:
Public Function ProcessRecords(.....) As Boolean ' I don't know how these should be defined. When calling the function, it is from a macro, and I am fairly certain that part is correct.
var_CurrentData(Counter) = Nz(!lng_Value1) ' I don't understand why the exclamation mark. The fields are not all long, and I am assuming that is what the lng_Value1 field was supposed to be. And I don't know if the 1 should be (1) for an array.

Any help would be greatly appreciated.


CODE
Public Function ProcessRecords(myTable As String, myField1 As String, myField2 As String, myField3 As String, myField4 As String, myField5 As String, myField6 As String) As Boolean
On Error GoTo Error_ProcessRecords
Dim rst_ProcessData As ADODB.Recordset
Dim var_CurrentData(1 To 5) As Variant
Dim Counter As Byte
Set rst_ProcessData = New ADODB.Recordset
With rst_ProcessData
.CursorLocation = adUseClient
.Properties("Update Resync") = adResyncAll
.Open myTable, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
If .BOF And .EOF And .RecordCount > 0 Then Err.Raise vbObject + 5000, "strpf_ProcessRecords", "Unable to open recordset."
.MoveFirst
If .RecordCount = 1 Then
    ProcessRecords = False
    GoTo Exit_ProcessRecords
End If
.MoveNext
Do Until .EOF
For Counter = 1 To 5
If Nz(.Fields(Counter)) <> 0 Then
    var_CurrentData(Counter) = Nz(!lng_Value1)
    Else
    .Fields(Counter) = var_CurrentData(Counter)
End If
Next Counter
.MoveNext
Loop
End With
ProcessRecords = True
Exit_ProcessRecords:
If Not rst_ProcessData Is Nothing Then If rst_ProcessData.State = adStateOpen Then rst_ProcessData.Close
Set rst_ProcessData = Nothing
Exit Function
Error_ProcessRecords:
ProcessRecords = False
Resume Exit_ProcessRecords

End Function
killerofgiants
In case people are wondering what the original code looks like.
CODE
Function CopyFieldRecords(pstrRST As String, pstrField1 As String, pstrField2 As String, pstrField3 As String, pstrField4 As String, pstrField5 As String, pstrID As String) As Boolean

'Originally posted by David in the Newsgroups in 1999
Dim db As Database
Dim rec As Recordset
Dim vCopyDown1 As Variant
Dim vCopyDown2 As Variant
Dim vCopyDown3 As Variant
Dim vCopyDown4 As Variant
Dim vCopyDown5 As Variant
CopyFieldRecords = True
On Error GoTo err_copyrecords

vCopyDown = Null
Set db = CurrentDb()
Set rec = db.OpenRecordset("Select * FROM [" & pstrRST & "]")
While Not rec.EOF

'Modifying code to do all 5 fields at the same time

'pstrField1 Begin
If Nz(rec(pstrField1), "") <> "" Then
     vCopyDown1 = rec(pstrField1)
Else
       'Only if we have something to copy down
        If Nz(vCopyDown1, "") <> "" Then
             rec.Edit
             rec(pstrField1) = vCopyDown1
             rec.Update
        End If
End If
'pstrField1 End / pstrField2 Begin
If Nz(rec(pstrField2), "") <> "" Then
     vCopyDown2 = rec(pstrField2)
Else
       'Only if we have something to copy down
        If Nz(vCopyDown2, "") <> "" Then
             rec.Edit
             rec(pstrField2) = vCopyDown2
             rec.Update
        End If
End If
'pstrField2 End / pstrField3 Begin
If Nz(rec(pstrField3), "") <> "" Then
     vCopyDown3 = rec(pstrField3)
Else
       'Only if we have something to copy down
        If Nz(vCopyDown3, "") <> "" Then
             rec.Edit
             rec(pstrField3) = vCopyDown3
             rec.Update
        End If
End If
'pstrField3 End / pstrField4 Begin
If Nz(rec(pstrField4), "") <> "" Then
     vCopyDown4 = rec(pstrField4)
Else
       'Only if we have something to copy down
        If Nz(vCopyDown4, "") <> "" Then
             rec.Edit
             rec(pstrField4) = vCopyDown4
             rec.Update
        End If
End If
'pstrField4 End / pstrField5 Begin
If Nz(rec(pstrField5), "") <> "" Then
     vCopyDown5 = rec(pstrField5)
Else
       'Only if we have something to copy down
        If Nz(vCopyDown5, "") <> "" Then
             rec.Edit
             rec(pstrField5) = vCopyDown5
             rec.Update
        End If
End If
'pstrField5 End
     rec.MoveNext
Wend

exit_copyrecords:
Exit Function

err_copyrecords:
MsgBox Error, vbCritical, "Copy Fields Down Records"
CopyFieldRecords = False
GoTo exit_copyrecords

End Function
BruceM
What identifies the record that is previous? Is the function run from an existing record on a form, or what exactly?

Why is the temporary table being modified? Are you saying the records are created, then modified later?

There's a lot going on in the code, much of which seems unlikely to be helpful, or at least is taking the long way around.


lng_Value1 seems to be a field in the rst_ProcessData recordset. That said, the whole business of a new recordset, followed by code to test for BOF, EOF, and so forth is greatly puzzling to me. If it is a new recordset, what records is it supposed to be working with? Why not use the temporary table as the recordset if those are the records you want to modify?

It is also unclear why the function returns a Boolean. The function call apparently needs to make use of the Boolean value, but I can't even guess why.
killerofgiants
Well, first, thank you for taking a look.
To give a little better understanding, this DB is importing an excel file (that will be deleted after import, so no linked tables).
One week's worth of data (actually only 4 days, as this was a holiday week) has over 10,000 records. Each record is an individual claim. The excel file that we receive has 15 columns, of which 6 are blanks or hidden, or otherwise unnecessary. It's an excel report, so the first thing I do is modify the imported table by eliminating report header, blank rows and blank columns. This leaves me only with rows of pertinent data that are the individual details of each claim as it was entered. ProdDate is the date of entry, ProdZID is the identifier for the person who did the entry, ProdForm, ProdBatch, and ProdDoc are the 3 parameters that determine the type of claim it is. These five fields are not dependent upon each other insofar as change. Additionally, the excel file that we receive re-uses one of the columns that can be blank (ProdDoc) as a Group Total line.
Whoever wrote the excel report file decided (for whatever reason), that the five particular columns ONLY change when the data is different.
For example: The column ProdDate only has the date listed 4 times, and it happens to be in rows 6, 2085, 5554, and 8137.
What this DB will be accumulating is total claims by keyer, date, and claimtype, with total keystrokes and total time.
For example, I know that once this data of 10,206 rows is modified, it boils down to 275 rows. So it definitely needs to be crunched.

If you look at the example, you can see that even tho the date change, the keyer may not change, and even tho one aspect of the 3 portions that form the specific claim type may change, the others are not dependent on it either. Because of how the excel file is created, with a group header (and group totals) mucking up the lot, I have to strip away all of the non-singular claim lines (it was originally 10,623 rows). If not, I would have used the group totals, and thus would have had significantly fewer rows, and already totaled information. But even the group totals are a little inconsistent, as the group totals are bey keyer/date and by only 1 of the 3 that form the actual claim type.

As far as what identifies these as previous, the first detail line has all data in all columns. After that, it is anyone's guess. It is equivalent to an Edit-->Fill-->Down in excel.

The temp table is being modified so that it can be totaled, and then put in a permanent table (tbl_DailyData)

The code was something someone wrote for me awhile back, but I have been unable to communicate with him/her since. I am unfamiliar with VBA, as I stated, and very unfamiliar with ADO (I did find on this website about activating the appropriate library!). When I received the code, it was given as untested, so my fear is that they put it together combining some of the information that I gave, and some default responses.


Example: (note, for spacing, lowercase x=null/blank field)

4 1/3/12 AAAAAA CMS15 NX BLK 1111111111111 100 00:00:45
5 xxxxxx xxxxxx xxxxx YY xxx 1111111111112 125 00:01:02
6 xxxxxx BBBBBB xxxxx NN xxx 1111111111113 114 00:00:58
8 1/4/12 xxxxxx UB04Z xx RED 1111111111114 753 00:02:04
9 xxxxxx xxxxxx DENT6 xx BLK 1111111111115 102 00:00:38
BruceM
I'm still having trouble following this. It seems the code is intended to function rather like a grouping query, which apparently is not among your options.

This is a bit beyond my full understanding, but it seems to me you could just open a recordset based on the table, as shown in the original code, rather than using the new ADO recordset. Are you importing the Excel data as a temp table, or are you working directly with the spreadsheet?

Is the sample data an example of raw data, or is it the finished result? It would probably be helpful to see a few lines of raw data, then show how they should look after processing.
killerofgiants
No problem, yeah, it is a bit confusing. I have uploaded a scrubbed copy of my DB (still work in progress).

Note: I have the 3 temp tables still there, so you can see the progression.

A saved import process imports an excel file into a temporary table: tbl_ImportProductivityA

qry_ImportProductivityA trims the fields, and filters out all of the undesired information, and makes a new temporary table: tbl_ImportProductivityB (it filters on field ImportProdICN having a length of the string field for every claim is 13 characters. This was the easiest method (I found), to ensure only rows of data that are an individual detail. (Weird note: Sometimes, when I open the tbl_ImportProductivityB table, it isn't sorted by the CountID field. I tried to add the Val(Trim(FIELD)) to this query, but it still opens weird, sometimes). Also, I made a copy of this temp table, and called it tbl_ImportProductivityB1, since the next step modifies tbl_ImportProductivityB to a version where I can start getting totals.

After tbl_ImportProductivityB (B1 in this case) gets made, this is where I run my code. Which modifies tbl_ImportProductivityB to something that can now be crunched down to manageable levels.

qry_ImportProductivityB takes the *final* data from tbl_ImportProductivityB and makes table tbl_ImportProductivityC, converting the 3 columns to a specific claim type, converting the time for each claim into seconds, and so forth.

qry_ImportProductivityC appends the data from tbl_ImportProductivityC to the final table: tbl_DailyClaims.

There may be times where this data is re-imported, therefore:
qry_ImportProductivityD deletes any records that are not in the new import.
qry_ImportProductivityE updates any records that are in the new import.

It's not perfect, I know, but the process is working for me, except the time for the function to run, and the fact that it balloons to over 200mb.

Thanks again.
BruceM
I only have Access 2003 here, so I can't look at the attachment. However, I should point out that you should not count on tables being in any particular order. If a sort order is needed, use a query.
killerofgiants
Well, nerf.

Attached now is the orginal (and scrubbed) excel file that we receive that I import into Access.

Originally, I had a 'dummy' excel file that would populate everything. But that requires the end user to ensure the update in excel took place. AND the excel dummy file was almost 30 MB with all the edit fill downs.

You'll notice several sets of columns are joined in the "meat" of the report.
Columns B & C
Columns F, G, H, & I
Columns J & K
Columns M & N

Additionally, there are many extra "spaces" in these fields, which is why I trim the imported data down.

I am sure some of these combines are due to the subtotal rows. But if you notice, Key Date, Clerk ID, Form Type, Batch Type, and Doc Type ONLY have new data if the data is different from the previous row(s), hence the Edit-->Fill-->Down process that I would pseudo-like to emulate.
BruceM
My first thought would be to modify the Excel spreadsheet, essentially to perform the autofill automatically, if possible (although the merged cells make that rather difficult.

In Access I'm not sure there is an alternative to looping through the recordset. However, I put together some code below that processes the records in a few seconds. Either you need to create a table with extra records such as those with Claim Type Total or Clerk Total removed, or else use the appropriate SQL for strSQL in the code. Frankly, I'm a little out of my element, so it could be that it isn't necessary to use .Edit and .Update as often as I did, but I didn't experiment any further. Essentially, the variables are for any fields that could be null.

I think I would try to assign row numbers in Excel, and import those values along with the rest of the data. Otherwise you do not have a reliable sort field. You could add an autonumber to the temporary table to keep the records in order, but that may not be 100% reliable.

CODE
Private Sub Command0_Click()

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim strSQL As String
  
  Dim datDate As Date
  Dim str2 As String
  Dim str4 As String
  Dim str5 As String
  Dim str6 As String
  
  Set db = CurrentDb
  
  strSQL = _
    "Select ID,Field1,Field2,Field4,Field5,Field6,Field10,Field12,Field13 " & _
    "FROM Test3 ORDER BY ID"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
          
  With rs
    .MoveFirst
    datDate = !Field1
    str2 = !Field2
    str4 = !Field4
    str5 = !Field5
    str6 = !Field6
    .MoveNext
    Do Until .EOF
    If IsNull(!Field1) Then
        .Edit
        !Field1 = datDate
        .Update
      Else
        datDate = !Field1
    End If
    If IsNull(!Field2) Then
        .Edit
        !Field2 = str2
        .Update
      Else
        str2 = !Field2
    End If
    If IsNull(!Field4) Then
        .Edit
        !Field4 = str4
        .Update
      Else
        str4 = !Field4
    End If
    If IsNull(!Field5) Then
        .Edit
        !Field5 = str5
        .Update
      Else
        str5 = !Field5
    End If
    If IsNull(!Field6) Then
        .Edit
        !Field6 = str6
        .Update
      Else
        str6 = !Field6
    End If
    .MoveNext
    Loop
  End With
    
End Sub
killerofgiants
Thank you for your efforts. I am working on a different part of the db at the moment. I will look thru and attempt to implement testing tonight, and I will post back tomorrow. Again, thank you for looking <
BruceM
You're welcome, and good luck. BTW, you would of course still need to run a grouping query. The suggestion just does the equivalent of autofill in Excel. Not sure exactly how to handle the time field, but I expect you would need a function to convert the text string into seconds, then convert it back into the desired format, or something like that.
killerofgiants
Yeah, the query beyond this is done, and efficient. (I've been super busy lately). I think what I may do is have the temp tables just be used in a temp database, so my db won't blow up, and I won't care if the temp one does (since it'll just be deleted anyway). I haven't had time to try out your code, but it looks similar to the one that I have that is currently working (and doesn't have the need to separate the other columns of data).
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.