Change Query on a Form OnOpen

[magmike]

I'm trying to recycle a form, rather than copy it and just change the
data source. What I have done on an OnClick Event is the following
(which isn't working!):

Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String
Dim stQuery As String

stForm = "ProspectSearch"
stQuery = "WHERE ProspectTable.Select = -1"

DoCmd.OpenForm stForm, , , stQuery

End Sub

ProspectSearch by default shows every record until a search term is
typed into a search box. I am trying to open it to where only records
whose field [Select] is checked. I've also tried the following (which
tells me I can't assign a value to this object):

Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String

stForm = "ProspectSearch"
DoCmd.OpenForm stForm

Forms!ProspectSearch!findquery.Form.Filter = "Select = -1"

End Sub

I also tried the above with '-1'

Thanks in advance for your help!

magmike

 

[Klatuu]

Use the Where argument of the OpenForm method to filter the records to only
those where Select is True:

Docmd.OpenForm ""ProspectSearch", , , "[Select] = True"

 

[magmike]

Use the Where argument of the OpenForm method to filter the records to only
those where Select is True:

    Docmd.OpenForm ""ProspectSearch", , , "[Select] = True"

--
Dave Hargis, Microsoft Access MVP

I'm trying to recycle a form, rather than copy it and just change the
data source. What I have done on an OnClick Event is the following
(which isn't working!):

Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String
Dim stQuery As String

stForm = "ProspectSearch"
stQuery = "WHERE ProspectTable.Select = -1"

DoCmd.OpenForm stForm, , , stQuery

End Sub

ProspectSearch by default shows every record until a search term is
typed into a search box. I am trying to open it to where only records
whose field [Select] is checked. I've also tried the following (which
tells me I can't assign a value to this object):

Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String

stForm = "ProspectSearch"
DoCmd.OpenForm stForm

Forms!ProspectSearch!findquery.Form.Filter = "Select = -1"

End Sub

I also tried the above with '-1'

Thanks in advance for your help!

magmike- Hide quoted text -

- Show quoted text -

That's not working for me. Please note, that what I am attempting to
filter is a subform, titled findquery, and populated by the form
findqueryFormProspects

I don't know if that makes any difference.

Something else I have tried:

DoCmd.OpenForm "ProspectSearch"
DoCmd.GoToControl "Forms!ProspectSearch!findqueryFormProspects.Form!
COMPANY"
DoCmd.ApplyFilter , "Select = True"

However, it keeps telling me that the field does not exist, but I've
checked and it does! Have I referred to it correctly?

 

[Klatuu]

If it is not working for you then you are not doing it quite right. It does
work.
There is one other issue. You should change the name of the field Select.
It is a reserved word in both Jet SQL and in Access. Using reserved words as
names can easily confuse Access and Jet. Select is probably then most used
work in SQL.
That may be your problem.
--
Dave Hargis, Microsoft Access MVP

Use the Where argument of the OpenForm method to filter the records to only
those where Select is True:

Docmd.OpenForm ""ProspectSearch", , , "[Select] = True"

--
Dave Hargis, Microsoft Access MVP

I'm trying to recycle a form, rather than copy it and just change the
data source. What I have done on an OnClick Event is the following
(which isn't working!):

Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String
Dim stQuery As String

stForm = "ProspectSearch"
stQuery = "WHERE ProspectTable.Select = -1"

DoCmd.OpenForm stForm, , , stQuery

End Sub

ProspectSearch by default shows every record until a search term is
typed into a search box. I am trying to open it to where only records
whose field [Select] is checked. I've also tried the following (which
tells me I can't assign a value to this object):

Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String

stForm = "ProspectSearch"
DoCmd.OpenForm stForm

Forms!ProspectSearch!findquery.Form.Filter = "Select = -1"

End Sub

I also tried the above with '-1'

Thanks in advance for your help!

magmike- Hide quoted text -

- Show quoted text -

That's not working for me. Please note, that what I am attempting to
filter is a subform, titled findquery, and populated by the form
findqueryFormProspects

I don't know if that makes any difference.

Something else I have tried:

DoCmd.OpenForm "ProspectSearch"
DoCmd.GoToControl "Forms!ProspectSearch!findqueryFormProspects.Form!
COMPANY"
DoCmd.ApplyFilter , "Select = True"

However, it keeps telling me that the field does not exist, but I've
checked and it does! Have I referred to it correctly?

 

[magmike]

If it is not working for you then you are not doing it quite right.  It does
work.
There is one other issue.  You should change the name of the field Select.  
It is a reserved word in both Jet SQL and in Access.  Using reserved words as
names can easily confuse Access and Jet.  Select is probably then most used
work in SQL.
That may be your problem.
--
Dave Hargis, Microsoft Access MVP

Use the Where argument of the OpenForm method to filter the records toonly
those where Select is True:
    Docmd.OpenForm ""ProspectSearch", , , "[Select] = True"
--
Dave Hargis, Microsoft Access MVP
:
I'm trying to recycle a form, rather than copy it and just change the
data source. What I have done on an OnClick Event is the following
(which isn't working!):
Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String
Dim stQuery As String
stForm = "ProspectSearch"
stQuery = "WHERE ProspectTable.Select = -1"
DoCmd.OpenForm stForm, , , stQuery
End Sub
ProspectSearch by default shows every record until a search term is
typed into a search box. I am trying to open it to where only records
whose field [Select] is checked. I've also tried the following (which
tells me I can't assign a value to this object):
Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String
stForm = "ProspectSearch"
DoCmd.OpenForm stForm
Forms!ProspectSearch!findquery.Form.Filter = "Select = -1"
End Sub
I also tried the above with '-1'
Thanks in advance for your help!
magmike- Hide quoted text -
- Show quoted text -

That's not working for me. Please note, that what I am attempting to
filter is a subform, titled findquery, and populated by the form
findqueryFormProspects

I don't know if that makes any difference.

Something else I have tried:

DoCmd.OpenForm "ProspectSearch"
DoCmd.GoToControl "Forms!ProspectSearch!findqueryFormProspects.Form!
COMPANY"
DoCmd.ApplyFilter , "Select = True"

However, it keeps telling me that the field does not exist, but I've
checked and it does! Have I referred to it correctly?- Hide quoted text -

- Show quoted text -

I'm asking for your help on doing it right. I have changed the field
and all references to Select to SelectMe. This is the exact code I am
running:

Docmd.OpenForm ""ProspectSearch", , , "[SelectMe] = True"

It is displaying all results instead of just those with SelectMe
checked. Keep in mind, this field is in a subform (findquery) which is
populated by the form findqueryForm. I have also tried it the
following ways as well:

DoCmd.OpenForm "ProspectSearch", , , "[SelectMe] = Yes"
DoCmd.OpenForm "ProspectSearch", , , "[SelectMe] = -1"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = True"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = Yes"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = -1"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = True"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = Yes"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = -1"

But none of these work either. I'm certain I'm doing something wrong,
because it isn't working right. Does anyone have any idea what it is I
am doing wrong?

Thanks in advance!
magmike

 

[Klatuu]

What data type is the field SelectMe
Don't get too frustrated, I know this works. It even describes it in the
OpenForm description in VBA Help. I also tested it on one of my forms to see
if I could recreate your problem.
Do you have any other filtering on the form or in the form's record source?
--
Dave Hargis, Microsoft Access MVP

If it is not working for you then you are not doing it quite right. It does
work.
There is one other issue. You should change the name of the field Select.
It is a reserved word in both Jet SQL and in Access. Using reserved words as
names can easily confuse Access and Jet. Select is probably then most used
work in SQL.
That may be your problem.
--
Dave Hargis, Microsoft Access MVP

Use the Where argument of the OpenForm method to filter the records to only
those where Select is True:

Docmd.OpenForm ""ProspectSearch", , , "[Select] = True"

:
I'm trying to recycle a form, rather than copy it and just change the
data source. What I have done on an OnClick Event is the following
(which isn't working!):

Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String
Dim stQuery As String

stForm = "ProspectSearch"
stQuery = "WHERE ProspectTable.Select = -1"

DoCmd.OpenForm stForm, , , stQuery

End Sub

ProspectSearch by default shows every record until a search term is
typed into a search box. I am trying to open it to where only records
whose field [Select] is checked. I've also tried the following (which
tells me I can't assign a value to this object):

Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String

stForm = "ProspectSearch"
DoCmd.OpenForm stForm

Forms!ProspectSearch!findquery.Form.Filter = "Select = -1"

End Sub

I also tried the above with '-1'

Thanks in advance for your help!

magmike- Hide quoted text -

- Show quoted text -

That's not working for me. Please note, that what I am attempting to
filter is a subform, titled findquery, and populated by the form
findqueryFormProspects

I don't know if that makes any difference.

Something else I have tried:

DoCmd.OpenForm "ProspectSearch"
DoCmd.GoToControl "Forms!ProspectSearch!findqueryFormProspects.Form!
COMPANY"
DoCmd.ApplyFilter , "Select = True"

However, it keeps telling me that the field does not exist, but I've
checked and it does! Have I referred to it correctly?- Hide quoted text -

- Show quoted text -

I'm asking for your help on doing it right. I have changed the field
and all references to Select to SelectMe. This is the exact code I am
running:

Docmd.OpenForm ""ProspectSearch", , , "[SelectMe] = True"

It is displaying all results instead of just those with SelectMe
checked. Keep in mind, this field is in a subform (findquery) which is
populated by the form findqueryForm. I have also tried it the
following ways as well:

DoCmd.OpenForm "ProspectSearch", , , "[SelectMe] = Yes"
DoCmd.OpenForm "ProspectSearch", , , "[SelectMe] = -1"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = True"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = Yes"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = -1"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = True"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = Yes"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = -1"

But none of these work either. I'm certain I'm doing something wrong,
because it isn't working right. Does anyone have any idea what it is I
am doing wrong?

Thanks in advance!
magmike

 

[magmike]

What data type is the field SelectMe
Don't get too frustrated, I know this works.  It even describes it in the
OpenForm description in VBA Help.  I also tested it on one of my forms to see
if I could recreate your problem.  
Do you have any other filtering on the form or in the form's record source?
--
Dave Hargis, Microsoft Access MVP

If it is not working for you then you are not doing it quite right.  It does
work.
There is one other issue.  You should change the name of the field Select.  
It is a reserved word in both Jet SQL and in Access.  Using reserved words as
names can easily confuse Access and Jet.  Select is probably then most used
work in SQL.
That may be your problem.
--
Dave Hargis, Microsoft Access MVP
:
Use the Where argument of the OpenForm method to filter the records to only
those where Select is True:
    Docmd.OpenForm ""ProspectSearch", , , "[Select] = True"
--
Dave Hargis, Microsoft Access MVP
:
I'm trying to recycle a form, rather than copy it and just change the
data source. What I have done on an OnClick Event is the following
(which isn't working!):
Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String
Dim stQuery As String
stForm = "ProspectSearch"
stQuery = "WHERE ProspectTable.Select = -1"
DoCmd.OpenForm stForm, , , stQuery
End Sub
ProspectSearch by default shows every record until a search term is
typed into a search box. I am trying to open it to where only records
whose field [Select] is checked. I've also tried the following (which
tells me I can't assign a value to this object):
Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String
stForm = "ProspectSearch"
DoCmd.OpenForm stForm
Forms!ProspectSearch!findquery.Form.Filter = "Select = -1"
End Sub
I also tried the above with '-1'
Thanks in advance for your help!
magmike- Hide quoted text -
- Show quoted text -
That's not working for me. Please note, that what I am attempting to
filter is a subform, titled findquery, and populated by the form
findqueryFormProspects
I don't know if that makes any difference.
Something else I have tried:
DoCmd.OpenForm "ProspectSearch"
DoCmd.GoToControl "Forms!ProspectSearch!findqueryFormProspects.Form!
COMPANY"
DoCmd.ApplyFilter , "Select = True"
However, it keeps telling me that the field does not exist, but I've
checked and it does! Have I referred to it correctly?- Hide quoted text -
- Show quoted text -

I'm asking for your help on doing it right. I have changed the field
and all references to Select to SelectMe. This is the exact code I am
running:

    Docmd.OpenForm ""ProspectSearch", , , "[SelectMe] = True"

It is displaying all results instead of just those with SelectMe
checked. Keep in mind, this field is in a subform (findquery) which is
populated by the form findqueryForm. I have also tried it the
following ways as well:

        DoCmd.OpenForm "ProspectSearch", , , "[SelectMe] = Yes"
        DoCmd.OpenForm "ProspectSearch", , , "[SelectMe] = -1"
        DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = True"
        DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = Yes"
        DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = -1"
        DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = True"
        DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = Yes"
        DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = -1"

But none of these work either. I'm certain I'm doing something wrong,
because it isn't working right. Does anyone have any idea what it is I
am doing wrong?

Thanks in advance!
magmike- Hide quoted text -

- Show quoted text -

SelectMe is a Yes/No field. There is not any other filtering already
on the form. I think the problem has to do with the fact that the
field in question is in a subform. When I change the form to just open
the subform form with the criteria, it works just fine. But I would
still like to use the whole form. Am I referencing the field or
subfield wrong, or is it even possible to filter the subform in this
manner?

 

[Klatuu]

I don't know why this did not dawn on my earlier. You don't open a subform.
It is opened by the parent form. What you can do is to filter the subform

Me.SubformControl.Form.Filter = "[Select] = True"
Me.SufromControl.Form.FilterOn = True
--
Dave Hargis, Microsoft Access MVP

What data type is the field SelectMe
Don't get too frustrated, I know this works. It even describes it in the
OpenForm description in VBA Help. I also tested it on one of my forms to see
if I could recreate your problem.
Do you have any other filtering on the form or in the form's record source?
--
Dave Hargis, Microsoft Access MVP

If it is not working for you then you are not doing it quite right. It does
work.
There is one other issue. You should change the name of the field Select.
It is a reserved word in both Jet SQL and in Access. Using reserved words as
names can easily confuse Access and Jet. Select is probably then most used
work in SQL.
That may be your problem.

:
Use the Where argument of the OpenForm method to filter the records to only
those where Select is True:

Docmd.OpenForm ""ProspectSearch", , , "[Select] = True"

:
I'm trying to recycle a form, rather than copy it and just change the
data source. What I have done on an OnClick Event is the following
(which isn't working!):

Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String
Dim stQuery As String

stForm = "ProspectSearch"
stQuery = "WHERE ProspectTable.Select = -1"

DoCmd.OpenForm stForm, , , stQuery

End Sub

ProspectSearch by default shows every record until a search term is
typed into a search box. I am trying to open it to where only records
whose field [Select] is checked. I've also tried the following (which
tells me I can't assign a value to this object):

Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String

stForm = "ProspectSearch"
DoCmd.OpenForm stForm

Forms!ProspectSearch!findquery.Form.Filter = "Select = -1"

End Sub

I also tried the above with '-1'

Thanks in advance for your help!

magmike- Hide quoted text -

- Show quoted text -

That's not working for me. Please note, that what I am attempting to
filter is a subform, titled findquery, and populated by the form
findqueryFormProspects

I don't know if that makes any difference.

Something else I have tried:

DoCmd.OpenForm "ProspectSearch"
DoCmd.GoToControl "Forms!ProspectSearch!findqueryFormProspects.Form!
COMPANY"
DoCmd.ApplyFilter , "Select = True"

However, it keeps telling me that the field does not exist, but I've
checked and it does! Have I referred to it correctly?- Hide quoted text -

- Show quoted text -

I'm asking for your help on doing it right. I have changed the field
and all references to Select to SelectMe. This is the exact code I am
running:

Docmd.OpenForm ""ProspectSearch", , , "[SelectMe] = True"

It is displaying all results instead of just those with SelectMe
checked. Keep in mind, this field is in a subform (findquery) which is
populated by the form findqueryForm. I have also tried it the
following ways as well:

DoCmd.OpenForm "ProspectSearch", , , "[SelectMe] = Yes"
DoCmd.OpenForm "ProspectSearch", , , "[SelectMe] = -1"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = True"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = Yes"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = -1"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = True"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = Yes"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = -1"

But none of these work either. I'm certain I'm doing something wrong,
because it isn't working right. Does anyone have any idea what it is I
am doing wrong?

Thanks in advance!
magmike- Hide quoted text -

- Show quoted text -

SelectMe is a Yes/No field. There is not any other filtering already
on the form. I think the problem has to do with the fact that the
field in question is in a subform. When I change the form to just open
the subform form with the criteria, it works just fine. But I would
still like to use the whole form. Am I referencing the field or
subfield wrong, or is it even possible to filter the subform in this
manner?

 

[magmike]

I don't know why this did not dawn on my earlier.  You don't open a subform.  
It is opened by the parent form.  What you can do is to filter the subform

    Me.SubformControl.Form.Filter = "[Select] = True"
    Me.SufromControl.Form.FilterOn = True
--
Dave Hargis, Microsoft Access MVP

What data type is the field SelectMe
Don't get too frustrated, I know this works.  It even describes it in the
OpenForm description in VBA Help.  I also tested it on one of my forms to see
if I could recreate your problem.  
Do you have any other filtering on the form or in the form's record source?
--
Dave Hargis, Microsoft Access MVP
:
If it is not working for you then you are not doing it quite right..  It does
work.
There is one other issue.  You should change the name of the field Select.  
It is a reserved word in both Jet SQL and in Access.  Using reserved words as
names can easily confuse Access and Jet.  Select is probably then most used
work in SQL.
That may be your problem.
--
Dave Hargis, Microsoft Access MVP
:
Use the Where argument of the OpenForm method to filter the records to only
those where Select is True:
    Docmd.OpenForm ""ProspectSearch", , , "[Select] = True"
--
Dave Hargis, Microsoft Access MVP
:
I'm trying to recycle a form, rather than copy it and just change the
data source. What I have done on an OnClick Event is the following
(which isn't working!):
Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String
Dim stQuery As String
stForm = "ProspectSearch"
stQuery = "WHERE ProspectTable.Select = -1"
DoCmd.OpenForm stForm, , , stQuery
End Sub
ProspectSearch by default shows every record until a search term is
typed into a search box. I am trying to open it to where only records
whose field [Select] is checked. I've also tried the following (which
tells me I can't assign a value to this object):
Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String
stForm = "ProspectSearch"
DoCmd.OpenForm stForm
Forms!ProspectSearch!findquery.Form.Filter = "Select = -1"
End Sub
I also tried the above with '-1'
Thanks in advance for your help!
magmike- Hide quoted text -
- Show quoted text -
That's not working for me. Please note, that what I am attempting to
filter is a subform, titled findquery, and populated by the form
findqueryFormProspects
I don't know if that makes any difference.
Something else I have tried:
DoCmd.OpenForm "ProspectSearch"
DoCmd.GoToControl "Forms!ProspectSearch!findqueryFormProspects.Form!
COMPANY"
DoCmd.ApplyFilter , "Select = True"
However, it keeps telling me that the field does not exist, but I've
checked and it does! Have I referred to it correctly?- Hide quoted text -
- Show quoted text -
I'm asking for your help on doing it right. I have changed the field
and all references to Select to SelectMe. This is the exact code I am
running:
    Docmd.OpenForm ""ProspectSearch", , , "[SelectMe] = True"
It is displaying all results instead of just those with SelectMe
checked. Keep in mind, this field is in a subform (findquery) which is
populated by the form findqueryForm. I have also tried it the
following ways as well:
        DoCmd.OpenForm "ProspectSearch", , , "[SelectMe] =Yes"
        DoCmd.OpenForm "ProspectSearch", , , "[SelectMe] =-1"
        DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = True"
        DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = Yes"
        DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = -1"
        DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = True"
        DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = Yes"
        DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = -1"
But none of these work either. I'm certain I'm doing something wrong,
because it isn't working right. Does anyone have any idea what it isI
am doing wrong?
Thanks in advance!
magmike- Hide quoted text -
- Show quoted text -

SelectMe is a Yes/No field. There is not any other filtering already
on the form. I think the problem has to do with the fact that the
field in question is in a subform. When I change the form to just open
the subform form with the criteria, it works just fine. But I would
still like to use the whole form. Am I referencing the field or
subfield wrong, or is it even possible to filter the subform in this
manner?- Hide quoted text -

- Show quoted text -

So how do I do that from a command button on a different form. My goal
here, is to be able to 'recycle' the form for different purposes and
filters. How can I filter the subform from another form? When I use
the Me.findquery.Form.Filter... it looks in the form the button is in.
What I want to do, is open another form, and then filter it's subform.

Can that work?

magmike

 

[Klatuu]

That may be a bit difficult. The problem is that there is a strange thing in
Access when opening forms with subforms. What happens is the subform opens
before the main form does, and when opening a form, you don't have a way to
directly address the subform. I can think of a couple of possiblities, but
without some testing, I don't know for sure which would work.

One way would be to pass a value in the OpenArgs argument of the OpenForm
when you are opening the main form. Then use the Load event of the form to
set the subform's filter and filteron properties.

Another would be to first open the form, then set the filter and filteron
properties by addressing it as:

Forms!NameOfForm!SubformControl.Form.Filter = "[SelectMe] = True"
Forms!NameOfForm!SubformControl.Form.FilterOn = True"

You might try both to see which works. The issue with the second method
might be a timing problem. There may not be enough time between opening the
form and issuing the second command. It might error out because the form
being opened has not had enough time to fully open. If that is the case, you
might try using a timing loop to give the form time to open. You could try
something like:

Dim strStartTime As Single
Dim strTimePast As Single

strStartTime = Timer
Do Until strTimePast > strStartTime + 0.5
strTimePast = Timer
Loop

This will pause your code for 1/2 of a second. You can increase or decrease
the wait time by changing the 0.5
--
Dave Hargis, Microsoft Access MVP

I don't know why this did not dawn on my earlier. You don't open a subform.
It is opened by the parent form. What you can do is to filter the subform

Me.SubformControl.Form.Filter = "[Select] = True"
Me.SufromControl.Form.FilterOn = True
--
Dave Hargis, Microsoft Access MVP

What data type is the field SelectMe
Don't get too frustrated, I know this works. It even describes it in the
OpenForm description in VBA Help. I also tested it on one of my forms to see
if I could recreate your problem.
Do you have any other filtering on the form or in the form's record source?

:
If it is not working for you then you are not doing it quite right.. It does
work.
There is one other issue. You should change the name of the field Select.
It is a reserved word in both Jet SQL and in Access. Using reserved words as
names can easily confuse Access and Jet. Select is probably then most used
work in SQL.
That may be your problem.

:
Use the Where argument of the OpenForm method to filter the records to only
those where Select is True:

Docmd.OpenForm ""ProspectSearch", , , "[Select] = True"

:
I'm trying to recycle a form, rather than copy it and just change the
data source. What I have done on an OnClick Event is the following
(which isn't working!):

Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String
Dim stQuery As String

stForm = "ProspectSearch"
stQuery = "WHERE ProspectTable.Select = -1"

DoCmd.OpenForm stForm, , , stQuery

End Sub

ProspectSearch by default shows every record until a search term is
typed into a search box. I am trying to open it to where only records
whose field [Select] is checked. I've also tried the following (which
tells me I can't assign a value to this object):

Private Sub SeeSelected_Click(Cancel As Integer)
Dim stForm As String

stForm = "ProspectSearch"
DoCmd.OpenForm stForm

Forms!ProspectSearch!findquery.Form.Filter = "Select = -1"

End Sub

I also tried the above with '-1'

Thanks in advance for your help!

magmike- Hide quoted text -

- Show quoted text -

That's not working for me. Please note, that what I am attempting to
filter is a subform, titled findquery, and populated by the form
findqueryFormProspects

I don't know if that makes any difference.

Something else I have tried:

DoCmd.OpenForm "ProspectSearch"
DoCmd.GoToControl "Forms!ProspectSearch!findqueryFormProspects.Form!
COMPANY"
DoCmd.ApplyFilter , "Select = True"

However, it keeps telling me that the field does not exist, but I've
checked and it does! Have I referred to it correctly?- Hide quoted text -

- Show quoted text -

I'm asking for your help on doing it right. I have changed the field
and all references to Select to SelectMe. This is the exact code I am
running:

Docmd.OpenForm ""ProspectSearch", , , "[SelectMe] = True"

It is displaying all results instead of just those with SelectMe
checked. Keep in mind, this field is in a subform (findquery) which is
populated by the form findqueryForm. I have also tried it the
following ways as well:

DoCmd.OpenForm "ProspectSearch", , , "[SelectMe] = Yes"
DoCmd.OpenForm "ProspectSearch", , , "[SelectMe] = -1"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = True"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = Yes"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findquery.Form![SelectMe] = -1"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = True"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = Yes"
DoCmd.OpenForm "ProspectSearch", , , "Forms!ProspectSearch!
findqueryForm.Form![SelectMe] = -1"

But none of these work either. I'm certain I'm doing something wrong,
because it isn't working right. Does anyone have any idea what it is I
am doing wrong?

Thanks in advance!
magmike- Hide quoted text -

- Show quoted text -

SelectMe is a Yes/No field. There is not any other filtering already
on the form. I think the problem has to do with the fact that the
field in question is in a subform. When I change the form to just open
the subform form with the criteria, it works just fine. But I would
still like to use the whole form. Am I referencing the field or
subfield wrong, or is it even possible to filter the subform in this
manner?- Hide quoted text -

- Show quoted text -

So how do I do that from a command button on a different form. My goal
here, is to be able to 'recycle' the form for different purposes and
filters. How can I filter the subform from another form? When I use
the Me.findquery.Form.Filter... it looks in the form the button is in.
What I want to do, is open another form, and then filter it's subform.

Can that work?

magmike