Inserting text into a memo field Results

Forgive me if this question has already been asked and solved. Just point me to the thread.

Basically what I want to do is add a specific piece of text to a text field / memo field (at the point where the cursor is) on the click of a button. I then want the cursor to be active at that point so I can continue typing without having to click back into the text field. The text could be hard coded or even double-click action from a separate lookup field

I tried Me.NameOfField = Me.NameOfField & "text to be added".

(I think that is the code. I am away from my home computer at the momemt so can't look it up)

This works perfectly except that it adds the text to the end of the existing text. If your cursor is on the next line or has gone down a couple of lines to start a new paragraph, the inserted text removes these because essentially the are NULL in terms of text. Also the focus has been lost so you have to click on the text / memo field again before you can type

I know this works because I have seen it, but it was a commercial programme and I could view the code.

Note these are shortcuts for commonly added pieces of text and I need several of these buttons to add different things.

I have also tried autokeys which works but the user has to remember which CNTL combination does what.

I presume Send Keys does it somehow but I can't get that to work

Any help would be appreciated

I have this database which has a comments field which often gets the same text added into it depending on what is in it. what I am trying to do is something similar to signiatures in outlook express.

What the end user wants to do is select from a dropdown box what text they want to add to the field and hit a button and it will insert it into the memo (comments) field in the form without overwriting any of the text they have in there already, they might want to just add one of the sentences or several.

Does anyone have any suggestions on how I might go about this?

/locomotion

Hello everyone - what I have is a a set of default text's that I need to be able to insert into a memo field - this is how I've set it up (Access 2000)

A table with the text fields in it called wordings
table comprises of
Id field
wordingnme (txt)
wording (Memo)
and I have a combo box on a form which looks up the wordingnme and hold this info - I need to have a button that will take this wordingnme and insert the wording that it relates to into the field of my record - Called Endor (memo)- I will have many text s say upto 50-60 but when I insert these into endor field I will probable only use say 5 or 6 at any one time on the record line so an example follows


line 45 in endor I wish to insert wordingnme "A" and then Wordingnme "B"
I also will do some free form type within this - any idea's

Hi everyone,

First off, thank you everyone for all the help you give people on this forum. Though this is my first post, I refer to this forum often and find a lot of the answers very helpful.

Here's what I'm working with: I work in a military police unit where we have to do "RAMs" (checks basically) of buildings on each shift. At the end of each shift, we have to produce a blotter, which is a record of everything that happens during the shift. The Access report I'm using (rptBlotter) pulls data from my continuous form "frmBlotterEntries" and displays it. I have the RAMs entered into a table (tblRAMs) with a starting time, and ending time, and person's name (the person conducting the RAM) for each record. I'd like to pull the RAMs from the RAM table, format them into a string, and insert them into the memo field of a new record in frmBlotterEntries, like this:

RANDOM ANTITERRORISM MEASURES
0500: Initiated by SSgt Smith. 0600: Terminated, all in order.
0900: Initiated by TSgt Johnson. 1000: Terminated, all in order.
1400: Initiated by SrA Brown. 1500: Terminated, all in order.

The code I have now goes to a new record on the continuous form, then performs a dlookup to get info from tblRAMs. It gets the ID (autonumber) from the first record retrieved, then increments it by 1 and loops, each time setting the memo field [Description] on frmBlotterEntries to Me.Description = Me.Description plus the info obtained from dlookup, a bit of formatting, and a line break. The first part of the loop checks to see if dlookup returns a null value and if it does, it exits the loop.

This works OK, however, it poses a few problems: The main problem is that if I sort by something other than the autonumber ID, I can't use the ID to increment and move to the next record with dlookup, which is the only (artificial) way to "movenext", if you will, when performing a dlookup.

Here's my question: Is there a way I can retrieve the data from tblRAMs using multiple recordsets, one for the tblRAMs records I'm retrieving and one for the continuous form that has the memo field that I want to format this text and insert it into? This way I can sort the records and still "movenext" without everything being out of order.

One caveat: I know nothing about recordsets, it is entirely unfamiliar territory for me.

Here's the code I have so far:


	Code:
	
Private Sub Command124_Click()

DoCmd.GoToRecord , , acNewRec
Me.Title = "RANDOM ANTITERRORISM MEASURES"
Me.Time = IIf(Forms![frmMain]![Shift] = "2", "1700", "0500")
Dim strRAMInit As Variant
Dim strRAMTerm As Variant
Dim i As Integer
i = DLookup("[ID]", "qryRAMs")
Do
    strRAMInit = DLookup("[Initiated]", "qryRAMs", "[ID] = " & i)
    strRAMTerm = DLookup("[Terminated]", "qryRAMs", "[ID] = " & i)
    If IsNull(strRAMInit) Or strRAMInit = "" Then
            Exit Do
    End If
        Me.Description = Me.Description & Format(strRAMInit, "hhnn") & ": Initiated by " & [ConductedBy] & ".  " &
Format(strRAMTerm, "hhnn") & ": Terminated, all in order.  "  & vbCrLf
        i = i + 1
Loop

End Sub



When I export my Remedy data to a CSV file, it automatically removes the CR/LF (not sure why). As a result, when I import the CSV file into an Access table the field becomes one LONG line of text. FYI, the import specification has that particular field set up as a memo field.

Here is an example of the contents of the memo field in one record:

2012-06-07 11:16:00 PCOLLINS Reviewed for completeness and forwarded to Charles for approval. 2012-06-12 14:19:57 PCOLLINS Status update from Anita Osborne (RCMP-SSC Business Management Office), "On Marty's desk for approval"

I want to insert a CR/LF right before each new time and date stamp within a single memo field, except for the very first occurance. The memo field is called "NBS Update", and the table is called "Remedy CCRR Data"

Any suggestions?

Hi,
I am working with Access 97 and experiencing difficulty in 97 returned errors when trying to perform a left function on a returned memo field. Details are below. Has anyone experienced this and have a workaround for it? Do you know if this is a known error in 97?
The purpose of this query is concatenate a series of comments registered against a specific demand id in one field (fConcatFld) which works fine in 2003 but only works in 97 when the total length of all comments being concatenated are less than 256.
Query
SELECT DemandNo, DemandDate, Comments FROM tblTemp WHERE (False)
UNION ALL SELECT DemandNo, DemandDate, Left(fConcatFld("tblDemandProgress","DemandNo;Dema ndDate","DemandProgress","String;DateTime",[DemandNo] & ";" & Format([DemandDate],"dd-mmm-yy"),"DemandProgressDate"), 4000) AS Comments
FROM qryEPMExtract_GetDistinct_Demands;

fConcatFld returns a single concatenated string to the query (details of fConcatFld below). Many of the concatenated strings returned by 'fConcatFld' are larger than '255', which means storing the comment in a text field would be inappropriate due to its length of 255 with a memo field better (storage of 2.5GB).
Access by default uses the data type of the field from which the comments are derived from (which in the database in this case is a text field) as the data type for the field the comments will be inserted into. To avoid automatic truncation of concatenated comments to the database comments field type (225 length) we have converted the field to a memo by performing a union with a temporary table (tblTemp) which contains an empty comment field of type memo.
This works successfully with concatenated comments of up to 4000 in Access 2003 but only with concatenated comments of total length of up to 255 in 97. When this query is executed in 97 the function executes, concatenating the first group of comments associated with a unique ID, but as soon as control is handed back to the query the following application error is thrown:
MSACCESS.EXE - Application Error
Instruction at 0x77fcbee8 referenced memory at 0xfffffffe. Memory could not
be "read"
When the argument in the 'LEFT' function (shown in query above) is reduced to

Hello people.

Im just after a bit of advice please??

I have created an audit trail for data held on a form so that when a field is changed it adds a line of text to a memo field with the date of the change and the old and new field values. The problem I have is that each time something is changed the line of text is added beneath the existing text in the memo field. This means that for users to view the most recent change they have to scroll all the way down to the bottom of the memo field. Is there a "Quick" way of me having the most recent entry first.
I am using the vbnewline command when adding the text. A sample is shown below:

Dim Response As Integer
Dim AnotherItem As Integer
Dim sqlstr As String

Set Myform = Forms![DataDetail]
Producer = Forms![ListOfProducers(Existing Data Item)]![LstProducers].Column(1)

Response = MsgBox("Are you sure you wish to add a Producer?", vbYesNo, "Data Dictionary v1.0")
sqlstr = "INSERT INTO DataProducers ( Data_Id, Producer_Id ) SELECT Data.Data_Id, Producers.Producer_Id FROM Data, Producers WHERE (((Data.Data_Id)=[Forms]![DataDetail]![Data_ID]) AND ((Producers.Producer_Id)=[Forms]![ListOfProducers(Existing Data Item)]![LstProducers]));"
If Response = 6 Then
DoCmd.RunSQL sqlstr
Myform!LstHistory = Myform!LstHistory & vbNewLine & "Changes made on " & Now & ""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "The Producer '" & Producer & "' was added to this data item"""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "-----------------------------------------------------"
MsgBox "Producer has been successfully added.", vbInformation, "Data Dictionary v1.0"


Thanks folks...

hi all, i have looked around the forum for an answer to my problem but have not found a solution, so here goes!

I'm using a passthrough query to collect some text data off my works sql server. The fetch works fine however, the text string result is often greater than 255 characters long. I note that as soon as the 255 character threshold is met, the query will insert the remainder text on the next line of the query. My question is how do i read all lines in the result and insert the complete result into a "memo field" within my table? I want to use an update query or similar?

Any ideas? Thanks

I have an application that has a text box on a form that points to a memo data type.

On the form, I wish to allow the users to insert the system date with a short-cut key. (Trying to use Ctrl-D).

I have coded a Keypress Event that checks for the Ctrl-D. "Current" code is below:
===============================
Private Sub PM_Comments_KeyPress(KeyAscii As Integer)
Dim MyString As String
Dim MyDate As Variant

'Check to see if user pressed Ctrl-D
If KeyAscii = 4 Then

MyDate = Date
MyString = Format(MyDate, "Short Date") & ":"
SendKeys ("") ' Perhaps I need to dump the Ctrl-D in the buffer???
SendKeys (MyString)

End If

End Sub
==================================
I say current because I have tried MANY variations. The code is executing properly on the Ctrl-D. I have watched the variables in debug mode and they contain what I want, namely "mm/dd/yy:" for the current date. Problem is, it inserts the TIME in the memo field. And it is the system time, not the integer of the date formatted into the time.

Crazy thing is, in debug mode, the darn thing will do what I want and insert the date in the VB editor page. Just won't do it in the memo field on the form.

NCWalker

Hi Folks
On a report, I would like to insert text from word documents

I am trying to generate school reports as follows.
I have 1 subjectstudent per page.
On the back of each page, I need to show generic information about the subject.

I tried importing this info into a memo field, but there are 2 problems.
1. I lose the formatting.
2. I sometimes get a "record too large" error when the info is more than 2000kb

I thought it would be great if I could put the subj info into a seperate DOCX file and then put it into the report (subj footer - force new page before and after)

Is this possible?
If so, how would I refer to the document that is required?
Each doc will be named by the subject code.

Thanks

Currently I have a text box in a report and i was wondering if it is possible to change the height, width, top and left values and the size of the text within the text box based on a string in another textbox. The strings can be TTC, DVM, SYF etc. and are not long or complex.

The reason for this is that I insert text files into a memo field which is linked to the text box on the report. The text files are of different sizes and i would like them to be centered on the report. I cannot just centre the text as this messes with its format.

Dear,

I have encountered the following problem in Access 2002 with Memo fields

Start with a table (The_Table) with a Memo field (The_Memo_Column)
I would like to populate this column from a edit field (The_Edit_Field) located into a Form (The_Form)

When I enter a large sentence in the edit field (seems more than 512 char ????)
and When I manage the memo field through a insert SQL query The text stored in the Memo field
is truncated and ends with strange āई characters sequence

By the way, when I manage the information directly through the standard Ms-access method


Action using a SQL query

I create a insert query

The_Query=
INSERT INTO The_Table (The_Memo_Column) VALUES (Forms!The_Form!The_Edit_Field);

On click on a button, I call the query as follow

DoCmd.SetWarnings False
DoCmd.OpenQuery The_Query, acNormal, acEdit
DoCmd.SetWarnings True
Action using the ms-access standard method

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


Thanks in advance for your help !


Aloha

PYC

I have text in a number of fields that I want to insert into a text box on a report. The size of the text box is large enough to accommodate, but I want each line to have a linefeed (or carriage return) before it.

The report is based on a query. I created a field in the query that consists of something like:...txtText1 & CHR (10) & txtText2 & CHR (10) txtText3 AS MYTEXT.

What I did was to set the control source on the text box to MYTEXT, hoping that there would be nice linefeeds in between the fields. Unfortunately, all I got were some funny-looking boxes.

What's interesting is that you CAN insert a linefeed into a memo field and that linefeed will display properly. I just don't know how to force one in there to separate lines of text.

Any ideas?

SHADOW

Hi Everyone,
I'm new to VBA programming and am having difficulty writing data to a memo field in Access using VBA. I have a very simple DB with a fieldname/ fieldtype list: [ID] = autocount; [descriptor] = memo; [data ] = memo; [recordnum] = integer; [desc-number] = integer. I want to add some extra text to [descriptor] and write it back into the field. The test code below allows me to write to one of the integer boxes and the merged text appears in the Msgbox but I nothing writes into the [descriptor] field. The same code writes the same thing to every record if I insert " 'test' " into SQLquery2 instead of the mergetext variable. What am I doing wrong?

thanks,
John H

Public Sub test1()

Dim C1, mergedcode As Integer
Dim mergetext, newtext, SQLquery1, SQLquery2 As String
C1 = 1
mergedcode = -6
While C1 < 10
newtext = DLookup("[descriptor]", "working-tbl-copy", "[ID] = " & C1)
mergetext = newtext & C1
MsgBox (mergetext)
SQLquery1 = "UPDATE [working-tbl-copy] SET [working-tbl-copy].[desc-number] = " & mergedcode & " WHERE [working-tbl-copy].[ID] = " & C1
SQLquery2 = "UPDATE [working-tbl-copy] SET [working-tbl-copy].[descriptor] = " & mergetext & " WHERE [working-tbl-copy].[ID] = " & C1
DoCmd.SetWarnings False
DoCmd.RunSQL SQLquery1
DoCmd.RunSQL SQLquery2
DoCmd.SetWarnings True
C1 = C1 + 1
Wend

End Sub

Hi,

I am having a problem with the attached code. I have a database with 2 main tables that are joined on a 1:Many relationship. The master table contains faults raised and the other table contains notes. There may be multiple notes per fault. What I want to do is be able to run a query that shows the fault and merges all of the notes for that fault into one continuos string of text. I managed to find some code that does this but my problem is that if any one of the note fields associated with a record has more than 255 characters the record is not exported to a table that is created to hold the fault and all of its concatenated notes. The note field's are all Memo fields and the field that they are being exported to is a Memo field so I'm not sure why this is happening. The code is shown below.

ReplaceStr and SQLFixup function are to replace unescaped quotes. Hijacked from Microsoft.

FixTable and CreateTables are the real workings of it all. CreateTables simply deletes the old table to hold the exported info and recreates it with the relevant field settings. FixTable is the workhorse which uses an SQL statement to insert values taken from the two main tables into the holding table. As well as this it concatenates the notes if the fault Reference (RefID) is the same.

I can post the database if required with some sample records to show the problem happening.

Option Compare Database
Option Explicit

_______________________________________________

Public Function ReplaceStr(TextIn, ByVal SearchStr As String, ByVal Replacement As String, _
ByVal CompMode As Integer)

Dim WorkText As String, Pointer As Integer

If IsNull(TextIn) Then

ReplaceStr = Null

Else

WorkText = TextIn
Pointer = InStr(1, WorkText, SearchStr, CompMode)

Do While Pointer > 0
WorkText = Left(WorkText, Pointer - 1) & Replacement & Mid(WorkText, Pointer + Len(SearchStr))
Pointer = InStr(Pointer + Len(Replacement), WorkText, SearchStr, CompMode)
Loop

ReplaceStr = WorkText

End If

End Function

_______________________________________________

Public Function SQLFixup(TextIn)

SQLFixup = ReplaceStr(TextIn, "'", "''", 0)

End Function

_______________________________________________

Public Function FixTable() As Boolean
On Error Resume Next

Dim db As DAO.Database, rs As DAO.Recordset, sSQL As String
'Dim strColumn1 As Long, strColumn2 As String

Dim strCol1 As Long 'RefID
Dim strCol2 As String 'Area
Dim strCol3 As String 'FullIssue (Issue Description & BuildRaised & VersionRaised)
Dim strCol4 As String 'Attachment
Dim strCol5 As String 'Priority
Dim strCol6 As Date 'DateRaised
Dim strCol7 As String 'FullNote (NoteType & NoteBy & NoteDate & Build & Version & Note
Dim strCol8 As String 'CurrentStatus

Set db = CurrentDb()
Call CreateTables(db)

sSQL = "SELECT tblIssue.RefID, Area, IssueDescription & ' [' & BuildRaised & ', ' & VersionRaised & ']' AS FullIssue, Attachment, Priority, DateRaised, '[' & NoteType & ', ' & NoteBy & ', ' & NoteDate & ', ' & Build & ', ' & Version & '] ' & Note AS FullNote, CurrentStatus" _
& " FROM tblIssue LEFT JOIN tblNotes ON tblIssue.RefID = tblNotes.RefID" _
& " ORDER BY tblIssue.RefID"

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst

strCol1 = rs!RefID
strCol2 = rs!Area
strCol3 = SQLFixup(rs!FullIssue)
strCol4 = rs!Attachment
strCol5 = rs!Priority
strCol6 = rs!DateRaised
strCol7 = SQLFixup(rs!FullNote)
strCol8 = rs!CurrentStatus

rs.MoveNext

Do Until rs.EOF
If strCol1 = rs!RefID Then
strCol7 = strCol7 & Chr(13) & rs!FullNote
Else
sSQL = "INSERT INTO tblExport (RefID, Area, FullIssue, Attachment, Priority, DateRaised, FullNote, CurrentStatus) VALUES('" & strCol1 & "','" & strCol2 & "','" & strCol3 & "','" & strCol4 & "','" & strCol5 & "','" & strCol6 & "','" & strCol7 & "','" & strCol8 & "')"
db.Execute sSQL
strCol1 = rs!RefID
strCol2 = rs!Area
strCol3 = SQLFixup(rs!FullIssue)
strCol4 = rs!Attachment
strCol5 = rs!Priority
strCol6 = rs!DateRaised
strCol7 = SQLFixup(rs!FullNote)
strCol8 = rs!CurrentStatus
End If

rs.MoveNext

Loop

' Insert Last Record

sSQL = "INSERT INTO tblExport (RefID, Area, FullIssue, Attachment, Priority, DateRaised, FullNote, CurrentStatus) VALUES('" & strCol1 & "','" & strCol2 & "','" & strCol3 & "','" & strCol4 & "','" & strCol5 & "','" & strCol6 & "','" & strCol7 & "','" & strCol8 & "')"
db.Execute sSQL
End If

Set rs = Nothing
Set db = Nothing

End Function

_______________________________________________

Private Function CreateTables(ByRef dbs As DAO.Database)

On Error Resume Next
Dim sSQL As String

' Delete Table, if exists
If DCount("*", "MsysObjects", "[Name]='tblExport'") = 1 Then
DoCmd.DeleteObject acTable, "tblExport"
End If

sSQL = "CREATE TABLE tblExport (RefID Long, Area Text, FullIssue Memo, Attachment Text, Priority Text, DateRaised DateTime, FullNote Memo, CurrentStatus Text)"
dbs.Execute sSQL

End Function

_______________________________________________

Hope someone can help with this problem as it is driving me up the wall!

Thanks in advance!

Derek

I am attempting to create a select query for use as the row source for a combobox to control data being entered into a memo field.

The table contains two fields - ID (Longint) and literatureRef (memo)

The query
"SELECT DISTINCT tblLiteratureRef.literatureRef
FROM tblLiteratureRef;"
results in an error message "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."

SELECT DISTINCTROW tblLiteratureRef.literatureRef
FROM tblLiteratureRef; does not give the error.

Similar queries on tables with an identical structure (but different field names) work as expected.

The only clue I can find is that the longest lieratureRef record is 393 characters long and suspect that Access may be creating a temporary table using a text field which would be subject to the 254 character limit.

Can anyone verify this? or suggest a way around the problem?

I have a form with a unbound text field which when a user inserts text and then removes the focus from that field, the text gets inserted into a memo field. By using:

Me.[NOTES] = Now() & " " & Environ("UserName") & " - " & Text27365 & vbCrLf & Me.[NOTES]

However, I have noticed an issue with this, in respect that when the user enter text then moves to the next record, the previous entered text is still there, This could then cause this old text to get inserted into the wrong record. How can I set this field to blank once it I have moved to the next record or closed the form?

Hi all,

I have an issue that really has me puzzled. In short, i have a memo field where the cursor automatically moves back to the start of the field everytime i start typing additional text into the field. I changed the cursor behavior to automatically move it to the end of the field by going to Tools/Options/Keyboard/Behavior Entering Field, thinking that would help but i still have the same issue.

Some Background: I have a legal database with a memo field called "DEFandTERMS" that houses predefined legal definitions and terms . When the user enters this memo field, a list box called "NamesList" becomes visible and displays all predefined definitions and terms for them to chose to insert into the memo field.

The code on the "DEFandTERMS" OnEnter event is:


	Code:
	Private Sub DEFandTERMS_Enter()
If MsgBox("Do you want to add predefined definitions?" & _
                    vbCrLf & vbCrLf & "Would you like to see them now?", _
                    vbYesNo, "Terms and Definitions") = vbYes Then
    Me.NamesList.Visible = True
    Me.testmultiselect.Visible = True
    Me.Box435.Visible = True
    Me.Label434.Visible = True
    Me.List436_Label.Visible = True
    Me.DEFCategory.Value = Null
    Me.DEFCategory.Value = Me.DEFandTERMS.Value
Me.testmultiselect.SetFocus
 End If
End Sub

The listbox is setup with a view of a field called "CategoryAbr". The definitions are housed in a field called "CategorySub" and there is a third field called "DefCateogry" that categogizes them from a-z. They are housed in a table called "DEFCategoryAll":

List Box
----------------------------------------------------
Name : NamesList
Control Source: DEFandTERMS
Row Source Type : Table/Query
Row Source : SELECT DEFCategoryAll.CategorySub, DEFCategoryAll.CategoryAbr FROM DEFCategoryAll;
Multi Select : Extended


	Code:
	no event procedures on listbox

Once the definitions have been selected, the user then clicks a command button to add the definitions to the memo field.

Using this code:


	Code:
	Private Sub testmultiselect_Click()
Dim oItem As Variant
    Dim sTemp As String
    Dim iCount As Integer
    

    iCount = 0
            
    If Me!NamesList.ItemsSelected.Count  0 Then
        For Each oItem In Me!NamesList.ItemsSelected
            If iCount = 0 Then
                sTemp = DEFCategory.Value & sTemp & vbCrLf & Me!NamesList.ItemData(oItem) & vbCrLf
                iCount = iCount + 1
            Else
               
                sTemp = DEFCategory.Value & vbCrLf & sTemp & vbCrLf & Me!NamesList.ItemData(oItem) & vbCrLf
                iCount = iCount + 1
            End If
        Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub
    End If
    Me!DEFandTERMS.Value = Trim(sTemp)
    Me.DEFCategory.Value = Null
End Sub


I also setup an unbound memo field called "DEFCategory" to be a brief catch all for definitions already housed in the "DEFandTERMS" memo field and any fromt he list box that the user chooses to add later. It has no relation to the the field in the table that the listbox is pulling from. Long story... but i went through several designs before finding this one that semi-works. Any help is greatly appreciated.

Ray

I've also posted this in this thread, but figured it belonged here too. The original poster and others did a wonderful job on this. However, I don't really like the fact that the audit trail was being appended to a memo field. It works very well, however it's not my preference.

My preference is to store the audit trail in a separate table. So, here's what I came up with that seems to work quite well. You may or may not want to use the same fields I do so amend it to your liking.



	Code:
	Option Compare Database
Option Explicit

Dim dbs As DAO.Database
Dim sAuditTable As String
Dim sSQL As String

Dim sTable As String        'Table where the record is being edited
Dim CTL As Control          'The control in the form being edited
Dim sFrom As String         'Original Data in the control
Dim sTo As String           'What the original data was changed to

Dim sPCName As String       'Name of the PC that is being used
Dim sPCUser As String       'Name of the User on the Networked PC
Dim sDBUser As String       'Name of the Database User
Dim sDateTime As String     'Date and Time of the change

'===========================================================================================
'
'   This Audit Trail will track changes to existing records.
'   In the "Before Update" event of the FORM enter the following:
'
'       Call AuditTrail(Me.Form, [RecordID])
'
'   Make sure to create a table called "tbl_AuditLog" and have the following fields:
'   (A table will be created automatically if it does not exist)
'
'       1. RecordID (This is a unique number)
'       2. txt_Table (This is the table where the record was changed)
'       3. lng_TblRecord (This is the RecordID number from the record being changed)
'       4. txt_Form (This is the form being used to edit the record)
'       5. txt_Control (This is the data entry control (field) that was edited
'       6. mem_From (This is the original data in the control (field)
'       7. mem_To (This is what the original data was changed to)
'       8. txt_PCName (This is the name of the PC used to edit the record)
'       9. txt_PCUser (This is the name of the user logged onto the PC)
'       10. txt_DBUser (This is the name of the person looged on to the databse if used)
'       11. dat_DateTime (This is the date and time the record was edited.)
'
'
'   The inspiration behind this code is from:
'       1. http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
'       2. http://www.access-programmers.co.uk/forums/showthread.php?t=44231
'
'
'   Be sure to enable the "Microsoft DAO 3.6 Object Library" Reference
'
'============================================================================================
    


Public Function AuditTrail(frm As Form, lngRecord As Long)
On Error GoTo Error_Handler
    
    '----------------------------------------------------------------------
    '   Skips this procedure if a new record is being entered in the form
    '----------------------------------------------------------------------
    If frm.NewRecord = True Then
        Exit Function
    End If
        
    '----------------------------------------------------------------------
    '   Checks to see if the tbl_AuditLog Exists
    '   Creates the table if it does not exist
    '----------------------------------------------------------------------
    Set dbs = CurrentDb
    dbs.TableDefs.Refresh
    
    sAuditTable = "tbl_AuditLog"
    On Error Resume Next
    If IsNull(dbs.TableDefs(sAuditTable)) Then
        'Table does not exist
        On Error GoTo Error_Handler
        sSQL = "CREATE TABLE tbl_AuditLog([RecordID] COUNTER PRIMARY KEY, [txt_Table] TEXT(50), [lng_TblRecord] LONG, " & _
                "[txt_Form] TEXT(50), [txt_Control] TEXT(50), [mem_From] MEMO, [mem_To] MEMO, [txt_PCName] TEXT(50), " & _
                "[txt_PCUser] Text(50), [txt_DBUser] Text(50), [dat_DateTime] DATETIME);"
        DoCmd.SetWarnings False
        DoCmd.RunSQL sSQL
        DoCmd.SetWarnings True
    Else
        'Table Exists. Do Nothing
        On Error GoTo Error_Handler
        
    End If

    Set dbs = Nothing
       
     
       
    '----------------------------------------------------------------------
    '   Runs through each control on the form and checks for edits/changes
    '----------------------------------------------------------------------
    For Each CTL In frm
    
        Select Case CTL.ControlType     'Only checks data entry type controls.
            Case acTextBox, acComboBox, acListBox, acOptionGroup
                
                sFrom = Nz(CTL.OldValue, "Null")
                sTo = Nz(CTL.Value, "Null")
                
                If sFrom  sTo Then
                
                    '-----------------------------------
                    '   Gets the required Info
                    '-----------------------------------
                    sTable = frm.RecordSource
                    sPCName = Environ("COMPUTERNAME")
                    sPCUser = Environ("Username")
                    sDBUser = "Me"      'Get Username from the database login
                    sDateTime = Now()

                    sSQL = "INSERT INTO tbl_AuditLog ([txt_Table], [lng_TblRecord], [txt_Form], [txt_Control], " & _
                           "[mem_From], [mem_To], [txt_PCName], [txt_PCUser], [txt_DBUser], [dat_DateTime]) " & _
                           "VALUES ('" & sTable & "', '" & lngRecord & "', '" & frm.Name & "', " & _
                           "'" & CTL.Name & "', '" & sFrom & "', '" & sTo & "', '" & sPCName & "', " & _
                           "'" & sPCUser & "', '" & sDBUser & "', '" & sDateTime & "')"
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL sSQL
                    DoCmd.SetWarnings True
    
                End If
        End Select
    Next CTL
    
Error_Handler_Exit:
   Exit Function

Error_Handler:
    MsgBox ("Error No: " & Err.Number & vbCrLf & vbCrLf & "Error Description: " & Err.Description)
    Err.Clear
    Resume Error_Handler_Exit

End Function



All:

I am trying to build a report that would look like this:

Your invoice is XX days past due. Please send it by mm/dd/yyyy to avoid additional fees.

I realize that I could create a report that would hard code the text into a textbox using a value of ="Your invoice is " & [PastDue] & " days past due"...
I also realize that I could break it up into multiple text fields with the info before and after each field (not very flexible)

But since I am going to generate this letter in multiple languages, and change the text depending on the lateness of payment, I would like to store the body of the letter in a memo field and have the [PastDue] calculated out with each letter. In effect, I would like to store the info that would go into a hardcoded textbox in my table and then calculate it at report time.

TIA,

Les Z


Not finding an answer? Try a Google search.