ACCESS FEED: RE: [MS_AccessPros] Subform Query in datasheet view

Phil-

To solve the disappearing problem, you need to use my "text box on top" trick noted earlier - and that works only in a Continuous form.

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: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Phil Knowles
Sent: Friday, April 26, 2013 2:23 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Subform Query in datasheet view

Hi John

If I do that, will it solve my problem of the blanks appearing and disappearing in the sub cats field and can I be confident that sorting and filtering will work properly?

Or do I have to try it and see?

Phil

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, 26 April 2013, 13:09
Subject: RE: [MS_AccessPros] Subform Query in datasheet view

Phil-

CTRL+' works in a Continuous Form, too. Sorting and filtering is also available via right-click. Consider temporarily replacing one of the Datasheet forms with a Continuous form that looks like Datasheet view and see if anyone complains! Note also that you can "automaitcallly" replicate data values for the user by putting a bit of code in the AfterUpdate of each control you want to have this behavior:

Private Sub txtLastName_AfterUpdate()
Me.txtLastName.DefaultValue = """" & Me.txtLastName & """"
End Sub

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: Friday, April 26, 2013 1:12 PM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] Subform Query in datasheet view

Hi John

In this instance the datasheet isn't a subform.

They like using datasheet view for its inbuilt functionality in the sorting and filtering area (which they use extensively) - which is great until it starts doing unexpected things.

They also use "Ctrl ' " to copy down the values in fields from the record above.

They might create one record and then create 10 similar records where they want to replicate a lot of the fields from previous records.

There is probably some other standard functionality of datasheets that they use aswell that I just can't bring to mind

I don't tend to use continuous forms but I am guessing I would have to somehow provide this functionality myself rather than it be standard?

I am still trying to work out exactly what it is doing - it seems that the fact that one column is sorted can have an effect on the filtering of another column ? but not in any sort of consistent manner (that I can spot). weird !

As always - any further input would be very welcome (but I don't want you guys wasting your time if it is a lost cause! - if it is just let me know and I'll stop being a pest)

Am I the only one that uses datasheet view to this extent?

cheers

Phil

________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Friday, 26 April 2013, 11:01
Subject: RE: [MS_AccessPros] Subform Query in datasheet view

Phil-

I've seen the sorting and filtering options go away under certain conditions - particularly if the datasheet is a subform. I've not been able to explain it.

Why do they insist on Datasheet view? Would they know the difference if you designed a Continuous form that simply *looks* like a datasheet?

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: Friday, April 26, 2013 12:43 AM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] Subform Query in datasheet view

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 <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.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]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]