Obtaining a filter from a form

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

Hello All

Is it possible to obtain a filter, for use in a query, from a form?

i.e. After a user has implemented various filters on various fields on a
form, I would like to have a query that would return the same recordset that
the user has arrived at on the form. This is so I could use this query, as
an update query, to set the value of a particular field in all the records
that the user has chosen to display.

Hope someone can help
Many thanks
Les
 
Hello Steve

When someone asks a question on these newsgroups along the lines of "Is it
possible to ...?", it is rarely likely to be helpful to receive an answer
"Yes". Anyone providing such an answer risks being seen as facetious, or
worse.

Perhaps this is why you now need to add the word "former" to your Access MVP
accolade?

Les
 
Although Steve's answer may have been a bit terse, it was correct. You really
did not give enough information to provide a more definitive answer.

I would say that yes it is possible in many (not all) cases. The
implementation depends on having some knowledge of VBA and SQL beyond using
the query grid. You will probably need to build the query's where clause on
the fly.

The VBA might be as simple and straightforward as the following or it might
need to be more complex - for instance
-- does the query for the form have a where clause
-- are there multiple tables involved for the form


Dim DbAny as DAO.Database
Set DbAny = CurrentDb()
StrWhere = Forms!FormName!Filter
strSQL = "UPDATE SomeTable SET SomeField = SomeValue" & _
" WHERE " & strWhere

DbAny.Execute strSQL, DbFailonError


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Hello John

Many thanks for your reply. I'm glad my little outburst hasn't got me
blacklisted!

I now understand that the code I need would have to take account of the
form's underlying recordset - in terms of any WHERE clause and/or any
multiple tables.
Before I go further I should explain what I want to do - because in fact I
can see two ways of solving this, one of which is along the lines of my
question in this post and the other is along the line of a post that I made
on the FormsCoding newsgroup last Friday (at 14:17), and it is probably only
worth persevering with one of the two approaches.

Essentially I want to add a button to the form header of a particular form
(which opens in continuous form view) which, when clicked, will add a
user-specified value to a particular field (called [stmt_datee]) in the
detail section for each of the records that are currently displayed: this
set of records will be form's opening dataset as further amended by the user
implementing various filters (using filter-by-selection).

The approach I was asking about on the FormsCoding newsgroup was to set the
value directly on the form by 'cycling' through the filtered recordset. In
the form header I have a textbox called [recs] with control source
=Count([ID1]) (ID1 is the key field from the underlying table, so [recs] is
a reliable count of the number of records being displayed), and the code I
had was:

Private Sub Command37_click()
Dim sdate As Date
Dim inc As Integer
sdate = InputBox("Enter statement date")
DoCmd.GoToRecord , , acFirst
[stmt_datee].Value = sdate
For inc = 1 To [recs].Value - 1
DoCmd.GoToRecord , , acNext
[stmt_datee].Value = sdate
Next inc
End Sub

But this doesn't work: sometimes it just sets the [stmt_datee] value in the
first or one other record, and sometimes I get a "Cannot go to the specified
record" message - even when there are, say, 4 records displayed.

I hope this makes sense, and that help me see (a) which approach to use and
(b) where I'm going wrong!

Many thanks
Les
 
I would probably use an update query, but you could probably use code that
looks like the following UNTESTED code

Private Sub Command37_Click
'By the way terrible name for the button
'Rename it to btnUpdateStmtDatee

Dim sDate as variant
On Error goto Proc_Err

sDate = InputBox("Enter StatementDate")

'Make sure you have a date
If IsDate(sDate) then

'Same any change to the current record
If Me.Dirty then Me.Dirty = False

'Update the records
With Me.RecordSetClone
.moveFirst
While not .EOF
.Edit
.Stmt_Datee = sDate
.Update
.Movenext
Wend
.Bookmark = Me.Bookmark
End With

End IF 'IsDate
exit Sub

Proc_Err:
MsgBox Err.Number & ": " & Err.Description,,"Failed to update all records"

End Sub


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Leslie said:
Hello John

Many thanks for your reply. I'm glad my little outburst hasn't got me
blacklisted!

I now understand that the code I need would have to take account of the
form's underlying recordset - in terms of any WHERE clause and/or any
multiple tables.
Before I go further I should explain what I want to do - because in fact I
can see two ways of solving this, one of which is along the lines of my
question in this post and the other is along the line of a post that I made
on the FormsCoding newsgroup last Friday (at 14:17), and it is probably only
worth persevering with one of the two approaches.

Essentially I want to add a button to the form header of a particular form
(which opens in continuous form view) which, when clicked, will add a
user-specified value to a particular field (called [stmt_datee]) in the
detail section for each of the records that are currently displayed: this
set of records will be form's opening dataset as further amended by the user
implementing various filters (using filter-by-selection).

The approach I was asking about on the FormsCoding newsgroup was to set the
value directly on the form by 'cycling' through the filtered recordset. In
the form header I have a textbox called [recs] with control source
=Count([ID1]) (ID1 is the key field from the underlying table, so [recs] is
a reliable count of the number of records being displayed), and the code I
had was:

Private Sub Command37_click()
Dim sdate As Date
Dim inc As Integer
sdate = InputBox("Enter statement date")
DoCmd.GoToRecord , , acFirst
[stmt_datee].Value = sdate
For inc = 1 To [recs].Value - 1
DoCmd.GoToRecord , , acNext
[stmt_datee].Value = sdate
Next inc
End Sub

But this doesn't work: sometimes it just sets the [stmt_datee] value in the
first or one other record, and sometimes I get a "Cannot go to the specified
record" message - even when there are, say, 4 records displayed.

I hope this makes sense, and that help me see (a) which approach to use and
(b) where I'm going wrong!

Many thanks
Les






John Spencer said:
Although Steve's answer may have been a bit terse, it was correct. You
really did not give enough information to provide a more definitive
answer.

I would say that yes it is possible in many (not all) cases. The
implementation depends on having some knowledge of VBA and SQL beyond
using the query grid. You will probably need to build the query's where
clause on the fly.

The VBA might be as simple and straightforward as the following or it
might need to be more complex - for instance
-- does the query for the form have a where clause
-- are there multiple tables involved for the form


Dim DbAny as DAO.Database
Set DbAny = CurrentDb()
StrWhere = Forms!FormName!Filter
strSQL = "UPDATE SomeTable SET SomeField = SomeValue" & _
" WHERE " & strWhere

DbAny.Execute strSQL, DbFailonError


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
Hello John

Many thanks for your further help. I will not be in the office until
tomorrow and so will not be able to try your suggested code until then: but
looking at it, and remembering the help I have had from you in the past, I
am confident that you have fixed my problem: and I will rename the command
button!

I am curious why you say that you would probably have used an update query
instead of updating the fields directly on the form. Also, I could not work
out how to get the form's ultimate filter (as updated by the user's various
filter-by-selection actions) into an update query.

Thanks again for the help - I'll post back tomorow to let you know the
result.
Les



John Spencer said:
I would probably use an update query, but you could probably use code that
looks like the following UNTESTED code

Private Sub Command37_Click
'By the way terrible name for the button
'Rename it to btnUpdateStmtDatee

Dim sDate as variant
On Error goto Proc_Err

sDate = InputBox("Enter StatementDate")

'Make sure you have a date
If IsDate(sDate) then

'Same any change to the current record
If Me.Dirty then Me.Dirty = False

'Update the records
With Me.RecordSetClone
.moveFirst
While not .EOF
.Edit
.Stmt_Datee = sDate
.Update
.Movenext
Wend
.Bookmark = Me.Bookmark
End With

End IF 'IsDate
exit Sub

Proc_Err:
MsgBox Err.Number & ": " & Err.Description,,"Failed to update all
records"

End Sub


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Leslie said:
Hello John

Many thanks for your reply. I'm glad my little outburst hasn't got me
blacklisted!

I now understand that the code I need would have to take account of the
form's underlying recordset - in terms of any WHERE clause and/or any
multiple tables.
Before I go further I should explain what I want to do - because in fact
I can see two ways of solving this, one of which is along the lines of my
question in this post and the other is along the line of a post that I
made on the FormsCoding newsgroup last Friday (at 14:17), and it is
probably only worth persevering with one of the two approaches.

Essentially I want to add a button to the form header of a particular
form (which opens in continuous form view) which, when clicked, will add
a user-specified value to a particular field (called [stmt_datee]) in the
detail section for each of the records that are currently displayed: this
set of records will be form's opening dataset as further amended by the
user implementing various filters (using filter-by-selection).

The approach I was asking about on the FormsCoding newsgroup was to set
the value directly on the form by 'cycling' through the filtered
recordset. In the form header I have a textbox called [recs] with control
source =Count([ID1]) (ID1 is the key field from the underlying table, so
[recs] is a reliable count of the number of records being displayed), and
the code I had was:

Private Sub Command37_click()
Dim sdate As Date
Dim inc As Integer
sdate = InputBox("Enter statement date")
DoCmd.GoToRecord , , acFirst
[stmt_datee].Value = sdate
For inc = 1 To [recs].Value - 1
DoCmd.GoToRecord , , acNext
[stmt_datee].Value = sdate
Next inc
End Sub

But this doesn't work: sometimes it just sets the [stmt_datee] value in
the first or one other record, and sometimes I get a "Cannot go to the
specified record" message - even when there are, say, 4 records
displayed.

I hope this makes sense, and that help me see (a) which approach to use
and (b) where I'm going wrong!

Many thanks
Les






John Spencer said:
Although Steve's answer may have been a bit terse, it was correct. You
really did not give enough information to provide a more definitive
answer.

I would say that yes it is possible in many (not all) cases. The
implementation depends on having some knowledge of VBA and SQL beyond
using the query grid. You will probably need to build the query's where
clause on the fly.

The VBA might be as simple and straightforward as the following or it
might need to be more complex - for instance
-- does the query for the form have a where clause
-- are there multiple tables involved for the form


Dim DbAny as DAO.Database
Set DbAny = CurrentDb()
StrWhere = Forms!FormName!Filter
strSQL = "UPDATE SomeTable SET SomeField = SomeValue" & _
" WHERE " & strWhere

DbAny.Execute strSQL, DbFailonError


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Leslie Isaacs wrote:
Hello Steve

When someone asks a question on these newsgroups along the lines of "Is
it possible to ...?", it is rarely likely to be helpful to receive an
answer "Yes". Anyone providing such an answer risks being seen as
facetious, or worse.

Perhaps this is why you now need to add the word "former" to your
Access MVP accolade?

Les



If you're VBA savvy, then the answer is yes. But if just using the
QBE, then
no.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



:

Hello All

Is it possible to obtain a filter, for use in a query, from a form?

i.e. After a user has implemented various filters on various fields
on a
form, I would like to have a query that would return the same
recordset that
the user has arrived at on the form. This is so I could use this
query, as
an update query, to set the value of a particular field in all the
records
that the user has chosen to display.

Hope someone can help
Many thanks
Les
Click to expand...
Click to expand...
Click to expand...
 
I would use a query if at all possible, because that is what a query is
designed for and a query is usually more efficient

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Hello John

I'm afraid I got an error when running the code you suggested:

438 Object doesn't support this property or method

What have I done wrong?

The code I have is:

Private Sub btnUpdateStmtDatee_Click()

Dim sDate As Variant
On Error GoTo Proc_Err

sDate = InputBox("Enter Statement Date for the items displayed")

'Make sure you have a date
If IsDate(sDate) Then

'Same any change to the current record
If Me.Dirty Then Me.Dirty = False

'Update the records
With Me.RecordsetClone
.MoveFirst
While Not .EOF
.Edit
.stmt_datee = sDate
.Update
.MoveNext
Wend
.Bookmark = Me.Bookmark
End With

End If 'IsDate
Exit Sub

Proc_Err:
MsgBox Err.number & ": " & Err.description, , "Failed to update all
records"

End Sub


Hope you can help.
Many thanks.
Les
 
John

Forget my previous post - it all works perfectly!

The problem was that, for other reasons, I had renamed the textbox
stmt_datee - the data source for that textbox is in fact the field
stmt_date.
When I alter the relevant line of code to .stmt_date = sDate, the sun shines
and all is well!

Many thanks for your help with this.

Les
 
Back
Top