Modify filtered results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

If - 23 Dec 2007 16:16 GMT

Hello,

In a search form, with a sub form (table filtered), I would like to be
able to modify a field by clicking a button.
This is the kind of file that I use. Http://allenbrowne.com/ser-62code.html.

I have this procedure, but it does update only one record.
I would like to have to change all the results of the search.

Private Sub Button_Click()
With Me.Subform.Form
Me.Subform.Form![FieldCheckbox] = -1
End With
End Sub

Reply to this Message

Marshall Barton - 23 Dec 2007 19:01 GMT

>In a search form, with a sub form (table filtered), I would like to be
>able to modify a field by clicking a button.
[quoted text clipped - 8 lines]
> End With
>End Sub

That will just modify the current record.

Your question is somewhat out of context so I don't know
what a good answer might be. However, you could use this
kind of code to do exactly what you asked:

Private Sub Button_Click()
With Me.Subform.Form.RecordsetClone
        .MoveFirst
        Do Until .EOF
            .Edit
        ![FieldCheckbox] = -1
            .Update
            .MoveNext
        Loop
End With
End Sub

But, it's possible that constructing and executing an UPDATE
query would be more efficient and/or better in a multi-user
environment??

Signature

Marsh
MVP [MS Access]

Reply to this Message

If - 23 Dec 2007 22:33 GMT

Thanks

Marshall Barton a écrit :

>> In a search form, with a sub form (table filtered), I would like to be
>> able to modify a field by clicking a button.
[quoted text clipped - 30 lines]
> query would be more efficient and/or better in a multi-user
> environment??

Reply to this Message

If - 24 Dec 2007 00:00 GMT

Thanks
How to do that by UPDATE Query ?

Marshall Barton a écrit :

Reply to this Message

Marshall Barton - 24 Dec 2007 17:47 GMT

I think this should be close to what you could use:

Private Sub Button_Click()
Dim strSQL As String
strSQL = "UPDATE table SET FieldCheckbox = False"
With Me.Subform.Form
    If .Filter = "" Or .FilterOn = False Then
        MsgBox "Form is not filtered"
    Else
        strSQL = strSQL & "WHERE " & .Filter
        DbEngins(0)(0).Execute strSQL, dbFileOnError
    End If
End With
End Sub

Signature

Marsh
MVP [MS Access]

>How to do that by UPDATE Query ?
>
[quoted text clipped - 33 lines]
>> query would be more efficient and/or better in a multi-user
>> environment??

Reply to this Message

If - 25 Dec 2007 00:34 GMT

Thanks for your help.
I have an error on this line : DbEngins(0)(0).Execute strSQL, dbFileOnError

Marshall Barton a écrit :

> I think this should be close to what you could use:
>
[quoted text clipped - 10 lines]
> End With
> End Sub

Reply to this Message

John W. Vinson - 25 Dec 2007 01:06 GMT

>Thanks for your help.
>I have an error on this line : DbEngins(0)(0).Execute strSQL, dbFileOnError
[quoted text clipped - 14 lines]
>> End With
>> End Sub

YOu need a blank before the word Where, after the leading quote. Your strSQL
is getting set to a meaningless

UPDATE table SET FieldCheckbox = FalseWHERE somefilterstring

Obviously you also need to replace 'table' with your actual tablename, and
FieldCheckbox with the real yes/no field name; and correct the dbEngins typo
to dbEngine.

John W. Vinson [MVP]

Reply to this Message

If - 25 Dec 2007 11:56 GMT

Thank you,
I have the lines which follow but I have an error of variable on this
instruction "dbFileOnError".

I have this now

Private Sub Button_Click()
Dim strSQL As String
strSQL = "UPDATE [My Table]SET [Field Checkbox] = True"
With Me.MyForm.Form
If .Filter = "" Or .FilterOn = False Then
MsgBox "Form is not filtered"
Else
CurrentDb.Execute strSQL & " WHERE " & .Filter
End If
End With
End Sub

Is the procedure correct?

Yves

John W. Vinson a écrit :

>> Thanks for your help.
>> I have an error on this line : DbEngins(0)(0).Execute strSQL, dbFileOnError
[quoted text clipped - 25 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 25 Dec 2007 17:13 GMT

>I have the lines which follow but I have an error of variable on this
>instruction "dbFileOnError".

oops!!!!

Should be

dbFailOnError

You want the query to "fail" (trigger an error which can be trapped). There's
no "file" involved.

Note that "I got an error" is of very little use to the volunteers here...
please specify the error message when you post to avoid the waste of a day in
getting your answer.

John W. Vinson [MVP]

Reply to this Message

Marshall Barton - 25 Dec 2007 17:20 GMT

>I have the lines which follow but I have an error of variable on this
>instruction "dbFileOnError".
[quoted text clipped - 10 lines]
>End With
>End Sub

Sorry about all the typos, If, and thanks to John for
spotting them.

You are now missing a space before the word SET, but because
ot the precedding ] I don't think it matters. Other than
that I don't see any syntax errors.

The use of CurrentDb instead of DbEngine(0)(0) is fine as
long as you do not have a use for the RecordsAffected
property. OTOH, since you removed the dbFailOnError
argument, I don't see how you can get an error message about
it. Maybe there's a scrap of code in another place that
caused the error?? Have you used the Debug - Compile menu
item to check for compile errors?

Signature

Marsh
MVP [MS Access]

Reply to this Message

If - 25 Dec 2007 19:54 GMT

Great thanks to you

Ok now it's fine

Private Sub Button_Click()
Dim strSQL As String
strSQL = "UPDATE [My Table]SET [Field Checkbox] = True"
With Me.MyForm.Form
If .Filter = "" Or .FilterOn = False Then
MsgBox "Form is not filtered"
Else
strSQL = strSQL & " WHERE " & .Filter
DBEngine(0)(0).Execute strSQL, dbFailOnError
End If
End With
End Sub

-------- Original Message --------
Subject: Re:Modify filtered results
From: Marshall Barton <marshbarton@wowway.com>
To:
Date: Tue Dec 25 2007 18:20:58 GMT+0100

>> I have the lines which follow but I have an error of variable on this
>> instruction "dbFileOnError".
[quoted text clipped - 25 lines]
> caused the error?? Have you used the Debug - Compile menu
> item to check for compile errors?

Reply to this Message