cursor position in text box.

[PaulG]

I have a form with a several text boxes on it and the last text box is set to memo so I can store a large amount of notes about each record. Does any one have an idea as to how to make the cursor go to the end of the line after the last word displayed in that text box?

 

[JANR]

Try this:

Private Sub [COLOR=red]Memo[/COLOR]_GotFocus()
Me.[COLOR=red]Memo[/COLOR].SelStart = Len(Me.[COLOR=red]Memo[/COLOR]) +1
End Sub

Change whats marked in red with your own controlname

JR

 

[PaulG]

Thank you that did it.

 

[JANR]

Glad to help, but you might want to trapp any NULL or ZLS memo fields. If a memo field is "empty" this will give you an error, to trap this modify the code to this:

Private Sub [COLOR=red]Memo[/COLOR]_GotFocus()
If Nz(Me.Memo, "") = "" Then
     Me.Memo.SelStart = 0
Else    
     Me.[COLOR=red]Memo[/COLOR].SelStart = Len(Me.[COLOR=red]Memo[/COLOR]) +1
End If
End Sub

JR

 

[boblarson]

And I'm going to throw a different look into it. If you are storing different notes together then you should really be using a junction table and storing the notes as individual notes. You can concatenate them for viewing but you really should be storing them as individual records.

 

[PaulG]

And I'm going to throw a different look into it. If you are storing different notes together then you should really be using a junction table and storing the notes as individual notes. You can concatenate them for viewing but you really should be storing them as individual records.

Wouldn't my Database become too large? I all ready have thousands of records with hundreds of notes for each record and I have to update it with new financial information every quarter.

 

[boblarson]

Wouldn't my Database become too large? I all ready have thousands of records with hundreds of notes for each record and I have to update it with new financial information every quarter.

Nope, not significantly and it would make it easy to get the notes that you might want easier. The problem with storing them in a single record concatenated is that they pretty much become useless to anyone. Also, just so you are aware - it is good to keep your memo fields in a separate table anyway to avoid corrupting the rest of your records.

 

[PaulG]

Nope, not significantly and it would make it easy to get the notes that you might want easier. The problem with storing them in a single record concatenated is that they pretty much become useless to anyone. Also, just so you are aware - it is good to keep your memo fields in a separate table anyway to avoid corrupting the rest of your records.

Sounds Logical, unfortunately I have no Idea how to view all of the different records in one big text box (memo box) at the bottom of my form after I have stored them all.

 

[boblarson]

What I would do is use a subform to display all notes associated with that certain record. But if you really want them concatenated you can use a bit of code to do that (although, it can be a little slow depending on how many records there are):

Private Sub Form_Current()
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select NoteFieldName From YourTableName WHERE RecordIDFieldName = " & Me!RecordIDField Order By DateTimeFieldName Desc"

Set rst = CurrentDb.OpenRecordset(strSQL)

With rst
   Do Until .EOF
       Me.YourTextBoxNameHere = Me.YourTextBoxNameHere & !NoteFieldName & vbCrlf & "____________________" & vbCrLf
       .MoveNext
   Loop
.Close
End With
Set rst = Nothing

You would need to replace NoteFieldName in this code with the actual name of the field in the table which stores the note. And then you would need to have a RecordIDFieldName which would be the Foreign Key (long integer) and would store the primary key of the main form/table for which the note is associated. And in your note table you should have a date/time field (I used DateTimeFieldName as the example name) which has its default set to =Now() and is a date/time field with General as the format. That way everytime a note is added it will have a date/time stamp which then can be used to order the notes in the order in which they were added.

 

[PaulG]

Thanks so much, I will Try it.