Is it possible to batch update a continuous form that is filtered?

[ptslv]

I have a requirement to receive tables filled with data, create a form that can be filtered for multiple criteria, and allow updating of two fields in each of the filtered records. The fields edited will always be the same field. The database received will not be normalized.

What I did was create a continuous form on one of the tables where I queried for FinalScore = NULL to pull all the records that need to be edited.

Then I added an unbound field for FinalScore and a comment field and tried to loop through the records but nothing updated. Waht is the best way to update the filtered records? By the way, I will not know what the filter is, so I will be using the form's filter method to add the filter to the where clause of my sql statement.



Thanks for any help.

[ItsMe]

You can pass the filter property of a form to a string variable.

Dim strWhere as string
Strwhere = me.filter

[ptslv]

I have tried that but it gives me a type mismatch error:

[code]

Dim strSQL As String, i As Integer
Dim rsDAO As DAO.Recordset
Dim myFilter As String
myFilter = Me.Filter
If myFilter Is Null Or myFilter = "" Then
strSQL = "SELECT * FROM tbl_Finals WHERE Final_Scoring Is NULL "
Else
strSQL = "SELECT * FROM tbl_Finals Final_Scoring Is NULL AND " & myFilter
End If


[code]

[ptslv]

type mismatch error at this line: myFilter = Me.Filter

[ItsMe]

Verify your data


Dim strWhere As String
strWhere = ""

If Me.FilterOn = True Then
strWhere = Me.Filter
Else
MsgBox "The Form's filter is not currently on"
End If

[ptslv]

OK, I have gone back into the code and made all my adjustments. My filtered data is coming in correctly now. My message says 7 records have been updated, but nothing is updated. Here is my code.

[code]

Private Sub ScoreFilteredRecs_Click()
Dim conn As ADODB.Connection

Set conn = CurrentProject.Connection
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim strSql As String, i As Integer
Dim myFilter As String
myFilter = Me.Filter
If myFilter = "" Then
strSql = "SELECT * FROM tbl_Finals WHERE Final_Scoring Is NULL "
Else
strSql = "SELECT * FROM tbl_Finals WHERE Final_Scoring Is NULL AND " & myFilter
End If

rst.Open strSql, conn, adOpenKeyset, adLockOptimistic


i = 0

Do Until rst.EOF
i = i + 1
If rst![Final_Scoring] = Null Or rst![Final_Scoring] = "" Then
rst![Final_Scoring] = Me.FinalScore.Value
End If
If rst![Comments] = Null Or rst![Comments] = "" Then
rst![Comments] = Me.FinalComments.Value
End If
rst.MoveNext
Loop

MsgBox i & " Records Updated."

rst.Close

End Sub


[code]

[ItsMe]

Untested Code


You need to do a better job of verifying if the filter property of the form has anything. Strings can not accept Null. I believe if the Form's Filter is not on the Filter property will be Null. I suggest something like this.

Dim strSql As String, i As Integer
Dim myFilter As String
'initialize the string variable
myFilter = ""
    If Me.FilterOn = True Then
    'Concatenate a where clause to the SQL string
        myFilter = " AND " & Me.Filter
    End If
strSql = "SELECT * FROM tbl_Finals WHERE Final_Scoring Is NULL " & myFilter

Your ADO needs an Update. You need to include an Update line before your MoveNext.

rst.Update
rst.MoveNext

Another approcah is to use DAO and create a recordset clone. The recordset clone will be a copy of the form's recordset and include the filter property, if it exists.

Dim intCount As Integer
Dim intAnswer As Integer
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
'no need to verify EOF or Movelast with
'Recordset Clone when working within cloned form's module
intCount = rs.RecordCount
intAnswer = MsgBox("You are about to update " & intCount & " records." & vbCrLf & _
                   "Do you want to continue?", vbInformation + vbYesNo + vbDefaultButton2, "Confirm Update!")
 
If intAnswer = vbYes Then

rs.MoveFirst 'I believe you need this. If it errors, remove it
While rs.EOF = False
rs.Edit
    If rs![Final_Scoring] = Null Or rs![Final_Scoring] = "" Then
        rs![Final_Scoring] = Me.FinalScore.Value
    End If
    
        If rs![Comments] = Null Or rs![Comments] = "" Then
            rs![Comments] = Me.FinalComments.Value
        End If
rs.Update
rs.MoveNext
Wend
End If

A third option would be to execute an action query and concatenate the filter property into an UPDATE string. No need for ADO or DAO. You would include the Final Scoring and Comments criteria in the WHERE clause of the SQL string

[ptslv]

I tried your second method, and did not get any error messages. But nothing updated. Is it possble that I am using the wrong type of form? I am using a continous form.

[ptslv]

Running the debugger, the code does not step into the Then portion of the if statement even though the field is NULL to set the value.

[ItsMe]

hmmm.

Try the IsNull function there.


change
rs![Final_Scoring] = Null
to
isnull(rs![Final_Scoring])

[ptslv]

It is finally updating the records. Thank you. How do reset the form to the original filter of "FinalScore = NULL"? I have tried to set it after the updates are completed, but all the table records are populating the form. When i close and reopen the form, the correct records now show.

[Code]

Me.Filter = ""
Me.Filter = "(C2.Final_Scoring Is NULL)"
Me.FinalScore = ""
Me.FinalComments = ""
Me.Requery

[Code]

[ItsMe]

THe property that you adjust to toggle the filter is the .FilterOn property and you reset the filter with

Me.Filter = ""

So first
Me.FilterOn = False
then
Me.filter = ""

Or you can toggle the existing criteria on and off

Me.filteron = true

then

Me.filteron = false

and back to

Me.filteron = true

again

[ptslv]

Thank you so much! That worked like a charm! It has taken me too long to get this form to work correctly. I appreciate all your assistance.

[ItsMe]

No problem....