471,594 Members | 1,923 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,594 software developers and data experts.

Pasting 1st line of memo field to a text field (revisited)

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

Jul 29 '06 #1
1 2755
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.OpenRecordset("Table3", 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

Jul 29 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by John | last post: by
2 posts views Thread by Kevin | last post: by
6 posts views Thread by Matt | last post: by
reply views Thread by Anwar ali | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.