Re: Non Updateable Recordset -- OOOOPS
From: JP (jnospamperelst_at_optnospamonline.net)
Date: 03/07/05
- Next message: Tom Ross: "Re: printing headers/footers in every page"
- Previous message: Ken Snell [MVP]: "Re: Non Updateable Recordset"
- In reply to: Howard: "Non Updateable Recordset"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 7 Mar 2005 13:02:18 -0500
Sorry, I just noticed that your SQL includes some functions that modify the
data that's in one or more of the tables (e.g., the Left$'s)
That too would make the resulting recordset non-updateable since there's no
way for Access (or SQL) to accept changes to a field that is the result of a
function.
To be updateable, all output from the query(ies) must be the original fields
without modification, calculation, concatenation, etc.
"Howard" <Howard@discussions.microsoft.com> wrote in message
news:6C317CA5-56A0-44BB-AE30-401188227EA4@microsoft.com...
> Hi!
> I have a form bound to a query which uses three tables; "business",
> "Contact", and
> "Industry". The business table has a business ID field. The Contact table
> has a Business ID field as a relationship to the business table. The
business
> table has an industry code field which joins to an industry code field in
the
> industry table. The query has fields in all three files to be able display
> the business data, the contact information, and the industry information.
If
> I run the query, I cannot change any data as it informs me that the
recordset
> is not updateable. Of course the form I have bound to this query is also
not
> updateable. I've done much stuff like this before. Nothing fancy. What
could
> be the problem. I now included the SQL code for the query. I thought it
might
> be helpful.
>
>
> SELECT Businesses.[Postal Code], Businesses.[Business Name],
> Businesses.[Business Activity], NAICIndustries_New.NAICINDUSTRY,
> NAICIndustries_New.NAICCODE, Businesses.EmployeeRange,
Businesses.[Business
> ID], Businesses.[Mailing Unit], Businesses.[Street Number],
> Businesses.[Street Name], Businesses.[Street Type], Businesses.[Street
> Direction], Businesses.[Address Line 1], Businesses.[Address Line 2],
> Left$([Address Line 2],13) AS City1, Left$([Address Line 2],10) AS City2,
> Left$([Address Line 2],9) AS City3, Left$([Address Line 2],7) AS City4,
> Left$([Address Line 2],5) AS City5, Businesses.Community,
> Businesses.Municipality, Businesses.Province, Businesses.[Mailing
Address],
> Businesses.[Mailing Postal Code], Businesses.[Last Update],
Contacts.Initial,
> Contacts.[Last Name], Contacts.Phone, Contacts.Ext,
> NAICIndustries_New.INDUSTRYCAPTION
> FROM (Businesses LEFT JOIN Contacts ON Businesses.[Business ID] =
> Contacts.[Business ID]) INNER JOIN NAICIndustries_New ON
Businesses.[Primary
> NAIC1] = NAICIndustries_New.NAICINDUSTRY
> WHERE (((Businesses.EmployeeRange)="10-19" Or
> (Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99")
AND
> ((Left$([Address Line 2],13))="Richmond Hill")) OR
> (((Businesses.EmployeeRange)="10-19" Or (Businesses.EmployeeRange)="20-49"
Or
> (Businesses.EmployeeRange)="50-99") AND ((Left$([Address Line
> 2],10))="Woodbridge")) OR (((Businesses.EmployeeRange)="10-19" Or
> (Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99")
AND
> ((Left$([Address Line 2],9))="Thornhill")) OR
> (((Businesses.EmployeeRange)="10-19" Or (Businesses.EmployeeRange)="20-49"
Or
> (Businesses.EmployeeRange)="50-99") AND ((Left$([Address Line
> 2],7))="Concord")) OR (((Businesses.EmployeeRange)="10-19" Or
> (Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99")
AND
> ((Left$([Address Line 2],5))="Maple"))
> ORDER BY Businesses.[Postal Code];
>
> --
> Thanks!
> -Howard
- Next message: Tom Ross: "Re: printing headers/footers in every page"
- Previous message: Ken Snell [MVP]: "Re: Non Updateable Recordset"
- In reply to: Howard: "Non Updateable Recordset"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
