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]
Tidak ada komentar:
Posting Komentar