al****@msn.com. au wrote:
A little while ago Ron P kindly responded to my inquiry on how to copy
the first line of memo field into a text field, using a query:
left([MemoField],instr(1,[MemoField],vbCrLf)-1)
This is great, but I would like to do this on a form, so that the
person entering the memo can use a button to paste the 1st line to a
title (text) field. I'm having trouble setting up the code to do this.
I'd also appreciate some ideas on how to get the 2nd and 3rd lines
copied to other fields.
Thanks
Alex
Here's some code you can modify. This opens up a table called Table3
and reads 2 records in this table that have memos. You can change the
count if you like.
It attempts to read and display the first 3 lines of the memo before
moving on to the next record. If there are only 1 or 2 lines, it
displays them then goes on to the next record.
It's possible that at the end of the memo the operator hits the tab key.
This will not have a CR/LF at the end.
When you read the memo in your form, in the AfterUpdate event you can
run the modified code I have provided. With the variable "i" you can
assign the result "s" string to Text1, Text2, or Text3 depending on
"i's" value.
You can copy this code into a code module. Then change the table name
and memo field name in the code below. It will run fine and demo how do
do what you want.
Sub abcdef()
Dim r As DAO.Recordset
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim s As String
'change Table3 to your table name
Set r = CurrentDb.OpenR ecordset("Table 3", dbOpenSnapshot)
Do While Not r.EOF
If Not IsNull(r!memoF) Then
k = k + 1 'record counter
'only check 2 records
If k = 3 Then Exit Do
'change all MemoF refs in this code to
'your memo field name
s = r!memoF 'assign to the string var
For j = 1 To 3
'find the first CR/LF
i = InStr(s, vbNewLine)
If i 0 Then
'it found a crlf
MsgBox k & " " & j & " " & Left(s, i - 1)
If i + 2 >= Len(r!memoF) Then
'there are no move cr/lf's
Exit For
Else
'we have more cr/lf's
s = Mid(s, i + 2)
End If
Else
'there was either 1 cr/lf or else this
'has no cr/lf left because of the tab
'key being pressed
MsgBox k & " " & j & " " & s
Exit For
End If
Next
End If
r.MoveNext
Loop
r.Close
Set r = Nothing
MsgBox "Done"
End Sub