• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1328
  • Last Modified:

Auto Expand Notes Field in a Form

I have a form that pulls a subform called "notes." The notes are little memos etc. on each client. The subform shows a list of all notes associated with that particular client (it's a continuous form).

Two things:
1. I know how to sort oldest to newest and vice versa, but I want it to show me the newest note with the cursor in a new blank note. In other words, I want to be able to quickly add new notes, but also see the latest notes. Ordinarily, regardless of sort, the "new" note is always at the bottom of the list.

2. Say a note is longer than fits in the form. Can I have the field auto-expand? I tried setting the autoexpand property to yes but it didn't do anything. What am I missing?

I attached a sample database saved in 2003 format.
notes.mdb
0
platdesign
Asked:
platdesign
  • 6
  • 5
  • 3
  • +2
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
you can use to enter long notes,
in the dbl click event of the textbox

private sub note_dblClick(cancel as integer)

docmd.runcommand accmdzoombox


end sub

0
 
harfangCommented:
Access makes no provisions for this type of layout.

* You cannot show the "new" record at the top
* There is no mechanism to automatically resize controls on forms

The usual solution for your problem would be:

* Add an unbound control above the list of notes, and use the AfterUpdate event (or a  [Save Note] button) to add the new note to the list of notes.

* Provide a scroll bar in the control, so the user can read all the text with relative ease.

A more involved solution would be:

Concatenate all the notes in a single large memo field, perhaps with some horizontal bars to separate them, and perhaps with a standard header (date and author of the note). That way, each note in the list will take only the space needed.

This can look good, but it requires several programming steps, and becomes unusable if you have a large number of notes. It also doesn't address the requirement to show a blank empty notes box at the top.

You can also take a look at Stephan Lebans site: http://www.lebans.com/ where he does some amazing tricks, among others with auto-resizing text boxes. However, that is *really* complicated.

(°v°)
0
 
Jeffrey CoachmanCommented:
"1. I know how to sort oldest to newest and vice versa, but I want it to show me the newest note with the cursor in a new blank note. In other words, I want to be able to quickly add new notes, but also see the latest notes. Ordinarily, regardless of sort, the "new" note is always at the bottom of the list."
Well if your goal is to have new records at the top, and Previous new recods below it, as harfang states, this is cannot be done.

If you have a sequentially numbered field, then all the most recenct records should be sorted towards the bottom by default.
If not, sort by the sequential Field in Ascening order.

Is this what you mean by:
 "show me the newest note with the cursor in a new blank note. In other words, I want to be able to quickly add new notes, but also see the latest notes."
?

JeffCoachman
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
mwolfe02Commented:
"1. I know how to sort oldest to newest and vice versa, but I want it to show me the newest note with the cursor in a new blank note. In other words, I want to be able to quickly add new notes, but also see the latest notes. Ordinarily, regardless of sort, the "new" note is always at the bottom of the list."

I've run into this same issue before and came up with the solution below.  Essentially, what the code does is position the cursor in the new record, but still display as many existing records as possible prior to the new one.  So, the new note is still at the bottom of the list (no way around that) but you will always see the latest notes.

As for request 2, unless you want to get real nutty with the code (a la Mr. Lebans), there's no way to auto-expand the text box on the form.  Using the ZoomBox as cap1 suggested is probably your best bet there.

'---------------------------------------------------------------------------------------
' Procedure : ShowBottomOfSort
' DateTime  : 9/10/2007 09:00
' Author    : Mike
' Purpose   : Display new record at the bottom of a set of continuous records.  Useful
'             for forms that sort records in ascending date order; records can appear
'             in chronological order from top to bottom, but still show the most recent
'             records initially.
' Requires  : UsableHeight function
' Call From : Form Load event
' Notes     : This function cannot be called on the Form Load event of a form if that
'             form is used as a subform and its Current Event property is not empty.
'             When we move records on the subform, the current event is triggered,
'             any code found is executed, then Access returns the focus back to the
'             object that had the focus prior to the function call, not the form object
'             passed to the function.
' Workaround: Set focus to subform in code first, then call function.
'---------------------------------------------------------------------------------------
'
Function ShowBottomOfSort(Frm As Form)
    On Error GoTo Err_ShowBottomOfSort
    Dim MaxVisibleRecords As Integer, rsClone As Recordset
 
    'Turn off screen repainting to hide our recordjumping activities
    Application.Echo False
 
    MaxVisibleRecords = Int(UsableHeight(Frm) / Frm.Section(acDetail).Height)
    'subtract 1 to account for partially visible records
    MaxVisibleRecords = MaxVisibleRecords - 1
 
    Set rsClone = Frm.RecordsetClone
    If rsClone.RecordCount = 0 Then GoTo Exit_ShowBottomOfSort
    rsClone.MoveLast
    DoCmd.GoToRecord , , IIf(Frm.AllowAdditions, acNewRec, acLast)
    
    If rsClone.RecordCount + IIf(Frm.AllowAdditions, 1, 0) > MaxVisibleRecords Then
        rsClone.Move -MaxVisibleRecords + IIf(Frm.AllowAdditions, 1, 0)
        Frm.Bookmark = rsClone.Bookmark
        rsClone.MoveLast
    End If
 
    Frm.Bookmark = rsClone.Bookmark
    If Frm.AllowAdditions Then
        DoCmd.GoToRecord , , acNewRec
    End If
 
 
Exit_ShowBottomOfSort:
    Application.Echo True
    Exit Function
Err_ShowBottomOfSort:
    MsgBox Err.Description
    Resume Exit_ShowBottomOfSort
End Function
 
'---------------------------------------------------------------------------------------
' Procedure : UsableHeight
' DateTime  : 9/10/2007 09:01
' Author    : Mike
' Purpose   : Returns the usable height of the detail section of a form.
'---------------------------------------------------------------------------------------
'
Function UsableHeight(Frm As Form) As Long
    On Error Resume Next
    
    'We need this line in case the form does not have a header or footer section
    UsableHeight = Frm.InsideHeight
    'If the form does not have a header or footer section, this line will raise an error
    UsableHeight = Frm.InsideHeight - _
                   Frm.Section(acHeader).Height - _
                   Frm.Section(acFooter).Height
 
End Function

Open in new window

notes.mdb
0
 
Jeffrey CoachmanCommented:
Then Open the form to a new record:

On the OnOpen Event of the from, put code like this:
  DoCmd.GoToRecord , , acNewRec

This says that as soon as you open the form, it will create a new record, ready for you to start typing into.

Give it a try, is this what you wanted.

Or am I still not understanding something?

Jeff
0
 
mwolfe02Commented:
Jeff,

If you just open the form and go to a new record it will display the new record at the top of the continuous form if there are more records than can be displayed in the form.

For example, say the continuous form will display up to four records.  If there are five records to display, when access jumps to the new record, it will show only the new record on the form.  

Using the code I submitted, the same situation would have the continuous form displaying the last three records in the sort order plus the new record at the bottom of the form.  This fulfills the requirement of "I want to be able to quickly add new notes, but also see the latest notes."

-Mike
0
 
platdesignAuthor Commented:
Thank you all for your excellent input!

In order of responses:

capricorn1:
The zoom is cute, but not quite what I'm looking for. I might use it anyway, and if I do, I'll be sure to award points accordingly.

===============
harfang:
"Add an unbound control above the list of notes, and use the AfterUpdate event (or a  [Save Note] button) to add the new note to the list of notes."
Any chance you can implement this in the sample I submitted? I already have a scroll bar in my actual database (too large to submit, hence the sample).

===============
boag2000:
Hi Jeff - my notes actually have a date field...see the sample. I can sort by date but if I have 8 or 9 notes and sort so that the oldest are on top, then I don't see the newest notes unless I scroll. If I sort so that the newest are on top, I'll see the newest, but I have to scroll to the bottom to add a NEW note.

==============
mwolfe02:
Your solution is great except that it makes my screen flicker each time I change records. Further, it always puts the cursor in the new note field, whereas I want the cursor to stay wherever it was when I move to the next parent record. (e.g. if the cursor is in the phone field of the client form and I move to the next client, your solution makes the cursor jump to notes instead of staying in the phone field of the next client).Thank you all for your excellent input!

In order of responses:

capricorn1:
The zoom is cute, but not quite what I'm looking for. I might use it anyway, and if I do, I'll be sure to award points accordingly.

===============
harfang:
"Add an unbound control above the list of notes, and use the AfterUpdate event (or a  [Save Note] button) to add the new note to the list of notes."
Any chance you can implement this in the sample I submitted? I already have a scroll bar in my actual database (too large to submit, hence the sample).

===============
boag2000:
Hi Jeff - my notes actually have a date field...see the sample. I can sort by date but if I have 8 or 9 notes and sort so that the oldest are on top, then I don't see the newest notes unless I scroll. If I sort so that the newest are on top, I'll see the newest, but I have to scroll to the bottom to add a NEW note.

==============
mwolfe02:
Your solution is great except that it makes my screen flicker each time I change records. Further, it always puts the cursor in the new note field, whereas I want the cursor to stay wherever it was when I move to the next parent record. (e.g. if the cursor is in the phone field of the client form and I move to the next client, your solution makes the cursor jump to notes instead of staying in the phone field of the next client).
0
 
harfangCommented:
Your sample database seems corrupted. However, I could take a look at the design before it crashed.

* You don't need the fields ID and Name in the subform
* You don't need the black on black labels in the subform's header

Instead:

* Use a single field "Notes" in the subform (the rest is managed automatically)
* Add a new textbox in the black header area (white background)
* Add the event handler below

Good luck!
(°v°)
Private Sub txtNewNote_AfterUpdate
    With Me.Recordset
        .AddNew
        !Note = Me.txtNewNote
        .Update
    End With
    txtNewNote = Null
End Sub

Open in new window

0
 
Jeffrey CoachmanCommented:
platdesign,

Please remember that you are requesting a non-standard interface here.
It probably will never be "Perfect", without a great deal of work.

As I am seeing form your posts, you have some pretty specific requirements for this "new" form.

Is this design something you have seen and worked with before?
Or are we inventing something new for you here?


It just seems like mwolfe02 went through a great deal of trouble creating his custom function, then you state:
  "whereas I want the cursor to stay wherever it was when I move to the next parent record. (e.g. if the cursor is in the phone field of the client form and I move to the next client, your solution makes the cursor jump to notes instead of staying in the phone field of the next client)."
...All of this is new information not mentioned in your original post.
So it would have been extremely difficult for anyone to predict what you ultimately wanted.

This is the best that I could do without creating to much custom programming...


JeffCoachman

Access-EEQ-24173131-NewNoteOnTop.mdb
0
 
platdesignAuthor Commented:
harfang:
Your solution worked great! I had to tweak it a little to get it to work in my database because I needed it to also link the note to the parent client. I attached my code below. I sorted the form newest on top, and then added the Requery so it should put the newly added note at the top. I tried Refresh first, but it didn't work.


boag2000:
You're right that I have some specific ideas in mind, and I did my best to put those ideas in writing in my initial post.

*mwolfe02 didn't create his snippet for me - he wrote that this is a solution he's come up with in the past and simply copied it here. In fact, if you look closely he has a date of 9/10/2007 in the code.

* mwolfe02's solution was not quite what I anticipated, nor did it specifically meet the requirements of my original question. In an effort to "make it work" I made the comments above about the cursor. That was my attempt to make an imperfect solution work for me.

* Lastly, I tried your file, but it's not at all what I'm looking for. I'm not even sure how it works: I can't seem to add new notes, and I can't switch to design view to see your code.

Thank you all again for your input and help. It looks like harfang's solution is the best one for me.

Private Sub NewNote_AfterUpdate()
    With Me.Recordset
        .AddNew
        !Note = Me.NewNote
        .Update
    End With
    NewNote = Null
    Me.Company = [Forms]![Clients].[ID]
    Me.Requery
End Sub

Open in new window

0
 
platdesignAuthor Commented:
One more thing...

I noticed that in a report, when a note is longer than there is room for, it auto expands...that's what I was hoping to accomplish in my form. I guess it's just not possible.
0
 
harfangCommented:
Thank you for the feedback.

You do need the Requery, because the new record is appended at the end, and you want it at the top. For the sake of clarity, you can set the client ID in the first pass. Currently, you are first creating the record (updating the *field*) and then updating it again (using the *control*). I added two suggestions in the snippet below (use only one of them, they are redundant).

> I guess it's just not possible.

I'm afraid it isn't. In a normal form, it's only horribly complicated (see Lebans), in continuous forms, I've never seen it done. Luckily, it exists for reports, because, once printed, a vertical scroll bar isn't very useful. But in forms, you have to use the scroll-bar or the zoom feature to read the entire text.

(°v°)
Private Sub NewNote_AfterUpdate()
    With Me.Recordset
        .AddNew
        !Note = Me.NewNote
        !Company = Forms!Clients!ID   ' using the parent form name
        !Company = Me.Parent!ID       ' using the parent property
        .Update
    End With
    NewNote = Null
    Me.Requery
End Sub

Open in new window

0
 
platdesignAuthor Commented:
Hi harfang,
I actually tried that before adding it outside the WITH statement. It keeps giving me an error: "Run-time error '3421': Data type conversion error."

When I hit Debug, the line "!Company = Forms!Clients!!ID" is highlighted in yellow. When I rollover it says "!Company = Null"

Ideas?
0
 
harfangCommented:
It's strange that your version works, and not mine... "!Company = Null" is logical, it means that this field has no default value and is thus Null on a new record. But I don't know why you would get a data type conversion error. Are both fields numeric or both fields text? This doesn't explain why you don't get the error with "Me.Company", it should be the same. I'm missing something here. Just keep your working solution in that case, it seems easiest...

(°v°)
0
 
platdesignAuthor Commented:
Could it be because the "company" field isn't actually in the subform? It updates the record in the table, but the field doesn't display in the subform itself.

In order to get it working my way I had to add the ID field to the form and then set it invisible. Ideally it would pull the ID from the parent table but I couldn't get that to work so I used this bandaid solution instead.
0
 
harfangCommented:
I see. Access normally enforces a strange rule: it names the control using the field name. If your subform's record source has a field "Company", and if you create a control for it, it will also name it "Company". This creates some confusion between the field and the control. When you write "Me.Company", it refers to the control, and only to the field if there is not control named the same...

Again, if your solution works, let's leave it at that...

(°v°)
0
 
platdesignAuthor Commented:
That's why I figured if I left it in the WITH statement, Access would assume I was referencing a control, not the field name. I was hoping there was a way to reference the source table's field inside the WITH statement, and I'm sure there is, but it's not worth the time trying to figure it out right now...

Thanks again!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 6
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now