customizing fields in query results: datasheet view

[spk]

Hello,
So I once took a class in Access but have since forgotten if this is possible or not....I have set up a switchboard that runs a macro query to display list of results in datasheet view. The last field in the datsheet is a "notes" field. I have been receiving complaints that the notes entered in the field run off the screen and therefore are difficult/time consuming to read. I am looking for a way to keep the information in the notes field confined so that:

The text will look like this
The text will look like this
The text will look like this
The text will look like this

And not:

The text will look like this. this. this. this. this. this. this. this. this. this. this.

Any help greatly appreciated.

[FishVal]

Hello,
So I once took a class in Access but have since forgotten if this is possible or not....I have set up a switchboard that runs a macro query to display list of results in datasheet view. The last field in the datsheet is a "notes" field. I have been receiving complaints that the notes entered in the field run off the screen and therefore are difficult/time consuming to read. I am looking for a way to keep the information in the notes field confined so that:

The text will look like this
The text will look like this
The text will look like this
The text will look like this

And not:

The text will look like this. this. this. this. this. this. this. this. this. this. this.

Any help greatly appreciated.

Hi, there.

Try smthng like this

SELECT .............., IIf(Len([txtLongText])>15,Left([txtLongText],15) & "...",[txtLongText]) AS [Truncated text]
FROM tblTable;
 

[missinglinq]

You can adjust the width of a given field in a query, just as you would in a datasheet form view or a spreadsheet, for that matter. You can also adjust the height of each row, in the same manner. The problem is, obviously, that you have to adjust the height of the entire row, not just the memo field! Because of this the whole thing looks rather ungainly, and the number of rows/records visible at any given times becomes rather limited!

A more sensible approach to this problem would be to use the query as the Record Source for a form, done in datasheet view. The form can then be opened from the switchboard. You can use the Forms Wizard to create the form, and it will look identical to the query display. You can size the width of the memo field to whatever you desire, but you can also use a little VBA magic to expand the memo field when your user needs to look at its content. In the Double Click event for this field use this code:

 Private Sub YourMemoFieldName_DblClick(Cancel As Integer)
       DoCmd.RunCommand acCmdZoomBox
 End Sub
 

When the user needs to view the entire contents of the memo filed, they simply double-click on it, and it expands to show the entire contents.

Welcome to TheScripts!

Linq ;0)>

[Jim Doherty]

Hello,
So I once took a class in Access but have since forgotten if this is possible or not....I have set up a switchboard that runs a macro query to display list of results in datasheet view. The last field in the datsheet is a "notes" field. I have been receiving complaints that the notes entered in the field run off the screen and therefore are difficult/time consuming to read. I am looking for a way to keep the information in the notes field confined so that:

The text will look like this
The text will look like this
The text will look like this
The text will look like this

And not:

The text will look like this. this. this. this. this. this. this. this. this. this. this.

Any help greatly appreciated.

Not sure what you mean .....but have you considered looking at the 'Enter Key Behaviour' of the textbox concerned. NEW LINE IN FIELD (the inputter can then decide where it wraps themselves?) or are you saying you need to ensure that the textbox only carries with it a certain number of characters per line, at which point it must WRAP. (presumably though only where the maximum last character in the line is a space otherwise you might cut your words in half, quarters, fifths you name it.

Fishvals example is what I'd go with on the face of what you are describing and then on double clicking the field that Fishval outlines bring up a larger text box as a dialog to display the actual contents. (but even here we have to decide where wrapping starts and finishes if we are trying to replicate what you outline in your example) All of this depends does it not, on the width and height of your current notes textbox ie the available space you currently have on screen to deal with notes? or have I interpreted it wrong?

Jim

[Jim Doherty]

Not sure what you mean .....but have you considered looking at the 'Enter Key Behaviour' of the textbox concerned. NEW LINE IN FIELD (the inputter can then decide where it wraps themselves?) or are you saying you need to ensure that the textbox only carries with it a certain number of characters per line, at which point it must WRAP. (presumably though only where the maximum last character in the line is a space otherwise you might cut your words in half, quarters, fifths you name it.

Fishvals example is what I'd go with on the face of what you are describing and then on double clicking the field that Fishval outlines bring up a larger text box as a dialog to display the actual contents. (but even here we have to decide where wrapping starts and finishes if we are trying to replicate what you outline in your example) All of this depends does it not, on the width and height of your current notes textbox ie the available space you currently have on screen to deal with notes? or have I interpreted it wrong?

Jim

Whoops I qualify...I misread your posting you are using 'datasheet' only not a form saved in datasheet discount my posting, I assumed you were working in a form saved as datasheet

[spk]

You can adjust the width of a given field in a query, just as you would in a datasheet form view or a spreadsheet, for that matter. You can also adjust the height of each row, in the same manner. The problem is, obviously, that you have to adjust the height of the entire row, not just the memo field! Because of this the whole thing looks rather ungainly, and the number of rows/records visible at any given times becomes rather limited!

A more sensible approach to this problem would be to use the query as the Record Source for a form, done in datasheet view. The form can then be opened from the switchboard. You can use the Forms Wizard to create the form, and it will look identical to the query display. You can size the width of the memo field to whatever you desire, but you can also use a little VBA magic to expand the memo field when your user needs to look at its content. In the Double Click event for this field use this code:

 Private Sub YourMemoFieldName_DblClick(Cancel As Integer)
       DoCmd.RunCommand acCmdZoomBox
 End Sub
 

When the user needs to view the entire contents of the memo filed, they simply double-click on it, and it expands to show the entire contents.

Welcome to TheScripts!

Linq ;0)>

Thanks to all for the quick replies.
I am going to try using the query as the Record Source for a form, done in datasheet view. As I said, I'm a little rusty with access, but I won't bother anyone with how to do that exactly until I've taken a look at it myself.

I did try creating an untitled form with the CmdZoomBox VBA and it works, but I'm still wondering if I can just wrap one field (text box)of the query in DS view after say 25 characters. This portion of the record is used least often, so breaking up the "flow" of the fields is not an issue (in other words I don't mind if the other fields in each record skip a row. I can always max out the char width to minimize how often it happens. Also, it's not terribly important that the wrap breaks at a space in the text. This query is for inhouse viewing only.
If it must be done in a form in DS view I'll do it that way, but for ease of use I'd rather not have to click or doubleclick on anything, if possible. Mouse-free, you know.

Hope this is making sense and I really appreciate the insight you've provided.

-Shawn

[FishVal]

Thanks to all for the quick replies.
I am going to try using the query as the Record Source for a form, done in datasheet view. As I said, I'm a little rusty with access, but I won't bother anyone with how to do that exactly until I've taken a look at it myself.

I did try creating an untitled form with the CmdZoomBox VBA and it works, but I'm still wondering if I can just wrap one field (text box)of the query in DS view after say 25 characters. This portion of the record is used least often, so breaking up the "flow" of the fields is not an issue (in other words I don't mind if the other fields in each record skip a row. I can always max out the char width to minimize how often it happens. Also, it's not terribly important that the wrap breaks at a space in the text. This query is for inhouse viewing only.
If it must be done in a form in DS view I'll do it that way, but for ease of use I'd rather not have to click or doubleclick on anything, if possible. Mouse-free, you know.

Hope this is making sense and I really appreciate the insight you've provided.

-Shawn

Hi, Shawn.

Despite you wouldn't like to move a mouse :) I'd like to suggest you a rather pretty solution which however requires some mouse clicks. I'm using it in a case of forms with many fields.

• create a form for viewing in Datasheet mode including 3-4 fields just to recognize a record
• add to the form subform based on the same table and set it view to Single form

Now you have a Datasheet convinient to browse through expanding subform as soon as a close look to a record is needed.

[spk]

Hello,
Haven't been here for a few days but did want to express my sincere and utmost gratitude for the very helpful (and prompt!!) suggestions from everyone.
I did wind up using a form as the record source for the query, opens in DS view and used the posted VBA to doubleclick and bring up a zoom box showing entire field contents. Client thrilled to bits about this, doesn't mind the doubleclicking at all.
Thanks again to everyone — wouldn't have been able to do this without ya!!
-Shawn