Hi John
I am afraid my users insist on using datasheet view.
I think I have to accept the way that my sub-cat field displays the values in this view.
But I have an add on problem
In datasheet view we used to be able use the drop down facility in the sub-cat column heading to sort and or filter records and this worked perfectly well. However, it now will do neither. when I try for example to filter the records that contain some text, I get no records displayed.
Any ideas what this might be?
cheers
Phil
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, 25 April 2013, 19:00
Subject: RE: [MS_AccessPros] Subform Query in datasheet view
Phil-
If you use a query with criteria dependant on the Category ID, then the rows in Datasheet or Continuous Form view won't show correctly.
One trick for Continuous forms: Include the "lookup" table in the form's Record Source query and the lookup value in the Record Source. Put a text box on top of the combo box that just covers the the "text box" part of the combo box and set its Control Source to the lookup value. Also lock the control so the user can't type in it. In the GotFocus event of the text box, move the focus to the underlying combo box so the user doesn't get confused. On rows that don't match the selected Category, the text box will display the correct value.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
________________________________
From: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Phil Knowles
Sent: Thursday, April 25, 2013 7:30 PM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] Subform Query in datasheet view
Hi John
It is filtered in the sense that it is using a query with a criteria?
Phil
________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Tuesday, 23 April 2013, 18:26
Subject: RE: [MS_AccessPros] Subform Query in datasheet view
Phil-
As long as the Row Source of the second combo is NOT filtered, it should work correctly and show all values on all rows. If you filter it, that's when the values will disappear because there's really only one copy of the control for all rows.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
From: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Phil Knowles
Sent: Tuesday, April 23, 2013 6:46 PM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] Subform Query in datasheet view
Hi John
The record source of the form is simply the table it is based on.
The row source of Category is
SELECT [category].id, [category].name from [category] order by ........
The control source is Category
The row source of Sub-cat is
SELECT [sub cat id], [sub cat name] from [sub cats query]
The control source is Sub-cats
aha - maybe the control source should be sub cats query ?
sub cats query has in its criteria field = [forms]![formname]![cat id]
cheers
Phil
________________________________
From: John Viescas <mailto:JohnV%40msn.com <mailto:JohnV%40msn.com> >
To: mailto:MS_Access_Professionals%40yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Tuesday, 23 April 2013, 17:24
Subject: RE: [MS_AccessPros] Subform Query in datasheet view
Phil-
What is the Record Source of your form, the Row Source of both combos, and
the Control Source of both combos? You shouldn't have a display problem if
the controls are bound to fields.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
From: mailto:MS_Access_Professionals%40yahoogroups.com
[mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of pdk444444
Sent: Tuesday, April 23, 2013 5:51 PM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: [MS_AccessPros] Subform Query in datasheet view
I have a Category table and a Sub-cat table which are maintained by a
form/subform arrangement.
Each Category may have 1 or more sub-cats (or none at all)
I have another table which contains fields for Category and Sub-cat each of
which are selected from a drop down list. They are selected seperately but I
want the sub-cats list to only contain the subcats that are valid for the
entered category.
I achieve this by having a query to select the sub-cats with criteria
[Forms]![Formname].[cat id].
In form view everything is fine.
I have 2 issues.
1. When the form is viewed in datasheet view (which it mostly is) I only see
the values for sub cat for those records with matching categories. The
records with other categories show as a blank field in sub-cat.
When i move to a record with a different category, the sub-cat values for
all records with that category appear and the sub-cats for all other
categorys go blank.
2. Accepting the way things are in issue 1, up until recently I have been
able to filter by sub-cat and sort by the sub-cat field in datasheet view
but now when I try to do this no records are shown.
Any help with either or both of these issues would be appreciated.
Phil
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (13) |
Tidak ada komentar:
Posting Komentar