Memo fields in forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field build in a form called comments that I want to be able to type
into. I have looked at the Northwinds database and formatted my comments
field to the same specs as in the Employees Notes field. I hear a beep when I
try to type into the Comments field in my form. Any ideas or suggestions?
Thanks in advance.
 
Is the control bound to a field in the form's Record Source? What is the
Data Type of that field in the table's design view? What is the Format of
that field?

Can you type data into any other controls on that form?
 
The form is built off of a table. The format of the Comment field is MEMO.
THe field is bound to the Comments field on the table and in the form. As
previously stated, I went into the sample database Northwind and looked at
the Employees form and table to make sure that my Comments field matched in
settings. it does but still doesnt allow for information to be added.
 
Can you type data into any other controls on that form? Is the form's
RecordSource updatable? Assuming that it's a query, does the query allow
editing of data or adding of new records? If not, then you won't be able to
add data via the form.

Is the form's AllowEdits property set to Yes?
--

Ken Snell
<MS ACCESS MVP>
 
I checked the form properties. It says that it allows deletions and additions
but...the add new records button is grayed out. It is based on a query from 3
tables but I did not change the query to disallow new records- don't know
where I would have made that choice. Could you guide me?
Thanks for any and all help.
 
Sounds as if the query is a nonupdatable one. There can be many reasons for
this, but in general it results from Jet not being able to identify a unique
record -- often, this can be fixed if you include the primary key of each
table in the query's output fields.

Otherwise, post the SQL statement that you're using...that may give us a
clue.
 
Here is the SQL from the query used to build the form:

SELECT DISTINCT Courses.[Course Title], [Student in Courses].[First Name],
[Student in Courses].[Last Name], Teachers.[Teacher Name], [Student in
Courses].[Course Status], [Student in Courses].Comments
FROM Teachers INNER JOIN (Courses INNER JOIN [Student in Courses] ON
(Courses.ID = [Student in Courses].ID) AND (Courses.[Section ID] = [Student
in Courses].[Section ID]) AND (Courses.[Teacher ID] = [Student in
Courses].[Teacher ID])) ON (Teachers.ID = Courses.[Teacher ID]) AND
(Teachers.ID = [Student in Courses].[Teacher ID])
WHERE (((Courses.[Course Title])<>"Homeroom") AND ((Teachers.[Teacher
Name])=[Enter Teacher Name:]))
ORDER BY Courses.[Course Title], [Student in Courses].[Last Name];

Hope this is helpful to you in finding the glitch to make the comment field
editable.
Thanks for all your help.
 
"Work" as meaning the query will cease to be nonupdatable because the word
DISTINCT is in the query, yes.

No promises that there aren't other things that might not "work" < g >.

--

Ken Snell
<MS ACCESS MVP>


mrsr84 said:
So if I understand correctly, remove the word DISTINCT and it will work?

Ken Snell said:
Rick is 100% correct.

--

Ken Snell
<MS ACCESS MVP>

Rick Brandt said:
mrsr84 wrote:
Here is the SQL from the query used to build the form:

SELECT DISTINCT [snip]

There you go. DISTINCT will do that.
Click to expand...
Click to expand...
Click to expand...
 
I removed the word DISTINCT from the query but still cannot add text to the
memo field. Any thoughts?


Ken Snell said:
"Work" as meaning the query will cease to be nonupdatable because the word
DISTINCT is in the query, yes.

No promises that there aren't other things that might not "work" < g >.

--

Ken Snell
<MS ACCESS MVP>


mrsr84 said:
So if I understand correctly, remove the word DISTINCT and it will work?

Ken Snell said:
Rick is 100% correct.

--

Ken Snell
<MS ACCESS MVP>

mrsr84 wrote:
Here is the SQL from the query used to build the form:

SELECT DISTINCT [snip]

There you go. DISTINCT will do that.
Click to expand...
Click to expand...
Click to expand...
 
Repost the query's SQL statement.... your previous message is no longer in
my newsreader.

--

Ken Snell
<MS ACCESS MVP>

mrsr84 said:
I removed the word DISTINCT from the query but still cannot add text to the
memo field. Any thoughts?


Ken Snell said:
"Work" as meaning the query will cease to be nonupdatable because the
word
DISTINCT is in the query, yes.

No promises that there aren't other things that might not "work" < g >.

--

Ken Snell
<MS ACCESS MVP>


mrsr84 said:
So if I understand correctly, remove the word DISTINCT and it will
work?

:

Rick is 100% correct.

--

Ken Snell
<MS ACCESS MVP>

mrsr84 wrote:
Here is the SQL from the query used to build the form:

SELECT DISTINCT [snip]

There you go. DISTINCT will do that.
Click to expand...
Click to expand...
Click to expand...
 
Here is the SQL of the query for Courses all year

SELECT DISTINCT Courses.[Course Title], [Student in Courses].[First Name],
[Student in Courses].[Last Name], Teachers.[Teacher Name], [Student in
Courses].[Course Status], [Student in Courses].Comments
FROM Teachers INNER JOIN (Courses INNER JOIN [Student in Courses] ON
(Courses.ID = [Student in Courses].ID) AND (Courses.[Section ID] = [Student
in Courses].[Section ID]) AND (Courses.[Teacher ID] = [Student in
Courses].[Teacher ID])) ON (Teachers.ID = Courses.[Teacher ID]) AND
(Teachers.ID = [Student in Courses].[Teacher ID])
WHERE (((Courses.[Course Title])<>"Homeroom") AND ((Teachers.[Teacher
Name])=[Enter Teacher Name:]))
ORDER BY Courses.[Course Title], [Student in Courses].[Last Name];

Thanks for any help you can offer.

Ken Snell (MVP) said:
Repost the query's SQL statement.... your previous message is no longer in
my newsreader.

--

Ken Snell
<MS ACCESS MVP>

mrsr84 said:
I removed the word DISTINCT from the query but still cannot add text to the
memo field. Any thoughts?


Ken Snell said:
"Work" as meaning the query will cease to be nonupdatable because the
word
DISTINCT is in the query, yes.

No promises that there aren't other things that might not "work" < g >.

--

Ken Snell
<MS ACCESS MVP>


So if I understand correctly, remove the word DISTINCT and it will
work?

:

Rick is 100% correct.

--

Ken Snell
<MS ACCESS MVP>

mrsr84 wrote:
Here is the SQL from the query used to build the form:

SELECT DISTINCT [snip]

There you go. DISTINCT will do that.
Click to expand...
Click to expand...
Click to expand...
 
It's not always easy to see why a query may be nonupdatable, but often the
cause is because ACCESS cannot identify/insert the primary key field's value
in the table(s) being edited.

Your query does not appear to include in the SELECT clause the primary key
field for the [Student in Courses] table, nor the primary key field for the
[Coursed] table, nor the primary key field for the [Teachers] table. Without
these fields, the query likely cannot create records or edit records because
the unique record cannot be identified.

Note that I am assuming that the word DISTINCT has been removed from the
query (per your earlier post).

See these articles for more information about what can make a query
nonupdatable (watch for line-wrapping):

When can I update data from a query?
http://msdn.microsoft.com/library/d...l/acconDeterminingWhenCanUpdateDataQueryS.asp

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc


--

Ken Snell
<MS ACCESS MVP>


mrsr84 said:
Here is the SQL of the query for Courses all year

SELECT DISTINCT Courses.[Course Title], [Student in Courses].[First Name],
[Student in Courses].[Last Name], Teachers.[Teacher Name], [Student in
Courses].[Course Status], [Student in Courses].Comments
FROM Teachers INNER JOIN (Courses INNER JOIN [Student in Courses] ON
(Courses.ID = [Student in Courses].ID) AND (Courses.[Section ID] =
[Student
in Courses].[Section ID]) AND (Courses.[Teacher ID] = [Student in
Courses].[Teacher ID])) ON (Teachers.ID = Courses.[Teacher ID]) AND
(Teachers.ID = [Student in Courses].[Teacher ID])
WHERE (((Courses.[Course Title])<>"Homeroom") AND ((Teachers.[Teacher
Name])=[Enter Teacher Name:]))
ORDER BY Courses.[Course Title], [Student in Courses].[Last Name];

Thanks for any help you can offer.

Ken Snell (MVP) said:
Repost the query's SQL statement.... your previous message is no longer
in
my newsreader.

--

Ken Snell
<MS ACCESS MVP>

mrsr84 said:
I removed the word DISTINCT from the query but still cannot add text to
the
memo field. Any thoughts?


:

"Work" as meaning the query will cease to be nonupdatable because the
word
DISTINCT is in the query, yes.

No promises that there aren't other things that might not "work" < g
.

--

Ken Snell
<MS ACCESS MVP>


So if I understand correctly, remove the word DISTINCT and it will
work?

:

Rick is 100% correct.

--

Ken Snell
<MS ACCESS MVP>

mrsr84 wrote:
Here is the SQL from the query used to build the form:

SELECT DISTINCT [snip]

There you go. DISTINCT will do that.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top