Modify filtered results

[If]

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

 

[Marshall Barton]

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

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??

 

[If]

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.
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

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??

 

[If]

Thanks
How to do that by UPDATE Query ?



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.
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

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??

 

[Marshall Barton]

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
--
Marsh
MVP [MS Access]

How to do that by UPDATE Query ?


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.
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

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??

 

[If]

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

Marshall Barton a écrit :

 

[John W. Vinson]

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

Marshall Barton a écrit :

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]

 

[If]

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 :

 

[John W. Vinson]

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]

 

[Marshall Barton]

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

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

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?

 

[If]

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 <[email protected]>
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".

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

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?