FILTERING -- ACCESS 2010

[amywolfie]

I have a main form with Region, and a State subform on it.

When I press a button, I would like to open subfrmCounty from
subfrmState filtering for:

PK_State:: fk_State
(i.e.: pass the State key so that only Counties for that state
display)

County_FC = "FC" And
MediaType Like "*Field*"

I have tried several combinations -- using Access macro to find
specific counties and then putting the County_FC & MediaType filters
on the query that opens subfrmCounty -- Nada. Haven't been able to
work it out in VBA Jet SQL.

What is the best/easiest way to capture all criteria in a single
routine? (Macro, VBA, etc.)

Thanks!
====

 

[Access Developer]

Have you tried setting the Master Link Fields and Child Link Fields
properties of the Subform Control? That's the usual way to control what is
displayed in a Subform. I've never had any problem, however, with resetting
the SQL of a Query used as the Record Source of the Form that is displayed
in the Subform Control.

Macros have so many limitations that I always use VBA for manipulations...
(You cannot do that with Data Macros for Access web apps, but that does not
seem to be the subject here.)

 

[amywolfie]

Larry:

Yes, subfrmState is linked to master frmRegion by State PK & fk.

I am trying to open Counties linked to a State with the above 3
criteria. Can this be done in VBA?

Thanks!

Amy
====

 

[John W. Vinson]

I have a main form with Region, and a State subform on it.

When I press a button, I would like to open subfrmCounty from
subfrmState filtering for:

PK_State:: fk_State
(i.e.: pass the State key so that only Counties for that state
display)

County_FC = "FC" And
MediaType Like "*Field*"

I have tried several combinations -- using Access macro to find
specific counties and then putting the County_FC & MediaType filters
on the query that opens subfrmCounty -- Nada. Haven't been able to
work it out in VBA Jet SQL.

What is the best/easiest way to capture all criteria in a single
routine? (Macro, VBA, etc.)

Thanks!
====

How about a solution with no code at all???

Base a sub-Subform on a query with the MediaType criterion; set the Subform's
Master and Link Fields to fk_StateID.

Am I misunderstanding your setup?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[amywolfie]

John:

This is what I'm trying to get to:

======

Dim strSQL As String

strSQL = "SELECT dbo_States.State_pk, dbo_County.County_sk,
dbo_States.StateNameFull, dbo_County.FC_County, dbo_County.MediaType "
& _
"FROM dbo_County " & " INNER JOIN dbo_States ON dbo_County.State_fk =
dbo_States.State_pk " & _
"WHERE(dbo_County.FC_County ='FC') AND (dbo_County.MediaType LIKE
'*Field*')AND (dbo_States.State_pk = dbo_County.State_fk);"

DoCmd.OpenForm "subfrmCounty", acNormal, strSQL

=====


This is actually working except for the last clause. I need the
record selected on subfrmState, via State_pk, to open subfrmCounty on
State_fk. It's a one-to-many scenario. Maybe I am overcomplexifying?

Thanks!

Amy
====

 

[Access Developer]

Amy,

I think we need a bit more information about your data and what you want to
show. Forms are simply a way to display and manipulate data in Access --
the data itself resides in Tables (from the names you use, it appears you
are linking to Tables in an SQL Server database). I also don't understand
what you mean when you say you link "State pk : state fk".

And, one doesn't use DoCmd.OpenForm to show a Subform; one uses it to open a
separate form. Are you really talking about Forms embedded in a Subform
Control, or are you talking about opening a separate form.

It's possible we just have some terminology difficulties, but, with
appropriately indexed data, you can likely do what you want with minimal or
no VBA.

I'm guessing you have three Tables (or Queries) involved: (1) Regions,
whose PK is a Region ID which is Record Source for your main Form. (2)
States, whose PK is state abbreviation or State Id, and each of which has a
Foreign Key to the Region ID of the region in which the state exists. (3)
Counties, whose PK is County ID, each of which has a Foreign Key to the
State ID of the state in which the County exists.

But, I'm somewhat puzzled as to what you want to see... You seem to have a
main Form whose Record Source is Region; I think from what you've said, on
that form you have a Subform Control containing a Continuous Forms view
Form, with Link Master Fields being the Region ID and Link Child Fields
being the Foreign Key to Region ID in the State Record.

Access does not allow Subform Controls in a Continuous View Form that is
itself displayed in a Subform Control. In the normal "shorthand" language
we use -- no subforms in a continuous forms view subform.

You will need to tell us whether my guesses so far are correct, and whether
you want (1) the State subform to be in Single Form View with an embedded
Subform for Continuous Forms View of Counties, or (2) you want the Subform
for displaying Counties to be a Subform Control on the Main Form at the same
level as the Subform displaying States, or (3) you want the Counties to be
displayed in a separate Form opened from VBA when the user chooses a State
record. In case (1), what John described can be done, with no code, only
appropriate Record definition in the Tables; in case (2) I'd have to review
some DBs or test, but it may be possible to do this without any VBA code,
but a "non-trivial" link in the Link fields for the County Subform; case (3)
would require some VBA code, and it might be a bit difficult to
automatically close the separate Form when the original Form is closed.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

John:

This is what I'm trying to get to:

======

Dim strSQL As String

strSQL = "SELECT dbo_States.State_pk, dbo_County.County_sk,
dbo_States.StateNameFull, dbo_County.FC_County, dbo_County.MediaType "
& _
"FROM dbo_County " & " INNER JOIN dbo_States ON dbo_County.State_fk =
dbo_States.State_pk " & _
"WHERE(dbo_County.FC_County ='FC') AND (dbo_County.MediaType LIKE
'*Field*')AND (dbo_States.State_pk = dbo_County.State_fk);"

DoCmd.OpenForm "subfrmCounty", acNormal, strSQL

=====


This is actually working except for the last clause. I need the
record selected on subfrmState, via State_pk, to open subfrmCounty on
State_fk. It's a one-to-many scenario. Maybe I am overcomplexifying?

Thanks!

Amy
====

 

[amywolfie]

Amy,

I think we need a bit more information about your data and what you want to
show.  Forms are simply a way to display and manipulate data in Access --  
the data itself resides in Tables (from the names you use, it appears you
are linking to Tables in an SQL Server database).  I also don't understand
what you mean when you say you link "State pk : state fk".

And, one doesn't use DoCmd.OpenForm to show a Subform; one uses it to open a
separate form.  Are you really talking about Forms embedded in a Subform
Control, or are you talking about opening a separate form.

It's possible we just have some terminology difficulties, but, with
appropriately indexed data, you can likely do what you want with minimal or
no VBA.

I'm guessing you have three Tables (or Queries) involved:  (1) Regions,
whose PK is a Region ID which is Record Source for your main Form.  (2)
States, whose PK is state abbreviation or State Id, and each of which hasa
Foreign Key to the Region ID of the region in which the state exists. (3)
Counties, whose PK is County ID, each of which has a Foreign Key to the
State ID of the state in which the County exists.

But, I'm somewhat puzzled as to what you want to see... You seem to have a
main Form whose Record Source is Region; I think from what you've said, on
that form you have a Subform Control containing a Continuous Forms view
Form, with Link Master Fields being the Region ID and Link Child Fields
being the Foreign Key to Region ID in the State Record.

Access does not allow Subform Controls in a Continuous View Form that is
itself displayed in a Subform Control.  In the normal "shorthand" language
we use -- no subforms in a continuous forms view subform.

You will need to tell us whether my guesses so far are correct, and whether
you want (1) the State subform to be in Single Form View with an embedded
Subform for Continuous Forms View of Counties, or (2) you want the Subform
for displaying Counties to be a Subform Control on the Main Form at the same
level as the Subform displaying States, or (3) you want the Counties to be
displayed in a separate Form opened from VBA when the user chooses a State
record.  In case (1), what John described can be done, with no code, only
appropriate Record definition in the Tables; in case (2) I'd have to review
some DBs or test, but it may be possible to do this without any VBA code,
but a "non-trivial" link in the Link fields for the County Subform; case (3)
would require some VBA code, and it might be a bit difficult to
automatically close the separate Form when the original Form is closed.

--
 Larry Linson, Microsoft Office Access MVP
 Co-author: "Microsoft Access Small Business Solutions", published by Wiley
 Access newsgroup support is alive and well in USENET
comp.databases.ms-access


John:

This is what I'm trying to get to:

======

Dim strSQL As String

strSQL = "SELECT dbo_States.State_pk, dbo_County.County_sk,
dbo_States.StateNameFull, dbo_County.FC_County, dbo_County.MediaType "
& _
"FROM dbo_County " & " INNER JOIN dbo_States ON dbo_County.State_fk =
dbo_States.State_pk " & _
"WHERE(dbo_County.FC_County ='FC') AND (dbo_County.MediaType LIKE
'*Field*')AND (dbo_States.State_pk = dbo_County.State_fk);"

DoCmd.OpenForm "subfrmCounty", acNormal, strSQL

=====

This is actually working except for the last clause.  I need the
record selected on subfrmState, via State_pk, to open subfrmCounty on
State_fk.  It's a one-to-many scenario.  Maybe I am overcomplexifying?

Thanks!

Amy
====







- Show quoted text -

Larry:

There is a main form, frmRegion.

There is a subform on this, subfrmStates, linked relationally by
Region_PK in Region to Region_fk in State.

The State subform is displaying continuous forms for all of the states
linked to a given region.

On the State subform is a button by each State line item, "Counties"

When this button is pressed, I need 3 filters to happen:

1) When frm_County opens, it displays Counties linked to the State
line item in the subfrmState
2) The field FC_County = "FC"
3) The field MediaType Like "*Field*"

What is the best way to accomplish this?

Thanks.
====

 

[Access Developer]

There is a main form, frmRegion.

There is a subform on this, subfrmStates, linked
relationally by Region_PK in Region to Region_fk in State.

The State subform is displaying continuous forms for
all of the states linked to a given region.

On the State subform is a button by each State line
item, "Counties"

When this button is pressed, I need 3 filters to happen:

1) When frm_County opens, it displays Counties
linked to the State line item in the subfrmState
2) The field FC_County = "FC"
3) The field MediaType Like "*Field*"

What is the best way to accomplish this?

(1a) What field in the States table contains the State identification?
(1b) What is the name of the Control, if any, in the Form displayed in
subfrmStates that contains the state identification?
(1b) What field in the Counties table contains the state identification?

(2) Is field FC_County in the Counties table?

(3) Is field MediaType in the Counties table?

(4) Are these conditions ANDed together? That is, do you want to show only
those counties in the state which have the characters "FC" in their
FC_County Field and which also have a field Media Type which somewhere
includes the characters "Field"?

 

[amywolfie]

 > There is a main form, frmRegion.

 > There is a subform on this, subfrmStates, linked
 > relationally by Region_PK in Region to Region_fk in State.

 > The State subform is displaying continuous forms for
 > all of the states linked to a given region.

 > On the State subform is a button by each State line
 > item, "Counties"

 > When this button is pressed, I need 3 filters to happen:

 > 1) When frm_County opens, it displays Counties
 > linked to the State line item in the subfrmState
 > 2) The field FC_County = "FC"
 > 3) The field MediaType Like "*Field*"

 > What is the best way to accomplish this?

 (1a) What field in the States table contains the State identification?State_pk
 (1b) What is the name of the Control, if any, in the Form displayed in
subfrmStates that contains the state identification? State_pk
 (1b) What field in the Counties table contains the state identification? State_fk

 (2) Is field FC_County in the Counties table? YES

 (3) Is field MediaType in the Counties table? YES

 (4) Are these conditions ANDed together?  That is, do you want to show only
those counties in the state which have the characters "FC" in their
FC_County Field and which also have a field Media Type which somewhere
includes the characters "Field"? This would be an "OR" statement in terms of "FC" or "*Field*"
Thanks.
=====

 --
 Larry Linson, Microsoft Office Access MVP
 Co-author: "Microsoft Access Small Business Solutions", published by Wiley
 Access newsgroup support is alive and well in USENET
comp.databases.ms-access

 

[Access Developer]

I did a small sample using the names you gave me and the Click Event code
that seems to work OK for showing counties in a separate form is:

Private Sub cmdShowCounties_Click()
On Error GoTo Err_cmdShowCounties_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCountiesList"

stLinkCriteria = "[State_fk]=" & "'" & Me![State_pk] & "' AND
([FC_County] = 'FC' OR [MediaType] Like '*Field*')"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdShowCounties_Click:
Exit Sub

Err_cmdShowCounties_Click:
MsgBox Err.Description
Resume Exit_cmdShowCounties_Click

End Sub

 

[amywolfie]

I did a small sample using the names you gave me and the Click Event code
that seems to work OK for showing counties in a separate form is:

Private Sub cmdShowCounties_Click()
On Error GoTo Err_cmdShowCounties_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmCountiesList"

    stLinkCriteria = "[State_fk]=" & "'" & Me![State_pk] & "' AND
([FC_County] = 'FC' OR [MediaType] Like '*Field*')"

    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdShowCounties_Click:
    Exit Sub

Err_cmdShowCounties_Click:
    MsgBox Err.Description
    Resume Exit_cmdShowCounties_Click

End Sub

--
 Larry Linson, Microsoft Office Access MVP
 Co-author: "Microsoft Access Small Business Solutions", published by Wiley
 Access newsgroup support is alive and well in USENET
comp.databases.ms-access

(1a) What field in the States table contains the State identification?
State_pk
(1b) What is the name of the Control, if any, in the Form displayed in
subfrmStates that contains the state identification? State_pk
(1b) What field in the Counties table contains the state identification?
State_fk

(2) Is field FC_County in the Counties table?  YES

(3) Is field MediaType in the Counties table?    YES

(4) Are these conditions ANDed together? That is, do you want to show only
those counties in the state which have the characters "FC" in their
FC_County Field and which also have a field Media Type which somewhere
includes the characters "Field"?  This would be an "OR" statement in terms
of "FC" or "*Field*"
Thanks.
=====





--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Larry:

When I run this, I get this error:

"Data type mismatch in criteria expression"

=======

State_Pk is an AutoNumber and is set as a Primary Key (coming from a
SQL table)

State_fk is a number also coming from a SQL table

They are both Long Integers

Thanks!

Amy
====

 

[David-W-Fenton]

Access does not allow Subform Controls in a Continuous View Form
that is itself displayed in a Subform Control. In the normal
"shorthand" language we use -- no subforms in a continuous forms
view subform.

This limitation can be gotten around (in terms of getting the data
displayed) by using subdatasheets.

 

[amywolfie]

This limitation can be gotten around (in terms of getting the data
displayed) by using subdatasheets.

That's a good tip! Thanks David.
====

 

[Access Developer]

What are the field types of your Fields that are used in the Criteria
expression? Are you sure you copied the quotes correctly? Some are single
quote characters (') and some are regular double quote characters (").

My assumption was that all the fields in the Criteria are field type Text.
If you used, say, an Autonumber for State_pk and a Long Integer for
State_fk, then the line that reads

stLinkCriteria = "[State_fk]=" & "'" & Me![State_pk] & "' AND ([FC_County]
= 'FC' OR [MediaType] Like '*Field*')"

stLinkCriteria = "[State_fk]= " & Me![State_pk] & " AND ([FC_County] =
'FC' OR [MediaType] Like '*Field*')"

<CAUTION: air code, untested>

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

I did a small sample using the names you gave me and the Click Event code
that seems to work OK for showing counties in a separate form is:

Private Sub cmdShowCounties_Click()
On Error GoTo Err_cmdShowCounties_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCountiesList"

stLinkCriteria = "[State_fk]=" & "'" & Me![State_pk] & "' AND
([FC_County] = 'FC' OR [MediaType] Like '*Field*')"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdShowCounties_Click:
Exit Sub

Err_cmdShowCounties_Click:
MsgBox Err.Description
Resume Exit_cmdShowCounties_Click

End Sub

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

(1a) What field in the States table contains the State identification?
State_pk
(1b) What is the name of the Control, if any, in the Form displayed in
subfrmStates that contains the state identification? State_pk
(1b) What field in the Counties table contains the state identification?
State_fk

(2) Is field FC_County in the Counties table? YES

(3) Is field MediaType in the Counties table? YES

(4) Are these conditions ANDed together? That is, do you want to show
only
those counties in the state which have the characters "FC" in their
FC_County Field and which also have a field Media Type which somewhere
includes the characters "Field"? This would be an "OR" statement in
terms
of "FC" or "*Field*"
Thanks.
=====





--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by
Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Larry:

When I run this, I get this error:

"Data type mismatch in criteria expression"

=======

State_Pk is an AutoNumber and is set as a Primary Key (coming from a
SQL table)

State_fk is a number also coming from a SQL table

They are both Long Integers

Thanks!

Amy
====

 

[Access Developer]

in message

This limitation can be gotten around (in terms of getting the data
displayed) by using subdatasheets.

<AWK! KOFF! KOFF! CHOKE!> Subdatasheets are "mean and evil through and
through" -- hierarchical database, not relational database. IMNSHO, they
fall in the same category as "Lookup Fields" and "Complex Data". My comment
about "Complex Data" is that those would be called "Multi-Value Fields" but
for the fact that the 'Softies realized the negative connotations in the
relational database community, those would be called "Multi-value Fields" --
illegitimate offspring of the forced union of Access with SharePoint.

 

[David-W-Fenton]

in message


<AWK! KOFF! KOFF! CHOKE!> Subdatasheets are "mean and evil through
and
through" -- hierarchical database, not relational database.
IMNSHO, they fall in the same category as "Lookup Fields" and
"Complex Data". My comment about "Complex Data" is that those
would be called "Multi-Value Fields" but for the fact that the
'Softies realized the negative connotations in the relational
database community, those would be called "Multi-value Fields" --
illegitimate offspring of the forced union of Access with
SharePoint.

I don't understand your dislike of subdatasheets. Perhaps you're
letting the performance problem of default subdatasheets in table
datasheets color your feelings about the feature as a whole, but I
use them in FORMS, not in table datasheets, and they are quite a
rich UI component:

http://dfenton.com/DFA/examples/Subdatasheets.png

A subdatasheet is just a subform, and that's all. If you think of it
that way and use it in that way, it becomes a very powerful feature,
and there is nothing here that violates database principles.

Just like lookup fields, if you use the feature in the right
location, it's very powerful. If you use it wrong, it causes
problems. Hating on the feature because it can be used wrong is
throwing out the baby with the bathwater.

 

[Access Developer]

OK, you've piqued my interest. I'll take a closer look at how you're using
subdatasheets in Forms but not in Tables.

OTOH, I can't think of a "right location" for using Lookup Fields (instead
of Lookup Tables).

 

[David-W-Fenton]

OTOH, I can't think of a "right location" for using Lookup Fields
(instead of Lookup Tables).

I use them all the time in queries I design for data cleanup
operations. Most of those are one-time use, but some of them end up
as the "maintenance/data cleanup" portion of existing apps.
Sometimes they are in forms, not queries, so in that case, it's just
a combo box.

That's the point really -- a lookup is a combo box in a form object,
is't just that one of those form objects (a table datasheet) is not
a valid place to store the DEFINITION of the combo box.

 

[Access Developer]

In my definition a "lookup field" as opposed to a "lookup table" or a
"lookup" is the recently-introduced field-type for tables, and is, as I have
said before, "inherently evil". The evidence of that is the many newsgroup
posts in which someone is working on a database they inherited, or created
long enough ago to forget, and are stymied by what turns out to be a "lookup
field" in one of the table definitions. The reason is that they obscure what
is actually stored (and will be retrieved by a query) by showing something
else when you look at the data in table view.

If you are saying that "lookups" (e.g. listboxes, combo boxes) and lookup
tables where it is clear that one table definition contains a foreign key to
another table are perfectly OK, then I am in agreement and have never argued
otherwise.

 

[amywolfie]

The discussion of subdatasheets is fascinating, but is anyone able to
address my question?
I really need this functionality!

**Larry -- any idea why I'm getting the "Data type mismatch in
criteria expression"
when I run the code that you supplied?

Thanks,

Amy
====