I have a process which imports a txt file into a memo field of my TBLORIGINAL table Each file may have one record or it may have multiple records Every record begins (exactly) with
Quote:
FRE Order Order RAD ShipDate Customer City State Postal Code
For each record in the memo field, I need to create a new record into my TBLPROCESSED table
This can probably be done while it is importing but I need to keep the original file intact
I suppose I could copy and paste but there must a vba way of doing this
I have a problem that is Vista related. My business partner has just purchased a laptop with Vista and one of the problem areas is SendKeys. I don't have Vista and he is away at the moment so we only have part time communication so I am trying a few solutions in the hope of getting the problem fixed.
He is getting a message along the line of.....Access needs to be reinstalled because SendKeys is not available.....he does not has his MSOffice with him so that can't be tried at the moment, although everything else is working OK.
There are a few macros involved but I have converted then into VB Module and that works OK for me on XP, although it does not carry out all instructions but it basically works. But it will be a few days before he can try what I have sent him.
Note: I just notice that my conversion to VB module is not doing the SendKeys. No messages etc it is if the section covering SendKeys was not there. The example below was done with XP and macro. The SendKeys and the various other actions are all in the module. Edit...All is OK....my conversion of the macros to a module worked:D
So I would like to try another possible solution and that being to avoid SendKeys.
The situation involves a file note entry system. Each file note creates a different record and in each record there is a memo field with the file note that was made and of course time and date.
There is another table that holds one record for each person that has a file note made. Whether one file note is made or a 100 file notes there is only record.......The record is created when the first file note is made and the table will not accept duplicates on the persons ID number.
When a file note is made the system then opens the record in the other table and adds with SendKeys to the entry in the memo field on the "one record only table"
A form opened on the "one record" looks like this and as you can see the contents of the memo field from the "many records" have been combined into one. Can I do this without using SendKeys
5/01/2008 11:34:51 AM Test 1 The quick brown fox jumped over the lazy dog
5/01/2008 11:35:21 AM Now is the time for all good men to come to the aid of the party
5/01/2008 11:35:50 AM Why did John have to buy a computer with Vista?
Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
5/01/2008 11:37:38 AM zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz zzzzzzzzzzzzzzz zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz xxxxxxxxxxxxxxxxxxxxxxxxxxxx
tblSFDC (contact information) includes CustID (primary key) and a memo field "Notes"
tblTheCall (call information) includes CustID (relates to tblSFDC) and "activity data fields" ie "Live", Left Message" , Sent email".
tblTheCall may in some cases have multiple records for each CustID
I need to : Concatenate from tblTheCall Field Names and field values (that are not null) Insert into tblSFDC.Notes Where tblTheCall.CustID = tblSFDC.CustID
I have an Access database where we grade reports. At the end of the grading form I have a memo field [Comments] for the grader to enter comments about why the report received the grade [Grade]. Each report is graded by multiple people. So for example, ReportNum 08-001 may be graded by 5 people and therefore will have 5 records in the database. I need a report to show all the comments for all the reports where the grade is <80. I know how to do this except for how to display all the comments from the memo field in the 5 records on one report for that ReportNum.
I want to create an app that works like Wikipedia. for example any word that have an explain and that's explain available in my database that word become hyperlink text with different color and so if operator click on that word open a new form and show the explain.
Memo field is called [Notes] and data is like this...
5/05/14 - Perry was on another call. LM 2/05/14 - Perry only at centre in the mornings, need to speak to him before sending samples. 13/06/13 - Perry in a meeting. lm 30/05/13 - See Little Hampton Early Learning - s/w Perry, has already received sample and info 29/05/13 - s/w Aspi, said to cb tomorrow and speak to Perry
I want to find each date in the Notes field so I can split the memo field data into another table where the memo field will become multiple records that hold date, text and customer/prospect ID fields. The customer table was easy because there was a <Div> tag before each date. However in the Prospects table there are no tags so how to change my vba code to search for each date before I split off the data.
Here is the part of the VBA code I used to find the <Div> tag in the customer notes field. How to find each date in the memo field? The date is in d/mm/yy format?
If Not rst.EOF Then Do StrSplit = Split(rst![Notes], "<Div>") For x = 1 To UBound(StrSplit)
I have a User Defined Type which stores an array..I have 2 instances of this UDT, one storing matched data, the other storing exception data..I was to parse these arrays to an Excel output routine. However, the parameter variable contains no data when accessing it..
Here is my code:
Code: Private Type TOutputRow RACFID As String FullName As String Access As String LastLoggedIn As Date End Type
I am trying to provide the user a custom search feature. They want to enter a keyword or phrase and search 3 memo fields and filter the form base on the records found. they also want to be able to search the whole phrase or any part of the phrase.
I have a like expression for any part of the phrase but I when I set it up for whole phrase it doesn't work. Even if I run a simple query and use
For example: There's an acronym the user is looking for : ACA
If I set my query up like this: [field1] like "*ACA*" or [field2] like "*ACA*" or [field3] like "*ACA*"
it not only finds records with that acronym but it also finds records where that combination is found in a word, for instance vacate.
Our quotation tool, which is built in access 2003, has a memo field, which is extracted in a .txt file, to be uploaded by another system.
When the user starts the memo field with a blank line, by hitting the enter key, the upload file writes the memo field as a new line, separating it from the line indicator and thus giving errors.
My question would be : How would I determine in VBA, whether the first line of the memo field is blank.
I have a subform that I use in a few different ways on my database. It has a comment log with a memo field for people to insert their comments. The struggle is some can be long some can be short. Is there any way to have each record height adjust to the length of the memo?
If it cannot be done for the subform in regular use, my main concern is having it formatted for reports that are generated for clients.
I clipped and attached an example from a report. Its not the best example but the extra column height is adding additional pages to my report to accommodate one long comment.
If I have an XML file, I can load it into an XML document and parse it.
For an HTML file, I have been unable to find similar parsing methods - that is, using DOM and not involving a browser (other than simply treating the file as text). I'd prefer to avoid the browser control because it is different for a number of different Access versions, so the only option remaining that I see is to run Internet Explorer with visibility off.
Can one parse HTML using the DOM without a browser?
I import a CSV field which has not preserved the CR/LF when it was exported from BCM Remedy. There is no setting for that. The memo field prints on my report like this:
A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed. 2012/05/24 10:44:28 AM PCOLLINS Sent to Ray Massie for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.
I want to add VBA code that inserts a CR/LF in the memo field before all but the first occurance of a string that looks like a date, the first occurance doesn't need it. I will do it right after I import the CSV file into the table, so it happens only once, and it always prints and displays the CR/LF.
The memo field is called "NBS Update" and the table is called "CCRR Remedy Data"Here is what I have, but don't actually know what to put in to find the date and add a CR/LF:
Code: Dim db As DAO.Database
Code: Dim db As DAO.Recordset Dim srtSQL As String
I had the need every so often to quickly find all instances of any (particular) word within the database, without going to the bother of thinking about where that word/subject might be in the data base, I designed a report which does the job nicely. It uses a search form/control, searches and finds all instances of the word (or words) I have asked it to find, then does a report showing all instances of whatever RTF memo fields that contain the word(s).It has to be RTF memo field because (if I wish) it goes straight into the book text I'm writing, and also it searches the book's RTF memo field. . The trouble is, at present, I then have to manually "eye scan" the resulting report to find the occurrences of the "key searched for" word, nestling/hidden somewhere within sometimes quite large chunks of text. I would love to have the report show the found "key word(s)" highlighted in (say) light blue or whatever colour, so that I can quickly & easily lock on to it visually.
I enter the word I want Access to find, into a form called "formfindword" in a text box called Text0. I click on a command box on this form, to open up the report called "Querywhatsandhowschooseword" based on a query of the same name. The query's "what" field (from the main database) has the criteria
= Like "*" and [forms]![Formfindword]![Text0] & "*"
The query's "how" field criteria (in the "or" line of the query design) is also
= Like "*" & [forms]![Formfindword]![Text0] & "*"
I have tried to use the Conditional Format facility in Report Layout view, without any luck.I think therefore that I will need some VBA code (which is totally foreign to me, unfortunately) to make this simple concept a reality.
I have code which is attached to onclick of a checkbox,
What I want it to do is if the user clicks it and checks it then to add some note to a memo field, if the user clicks it and its already checked then it doesn't add a note to a memo field.
Code: Private Sub Check45_Click() If Check45 = 0 Then Check45 = 1 If Len([Forms]![Customer]!Notes.Value & "") = 0 Then
[Code] ....
But I can't seem to add any other lines which make sense to me that it doesnt add "letter sent" to a memo field if its already checked?
Can someone get me started in the right direction?
I have an unbound field on a form "txtScan". This field is populated by a barcode scanner.
The data looks like this ~Field1~Field2~Field3~Field4~Field5~Field6~Field7~ Field8~Field9 I am using ~ as a delimiter. Since I am encrypting the barcode, I can pretty much do anything to the barcode.
I need to parse ~Field1~ into txtFirstName ~Field2~ into txtLastName ~Field3~ into txtCompany etc. A key point is that one of the fields may be blank so, if field 2 in the string is blank, "Field2" does not get populated.
I've never tackled anything like this before and don't know where to start.
Two Solutions to address moving an Access Memo field into Excel when string has > 255 characters. All my 'reports' use Excel VBA (Access Reports are not used). The Excel reports can have 40,000 records. Speed to create the report can be an issue.
Describing 2 Solutions below to address moving Access memo fields with > 255 characters into Excel.After running this code
Code: 720 ObjXL.DisplayAlerts = False ObjXL.Columns("X:X").Select ObjXL.Selection.NumberFormat = "@" ' set column to Text 730 ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsNutsAndBolts
The Comments column are limited to 255 characters. So, the CopyFromRecordset (recordsetvariable) creates the 255 character limitation.
The reason? The 255 character limit is because CopyFromRecordset sutomatically uses the Characters property of the Range object. The 255 limit would not be there if the Cell Value property is used to assign the string to that cell.
Dim sRx as String ' String Prescription sRx = "String with > 255 characters ... you fill in the rest ...." Cells(1, 1).Value = sRx ' Cell's Value property assignment can be very large
Solution 1:
The record set is still in memory. By using a loop, a cursor can start with record 1 (memo column) and assign that value to the Excel row / column using the .value as shown above. Basically, this moves one memo field at a time, record by record. e.g. Read First recordset in Access, copy to variable, assign value to Excel row/column Then move next on each Access and Excel.
Solution 2:
An Access Memo filed [RxNotes] can have up to 750 characters. Cut it apart into three new fields that end up out in the very right Excel columns AA, AB, AC.
Note1=Mid([RxNotes],1,250) Note2=Mid([RxNotes],251,250) Note3=Mid([RxNotes],501,250) Then using Excel Object - Concat the cells back cell by cell... X2=CONCATENATE(AA2,AB2,AC2))
Then delete the columns AA, AB, AC to hide the evidence..Neither solution is all that elequent. Read about this and by golly, it made a difference
ConcatComments = "'" & CommentString
Before using the CopyFromRecordset be sure to add a single quote in front of the large string.
Turns out the interface between Access and Excel look for this to prepare Excel immediately for the string to be a string, not something else. Some of my strings had weird print characters that kind of looked like Japenese characters. It seemed random, it always happened if the string was 255 or more characters (ramdonly, not always). The single quote doesn't show up in Excel, but got rid of all the noise.
I have a Date Field and I would like to complete another field depending upon the contents of that date field.
In this way: if the Date is, say, 4/27/12 I want the other field to read: April 2012 and so on. Reading the month, putting it into words, reading the year, writing it out. This second field is NOT a date field.
I have a spread sheet which on a per line basis contains columns whose cells contain the path to a file. It looks as if many of the files are tif, pdf, word and excel etc, so multiple file types.
Writing a routine which would take each row and then for each line of file paths turn them into one multi-page document - pdf or something?
I've built an HTML web form that sends an email to me upon submission with all the field inputs. I have previously found success using VBA in Outlook to parse out these emails and feed the substrings into an Excel Sheet (hooray!), but now am looking to feed the substrings into Access, specifically into a table in an existing .mdb file. I figure the way Outlook will "talk to" Access will likely be a bit different from the way it does for Excel.
The way my previous script engaged with Excel was through a rule; every time a new email was received, the following script would run if the message had the subject line generated by my web form. The script would check whether Excel and the target workbook/sheet was open, act accordingly, and then input the substrings (I've excluded that part below), then return Excel and the wb/ws to their initial state. I imagine though, that with Access I might be able to feed the substrings into the .mdb without having to open it proper, the way a front-end talks to a back-end. But perhaps I'm mistaken on that front.
Code: Option Explicit Option Compare Text Public Const xlUp As Integer = -4162 'I genuinely do not know what this is _ about. It came with the initial _ borrowed code. Public Const wbPath As String = "C:...Workbook.xlsx" 'Workbook path
In a query, I have the following data. If a number has more than six digits, I need to parse past the fifth digit and that becomes a new field. If it has five or fewer digits, no record is entered into Column2. For example:
My form has a listbox (lstHeatTreatments - Multi-Select disabled) that displays Heat Treatment descriptions and an unbound textbox (txtHTDetails) that I would like to have display the corresponding memo field when a description is selected from the listbox.
This is my code so far:
Code: Private Sub lstHeatTreatments_AfterUpdate() Dim myConnection As ADODB.Connection Dim myRecordSet As New ADODB.Recordset Dim mySQL As String Dim selectedRequirementKey As Long Set myConnection = CurrentProject.AccessConnection Set myRecordSet.ActiveConnection = myConnection
We need to move rich text from an Access memo field to a Word text box. So far the best Ive been able to come up with is in the code below. In this code pprs!What is a record set field of a table memo field that is bound to a text box enabled for rich text. The rich text seems to be stored as html as so I can get word to convert it by enclosing it in html tags.
Dim What As Word.Shape Set What = doc.Shapes.AddTextbox(msoTextOrientationHorizontal , doc.PageSetup.LeftMargin, 225, 534, 0) Dim sPath As String sPath = "G:Temp.html" Open sPath For Output As 1 Print #1, "<HTML>" & pprs!What & " </HTML>" Close #1 What.TextFrame.TextRange.InsertFile (sPath)
I need some help. I created a database to capture information from an employee survey. Most of the answers required a check if they agree or no check if they do not agree. There was one "memo" field inwhich we allow the employee to VENT. I have provided the team with all sorts of reports with percentages and figures. Now they want to remove any comment that had money, pay raise, bonus etc. They also want to organize the comments in some type of flow. Like all the "pats on the back" in one area, Day offs in another, plaque on wall grouped together, etc.
Ok my database tracks escalations through my team, I have a main table that stores the unique ID's from the other tables I use in my Combo boxes, this part works really well, no problems.
My issue is with a tableI have claled "TBL_EscJournal", this table has the following fields:
JournalID (Unique Ref, generated by autonumber) EscID (the escalation Id that this journal is relevant to) Journalcreator (captures name of person who as entered the journal) JournalNotes (memo field where you enter your update) JournalDate (Date/Time the journal was entered)
So typically when viewing the main detailed form for a particular escalation I have a subform that shows all the journal entries relevant ot that escalation.
This table is huge, about 70% the total size of my database, partly because of the number of journal entries and partly because it is a memo field and a lot of data is required sometimes.
Up until now th edatabase has been located on a local server and has been fine for local users (2-3 of us) however there is a requirement for another office to use this database.
I am now experiencing massive performance issues, whereby the data is tkaig a long time to refresh on the other sites.
I have migrated the DB over to a SQL back end but still finding performance issues, which further testing has shown that the TBL_EscJournal is the cause.
So a coupel of things really, is there another way I can layout this table to improve performance or should I be uerying the data from this tabel in another way, would it be better for me to split TBL_EscJournal in two, the first part keeping the date/time and person who entered and the second part keeping the notes. Possibly increasing the query speed by carrying out the query on the first part of the table and not on the notes (memo) part ?
I hope this makes sense, if not feel free to PM me,
I am in the process of changing over a text field to a memo field to generate more space (in 5 different databases :eek: ) Thanks to all the good info on several searches of this forum, I am pretty clear on how to do that.
The question is... when I convert a field that already has information in it, will I lose the current information?
