Problems adding text to memo field

[Guest]

In my Form, I have a field that is based off of a field in a table called
"comments" which is set to memo. Inside the form, I cannot add information.
When I try to type, it beeps.
What am I doing wrong?

 

[John Vinson]

In my Form, I have a field that is based off of a field in a table called
"comments" which is set to memo. Inside the form, I cannot add information.
When I try to type, it beeps.
What am I doing wrong?

Hard to tell. What's the Control Source property of the textbox?
What's the Recordsource property of the form (post the SQL if it's a
query)? Is the textbox bound to the memo field Enabled or Locked (view
its Properties to see)?

John W. Vinson[MVP]

 

[Guest]

Thanks for the response. Here aer the answers to the questions you asked:

What's the Control Source property of the textbox? This comes from the Query
and the field comes from a table called Gr 8 Student info. That field is a
Memo field and is both enabled and not locked.

What's the Recordsource property of the form (post the SQL if it's a

query)? Here is the SQL coding of the query the form is based on :

SELECT [78 list].[Teacher Name], [78 list].[Term Code], [78 list].[Section
ID], [78 list].[Student ID], [78 list].[Last Name], [78 list].[First Name],
[78 list].[Course Title], [Gr 8 Homerooms].HomeroomNumber, [Gr 8
Homerooms].[Teacher Name], [Gr 8 Student Info Query].CommentID, [Gr 8 Student
Info Query].Comments, [Gr 8 Student Info Query].[Course Status]
FROM ([Gr 8 Student Info Query] INNER JOIN [78 list] ON [Gr 8 Student Info
Query].[Student ID] = [78 list].[Student ID]) INNER JOIN [Gr 8 Homerooms] ON
[Gr 8 Student Info Query].[HR ID] = [Gr 8 Homerooms].[HR ID]
WHERE ((([78 list].[Teacher Name])=[Enter Teacher's Name]) AND (([78
list].[Term Code])=[Enter Term]) AND (([78 list].[Course Title])<>"Homeroom")
AND (([78 list].Grade)=8))
ORDER BY [78 list].[Section ID], [78 list].[Last Name];

Thanks again for your help.

 

[John Vinson]

Thanks for the response. Here aer the answers to the questions you asked:

What's the Control Source property of the textbox? This comes from the Query
and the field comes from a table called Gr 8 Student info. That field is a
Memo field and is both enabled and not locked.

What's the Recordsource property of the form (post the SQL if it's a

query)? Here is the SQL coding of the query the form is based on :

SELECT [78 list].[Teacher Name], [78 list].[Term Code], [78 list].[Section
ID], [78 list].[Student ID], [78 list].[Last Name], [78 list].[First Name],
[78 list].[Course Title], [Gr 8 Homerooms].HomeroomNumber, [Gr 8
Homerooms].[Teacher Name], [Gr 8 Student Info Query].CommentID, [Gr 8 Student
Info Query].Comments, [Gr 8 Student Info Query].[Course Status]
FROM ([Gr 8 Student Info Query] INNER JOIN [78 list] ON [Gr 8 Student Info
Query].[Student ID] = [78 list].[Student ID]) INNER JOIN [Gr 8 Homerooms] ON
[Gr 8 Student Info Query].[HR ID] = [Gr 8 Homerooms].[HR ID]
WHERE ((([78 list].[Teacher Name])=[Enter Teacher's Name]) AND (([78
list].[Term Code])=[Enter Term]) AND (([78 list].[Course Title])<>"Homeroom")
AND (([78 list].Grade)=8))
ORDER BY [78 list].[Section ID], [78 list].[Last Name];

Can you update fields *other than* the memo field? If you open this
query in datasheet view is it editable (i.e. is there a "new record"
line at the bottom, with no data and a *> icon)? It's a three-table
join so it might not be; you might want to consider removing [Gr 8
Homerooms] from the query and using a Combo Box to store the HR ID
while displaying the HomeroomNumber. If [Gr 8 Student Info Query] is
not updateable, then this query won't be either.

John W. Vinson[MVP]

 

[Guest]

Thanks for the information. I have removed Homerooms like you suggested and
just to check I ran student info without any other information joined and
could add records. But I do not understand why I would want to- the records
are finite. I just want to edit a field- Comments.
Please explain why the ability to add a new record is necessary and would I
be better off adding the additional fields as a sub form so that they appear?
Thanks again.

Thanks for the response. Here aer the answers to the questions you asked:

What's the Control Source property of the textbox? This comes from the Query
and the field comes from a table called Gr 8 Student info. That field is a
Memo field and is both enabled and not locked.

What's the Recordsource property of the form (post the SQL if it's a

query)? Here is the SQL coding of the query the form is based on :

SELECT [78 list].[Teacher Name], [78 list].[Term Code], [78 list].[Section
ID], [78 list].[Student ID], [78 list].[Last Name], [78 list].[First Name],
[78 list].[Course Title], [Gr 8 Homerooms].HomeroomNumber, [Gr 8
Homerooms].[Teacher Name], [Gr 8 Student Info Query].CommentID, [Gr 8 Student
Info Query].Comments, [Gr 8 Student Info Query].[Course Status]
FROM ([Gr 8 Student Info Query] INNER JOIN [78 list] ON [Gr 8 Student Info
Query].[Student ID] = [78 list].[Student ID]) INNER JOIN [Gr 8 Homerooms] ON
[Gr 8 Student Info Query].[HR ID] = [Gr 8 Homerooms].[HR ID]
WHERE ((([78 list].[Teacher Name])=[Enter Teacher's Name]) AND (([78
list].[Term Code])=[Enter Term]) AND (([78 list].[Course Title])<>"Homeroom")
AND (([78 list].Grade)=8))
ORDER BY [78 list].[Section ID], [78 list].[Last Name];

Can you update fields *other than* the memo field? If you open this
query in datasheet view is it editable (i.e. is there a "new record"
line at the bottom, with no data and a *> icon)? It's a three-table
join so it might not be; you might want to consider removing [Gr 8
Homerooms] from the query and using a Combo Box to store the HR ID
while displaying the HomeroomNumber. If [Gr 8 Student Info Query] is
not updateable, then this query won't be either.

John W. Vinson[MVP]

 

[John Vinson]

Thanks for the information. I have removed Homerooms like you suggested and
just to check I ran student info without any other information joined and
could add records. But I do not understand why I would want to- the records
are finite. I just want to edit a field- Comments.
Please explain why the ability to add a new record is necessary and would I
be better off adding the additional fields as a sub form so that they appear?
Thanks again.

A Query is either editable - meaning that you can add records, delete
records, or edit the values in existing records - or it is not, in
which case you can do none of these things. In order to be able to
edit the memo field you must use an editable query. If you don't want
the user able to edit the other fields or to add records, simply set
the Form's AllowAdditions property to No; and set the Enabled property
of each textbox to False, and its Locked property to True except for
the one textbox for the memo field.

John W. Vinson[MVP]

 

[Guest]

Thanks for the information. I have isolated the problem in the query to
adding that comment memo field. Once that is added, the query will not allow
for additions (or editing text in the field- would assume due to no *). Any
suggestions as to how to get that field onto the form and making it editable?
Thanks again for ALL YOUR HELP.

 

[John Vinson]

Thanks for the information. I have isolated the problem in the query to
adding that comment memo field. Once that is added, the query will not allow
for additions (or editing text in the field- would assume due to no *). Any
suggestions as to how to get that field onto the form and making it editable?
Thanks again for ALL YOUR HELP.

Is [GR 8 Student Info Query] an updateable query? Could you perhaps
use a Subform based on this query, and displaying only the [Comments]
field textbox, rather than trying to use a multitable query as the
recordsource for the main form? (If there is more than one comment
this would be the preferred method in any case).

John W. Vinson[MVP]

 

[Guest]

As you suggested, I built a subform based on a new query with the following
SQL coding
SELECT [Gr 8 Student Info].[Student ID], [Gr 8 Student Info].Comments, [Gr 8
Student Info].[Course Status]
FROM [Gr 8 Student Info];

The problem is that when I change the student record, it does not change to
a new student's comment field. The subform contains 222 record areas.

Also, the course status field- I used option groupings to make toggle
buttons- I am not able to have them work- without beeping- if I have them
stored in the Course status field. If I ask for them to be saved for later
use, the buttons can be pressed but I do not know where the information is
being saved.

I appreciate all the help you are giving me. I figure that once I get this
grade's information created with forms, reports, queries and tables, I can
then replicate it for the other 3 grades.

Thanks again.

Thanks for the information. I have isolated the problem in the query to
adding that comment memo field. Once that is added, the query will not allow
for additions (or editing text in the field- would assume due to no *). Any
suggestions as to how to get that field onto the form and making it editable?
Thanks again for ALL YOUR HELP.

Is [GR 8 Student Info Query] an updateable query? Could you perhaps
use a Subform based on this query, and displaying only the [Comments]
field textbox, rather than trying to use a multitable query as the
recordsource for the main form? (If there is more than one comment
this would be the preferred method in any case).

John W. Vinson[MVP]

 

[John Vinson]

As you suggested, I built a subform based on a new query with the following
SQL coding
SELECT [Gr 8 Student Info].[Student ID], [Gr 8 Student Info].Comments, [Gr 8
Student Info].[Course Status]
FROM [Gr 8 Student Info];

The problem is that when I change the student record, it does not change to
a new student's comment field. The subform contains 222 record areas.

What's the Master/Child Link Field property of the Subform? It should
be the Student ID. Or - how are you changing the student record?

Also, the course status field- I used option groupings to make toggle
buttons- I am not able to have them work- without beeping- if I have them
stored in the Course status field. If I ask for them to be saved for later
use, the buttons can be pressed but I do not know where the information is
being saved.

An Option Group control has a value (the numeric value assigned to the
selected button or checkbox within it). The Option Group also has a
Control Source property, the name of the field (which must be a
numeric, integer or long integer, field) into which you wish to store
that number. The "saved for later use" option lets you use VBA code to
retrieve and use the value, but if you choose that option, the
selected value is not stored ANYWHERE until you write VBA code to
store it. That's beyond what you need to tackle right now, and
unnecessary. If the Status field is Text rather than Numeric, I'd
suggest using a Listbox or Combo Box instead of an Option Group
control.

I appreciate all the help you are giving me. I figure that once I get this
grade's information created with forms, reports, queries and tables, I can
then replicate it for the other 3 grades.

WHOA!!!!!

If you have a table named [GR 8 Student Info] and another table named
[GR 7 Student Info] and another named...

Your design *IS WRONG*.

Storing data - a child's current grade level - in a table name *is
simply incorrect*. Instead, put all the students in one table; use a
Grade field in the table, and base your form on a Query selecting
records for the desired grade.

John W. Vinson[MVP]

 

[Guest]

Thanks for the information- I receive all the information about the students
in 2 separate text files- one for grades 5 and 6 and the other for grades 7
and 8. Also, the comments used are different for the 2 different groupings.
Any suggestions as to how to bring in the data into 1 table? Teachers are
different in all grades -except for the specialists.

Thanks in advance.

As you suggested, I built a subform based on a new query with the following
SQL coding
SELECT [Gr 8 Student Info].[Student ID], [Gr 8 Student Info].Comments, [Gr 8
Student Info].[Course Status]
FROM [Gr 8 Student Info];

The problem is that when I change the student record, it does not change to
a new student's comment field. The subform contains 222 record areas.

What's the Master/Child Link Field property of the Subform? It should
be the Student ID. Or - how are you changing the student record?

Also, the course status field- I used option groupings to make toggle
buttons- I am not able to have them work- without beeping- if I have them
stored in the Course status field. If I ask for them to be saved for later
use, the buttons can be pressed but I do not know where the information is
being saved.

An Option Group control has a value (the numeric value assigned to the
selected button or checkbox within it). The Option Group also has a
Control Source property, the name of the field (which must be a
numeric, integer or long integer, field) into which you wish to store
that number. The "saved for later use" option lets you use VBA code to
retrieve and use the value, but if you choose that option, the
selected value is not stored ANYWHERE until you write VBA code to
store it. That's beyond what you need to tackle right now, and
unnecessary. If the Status field is Text rather than Numeric, I'd
suggest using a Listbox or Combo Box instead of an Option Group
control.

I appreciate all the help you are giving me. I figure that once I get this
grade's information created with forms, reports, queries and tables, I can
then replicate it for the other 3 grades.

WHOA!!!!!

If you have a table named [GR 8 Student Info] and another table named
[GR 7 Student Info] and another named...

Your design *IS WRONG*.

Storing data - a child's current grade level - in a table name *is
simply incorrect*. Instead, put all the students in one table; use a
Grade field in the table, and base your form on a Query selecting
records for the desired grade.

John W. Vinson[MVP]

 

[Guest]

I have managed to get all the records into 1 table- using Excel and then
importing in the data into Access.
A question:
1- do you recommend using the table analyzer to split up the data into
tables in the format that Access would like and thereby establishing the
relationships?

Thanks.

Thanks for the information- I receive all the information about the students
in 2 separate text files- one for grades 5 and 6 and the other for grades 7
and 8. Also, the comments used are different for the 2 different groupings.
Any suggestions as to how to bring in the data into 1 table? Teachers are
different in all grades -except for the specialists.

Thanks in advance.

As you suggested, I built a subform based on a new query with the following
SQL coding
SELECT [Gr 8 Student Info].[Student ID], [Gr 8 Student Info].Comments, [Gr 8
Student Info].[Course Status]
FROM [Gr 8 Student Info];

The problem is that when I change the student record, it does not change to
a new student's comment field. The subform contains 222 record areas.

What's the Master/Child Link Field property of the Subform? It should
be the Student ID. Or - how are you changing the student record?

Also, the course status field- I used option groupings to make toggle
buttons- I am not able to have them work- without beeping- if I have them
stored in the Course status field. If I ask for them to be saved for later
use, the buttons can be pressed but I do not know where the information is
being saved.

An Option Group control has a value (the numeric value assigned to the
selected button or checkbox within it). The Option Group also has a
Control Source property, the name of the field (which must be a
numeric, integer or long integer, field) into which you wish to store
that number. The "saved for later use" option lets you use VBA code to
retrieve and use the value, but if you choose that option, the
selected value is not stored ANYWHERE until you write VBA code to
store it. That's beyond what you need to tackle right now, and
unnecessary. If the Status field is Text rather than Numeric, I'd
suggest using a Listbox or Combo Box instead of an Option Group
control.

I appreciate all the help you are giving me. I figure that once I get this
grade's information created with forms, reports, queries and tables, I can
then replicate it for the other 3 grades.

WHOA!!!!!

If you have a table named [GR 8 Student Info] and another table named
[GR 7 Student Info] and another named...

Your design *IS WRONG*.

Storing data - a child's current grade level - in a table name *is
simply incorrect*. Instead, put all the students in one table; use a
Grade field in the table, and base your form on a Query selecting
records for the desired grade.

John W. Vinson[MVP]

 

[John Vinson]

Thanks for the information- I receive all the information about the students
in 2 separate text files- one for grades 5 and 6 and the other for grades 7
and 8.

Fine; no problem; run two append queries appending the two sets of
data into the one table.

Also, the comments used are different for the 2 different groupings.

The Comments field should be in whatever table the comment applies to.
I don't know what you mean by "groupings". Could you describe what
tables (other than [Gr 8 Student Info]) you have, how the tables are
related, and what real-life entity the table represents?

Any suggestions as to how to bring in the data into 1 table? Teachers are
different in all grades -except for the specialists.

Again... you should have a Table for each type of Entity. A Teacher is
an entity; you should have a table of Teachers, and a table of
Classes, and (almost surely) a table of TeacherClasses with a record
for each class that each teacher teaches.


John W. Vinson[MVP]

 

[John Vinson]

I have managed to get all the records into 1 table- using Excel and then
importing in the data into Access.
A question:
1- do you recommend using the table analyzer to split up the data into
tables in the format that Access would like and thereby establishing the
relationships?

It is a sometimes helpful source of suggestions - but an understanding
of the principles of normalization and relational design is probably
necessary. The wizard has no human intelligence and may make some
invalid suggestions.

Try it, and post the suggested tables and their relationships here; I
think between us we can come up with a good, well-normalized design.

John W. Vinson[MVP]

 

[Guest]

John- thanks for all your help. I have now built a completely new database
based on your suggestions. The database contains the following tables:
Students info- containing StudentID(PK) Last Name, FirstName, Grade, HR#(FK)
(in case I want to search by HR), CommentID(FK), Comments(Memo field type),
CourseStatus ID(FK), Course Status--I used the ID field as a means to create
relationships to other tables
Teachers- ID(PK) and TeacherLastName;
TeacherClasses- ID(PK) Course ID, Course, SectionID, TeacherID(FK),
TeacherName, StudentID(FK);
Classes- ID (PK), Course, SectionID, Term(some classes run quarterly,
semester, yearly, 15 classes or 17 classes);
Homerooms- HR#(PK), HR Teacher;
Comments 7 8- these are only for grades 7 and 8 -ID(PK) and CommentText;
Comments 5 6- only for grades 5 and 6-ID(PK) and CommentText;
Course Status- ID(PK) and Course Status

I have built a query with the following SQL coding:
SELECT [All Students in TMS 2004 2005].[Teacher Name], [All Students in TMS
2004 2005].[Course Title], [All Students in TMS 2004 2005].[Section ID], [All
Students in TMS 2004 2005].[Last Name], [All Students in TMS 2004
2005].[First Name], [All Students in TMS 2004 2005].[Term Code], [Students
Info].Comments, [Students Info].CourseStatus
FROM [All Students in TMS 2004 2005] INNER JOIN [Students Info] ON [All
Students in TMS 2004 2005].[Student ID] = [Students Info].[Student ID]
WHERE ((([All Students in TMS 2004 2005].[Teacher Name])=[Enter Teacher])
AND (([All Students in TMS 2004 2005].[Course Title])<>"Homeroom") AND (([All
Students in TMS 2004 2005].[Term Code])=[Enter 1st Term]) AND (([All Students
in TMS 2004 2005].Grade)=[Enter Grade])) OR ((([All Students in TMS 2004
2005].[Term Code])=[Enter 2nd Term])) OR ((([All Students in TMS 2004
2005].[Term Code])=[Enter 3rd Term])) OR ((([All Students in TMS 2004
2005].[Term Code])=[Enter Last Term]))
ORDER BY [All Students in TMS 2004 2005].[Course Title], [All Students in
TMS 2004 2005].[Section ID], [All Students in TMS 2004 2005].[Last Name];

I used the original data table with all the student information as well as a
few other fields.

I used the suggestion of a combo box for choosing Course status and have the
value stored in the student's data Course status field.

I now need help getting the comments for a particular grade to show up in
the form. I want to have the value chosen by the user to be stored in the
Comments memo field which is on the form and then be able to type in
additional information.

Thanks again for all your help. Hopefully I have provided you with enough
information.

Thanks for the information- I receive all the information about the students
in 2 separate text files- one for grades 5 and 6 and the other for grades 7
and 8.

Fine; no problem; run two append queries appending the two sets of
data into the one table.

Also, the comments used are different for the 2 different groupings.

The Comments field should be in whatever table the comment applies to.
I don't know what you mean by "groupings". Could you describe what
tables (other than [Gr 8 Student Info]) you have, how the tables are
related, and what real-life entity the table represents?

Any suggestions as to how to bring in the data into 1 table? Teachers are
different in all grades -except for the specialists.

Again... you should have a Table for each type of Entity. A Teacher is
an entity; you should have a table of Teachers, and a table of
Classes, and (almost surely) a table of TeacherClasses with a record
for each class that each teacher teaches.


John W. Vinson[MVP]

 

[John Vinson]

On Wed, 2 Mar 2005 17:51:02 -0800, mrsr84
I've printed this out for cogitation but today's looking really busy,
and I'm leaving town for the weekend - so no guarantees.

Just one quick suggestion - if (as I gather) the [Comments 7 8] table
comments apply to all students in a grade, and likewise the [Comments
5 6] table, you might want to consider instead using one GradeComments
table with fields ID, Grade, and CommentText. YOu could then use a
Subform using Grade as the master/child link field.


John W. Vinson[MVP]

 

[Guest]

John-
Thanks for the suggestion but I cannot see how that will look. The comments
are already numbered on existing paper sheets (which the database report will
replicate) so having an id field with a unique number is impossible.
Hope you have a great weekend away and I look forward to any ideas you might
come up with.

 

[John Vinson]

John-
Thanks for the suggestion but I cannot see how that will look. The comments
are already numbered on existing paper sheets (which the database report will
replicate) so having an id field with a unique number is impossible.

Could the manually created number be the unique ID, or do you have
multiple paper sheets all with comment number 1?

Hope you have a great weekend away and I look forward to any ideas you might
come up with.

I do NOT understand the comments. Do the comments relate to a student?
to a grade as a whole? Are they totally independent of the other
tables? You have a CommentID field in the student table - this would
mean that each student would have one and only one comment; is that
correct?

John W. Vinson[MVP]