Moving a field into a Temp field receiving a [3027] Cannot Update. DB is read-only.

[dowlingm815]

In the following code, an error is generated when trying to move a selected record into a temp field to evaluate the record. the error is "[3027] Cannot Update. DB is read-only."

I am looking to get the selected record evaluate it. if a criteria is met, it will written to a table.

Option Compare Database
 
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strSQL As String
Dim strFirstRec As String
Dim strPR_ID_TEMP As String
Dim strWorkflow_Step_Name_TEMP As String
Dim strWorkflow_Step_Date_TEMP As String
 
Private Sub Generate__PR_Status_Table_Click()
On Error GoTo Err_Hndlr
 
 
strSQL = "SELECT T_PRApprovalHistory.PR_ID,  " & _
                "T_PRApprovalHistory.PR_Date,  " & _
                "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                "T_PRApprovalHistory.Workflow_Step_Date,  " & _
                "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
        "FROM T_PRApprovalHistory  " & _
        "GROUP BY T_PRApprovalHistory.PR_ID,  " & _
                 "T_PRApprovalHistory.PR_Date,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Date " & _
        "HAVING (((T_PRApprovalHistory.PR_ID)=11050254)) " & _
        "ORDER BY T_PRApprovalHistory.Workflow_Step_Date"
 
 
 
Set rst = CurrentDb.OpenRecordset(strSQL)
 
 
strFirstRec = "Yes"
 
Do Until rst.EOF
    Debug.Print strFirstRec; " "; rst!PR_ID & " "; rst!PR_Date&; " "; rst!Workflow_Step_Name&; " "; rst!workflow_Step_Date; " "; rst!CountOfWorkflow_Step_Date
    If strFirstRec = "Yes" Then
        strFirstRec = "No"
        rst!workflow_Step_Date = strWorkflow_Step_Date_TEMP
        rst!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
        rst!PR_ID = strPR_ID_TEMP
 
    End If
 
    If rst!PR_ID = strPR_ID_TEMP Then
        If rst!Workflow_Step_Name = strWorkflow_Step_Name_TEMP Then
            If rst!workflow_Step_Date = strWorkflow_Step_Date_TEMP Then
                rst!workflow_Step_Date = strWorkflow_Step_Date_TEMP
            End If
        Else
'            write record
            rst!workflow_Step_Date = strWorkflow_Step_Date_TEMP
            rst!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
        End If
    Else
'        write record
        rst!workflow_Step_Date = strWorkflow_Step_Date_TEMP
        rst!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
        rst!PR_ID = strPR_ID_TEMP
    End If
 
    rst.MoveNext
Loop
Exit Sub
 
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Generate__PR_Status_Table_Click()"
End Sub

[NeoPa]

This is unlikely to be related to your code at all.

You posted the error message (helpfully). Did it not occur to you that your problem was with the access to the database file itself. I suggest you look at that issue and see what you can find. We would need more information about this before we can shed light in the right direction.

[patjones]

Hi dowling -

Your query has a "GROUP BY", which makes the resulting recordset read-only.

As a side note, usually when you make a change to a recordset, you need to use the "Edit" and "Update" recordset methods. For instance:

rst.Edit
 
rst!Workflow_Step_Date = strWorkflow_Step_Date_TEMP
rst!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
 
rst.Update

Pat

[dowlingm815]

Pat,

The code is reading the data and then setting a temporary field equal to read-only fields. It is not editing the fields, therefore, I am not following your direction.

Mary

[dowlingm815]

NeoPa,

I am not sure what i am looking for within the db. the structure is as follows:

 
"[ID] COUNTER CONSTRAINT ndxID PRIMARY KEY,  " & _
           "[PR_ID] TEXT(25)," & _
            "[External_PR] TEXT(25), " & _
            "[PR_Name] TEXT(30), " & _
            "[PR_Date]DATETIME, " & _
            "[Buyer_username]TEXT(30), " & _
            "[Buyer_First_Name]TEXT(30), " & _
            "[Buyer_Last_Name]TEXT(30), " & _
            "[Buyer_Email]TEXT(30), " & _
            "[Department]TEXT(30), " & _
             "[Workflow_Step_Name]TEXT(30), " & _
             "[Workflow_Step_Date]DATETIME, " & _
             "[Workflow_Step_Action]TEXT(30), " & _
             "[Approver_Username]TEXT(30), " & _
             "[Approver_First_Name]TEXT(30), " & _
             "[Approver_Last_Name]TEXT(30), " & _
             "[Approver: Email]TEXT(30), " & _
             "[Approver: Department]TEXT(30)" & _
 

[patjones]

dowling -

Are not rst!Workflow_Step_Date and rst!Workflow_Step_Name fields from the query in strSQL, which has a GROUP BY clause? Queries that involve GROUP BY clauses are read-only, which means that you cannot assign values to rst!Workflow_Step_Date and rst!Workflow_Step_Name as you are trying to do here:

rst!Workflow_Step_Date = strWorkflow_Step_Date_TEMP 
rst!Workflow_Step_Name = strWorkflow_Step_Name_TEMP

To test what I'm saying, I made a simple piece code with a GROUP BY query and tried to assign something to one of the fields in the query as you are trying to do, and also received error 3207.

Perhaps what you mean to do is

strWorkflow_Step_Date_TEMP = rst!Workflow_Step_Date
strWorkflow_Step_Name_TEMP = rst!Workflow_Step_Name

If I'm off track here, perhaps NeoPa can shed light on the issue...

Pat

[dowlingm815]

If read-only is the case and then the value of each record cannot be copied into another record, true?

In SQL, a GROUP BY can created a table. Would this be the solution, create a SQL statement creating a table instead so it is readable?

SELECT T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date, Count(T_PRApprovalHistory.PR_Date) AS [CountOfPR Date] INTO T_Group_By_Test
FROM T_PRApprovalHistory
GROUP BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date
HAVING (((T_PRApprovalHistory.PR_ID)=11050254))
ORDER BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.Workflow_Step_Date;
 
 

[patjones]

dowling -

Yes, you can create a temporary table and put the results of your GROUP BY query into it. Then you could do anything you want with the records in the table.

For example:

Dim rstTemp As Recordset
Dim rst As Recordset
Dim i As Long
 
'Create temporary table
CurrentDb.Execute("CREATE TABLE tblTemp(PR_Id SMALLINT, Workflow_Step_Date DATETIME, Workflow_Step_Name VARCHAR(50))")
 
'Bind rstTemp to the temporary table
Set rstTemp = CurrentDb.OpenRecordset("tblTemp")
 
'Grab the data and put it in rst
Set rst = CurrentDb.OpenRecordset("SELECT T_PRApprovalHistory.PR_ID,  " & _
                "T_PRApprovalHistory.PR_Date,  " & _
                "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                "T_PRApprovalHistory.Workflow_Step_Date,  " & _
                "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
        "FROM T_PRApprovalHistory  " & _
        "GROUP BY T_PRApprovalHistory.PR_ID,  " & _
                 "T_PRApprovalHistory.PR_Date,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Date " & _
        "HAVING (((T_PRApprovalHistory.PR_ID)=11050254)) " & _
        "ORDER BY T_PRApprovalHistory.Workflow_Step_Date")
 
'Move the data into the temporary table
rst.MoveFirst
Do While rst.EOF = False
 
    rstTemp.AddNew
 
    For i = 0 To rst.Fields.Count - 1
         rstTemp.Fields(i).value = rst.Fields(i).value  
    Next
 
    rstTemp.Update
    rst.MoveNext
 
Loop

At this point, rstTemp (and thus tblMain) should have the results of the GROUP BY query in it.

In this code, the line in bold print is what actually assigns the field value from the original query results into the temporary table. In the CREATE TABLE statement, you can add or take away fields as you see fit (I just put three of your fields in for illustration. You might also need to adjust the field types according to your needs.

Pat

[NeoPa]

@dowlingm815
If your error message is accurate, then the contents of the database (neither code nor data) are not where you need to be looking. I would be looking at the access (small-'a') you have to your database file. Maybe it's on a share you don't have full rights to. Maybe it's simply got the Read-Only attribute set. Have you tried renaming that file? That usually indicates whether you have enough right to do any work in it at all. If you can't rename the file, you won't get very far inside it (unless it's simply in use at the time). If this is your issue we'd need more details to help you progress.

[patjones]

If your error message is accurate, then the contents of the database (neither code nor data) are not where you need to be looking.

I think that could be one cause for this error. But I was able to build a simple table and form, and regenerate this same error by attempting to update a recordset bound to a GROUP BY query, just like dowling is trying to do. When I modified the SQL to not include any aggregates I was able to do the update fine.

I could very well be wrong, but I would be interested to know whether the solution I laid out above will work for what dowling wants to do...

Pat

[NeoPa]

@zepphead80
That applies to all of us Pat.

If you experience that message when the file is clearly fully accessible, then don't let me stop you pursuing that approach. Personally I'd check the access to the file first, that being pretty quickly and easily done after all, but I expect that your understanding may well reap dividends if that's not the problem.

[dowlingm815]

the database is on my c: drive, there are no accessibility issues.

[dowlingm815]

can you kindly post your code from when it works to when it doesn' t work. when i elminate the count function, i receive a 3219 invalid operaton error.

 
Option Compare Database
 
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strSQL As String
Dim strFirstRec As String
Dim strPR_ID_TEMP As String
Dim strWorkflow_Step_Name_TEMP As String
Dim strWorkflow_Step_Date_TEMP As String
 
Private Sub Generate__PR_Status_Table_Click()
On Error GoTo Err_Hndlr
 
    MsgBox "Entered the application", vbInformation
 
 
strSQL = "SELECT T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date INTO T_Group_By_Test " & _
"FROM T_PRApprovalHistory GROUP BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date HAVING (((T_PRApprovalHistory.PR_ID) = 11050254)) ORDER BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.Workflow_Step_Date;"
 
Set rst = CurrentDb.OpenRecordset(strSQL)
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Generate__PR_Status_Table_Click()"
End Sub
 

[patjones]

Simply removing the COUNT function isn't what I suggested. Please read my post #8 and try to implement what I'm doing there.

Just taking out the COUNT function won't work, because the query still contains a GROUP BY clause. If you really need to use the results of this query, write them to a table and then work with the table as I'm suggesting in post #8...

Pat

[patjones]

Suppose I have some code, which prints out a list for a particular employee, with: ID, last name, first name, date worked, hours worked. Then I change the hours worked each day and reprint the results. This works...

Dim strSQL As String
Dim rst As Recordset
 
strSQL = "SELECT tblMain.fldERN, tblNames.fldNameLast, tblNames.fldNameFirst, tblMain.fldDate, tblMain.fldHours 
          FROM tblMain INNER JOIN tblNames ON tblMain.fldERN = tblNames.fldERN 
          WHERE tblMain.fldERN = '" & Replace(Replace(Me.txtERN,"'",""),"""","") & "'"
 
Set rst = CurrentDb.OpenRecordset(strSQL)
 
With rst
 
    'Print old records
    .MoveFirst
 
    While Not .EOF
        Debug.Print !fldERN & "   " & !fldNameLast & "   " & !fldNameFirst & "   " & Format(!fldDate, "mm/dd/yy") & "   " & !fldHours
        .MoveNext
    Wend
 
    'Modify the records
    .MoveFirst
 
    While Not .EOF
        .Edit
        !fldHours = Me.txtHoursNew
        .Update
        .MoveNext
    Wend
 
    'Print modified records
    .MoveFirst
 
    While Not .EOF
        Debug.Print !fldERN & "   " & !fldNameLast & "   " & !fldNameFirst & "   " & Format(!fldDate, "mm/dd/yy") & "   " & !fldHours
        .MoveNext
    Wend
 
End With

Now, suppose I change the SQL string to

strSQL = "SELECT tblMain.fldERN, tblNames.fldNameLast, tblNames.fldNameFirst, tblMain.fldDate, SUM(tblMain.fldHours) AS [Hours Count] 
          FROM tblMain INNER JOIN tblNames ON tblMain.fldERN = tblNames.fldERN 
          WHERE tblMain.fldERN = '" & Replace(Replace(Me.txtERN, "'", ""), """", "") & "' 
          GROUP BY tblMain.fldERN, tblNames.fldNameLast, tblNames.fldNameFirst, tblMain.fldDate "
 

which basically does the same thing, except it would, for instance, combine multiple records with the same date. I'll be able to print the records just as before:

With rst
 
    'Print old records
    .MoveFirst
 
    While Not .EOF
        Debug.Print !fldERN & "   " & !fldNameLast & "   " & !fldNameFirst & "   " & Format(!fldDate, "mm/dd/yy") & "   " & ![Hours Count]
        .MoveNext
    Wend
 

But when I go and try to modify something from within the query (it doesn't matter what field you pick):

    'Modify the records
    .MoveFirst
 
    While Not .EOF
        .Edit
        ![Hours Count] = Me.txtHoursNew
        .Update
        .MoveNext
    Wend
 

I get error 3027, just as you do. This is because the individual rows that result from doing a GROUP BY are combinations of multiple rows from the source table(s). In my instance, I can't go and modify total hours worked ([Hours Count]), because that result is the total from multiple rows in my source table.

If however I took my recordset here and dumped it into another table that I just created for that purpose, I'd be able to work with those records however I wanted to.

Does this make any sense?

Pat

[dowlingm815]

pat,

thanks for all your help. the temporary file is the answer so far. however, i have yet to test it against moving data into a field. currently, i am recievind an [0] error code and i can't seem to find out how to elimate it. i know it means eof. the code builds the table, however, i am still receiving that error.

mary

the code is as follows:

Option Compare Database
 
Dim rst As DAO.Recordset
Dim rstTemp As Recordset
Dim i As Long
Dim dbs As DAO.Database
Dim strSQL As String
Dim strFirstRec As String
Dim strPR_ID_TEMP As String
Dim strWorkflow_Step_Name_TEMP As String
Dim strWorkflow_Step_Date_TEMP As String
 
Private Sub Generate__PR_Status_Table_Click()
On Error GoTo Err_Hndlr
 
 
'Delete temporary table
DoCmd.RunSQL "DROP TABLE tblTemp;"
 
'Create temporary table
CurrentDb.Execute ("CREATE TABLE tblTemp(PR_Id integer, PR_Date DATETIME, Workflow_Step_Name VARCHAR(50), Workflow_Step_Date DATETIME, CountOfWorkflow_Step_Date smallint)")
 
'Bind rstTemp to the temporary table
Set rstTemp = CurrentDb.OpenRecordset("tblTemp")
 
 
'Grab the data and put it in rst
Set rst = CurrentDb.OpenRecordset("SELECT T_PRApprovalHistory.PR_ID,  " & _
                "T_PRApprovalHistory.PR_Date,  " & _
                "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                "T_PRApprovalHistory.Workflow_Step_Date,  " & _
                "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
        "FROM T_PRApprovalHistory  " & _
        "GROUP BY T_PRApprovalHistory.PR_ID,  " & _
                 "T_PRApprovalHistory.PR_Date,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Date " & _
        "HAVING (((T_PRApprovalHistory.PR_ID)=11050254)) " & _
        "ORDER BY T_PRApprovalHistory.Workflow_Step_Date")
 
 
'Move the data into the temporary table
rst.MoveFirst
Do While rst.EOF = False
'    Debug.Print rst!PR_ID&; " "; rst!PR_Date&; " "; rst!Workflow_Step_Name&; " "; rst!workflow_Step_Date
    rstTemp.AddNew
 
    For i = 0 To rst.Fields.Count - 1
        rstTemp.Fields(i).Value = rst.Fields(i).Value
'        Debug.Print rst.Fields(i).Value
    Next
 
    rstTemp.Update
    rst.MoveNext
 
Loop
 
rst.Close
 
 
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Generate__PR_Status_Table_Click()"
End Sub
 
 

[dowlingm815]

Besides the [0] error, with part of the logic added, the reason why i originally posted, moving the field, a new error is generated, [3020] update or cancel without AddNew or Edit. what i don't understand, the field that is being updated is a holding area not a record. the code is as follows:

Option Compare Database
 
Dim rst As DAO.Recordset
Dim rstTemp As Recordset
Dim i As Long
Dim dbs As DAO.Database
Dim strSQL As String
Dim strFirstRec As String
Dim strPR_ID_TEMP As String
Dim strWorkflow_Step_Name_TEMP As String
Dim strWorkflow_Step_Date_TEMP As Date
 
Private Sub Generate__PR_Status_Table_Click()
On Error GoTo Err_Hndlr
 
 
'Delete temporary table
DoCmd.RunSQL "DROP TABLE tblTemp;"
 
'Create temporary table
CurrentDb.Execute ("CREATE TABLE tblTemp(PR_Id integer, PR_Date DATETIME, Workflow_Step_Name VARCHAR(50), Workflow_Step_Date DATETIME, CountOfWorkflow_Step_Date smallint)")
 
'Bind rstTemp to the temporary table
Set rstTemp = CurrentDb.OpenRecordset("tblTemp")
 
 
'Grab the data and put it in rst
Set rst = CurrentDb.OpenRecordset("SELECT T_PRApprovalHistory.PR_ID,  " & _
                "T_PRApprovalHistory.PR_Date,  " & _
                "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                "T_PRApprovalHistory.Workflow_Step_Date,  " & _
                "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
        "FROM T_PRApprovalHistory  " & _
        "GROUP BY T_PRApprovalHistory.PR_ID,  " & _
                 "T_PRApprovalHistory.PR_Date,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Date " & _
        "HAVING (((T_PRApprovalHistory.PR_ID)=11050254)) " & _
        "ORDER BY T_PRApprovalHistory.Workflow_Step_Date")
 
 
'Move the data into the temporary table
rst.MoveFirst
Do While rst.EOF = False
'    Debug.Print rst!PR_ID&; " "; rst!PR_Date&; " "; rst!Workflow_Step_Name&; " "; rst!workflow_Step_Date
    rstTemp.AddNew
 
    For i = 0 To rst.Fields.Count - 1
        rstTemp.Fields(i).Value = rst.Fields(i).Value
'        Debug.Print rst.Fields(i).Value
    Next
 
    rstTemp.Update
    rst.MoveNext
 
Loop
 
rst.Close
 
'********************************************************logic*****************************************
 
strFirstRec = "Yes"
rstTemp.MoveFirst
 
Do While rstTemp.EOF = False
    Debug.Print rstTemp!PR_ID&; " "; rstTemp!PR_Date&; " "; rstTemp!Workflow_Step_Name&; " "; rstTemp!workflow_Step_Date
    If strFirstRec = "Yes" Then
        strFirstRec = "No"
        rstTemp!workflow_Step_Date = strWorkflow_Step_Date_TEMP
        rstTemp!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
        rstTemp!PR_ID = strPR_ID_TEMP
 
    End If
 
    If rstTemp!PR_ID = strPR_ID_TEMP Then
        If rstTemp!Workflow_Step_Name = strWorkflow_Step_Name_TEMP Then
            If rstTemp!workflow_Step_Date = strWorkflow_Step_Date_TEMP Then
                rstTemp!workflow_Step_Date = strWorkflow_Step_Date_TEMP
            End If
        Else
'            write record
            rstTemp!workflow_Step_Date = strWorkflow_Step_Date_TEMP
            rstTemp!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
        End If
    Else
'        write record
        rstTemp!workflow_Step_Date = strWorkflow_Step_Date_TEMP
        rstTemp!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
        rstTemp!PR_ID = strPR_ID_TEMP
    End If
 
    rstTemp.MoveNext
 
Loop
 
rstTemp.Close
 
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Generate__PR_Status_Table_Click()"
End Sub
 

[patjones]

What lines are you getting the errors on?

Pat

[dowlingm815]

rstTemp!workflow_Step_Date = strWorkflow_Step_Date_TEMP 
 

[patjones]

That's because you have not preceded that part of the code with an rstTemp.Edit:

rstTemp.Edit
 
rstTemp!workflow_Step_Date = strWorkflow_Step_Date_TEMP
rstTemp!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
rstTemp!PR_ID = strPR_ID_TEMP
 
rstTemp.Update

But, my other concern here is that, up to this point in the code, you have not assigned anything to strWorkflow_Step_Date_TEMP or strWorkflow_Step_Name_TEMP or strPR_ID_TEMP ...so what is the point of those three lines of code?

Pat

[dowlingm815]

pat,

the issue was i was not assigning the correct fields, it should have been reverse to the above post.

this application summarizes data by grouping and evaluate the workflow dates to ensure the last workflow date is written to a table. For instance, the input data would be:

11050254 5/18/2009 Banner Budget Auth 5/18/2009
11050254 5/18/2009 Department Approvals 5/18/2009
11050254 5/18/2009 Business Office Approvals 5/27/2009
11050254 5/18/2009 Purchasing Approvals 7/21/2009
11050254 5/18/2009 Purchasing Approvals 7/22/2009
11050254 5/18/2009 Purchasing Next FY 7/23/2009
11050254 5/18/2009 Processed FY Orders 7/24/2009
11050254 5/18/2009 Purchasing Next FY 7/24/2009

the summary table will contain the following:
11050254 5/18/2009 Banner Budget Auth 5/18/2009
11050254 5/18/2009 Department Approvals 5/18/2009
11050254 5/18/2009 Business Office Approvals 5/27/2009
11050254 5/18/2009 Purchasing Approvals 7/22/2009
11050254 5/18/2009 Processed FY Orders 7/24/2009
11050254 5/18/2009 Purchasing Next FY 7/24/2009

The code was altered as shown below which i am now getting the correct results but still receiving [0] EOF error, any suggestions?

Option Compare Database
 
Dim rst As DAO.Recordset
Dim rstTemp As Recordset
Dim i As Long
Dim dbs As DAO.Database
Dim strSQL As String
 
Dim strFirstRec As String
 
'*********temp fields for writing records
Dim strPR_ID_TEMP As String
Dim strWorkflow_Step_Name_TEMP As String
Dim strWorkflow_Step_Date_TEMP As Date
Dim strPR_Date_TEMP As Date
Dim strCountOfWorkflow_Step_Date As Integer
 
 
Private Sub Generate__PR_Status_Table_Click()
On Error GoTo Err_Hndlr
 
 
'Delete temporary table
DoCmd.RunSQL "DROP TABLE tblTemp;"
 
'Create temporary table
CurrentDb.Execute ("CREATE TABLE tblTemp(PR_Id integer, PR_Date DATETIME, Workflow_Step_Name VARCHAR(50), Workflow_Step_Date DATETIME, CountOfWorkflow_Step_Date smallint)")
 
'Bind rstTemp to the temporary table
Set rstTemp = CurrentDb.OpenRecordset("tblTemp")
 
 
'Grab the data and put it in rst
Set rst = CurrentDb.OpenRecordset("SELECT T_PRApprovalHistory.PR_ID,  " & _
                "T_PRApprovalHistory.PR_Date,  " & _
                "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                "T_PRApprovalHistory.Workflow_Step_Date,  " & _
                "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
        "FROM T_PRApprovalHistory  " & _
        "GROUP BY T_PRApprovalHistory.PR_ID,  " & _
                 "T_PRApprovalHistory.PR_Date,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Date " & _
        "HAVING (((T_PRApprovalHistory.PR_ID)=11050254)) " & _
        "ORDER BY T_PRApprovalHistory.Workflow_Step_Date, T_PRApprovalHistory.Workflow_Step_Name")
 
 
'Move the data into the temporary table
rst.MoveFirst
Do While rst.EOF = False
    Debug.Print rst!PR_ID&; " "; rst!PR_Date&; " "; rst!Workflow_Step_Name&; " "; rst!workflow_Step_Date
    rstTemp.AddNew
 
    For i = 0 To rst.Fields.Count - 1
        rstTemp.Fields(i).Value = rst.Fields(i).Value
    Next
 
    rstTemp.Update
    rst.MoveNext
 
Loop
 
rst.Close
 
'********************************************************logic*****************************************
 
strFirstRec = "Yes"
 
' **** initialize temporary fields, date is set to dummy initial date, it has no meaning
strWorkflow_Step_Date_TEMP = "03/19/2007"
strWorkflow_Step_Name_TEMP = " "
strPR_ID_TEMP = " "
strPR_Date_TEMP = "03/19/2007"
strCountOfWorkflow_Step_Date = "0"
 
'**** create output table
'Delete temporary table
DoCmd.RunSQL "DROP TABLE tblApprovalHistorySummary;"
 
'Create temporary table
CurrentDb.Execute ("CREATE TABLE tblApprovalHistorySummary(PR_Id integer, PR_Date DATETIME, Workflow_Step_Name VARCHAR(50), Workflow_Step_Date DATETIME, CountOfWorkflow_Step_Date smallint)")
 
'Bind rstTemp to the temporary table
Set rstSummary = CurrentDb.OpenRecordset("tblApprovalHistorySummary")
 
 
rstTemp.MoveFirst
 
Do While rstTemp.EOF = False
 
    Debug.Print "Temp file "; rstTemp!PR_ID&; " "; rstTemp!PR_Date&; " "; rstTemp!Workflow_Step_Name&; " "; rstTemp!workflow_Step_Date
 
    If strFirstRec = "Yes" Then
        strFirstRec = "No"
        strWorkflow_Step_Date_TEMP = rstTemp!workflow_Step_Date
        strWorkflow_Step_Name_TEMP = rstTemp!Workflow_Step_Name
        strPR_ID_TEMP = rstTemp!PR_ID
        strPR_Date_TEMP = rstTemp!PR_Date
        strCountOfWorkflow_Step_Date = rstTemp!CountOfWorkflow_Step_Date
    End If
 
    If rstTemp!PR_ID = strPR_ID_TEMP Then
        If rstTemp!Workflow_Step_Name = strWorkflow_Step_Name_TEMP Then
            If rstTemp!workflow_Step_Date = strWorkflow_Step_Date_TEMP Then
                strWorkflow_Step_Date_TEMP = rstTemp!workflow_Step_Date
            End If
        Else
'***        write record
            rstSummary.AddNew
                rstSummary!workflow_Step_Date = strWorkflow_Step_Date_TEMP
                rstSummary!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
                rstSummary!PR_ID = strPR_ID_TEMP
                rstSummary!PR_Date = strPR_Date_TEMP
                rstSummary!CountOfWorkflow_Step_Date = strCountOfWorkflow_Step_Date
            rstSummary.Update
 
            strWorkflow_Step_Date_TEMP = rstTemp!workflow_Step_Date
            strWorkflow_Step_Name_TEMP = rstTemp!Workflow_Step_Name
        End If
    Else
'***   write record
        rstSummary.AddNew
            rstSummary!workflow_Step_Date = strWorkflow_Step_Date_TEMP
            rstSummary!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
            rstSummary!PR_ID = strPR_ID_TEMP
            rstSummary!PR_Date = strPR_Date_TEMP
            rstSummary!CountOfWorkflow_Step_Date = strCountOfWorkflow_Step_Date
        rstSummary.Update
 
        strWorkflow_Step_Date_TEMP = rstTemp!workflow_Step_Date
        strWorkflow_Step_Name_TEMP = rstTemp!Workflow_Step_Name
        strPR_ID_TEMP = rstTemp!PR_ID
    End If
 
    rstTemp.MoveNext
Loop
 
rstTemp.Close
rstSummary.Close
 
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Generate__PR_Status_Table_Click()"
End Sub
 
 

[patjones]

What line are you getting the error on?

[dowlingm815]

got it...there was no exit sub. it went right to Err_Hndlr.

Case closed.

thank you

[patjones]

You're welcome!

Seeing your sample data really makes it clear what you are doing. I'd suggest posting some sample data the next time you have a problem, and what you want the result to look like, to help people understand better what you're trying to do.

In your case, while the algorithm you have appears to work, I can't help but think there might be a more straightforward method. For instance, I wonder if there is a way you can write the SQL so that it just gives you the end result you want.

If there aren't any confidentiality concerns, maybe you could make a wider range of your sample data available for me to play with?

Pat

[dowlingm815]

Good morning Pat,

I had to alter to the code to accommodate the workflow order. Ultimately, the Summary table will feed a report that will detail the number of days it takes for the workflow approval process.

Here is the new table, tblWorkflowApprovalStep, that was added

[data]
Workflow_Step_Order Workflow_Step_Name
1 Auto Reject
2 Banner Budget Auth
3 Radiation Safety Approval
4 Hazardous Material Approval
5 IT Approval
6 Department Approvals
7 Catch All
8 Business Office Approvals
9 Grant Approvals
10 Plant Approvals
11 Form Approval
12 New Supplier Data Entry
13 Sole Source Approval
14 Purchasing Approvals
15 Purchasing Next FY
16 Processed FY Orders
17 Banner PR Create
18 Create PO

[/data]
here is some test data from the T_PRApprovalHistory.

[data]
PR_ID PR_Date Workflow_Step_Name Workflow_Step_Date
11050254 18-May-09 Department Approvals 18-May-09
11050254 18-May-09 Department Approvals 18-May-09
11050254 18-May-09 Department Approvals 18-May-09
11050254 18-May-09 Banner Budget Auth 18-May-09
11050390 18-May-09 Banner Budget Auth 18-May-09
11050390 18-May-09 Department Approvals 18-May-09
11050390 18-May-09 Department Approvals 18-May-09
11050254 18-May-09 Business Office Approvals 27-May-09
11050390 18-May-09 Business Office Approvals 27-May-09
11050390 18-May-09 Purchasing Approvals 03-Jun-09
11050390 18-May-09 Purchasing Next FY 25-Jun-09
11050390 18-May-09 Processed FY Orders 29-Jun-09
11050254 18-May-09 Purchasing Approvals 21-Jul-09
11050254 18-May-09 Purchasing Approvals 22-Jul-09
11050254 18-May-09 Purchasing Next FY 23-Jul-09
11050254 18-May-09 Purchasing Next FY 24-Jul-09
11050254 18-May-09 Processed FY Orders 24-Jul-09
13728252 01-Dec-09 Grant Approvals 02-Dec-09
13728252 01-Dec-09 Banner Budget Auth 02-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
13728252 01-Dec-09 Form Approval 07-Dec-09
14265338 14-Jan-10 Form Approval 15-Jan-10
14264637 14-Jan-10 Form Approval 05-Feb-10
14729068 12-Feb-10 Banner Budget Auth 12-Feb-10
14729068 12-Feb-10 Purchasing Approvals 15-Feb-10
14729068 12-Feb-10 Department Approvals 15-Feb-10
14729068 12-Feb-10 Grant Approvals 15-Feb-10


[/data]


this is the new code.

Option Compare Database
 
Dim rst As DAO.Recordset
Dim rstTemp As Recordset
Dim i As Long
Dim dbs As DAO.Database
Dim strSQL As String
 
Dim strFirstRec As String
 
'*********temp fields for writing records
Dim strPR_ID_TEMP As String
Dim strWorkflow_Step_Order_TEMP As Integer
Dim strWorkflow_Step_Name_TEMP As String
Dim strWorkflow_Step_Date_TEMP As Date
Dim strPR_Date_TEMP As Date
Dim strCountOfWorkflow_Step_Date_TEMP As Integer
 
 
Private Sub Generate__PR_Status_Table_Click()
On Error GoTo Err_Hndlr
 
 
'Delete temporary table
DoCmd.RunSQL "DROP TABLE tblTemp;"
 
'Create temporary table
CurrentDb.Execute ("CREATE TABLE tblTemp(PR_Id integer, PR_Date DATETIME, Workflow_Step_Order smallint, Workflow_Step_Name VARCHAR(50), Workflow_Step_Date DATETIME, CountOfWorkflow_Step_Date smallint)")
 
'Bind rstTemp to the temporary table
Set rstTemp = CurrentDb.OpenRecordset("tblTemp")
 
 
'Grab the data and put it in rst
Set rst = CurrentDb.OpenRecordset("SELECT T_PRApprovalHistory.PR_ID,  " & _
                "T_PRApprovalHistory.PR_Date,  " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                "T_PRApprovalHistory.Workflow_Step_Date,  " & _
                "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
        "FROM T_PRApprovalHistory INNER JOIN tblWorkflowApprovalStep ON T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
        "GROUP BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, tblWorkflowApprovalStep.Workflow_Step_Order, T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date " & _
        "HAVING (((T_PRApprovalHistory.PR_ID)=13728252)or " & _
                    "((T_PRApprovalHistory.PR_ID)=14264637)or " & _
                    "((T_PRApprovalHistory.PR_ID)=14265338)or " & _
                    "((T_PRApprovalHistory.PR_ID)=11050390)or " & _
                    "((T_PRApprovalHistory.PR_ID)=11050254)or " & _
                    "((T_PRApprovalHistory.PR_ID)=14729068)) " & _
        "ORDER BY T_PRApprovalHistory.PR_ID, tblWorkflowApprovalStep.Workflow_Step_Order, T_PRApprovalHistory.Workflow_Step_Date")
 
 
'Move the data into the temporary table
rst.MoveFirst
Do While rst.EOF = False
    rstTemp.AddNew
        For i = 0 To rst.Fields.Count - 1
            rstTemp.Fields(i).Value = rst.Fields(i).Value
        Next
    rstTemp.Update
    rst.MoveNext
Loop
 
rst.Close
 
'********************************************************logic*****************************************
 
strFirstRec = "Yes"
 
' **** initialize temporary fields, date is set to dummy initial date, it has no meaning
 
strPR_ID_TEMP = " "
strPR_Date_TEMP = "03/19/2007"
strWorkflow_Step_Order_TEMP = "0"
strWorkflow_Step_Name_TEMP = " "
strWorkflow_Step_Date_TEMP = "03/19/2007"
strCountOfWorkflow_Step_Date_TEMP = "0"
 
'**** create output table
'Delete temporary table
DoCmd.RunSQL "DROP TABLE tblApprovalHistorySummary;"
 
'Create temporary table
CurrentDb.Execute ("CREATE TABLE tblApprovalHistorySummary(PR_Id integer, PR_Date DATETIME, Workflow_Step_Order smallint, Workflow_Step_Name VARCHAR(50), Workflow_Step_Date DATETIME, CountOfWorkflow_Step_Date smallint)")
 
'Bind rstTemp to the temporary table
Set rstSummary = CurrentDb.OpenRecordset("tblApprovalHistorySummary")
 
 
rstTemp.MoveFirst
 
Do While rstTemp.EOF = False
 
    Debug.Print "Temp file "; rstTemp!PR_ID&; " "; rstTemp!PR_Date&; " "; rstTemp!Workflow_Step_Name&; " "; rstTemp!workflow_Step_Date
 
    If strFirstRec = "Yes" Then
        strFirstRec = "No"
        strPR_ID_TEMP = rstTemp!PR_ID
        strPR_Date_TEMP = rstTemp!PR_Date
        strWorkflow_Step_Order_TEMP = rstTemp!Workflow_Step_Order
        strWorkflow_Step_Date_TEMP = rstTemp!workflow_Step_Date
        strWorkflow_Step_Name_TEMP = rstTemp!Workflow_Step_Name
        strCountOfWorkflow_Step_Date_TEMP = rstTemp!CountOfWorkflow_Step_Date
    End If
 
    If rstTemp!PR_ID = strPR_ID_TEMP Then
        If rstTemp!Workflow_Step_Name = strWorkflow_Step_Name_TEMP Then
            If rstTemp!workflow_Step_Date = strWorkflow_Step_Date_TEMP Then
                strWorkflow_Step_Date_TEMP = rstTemp!workflow_Step_Date
            End If
        Else
'***        write record
            rstSummary.AddNew
                rstSummary!PR_ID = strPR_ID_TEMP
                rstSummary!PR_Date = strPR_Date_TEMP
                rstSummary!Workflow_Step_Order = strWorkflow_Step_Order_TEMP
                rstSummary!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
                rstSummary!workflow_Step_Date = strWorkflow_Step_Date_TEMP
                rstSummary!CountOfWorkflow_Step_Date = strCountOfWorkflow_Step_Date_TEMP
            rstSummary.Update
 
'***  assign temp values
                strPR_ID_TEMP = rstTemp!PR_ID
                strPR_Date_TEMP = rstTemp!PR_Date
                strWorkflow_Step_Order_TEMP = rstTemp!Workflow_Step_Order
                strWorkflow_Step_Date_TEMP = rstTemp!workflow_Step_Date
                strWorkflow_Step_Name_TEMP = rstTemp!Workflow_Step_Name
                strCountOfWorkflow_Step_Date_TEMP = rstTemp!CountOfWorkflow_Step_Date
        End If
    Else
'***   write record
        rstSummary.AddNew
            rstSummary!PR_ID = strPR_ID_TEMP
            rstSummary!PR_Date = strPR_Date_TEMP
            rstSummary!Workflow_Step_Order = strWorkflow_Step_Order_TEMP
            rstSummary!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
            rstSummary!workflow_Step_Date = strWorkflow_Step_Date_TEMP
            rstSummary!CountOfWorkflow_Step_Date = strCountOfWorkflow_Step_Date_TEMP
        rstSummary.Update
 
'***  assign temp values
        strPR_ID_TEMP = rstTemp!PR_ID
        strPR_Date_TEMP = rstTemp!PR_Date
        strWorkflow_Step_Order_TEMP = rstTemp!Workflow_Step_Order
        strWorkflow_Step_Date_TEMP = rstTemp!workflow_Step_Date
        strWorkflow_Step_Name_TEMP = rstTemp!Workflow_Step_Name
        strCountOfWorkflow_Step_Date_TEMP = rstTemp!CountOfWorkflow_Step_Date
    End If
 
    rstTemp.MoveNext
Loop
 
rstTemp.Close
rstSummary.Close
Exit Sub
 
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Generate__PR_Status_Table_Click()"
End Sub

[patjones]

Great! I'm glad it's working for now. When I get a chance I'll look at your data and see if there isn't a simpler way to accomplish what you want.

Pat