Data Entry Mode

[Guest]

How does one make your form Data Entry Only if the query it is based on has
to use the totals row? I use First under a memo field to be able to enter
more than 255 characters but it appears to disable the entry only mode on the
form.

Is there a way around this? I need the form to not show any previous
records.

 

[Steve Schapel]

Brian

It is not clear why you are using a Totals Query. Can't the result you
require be obtained with a simple Select Query? Can you please post
back with the SQL view of the query that is exhibiting this behaviour?

 

[Guest]

Here is the SQL. The reason for the Totals Query is that I have to use First
in the totals row because one of my fields is a memo field and I have to use
this to allow more than 255 characters in the query. If access would allow
sorting on more than 255 characters I would not have this issue.

I may have to use a new guaery for this data entry only query and then join
or union the data to my other queries which eventually feed a monthly report.


I don't need the other Group BY's but what else can I use if I use the First
in the totals row under the memo field?

SELECT First([REQUEST FOR LOSS CONTROL SERVICE].[NOTEWORTHY ACCOMPLISHMENTS
FOR THE MONTH]) AS [FirstOfNOTEWORTHY ACCOMPLISHMENTS FOR THE MONTH],
[REQUEST FOR LOSS CONTROL SERVICE].[STAFF MEETING/TRAINING], [REQUEST FOR
LOSS CONTROL SERVICE].[VACATION/SICK/HOLIDAY], [REQUEST FOR LOSS CONTROL
SERVICE].[SPECIAL PROJECTS], [REQUEST FOR LOSS CONTROL SERVICE].ID, [REQUEST
FOR LOSS CONTROL SERVICE].[DUE DATE:], [REQUEST FOR LOSS CONTROL
SERVICE].[ACCOUNT NAME], [REQUEST FOR LOSS CONTROL SERVICE].[POLICY NUMBER],
[REQUEST FOR LOSS CONTROL SERVICE].[ASSIGNED CONSULTANT], [REQUEST FOR LOSS
CONTROL SERVICE].[DATE OF VERBAL REPORT], [REQUEST FOR LOSS CONTROL
SERVICE].[DATE OF SURVEY], [REQUEST FOR LOSS CONTROL SERVICE].[DATE WRITTEN
REPORT SENT], [REQUEST FOR LOSS CONTROL SERVICE].COMMENTS, [REQUEST FOR LOSS
CONTROL SERVICE].[NEW DUE DATE], [REQUEST FOR LOSS CONTROL SERVICE].[DATE
RECEIVED:], [REQUEST FOR LOSS CONTROL SERVICE].[CANCEL REQUEST], [REQUEST FOR
LOSS CONTROL SERVICE].[INITIAL EVALUATION], [REQUEST FOR LOSS CONTROL
SERVICE].[PROGRAM/MGMT EVALUATION], [REQUEST FOR LOSS CONTROL
SERVICE].[JOBSITE SURVEY], [REQUEST FOR LOSS CONTROL SERVICE].[OPERATIONS
SURVEY], [REQUEST FOR LOSS CONTROL SERVICE].[LOSS ANALYSIS], [REQUEST FOR
LOSS CONTROL SERVICE].[ACCIDENT INVESTIGATION], [REQUEST FOR LOSS CONTROL
SERVICE].TRAINING, [REQUEST FOR LOSS CONTROL SERVICE].[SAFETY MEETING],
[REQUEST FOR LOSS CONTROL SERVICE].[ACTION PLANNING], [REQUEST FOR LOSS
CONTROL SERVICE].[PROGRESS REPORT], [REQUEST FOR LOSS CONTROL
SERVICE].[INDUSTRIAL HYGIENE/ERGONOMICS], [REQUEST FOR LOSS CONTROL
SERVICE].[PHONE SURVEY/OTHER], [REQUEST FOR LOSS CONTROL SERVICE].[TYPE OF
SERVICE], [REQUEST FOR LOSS CONTROL SERVICE].[CALL TYPE], [REQUEST FOR LOSS
CONTROL SERVICE].[LOCATION SERVICING DIVISION]
FROM [REQUEST FOR LOSS CONTROL SERVICE]
GROUP BY [REQUEST FOR LOSS CONTROL SERVICE].[STAFF MEETING/TRAINING],
[REQUEST FOR LOSS CONTROL SERVICE].[VACATION/SICK/HOLIDAY], [REQUEST FOR LOSS
CONTROL SERVICE].[SPECIAL PROJECTS], [REQUEST FOR LOSS CONTROL SERVICE].ID,
[REQUEST FOR LOSS CONTROL SERVICE].[DUE DATE:], [REQUEST FOR LOSS CONTROL
SERVICE].[ACCOUNT NAME], [REQUEST FOR LOSS CONTROL SERVICE].[POLICY NUMBER],
[REQUEST FOR LOSS CONTROL SERVICE].[ASSIGNED CONSULTANT], [REQUEST FOR LOSS
CONTROL SERVICE].[DATE OF VERBAL REPORT], [REQUEST FOR LOSS CONTROL
SERVICE].[DATE OF SURVEY], [REQUEST FOR LOSS CONTROL SERVICE].[DATE WRITTEN
REPORT SENT], [REQUEST FOR LOSS CONTROL SERVICE].COMMENTS, [REQUEST FOR LOSS
CONTROL SERVICE].[NEW DUE DATE], [REQUEST FOR LOSS CONTROL SERVICE].[DATE
RECEIVED:], [REQUEST FOR LOSS CONTROL SERVICE].[CANCEL REQUEST], [REQUEST FOR
LOSS CONTROL SERVICE].[INITIAL EVALUATION], [REQUEST FOR LOSS CONTROL
SERVICE].[PROGRAM/MGMT EVALUATION], [REQUEST FOR LOSS CONTROL
SERVICE].[JOBSITE SURVEY], [REQUEST FOR LOSS CONTROL SERVICE].[OPERATIONS
SURVEY], [REQUEST FOR LOSS CONTROL SERVICE].[LOSS ANALYSIS], [REQUEST FOR
LOSS CONTROL SERVICE].[ACCIDENT INVESTIGATION], [REQUEST FOR LOSS CONTROL
SERVICE].TRAINING, [REQUEST FOR LOSS CONTROL SERVICE].[SAFETY MEETING],
[REQUEST FOR LOSS CONTROL SERVICE].[ACTION PLANNING], [REQUEST FOR LOSS
CONTROL SERVICE].[PROGRESS REPORT], [REQUEST FOR LOSS CONTROL
SERVICE].[INDUSTRIAL HYGIENE/ERGONOMICS], [REQUEST FOR LOSS CONTROL
SERVICE].[PHONE SURVEY/OTHER], [REQUEST FOR LOSS CONTROL SERVICE].[TYPE OF
SERVICE], [REQUEST FOR LOSS CONTROL SERVICE].[CALL TYPE], [REQUEST FOR LOSS
CONTROL SERVICE].[LOCATION SERVICING DIVISION]
ORDER BY [REQUEST FOR LOSS CONTROL SERVICE].[ASSIGNED CONSULTANT];

 

[Steve Schapel]

Brian,

Thanks for the further information. I'm afraid I still can't understand
why you are applyting the First() function to the memo field, which I
assume is the [NOTEWORTHY ACCOMPLISHMENTS FOR THE MONTH] field. I know
nothing of any 255 character limit on anything that would prevent you
from using a simple Select Query. No Totals, no First(), no Group By.
Have you tried this? If so, what specific problem did you encounter?
You mentioned something about a problem with sorting, but I can't see
the relevance of this, as you are sorting on the [ASSIGNED CONSULTANT]
field, are you not? And if this is supposed to be the Record Source of
a form with Data Entry = Yes, I would imagine that any sorting would be
irrelevant anyway, since you can't see the previously entered records.
Sorry to be thick here, but on the face of what you have told us so far,
this should be a very simple matter, I mean there's only one table
involved. In fact, when it comes to that, why don't you just base your
form directly on the table?

 

[Guest]

Probably will have to drop the first thing and go with the select query.
Just to explain, I use these records and combine them with other records into
a Union Query which I end up sorting and feeding to a monthly report as I
said.

The problem is that memo fields get truncated and I need the ability to have
more than 255 characters entered and to show up on reports that require
sorting and grouiping.

Unfortunately I know of no way around the issue and even the firstof works
to a point and then it truncates in some cases as well in reports.

Do you think access will ever allow sorting on more than 255 characters or
do people just switch to a different database program?.

Brian,

Thanks for the further information. I'm afraid I still can't understand
why you are applyting the First() function to the memo field, which I
assume is the [NOTEWORTHY ACCOMPLISHMENTS FOR THE MONTH] field. I know
nothing of any 255 character limit on anything that would prevent you
from using a simple Select Query. No Totals, no First(), no Group By.
Have you tried this? If so, what specific problem did you encounter?
You mentioned something about a problem with sorting, but I can't see
the relevance of this, as you are sorting on the [ASSIGNED CONSULTANT]
field, are you not? And if this is supposed to be the Record Source of
a form with Data Entry = Yes, I would imagine that any sorting would be
irrelevant anyway, since you can't see the previously entered records.
Sorry to be thick here, but on the face of what you have told us so far,
this should be a very simple matter, I mean there's only one table
involved. In fact, when it comes to that, why don't you just base your
form directly on the table?

--
Steve Schapel, Microsoft Access MVP

Here is the SQL. The reason for the Totals Query is that I have to use First
in the totals row because one of my fields is a memo field and I have to use
this to allow more than 255 characters in the query. If access would allow
sorting on more than 255 characters I would not have this issue.

I may have to use a new guaery for this data entry only query and then join
or union the data to my other queries which eventually feed a monthly report.


I don't need the other Group BY's but what else can I use if I use the First
in the totals row under the memo field?

 

[Steve Schapel]

Brian,

First of all, if the purpose of the query that we have been discussing
is to serve as the Record Source of a data entry form, then I suggest
you simply relate to it as such. As far as I can see, your eventual
need to use this data in a report is a separate issue, unless I am
missing something.

It would surprise me if Access ever allowed sorting on more than 255
characters. The need to do so would be extremely rare, I certainly
can't imagine a situation where it would be required, and I have never
come across anyone wanting this. If this is what you want, it would be
very simple to set this up via calculated fields using Left() and Mid()
functions.

There are certain specific situations where the contents of Memo fields
get truncated. If your meaning is that the memo data gets truncated to
255 characters in your data entry form, then that is very surprising,
and definitely should not happen. If your meaning is that the memo data
gets truncated when you try to put it on the report, it almost certainly
indicates a design problem with the report, or with the query that the
report is based on, and is probably very easily overcome.

 

[Van T. Dinh]

Totals Queries (those that use "GROUP BY") are *not* updateable AFAIK. In
fact, I don't think you can edit existing rows returned by this Query. I
noted that you mentioned so in your first post but if the Memo Field is
updateable, the Query you used to edit the data must be different from the
SQL String you posted.

--
HTH
Van T. Dinh
MVP (Access)

Here is the SQL. The reason for the Totals Query is that I have to use First
in the totals row because one of my fields is a memo field and I have to use
this to allow more than 255 characters in the query. If access would allow
sorting on more than 255 characters I would not have this issue.

I may have to use a new guaery for this data entry only query and then join
or union the data to my other queries which eventually feed a monthly report.


I don't need the other Group BY's but what else can I use if I use the First
in the totals row under the memo field?

SELECT First([REQUEST FOR LOSS CONTROL SERVICE].[NOTEWORTHY ACCOMPLISHMENTS
FOR THE MONTH]) AS [FirstOfNOTEWORTHY ACCOMPLISHMENTS FOR THE MONTH],
[REQUEST FOR LOSS CONTROL SERVICE].[STAFF MEETING/TRAINING], [REQUEST FOR
LOSS CONTROL SERVICE].[VACATION/SICK/HOLIDAY], [REQUEST FOR LOSS CONTROL
SERVICE].[SPECIAL PROJECTS], [REQUEST FOR LOSS CONTROL SERVICE].ID, [REQUEST
FOR LOSS CONTROL SERVICE].[DUE DATE:], [REQUEST FOR LOSS CONTROL
SERVICE].[ACCOUNT NAME], [REQUEST FOR LOSS CONTROL SERVICE].[POLICY NUMBER],
[REQUEST FOR LOSS CONTROL SERVICE].[ASSIGNED CONSULTANT], [REQUEST FOR LOSS
CONTROL SERVICE].[DATE OF VERBAL REPORT], [REQUEST FOR LOSS CONTROL
SERVICE].[DATE OF SURVEY], [REQUEST FOR LOSS CONTROL SERVICE].[DATE WRITTEN
REPORT SENT], [REQUEST FOR LOSS CONTROL SERVICE].COMMENTS, [REQUEST FOR LOSS
CONTROL SERVICE].[NEW DUE DATE], [REQUEST FOR LOSS CONTROL SERVICE].[DATE
RECEIVED:], [REQUEST FOR LOSS CONTROL SERVICE].[CANCEL REQUEST], [REQUEST FOR
LOSS CONTROL SERVICE].[INITIAL EVALUATION], [REQUEST FOR LOSS CONTROL
SERVICE].[PROGRAM/MGMT EVALUATION], [REQUEST FOR LOSS CONTROL
SERVICE].[JOBSITE SURVEY], [REQUEST FOR LOSS CONTROL SERVICE].[OPERATIONS
SURVEY], [REQUEST FOR LOSS CONTROL SERVICE].[LOSS ANALYSIS], [REQUEST FOR
LOSS CONTROL SERVICE].[ACCIDENT INVESTIGATION], [REQUEST FOR LOSS CONTROL
SERVICE].TRAINING, [REQUEST FOR LOSS CONTROL SERVICE].[SAFETY MEETING],
[REQUEST FOR LOSS CONTROL SERVICE].[ACTION PLANNING], [REQUEST FOR LOSS
CONTROL SERVICE].[PROGRESS REPORT], [REQUEST FOR LOSS CONTROL
SERVICE].[INDUSTRIAL HYGIENE/ERGONOMICS], [REQUEST FOR LOSS CONTROL
SERVICE].[PHONE SURVEY/OTHER], [REQUEST FOR LOSS CONTROL SERVICE].[TYPE OF
SERVICE], [REQUEST FOR LOSS CONTROL SERVICE].[CALL TYPE], [REQUEST FOR LOSS
CONTROL SERVICE].[LOCATION SERVICING DIVISION]
FROM [REQUEST FOR LOSS CONTROL SERVICE]
GROUP BY [REQUEST FOR LOSS CONTROL SERVICE].[STAFF MEETING/TRAINING],
[REQUEST FOR LOSS CONTROL SERVICE].[VACATION/SICK/HOLIDAY], [REQUEST FOR LOSS
CONTROL SERVICE].[SPECIAL PROJECTS], [REQUEST FOR LOSS CONTROL SERVICE].ID,
[REQUEST FOR LOSS CONTROL SERVICE].[DUE DATE:], [REQUEST FOR LOSS CONTROL
SERVICE].[ACCOUNT NAME], [REQUEST FOR LOSS CONTROL SERVICE].[POLICY NUMBER],
[REQUEST FOR LOSS CONTROL SERVICE].[ASSIGNED CONSULTANT], [REQUEST FOR LOSS
CONTROL SERVICE].[DATE OF VERBAL REPORT], [REQUEST FOR LOSS CONTROL
SERVICE].[DATE OF SURVEY], [REQUEST FOR LOSS CONTROL SERVICE].[DATE WRITTEN
REPORT SENT], [REQUEST FOR LOSS CONTROL SERVICE].COMMENTS, [REQUEST FOR LOSS
CONTROL SERVICE].[NEW DUE DATE], [REQUEST FOR LOSS CONTROL SERVICE].[DATE
RECEIVED:], [REQUEST FOR LOSS CONTROL SERVICE].[CANCEL REQUEST], [REQUEST FOR
LOSS CONTROL SERVICE].[INITIAL EVALUATION], [REQUEST FOR LOSS CONTROL
SERVICE].[PROGRAM/MGMT EVALUATION], [REQUEST FOR LOSS CONTROL
SERVICE].[JOBSITE SURVEY], [REQUEST FOR LOSS CONTROL SERVICE].[OPERATIONS
SURVEY], [REQUEST FOR LOSS CONTROL SERVICE].[LOSS ANALYSIS], [REQUEST FOR
LOSS CONTROL SERVICE].[ACCIDENT INVESTIGATION], [REQUEST FOR LOSS CONTROL
SERVICE].TRAINING, [REQUEST FOR LOSS CONTROL SERVICE].[SAFETY MEETING],
[REQUEST FOR LOSS CONTROL SERVICE].[ACTION PLANNING], [REQUEST FOR LOSS
CONTROL SERVICE].[PROGRESS REPORT], [REQUEST FOR LOSS CONTROL
SERVICE].[INDUSTRIAL HYGIENE/ERGONOMICS], [REQUEST FOR LOSS CONTROL
SERVICE].[PHONE SURVEY/OTHER], [REQUEST FOR LOSS CONTROL SERVICE].[TYPE OF
SERVICE], [REQUEST FOR LOSS CONTROL SERVICE].[CALL TYPE], [REQUEST FOR LOSS
CONTROL SERVICE].[LOCATION SERVICING DIVISION]
ORDER BY [REQUEST FOR LOSS CONTROL SERVICE].[ASSIGNED CONSULTANT];

 

[Guest]

I appreciate all the information.

All my memo fields for data entry but may be in a query used to supply other
fields for the form being used. I need to learn more about Left() and Mid()
and if this will help me in some situations.

I have more which right now are truncating down to 255 characters after I
type more than that. I alos have queries which obviously have fields that
truncate.

You make it sound so simple and maybe my lack of knowledge is the problem.
I have been searching for some time answers to help solve this issue nad is
where I came across the Firstof workaround.

 

[Steve Schapel]

Brian,

Using the "FirstOf workaround" is for a situation where you have a
Totals Query for some legitimate purpose, and you need to include a Memo
field within this query. This is not the case with the example you
gave. In the example you gave, this was meant to be the Record Source
of a form, and there is no purpose served in a Totals Query here. The
form should be based on a Select Query, or directly on the table itself.

I think in order for me to be able to help further, I would need you to
supply very specific examples with full details of where you
experiencing this problem with data being truncated. However, I have
found some information that Memo fields can be truncated if there is
formatting applied. Have a look at the Format property of the form
controls, and the Format property of the field in table design, and
remove anything entered there.

 

[Guest]

Yes, I will have do some looking into. I did read other information about
the formatting so maybe I can change something to help my situation. I
appreciate the feedback on the basis of my example and I will at changing and
trying a select query and then see if my other needs can be modified to work
as intended.
I will do my part and then get back to this forum if I exhaust what I know.

Thanks again

 

[Guest]

According to The AccessWeb at http://mvps.org/access/general/gen0011.htm
under "Some common Access specifications", the number of characters in a TEXT
field is limited to 255 whereas a MEMO field's limit is 65,535 characters.

It sounds like what you are calling a memo field is in fact a text field.

And I agree with Steve... if you are doing data entry and the data is going
into one table, base your form on the table itself. Why would you want to
sort while entering data? You do sorting and grouping to find records or to
organize the records for printing.

You can't control where the record gets inserted in the table - only how it
is grouped/sorted when it comes out.

Steve too

 

[Guest]

Thank You to all. I did make all queries select as they were total queries
due to wanting to use the words "No comment" until comments were entered into
a form to feed a report. I just dropped using that expression which meant I
could remove the totals row.

I have some other issues I will post but again thanks to all for pointing me
in the right direction on this one.

 

[Steve Schapel]

Steve too

Actually, sorting data within the query that a form is based on is very
commonly done, and is often important, especially with a continuous view
form for example. It is unusual to want to use a Memo field as the
basis of the sort, and if it is used, the first 255 characters of the
Memo field, which is what Access uses, would normally be sufficient in
order to produce a correct ordering of the records.

There are certain circumstances where Access does truncate the contents
of a Memo field to the first 255 characters, and I think this is what
Brian is experiencing. What I haven't been able to understand is why
Brian is encountering this problem, as there seems to be no need for it
with what he is trying to do.

 

[Steve Schapel]

Brian,

Your statement of "a form to feed a report" is telling. A form does not
feed a report. A report is fed by a query, which is based on a table or
a set of tables. The fact that the tables are also related to a form is
immaterial. I don't think it is useful to consider that there is any
connection between a form and a report.

I am not sure where you want the words "no comment" to appear. Without
knowing more details, it is hard to comment. But it is very difficult
to see how this requirement is related to the use of a Totals Query.
Even if using a Totals Query did help you to obtain this result, it is
almost certain that there are simpler ways to achieve the same result.

 

[Guest]

Steve, thanks for correcting me. Yes, my query does feed the report, not the
form.

Do you know of a way to have my report Say "No Comment" in a field until
text is entered into a form and taken from the query?

I had been using NonBlankComments: First(IIf(Len(Trim$([Comments] &
""))=0,"No Comments",[Comments])) with Expression in the totals row which
created the problem with the memo field.

 

[Steve Schapel]

Brian,

Once again, I don't know why you are using a Totals Query. Once again,
I don't know the purpose of the First() function in this context. Once
again, I suspect this should be within a Select Query, no Group By's or
aggregates, with a calculated expression like any of the following...

NonBlankComments: IIf(Len(Trim$([Comments] & ""))=0,"No
comments",[Comments])
NonBlankComments: IIf(Len([Comments]),[Comments],"No comments")
NonBlankComments: IIf(IsNull([Comments]),"No comments",[Comments])
NonBlankComments: Nz([Comments],"No comments")

 

[Guest]

Thank You Steve. The first one you gave me was perfect. I am learning more
all the time.

Thanks.

Brian,

Once again, I don't know why you are using a Totals Query. Once again,
I don't know the purpose of the First() function in this context. Once
again, I suspect this should be within a Select Query, no Group By's or
aggregates, with a calculated expression like any of the following...

NonBlankComments: IIf(Len(Trim$([Comments] & ""))=0,"No
comments",[Comments])
NonBlankComments: IIf(Len([Comments]),[Comments],"No comments")
NonBlankComments: IIf(IsNull([Comments]),"No comments",[Comments])
NonBlankComments: Nz([Comments],"No comments")

--
Steve Schapel, Microsoft Access MVP

Steve, thanks for correcting me. Yes, my query does feed the report, not the
form.

Do you know of a way to have my report Say "No Comment" in a field until
text is entered into a form and taken from the query?

I had been using NonBlankComments: First(IIf(Len(Trim$([Comments] &
""))=0,"No Comments",[Comments])) with Expression in the totals row which
created the problem with the memo field.

 

[Steve Schapel]

Brian,

You're welcome.

Of the 4 expressions I gave you, I would personally rank them in terms
of preference from the bottom up. I personally would never use the
first one I gave you... I only included it to give recognition to the
type of approach you had used in your original one.