adding records if record source is query

[Guest]

I have a form that I want to use to add new records or to update old ones.
It is a simple form with records from a single file. I want to make the form
so that a user can only see or update forms that he or she entered. When I
make the record source a query ( select submitter like currentuser), the code
I put in to enter default information comes up saying that the field cannot
be updated.

What do I need to do to make it so that the user can add new records, but
can only see existing records that he or she submitted?

 

[Allen Browne]

You can use Criteria in your query to limit it to only the records that
person created, but you cannot perform any kind of grouping or
de-duplication and still have it updatable.

Any of the following will make your query results read-only:
.. It has a GROUP BY clause (totals query).

.. It has a TRANSFORM clause (crosstab query).

.. It contains a DISTINCT predicate.

.. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause
(performs aggregation).

.. It involves a UNION.

.. It has a subquery in the SELECT clause.

.. It uses JOINs of different directions on multiple tables in the FROM
clause.

.. The query's Recordset Type property is set to Snapshot.

.. The query is based on another query that is read-only (stacked query.)

.. Your permissions are read-only (Access security.)

.. The database is opened read-only, or the file attributes are read-only, or
the database is on read-only media (e.g. CD-ROM, network drive without write
privileges.)

 

[Guest]

Thank you for the information. Unfortunately, none of the conditions that
you listed are true for my query. If I remove the query, everything works -
if I put the query back in, I can no longer update any fields. I am an
Access beginner, and this is a fairly simple form. I am not sure how to
proceed at this point.

 

[Allen Browne]

Open the query in design view.
Switch to SQL View (View menu.)
Copy the SQL statement as post it here.

 

[Guest]

This is the query - it is in the record source for the form:

SELECT [purchase requisition].submitter FROM [purchase requisition] WHERE
((([purchase requisition].submitter) Like "currentuser"));

 

[Allen Browne]

Okay, that's a very simple query.
Apparently the form has only one bound control (submitter.)
Are you trying to assign a value to a control bound to something else?

What exactly is failing? What exactly is the error message?
What are you doing at the time (or what code is executing)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This is the query - it is in the record source for the form:

SELECT [purchase requisition].submitter FROM [purchase requisition] WHERE
((([purchase requisition].submitter) Like "currentuser"));

Open the query in design view.
Switch to SQL View (View menu.)
Copy the SQL statement as post it here.

 

[Guest]

Based on your questions, I realized that there was a major thing that I
hadn't understood when I set up the query - that I had to select all the
fields, not just the one on which I wanted to match (I really am a beginner).
I couldn't update fields that were not available to me. Thank you so much
for your patience. I am sure that this is not the last question that I will
post.

Okay, that's a very simple query.
Apparently the form has only one bound control (submitter.)
Are you trying to assign a value to a control bound to something else?

What exactly is failing? What exactly is the error message?
What are you doing at the time (or what code is executing)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This is the query - it is in the record source for the form:

SELECT [purchase requisition].submitter FROM [purchase requisition] WHERE
((([purchase requisition].submitter) Like "currentuser"));

Open the query in design view.
Switch to SQL View (View menu.)
Copy the SQL statement as post it here.

Thank you for the information. Unfortunately, none of the conditions
that
you listed are true for my query. If I remove the query, everything
works -
if I put the query back in, I can no longer update any fields. I am an
Access beginner, and this is a fairly simple form. I am not sure how
to
proceed at this point.

:

You can use Criteria in your query to limit it to only the records
that
person created, but you cannot perform any kind of grouping or
de-duplication and still have it updatable.

Any of the following will make your query results read-only:
.. It has a GROUP BY clause (totals query).

.. It has a TRANSFORM clause (crosstab query).

.. It contains a DISTINCT predicate.

.. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause
(performs aggregation).

.. It involves a UNION.

.. It has a subquery in the SELECT clause.

.. It uses JOINs of different directions on multiple tables in the
FROM
clause.

.. The query's Recordset Type property is set to Snapshot.

.. The query is based on another query that is read-only (stacked
query.)

.. Your permissions are read-only (Access security.)

.. The database is opened read-only, or the file attributes are
read-only, or
the database is on read-only media (e.g. CD-ROM, network drive without
write
privileges.)

I have a form that I want to use to add new records or to update old
ones.
It is a simple form with records from a single file. I want to make
the
form
so that a user can only see or update forms that he or she entered.
When
I
make the record source a query ( select submitter like currentuser),
the
code
I put in to enter default information comes up saying that the field
cannot
be updated.

What do I need to do to make it so that the user can add new
records,
but
can only see existing records that he or she submitted?