470,593 Members | 2,304 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,593 developers. It's quick & easy.

How can I pick up part of a memo field from a Query and append it to the next record

I have a database wherein a lot of fields are updated by my team on a daily basis. When they are working on a lot of rows of same patient and certain fields have to be filled with same data, to save time I have made a Duplicate button. When this button is clicked, certain fields of the next record are auto-filled as duplicate of the last record. I bring the last record using a query "Aging 010109- Query For DLast," which is working fine. I am able to duplicate the fields which are to be duplicated completely; however, in "Remarks" field (which contains remarks for various dates) I just want to append only the most recently worked to the next record's "Remarks" and not the complete "Remarks." The separator can be date (MM-dd-yy). I used the following event procedure.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Duplicate_Click()
  2. 'when click Duplicate record and go to Next record and duplicate the following fields.
  3. DoCmd.GoToRecord , , acNext
  4. If ([DOB] = DLast("DOB", "Aging 010109- Query For DLast"))Then
  5. 'Duplicate the FollowupOn
  6. [FollowupOn] = DLast("FollowupOn", "Aging 010109- Query For DLast")
  7. 'Append recent Remarks to the next record.
  8. [Remarks] = DLast("Remarks", "Aging 010109- Query For DLast")
  9. This duplicates the complete Remarks……….
I would really appreciate if somebody can help me to duplicate just some lines of “Remarks” which are on the left of today’s date (DD-MM-YY). Following is just an example of how we put remarks.
Insurance denied the claim for medical records. Records retrieved and faxed ..pg 10-23-10
The claim is under review. Need to followup in 15 days ..pj 10-08-10
Thanks everybody for your help.

Oct 23 '10 #1
3 1438
8,800 Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. 'From a MEMO Field, will pick up from the Current Date on assuming the Date is in
  2. 'the Format mm-dd-yy as in 10-23-10.
  3. [Remarks] = Mid$(DLast("Remarks", "Aging 010109- Query For DLast"), _
  4.             InStr(DLast("Remarks", "Aging 010109- Query For DLast"), Format$(Date, "mm-dd-yy")))
Oct 23 '10 #2

Thanks for your post ADezii. I could find the answer. I would really appreciate if you can just see if that's fine. What I did was following:
'Get the last Remarks.
Dim FTempRemarks As String
FTempRemarks = DLast("Remarks", "Aging 010109- Query For DLast")
[Remarks] = Left([FTempRemarks], InStr(1, [FTempRemarks], vbCrLf) - 1) & vbCrLf & vbCrLf & Me.Remarks

I chose a line break instead of a date because that gave me more flexibility.

Thanks for your efforts and have a nice day,

Oct 25 '10 #3
8,800 Expert 8TB
You are quite welcome, and have a nice day yourself.
Oct 25 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by David C | last post: by
27 posts views Thread by Kim Webb | last post: by
1 post views Thread by Montana_Trader | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.