ACCESS FEED: [MS_AccessPros] Re: Recordset is not updatable

Kamis, 05 Juli 2012

[MS_AccessPros] Re: Recordset is not updatable

John,

You are correct. The query joins 2 tables to retrieve the resultant rows. I guess I will have to code 2 separate queries to get the row I need to display.

Thanks again for your very informative notes.

Thomas

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Thomas-
>
>
>
> In general, not updateable queries include:
>
>
>
> 1) Totals queries (GROUP BY, HAVING)
>
> 2) Queries that include a Totals query as a subquery
>
> 3) Any query using DISTINCT
>
> 4) Any query involving more than one table and one or more aren't JOINed
>
> 5) Any query that JOINs tables where the join key fields aren't unique it at
> least one of the two tables
>
> 6) Any query joining tables that don't follow a "drill-down" pattern
>
> For example, A -> B -> C may be updatable whereas A -> B <- C is not
>
> 7) Any table using JOINs on calculated fields
>
>
>
> There are probably some other rules, but those are the worst offenders.
>
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> <http://www.viescas.com/> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Thomas
> Sent: Thursday, July 05, 2012 3:48 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Recordset is not updatable
>
>
>
>
>
>
> John,
> That's interesting. I didn't realize there are restrictions on select statements
> which cause the record set to be not updatable,
>
> where can I find more info on the restrictions?
>
> Thanks
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> wrote:
> >
> > Thomas-
> >
> >
> >
> > Curious why you're not just doing:
> >
> >
> >
> > Me.RecordSource = strSQLSelect
> >
> >
> >
> > What I want to see is what's in strSQLSelect - the SQL defined there must be
> > resulting in a query that's not updateable.
> >
> >
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > <http://www.viescas.com/> http://www.viescas.com/
> >
> > (Paris, France)
> >
> >
> >
> >
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Thomas
> > Sent: Thursday, July 05, 2012 7:45 AM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: Re: [MS_AccessPros] Recordset is not updatable
> >
> >
> >
> >
> >
> >
> > John,
> > The Record source of the form is blank - nothing.
> >
> > The Event procedure for the 'On Open'
> >
> > Set db = currentdb()
> > Set ts = db.Openrecordset(strSQLSelect)
> > Me.Form.Recordsource = strSQLSelect
> >
> > Thomas
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> > wrote:
> > >
> > > Thomas-
> > >
> > >
> > >
> > > It would help to see the Record Source of the form (the SQL if a query) and
> > the
> > > code that's applying the filter.
> > >
> > >
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > <http://www.viescas.com/> http://www.viescas.com/
> > >
> > > (Paris, France)
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Thomas
> > > Sent: Wednesday, July 04, 2012 6:45 AM
> > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: [MS_AccessPros] Recordset is not updatable
> > >
> > >
> > >
> > >
> > >
> > > The form shows this message when I attempt to update a field.
> > >
> > > In the 'Open' event, the VBA retrieves records based on criteria defined in
> > the
> > > 'where' clause.
> > >
> > > The form displays the record(s) retrieve. However, one of the field was
> > defined
> > > as a combo box which the user is able to select a value from the drop down.
> > >
> > > Any ideas how I can make it updatable?
> > >
> > > Thanks,
> > > Thomas
> > >
> > >
> > >
> > >
> > >
> > > [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]
>