Cant enter data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Appache - 10 Nov 2006 16:44 GMT

I have a form which is based on a query. Even though fields are not locked or
disabled, when I try to enter data it doesnt allow me to do that.. Do you
have any idea why?

Signature

Thanks

Reply to this Message

Dirk Goldgar - 10 Nov 2006 16:54 GMT

> I have a form which is based on a query. Even though fields are not
> locked or disabled, when I try to enter data it doesnt allow me to do
> that.. Do you have any idea why?

I'm assuming that the form doesn't have its AllowEdits and
AllowAdditions properties set to No.

Check to see if the query itself is updatable. Most simple queries are,
but it's possible for a query to be non-updatable. Most common causes
are GROUP BY or DISTINCT clauses in the SQL (grouping or UniqueValues
property set in query design view), and queries involving three tables
in a many-to-one-to-many relationship. There's a help topic on
troubleshooting queries that might help. If your problem is a
many-to-one-to-many relationship, you may be able to make the form
updatable by specifying the form's RecordsetType property as "Dynaset
(Inconsistent Updates)".

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Appache - 13 Nov 2006 17:25 GMT

I am using GROUP BY in the query. Do you mean it can cause that problem?? If
so how can I solve the problem? I have to use GROUP BY.

Signature

Thanks

> > I have a form which is based on a query. Even though fields are not
> > locked or disabled, when I try to enter data it doesnt allow me to do
[quoted text clipped - 12 lines]
> updatable by specifying the form's RecordsetType property as "Dynaset
> (Inconsistent Updates)".

Reply to this Message

Douglas J. Steele - 13 Nov 2006 17:29 GMT

Any query that uses GROUP BY is automatically not updatable. Since a single
row summarizes data from mutliple rows, how would Access (or any other DBMS)
know what you actually wanted updated if you made a change to a value?

Perhaps if you explained why you need to use GROUP BY, we can offer an
alternative solution.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I am using GROUP BY in the query. Do you mean it can cause that problem??
>If
[quoted text clipped - 16 lines]
>> updatable by specifying the form's RecordsetType property as "Dynaset
>> (Inconsistent Updates)".

Reply to this Message

Appache - 13 Nov 2006 19:02 GMT

Ok changed my mind and I am not using Group By but it stil doesnt allow me to
update records. I am going to tell you what I have. First of all I used make
table query and created local tables from the server tables which have more
than 200 thousand records. Anyway I reduced each of them less than 1000 by
qualifying them. Because of some tables have repeating records, Access doesnt
allow me to set up a primary key for some tables (not all of them). I am not
using Group By statement but still I dont understand why it doesnt let me
enter data?

Signature

Thanks

> Any query that uses GROUP BY is automatically not updatable. Since a single
> row summarizes data from mutliple rows, how would Access (or any other DBMS)
[quoted text clipped - 23 lines]
> >> updatable by specifying the form's RecordsetType property as "Dynaset
> >> (Inconsistent Updates)".

Reply to this Message

Douglas J. Steele - 13 Nov 2006 20:29 GMT

So what is the SQL for the query you're using for the RecordSource of the
form?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Ok changed my mind and I am not using Group By but it stil doesnt allow me
> to
[quoted text clipped - 42 lines]
>> >> updatable by specifying the form's RecordsetType property as "Dynaset
>> >> (Inconsistent Updates)".

Reply to this Message

Appache - 14 Nov 2006 14:40 GMT

SELECT Inquires.PIDM, Inquires.ID, Inquires.HowtoContact, Inquires.Company,
Inquires.Phone, Inquires.Extension, Inquires.[ID/Status],
Inquires.ReferredBy, Inquires.MobilePhone, Inquires.WorkEmail,
Inquires.AttendedOpenHouse, Inquires.AttendedBarneyOpenHouse,
Inquires.ApptwithAdvisor, Inquires.LastResults, Inquires.LastReach,
Inquires.LastAttempt, Inquires.LastMeeting, Inquires.UndergraduateSchool,
Inquires.Major, Inquires.GPA, Inquires.YearGraduated,
Inquires.WorkExperience, Inquires.PendingDocuments, Inquires.Received,
Inquires.RecordCreator, Inquires.RecordMgr, Inquires.CreateDate,
Inquires.LetterDate, Inquires.Comments, LocalGoremal.LastOfGOREMAL_EMAL_CODE,
LocalGoremal.LastOfGOREMAL_EMAIL_ADDRESS,
LocalSaradapp.MinOfSARADAP_TERM_CODE_ENTRY,
LocalSaradapp.LastOfSARADAP_APPL_DATE, LocalSpbpers.SPBPERS_NAME_PREFIX,
LocalSpbpers.SPBPERS_BIRTH_DATE, LocalSpbpers.SPBPERS_CITZ_IND,
LocalSpbpers.SFBSTDN_DEGC_CODE, LocalSpraddr.LastOfSPRADDR_STREET_LINE1,
LocalSpraddr.LastOfSPRADDR_CITY, LocalSpraddr.LastOfSPRADDR_STAT_CODE,
LocalSpraddr.LastOfSPRADDR_ZIP, LocalSpraddr.LastOfSPRADDR_CNTY_CODE,
LocalSpriden.LastOfSPRIDEN_ID, LocalSpriden.LastOfSPRIDEN_LAST_NAME,
LocalSpriden.LastOfSPRIDEN_FIRST_NAME, LocalSprtele.LastOfSPRTELE_PHONE_AREA,
LocalSprtele.LastOfSPRTELE_PHONE_NUMBER
FROM (((((Inquires INNER JOIN LocalSaradapp ON Inquires.PIDM =
LocalSaradapp.SARADAP_PIDM) INNER JOIN LocalSpbpers ON Inquires.PIDM =
LocalSpbpers.SPBPERS_PIDM) INNER JOIN LocalSpraddr ON Inquires.PIDM =
LocalSpraddr.SPRADDR_PIDM) INNER JOIN LocalSpriden ON Inquires.PIDM =
LocalSpriden.SPRIDEN_PIDM) INNER JOIN LocalSprtele ON Inquires.PIDM =
LocalSprtele.SPRTELE_PIDM) INNER JOIN LocalGoremal ON Inquires.PIDM =
LocalGoremal.GOREMAL_PIDM;

Signature

Thanks

> So what is the SQL for the query you're using for the RecordSource of the
> form?
[quoted text clipped - 45 lines]
> >> >> updatable by specifying the form's RecordsetType property as "Dynaset
> >> >> (Inconsistent Updates)".

Reply to this Message

Douglas J. Steele - 14 Nov 2006 14:50 GMT

See whether this page helps:

http://msdn2.microsoft.com/en-us/library/aa198446(office.10).aspx

(Don't worry that it says Access 2002: it applies to all versions)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> SELECT Inquires.PIDM, Inquires.ID, Inquires.HowtoContact,
> Inquires.Company,
[quoted text clipped - 86 lines]
>> >> >> "Dynaset
>> >> >> (Inconsistent Updates)".

Reply to this Message

Klatuu - 10 Nov 2006 16:55 GMT

If your query is based on more that one table, this can happen. The first
cure is to be sure that all the fields used for joining the tables are
indexed. This can cause that to happen.

> I have a form which is based on a query. Even though fields are not locked or
> disabled, when I try to enter data it doesnt allow me to do that.. Do you
> have any idea why?

Reply to this Message

Appache - 10 Nov 2006 17:11 GMT

Query based on 8 tables and I indexed all the fields of joining table but it
didnt work..Any other idea?

Signature

Thanks

> If your query is based on more that one table, this can happen. The first
> cure is to be sure that all the fields used for joining the tables are
[quoted text clipped - 3 lines]
> > disabled, when I try to enter data it doesnt allow me to do that.. Do you
> > have any idea why?

Reply to this Message

John Spencer - 10 Nov 2006 21:18 GMT

That many tables in one query will almost surely not be updatable.
You can try the following but I think it will probably not work for you
-- make sure the primary key fields from every table is included in the list
of fields

Usually, if you have that many tables involved you would be using sub forms
and comboboxes on the main form for the fields in the subordinate tables.

> Query based on 8 tables and I indexed all the fields of joining table but
> it
[quoted text clipped - 10 lines]
>> > you
>> > have any idea why?

Reply to this Message