Cannot Update or Enter New Into Form-Access 2002

I created a main form that uses 4 subforms. All of the records show up
perfectly, but I am not allowed to enter a new record OR update the existing
records in the main form and subforms. I have checked and modified the
various properties in the main form and subforms and cannot find the problem.
The properties are currently set to "Yes" for Allow Edits, Allow Deletions,
Allow Additions, and Data Entry. The Record Locks property is set to No
Locks.

I am not a programmer, but know enough about how to use the core MS Access
tool to be dangerous. Any advice you can offer is appreciated.

LynB wrote:
I created a main form that uses 4 subforms. All of the records show
up perfectly, but I am not allowed to enter a new record OR update
the existing records in the main form and subforms. I have checked
and modified the various properties in the main form and subforms and
cannot find the problem. The properties are currently set to "Yes"
for Allow Edits, Allow Deletions, Allow Additions, and Data Entry.
The Record Locks property is set to No Locks.

I am not a programmer, but know enough about how to use the core MS
Access tool to be dangerous. Any advice you can offer is appreciated.

Each of your forms should be using as their RecordSource a single table or a
basic SELECT query based on a single table. Are they? I suspect not. More
likely they are based on queries that do not produce editable result sets and
thus the forms cannot perform edits on them.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hi Rick. Thank you for replying so quickly. I did check the forms and
subforms and here is what I found. I have two forms giving me trouble. Both
of my main forms are pointing to a single table as a source. In one of the
main forms, there is one subform that is also using a single table as a
source. The other main form has 4 different subforms, each one using a query
as a source and each query is pointing to a single table (no joins). Could
the fact I don't have a primary key designated be the problem? I recall
seeing something about that when I searched the bulletin board.

"Rick Brandt" wrote:

LynB wrote:
I created a main form that uses 4 subforms. All of the records show
up perfectly, but I am not allowed to enter a new record OR update
the existing records in the main form and subforms. I have checked
and modified the various properties in the main form and subforms and
cannot find the problem. The properties are currently set to "Yes"
for Allow Edits, Allow Deletions, Allow Additions, and Data Entry.
The Record Locks property is set to No Locks.

I am not a programmer, but know enough about how to use the core MS
Access tool to be dangerous. Any advice you can offer is appreciated.

Each of your forms should be using as their RecordSource a single table or a
basic SELECT query based on a single table. Are they? I suspect not. More
likely they are based on queries that do not produce editable result sets and
thus the forms cannot perform edits on them.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

LynB wrote:
Hi Rick. Thank you for replying so quickly. I did check the forms and
subforms and here is what I found. I have two forms giving me
trouble. Both of my main forms are pointing to a single table as a
source. In one of the main forms, there is one subform that is also
using a single table as a source. The other main form has 4
different subforms, each one using a query as a source and each query
is pointing to a single table (no joins). Could the fact I don't
have a primary key designated be the problem? I recall seeing
something about that when I searched the bulletin board.

That normally only applies to ODBC links to server tables (no PK - no editing).
Standard Access/Jet tables would not care about that.

Can you edit data directly in the queries? Even if only based on one table
there are numerous things that can make a query read only.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Rick - No, I cannot edit/update or add new records directly in the queries.

"LynB" wrote:

I created a main form that uses 4 subforms. All of the records show up
perfectly, but I am not allowed to enter a new record OR update the existing
records in the main form and subforms. I have checked and modified the
various properties in the main form and subforms and cannot find the problem.
The properties are currently set to "Yes" for Allow Edits, Allow Deletions,
Allow Additions, and Data Entry. The Record Locks property is set to No
Locks.

I am not a programmer, but know enough about how to use the core MS Access
tool to be dangerous. Any advice you can offer is appreciated.

LynB wrote:
Rick - No, I cannot edit/update or add new records directly in the
queries.

Well then THAT is what needs to be corrected. Are you using DISTINCT? Do you
have VIEW - TOTALS enabled? Those will both render a query's output as read
only. Does the query include the Primary Key of the table in its output? If
it's none of those post the SQL of your queries and maybe someone can see what
is making them read only.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thanks Rick -Yes I am using distinct in the query. Otherwise I am not able
to get the results I need. Here is one of the queries (it's pretty simple):

SELECT DISTINCT [Firm Relation_T].[Firm NM], [Firm Relation_T].AMFN, [Firm
Relation_T].AMLN, [Firm Relation_T].AMPhone, [AMFN] & " " & [AMLN] AS AMFLN
FROM [Firm Relation_T];

It is pulling only some of the columns in the [Firm Relation_T] table.
There is a one to many relationship if I pull the entire table, which is why
I was using distinct.

Also, you asked if I was pulling the primary key in the query and I am not
as that table does not have a primary key. I am not sure what View-Totals is
and do not think I am using that. Thoughts?



"LynB" wrote:

I created a main form that uses 4 subforms. All of the records show up
perfectly, but I am not allowed to enter a new record OR update the existing
records in the main form and subforms. I have checked and modified the
various properties in the main form and subforms and cannot find the problem.
The properties are currently set to "Yes" for Allow Edits, Allow Deletions,
Allow Additions, and Data Entry. The Record Locks property is set to No
Locks.

I am not a programmer, but know enough about how to use the core MS Access
tool to be dangerous. Any advice you can offer is appreciated.

LynB wrote:
Thanks Rick -Yes I am using distinct in the query. Otherwise I am
not able to get the results I need. Here is one of the queries (it's
pretty simple):

SELECT DISTINCT [Firm Relation_T].[Firm NM], [Firm Relation_T].AMFN,
[Firm Relation_T].AMLN, [Firm Relation_T].AMPhone, [AMFN] & " " &
[AMLN] AS AMFLN FROM [Firm Relation_T];

It is pulling only some of the columns in the [Firm Relation_T] table.
There is a one to many relationship if I pull the entire table, which
is why I was using distinct.

Use a form/subform setup with each form bound to ONE table or query based on one
table. You cannot edit a query that uses DISTINCT period.

Also, you asked if I was pulling the primary key in the query and I
am not as that table does not have a primary key.

Well it should have.

I am not sure what View-Totals is and do not think I am using that. Thoughts?

In query design you use View - Totals so you can "Group By" some fields and
aggregate on others using Min, Max, Sum, Count, etc.. Such a query is also
never editable.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Rick -

Can't thank you enough for your help and patience. As much as I hate the
thought of redesigning some of this, I may have to. At least I know for the
future what NOT to do. Thanks again!!

Lyn

"LynB" wrote:

I created a main form that uses 4 subforms. All of the records show up
perfectly, but I am not allowed to enter a new record OR update the existing
records in the main form and subforms. I have checked and modified the
various properties in the main form and subforms and cannot find the problem.
The properties are currently set to "Yes" for Allow Edits, Allow Deletions,
Allow Additions, and Data Entry. The Record Locks property is set to No
Locks.

I am not a programmer, but know enough about how to use the core MS Access
tool to be dangerous. Any advice you can offer is appreciated.