Vba code line break Results

I have a message box (pops up on the after update code of a field) with a long-ish message. I'd really like to force the message into two lines. Is this possible?

I know how to code forced line breaks in Word macros, but Access VBA is still doing my head in.


I have a (rich text) textbox on one of my forms. I'd like the user to be able to use the enter key to go to a new line. As I found out the enter key does not normally behave that way in a textbox I am trying to solve this through vba code. I came as far as the following:

Private Sub mail_text_Enter()
mail_text.Value = mail_text.Value & "
End Sub

which is wildly unsuccesfull as it only adds a line break at the end of the text (I don't know how to get the cursor position) and it doesn't even set focus back to the textbox.

I feel I'm going totally the wrong way about this. Does someone know how to do this?



(Not sure where I should have asked this questions. Moderators, please feel free to move this thread if I asked this in the wrong place)

How do you go about reviewing and writing code?

I have a project to automate a tedious process at work that involves controlling another program from Excel. This other programs has it’s own programming language that is very similar to VBA.

I am close to finishing this project but am having a major headache trying to review my code for lines that are redundant, not used, where I performing the same or similiar procedures repeatedly and so on.

I am certain that part of the problem is that I just jumped into writing the code with not much of a plan of attack other than trying to break the code into chucks that perform a particular task and then one “all-in-one” that calls the separate functions.

My question is what tools do you use to review your code (VBA in general)?

Here are some tools that I have found

Printing VBA Code

Code Flowcharting (Big differences in prices but I haven’t looked into all the features!)
http://www.kunzlertech.com/ ($17.95)
http://www.fatesoft.com/s2f/ ($99.95)
http://www.aivosto.com/visustin.html ($249)

Code Analyzer?

Do any of you use tools or does it just boil down to programming practices?

Hi all,

I have a username/password form and when the user clicks ok the program goes through some checks and then is meant to load another form.

But when the user clicks on the program brings up the vba code window and highlights a line in yellow. When I press F5 the program continues and carrys on as normal and loads the form.

when I next load the form it goes in stragiht away.

The line it stops on is:

	    Case 0, 1

There isn't a break point here (i.e. not in red).

Any ideas anybody?


Hi there, I have an Access 2007 front-end with a SQL 2008 backend. I am successfully using a form with combo boxes to filter my passthrough query by using a command button to rewrite the SQL string based on the data on the form.

My problem is that I am having difficulty writing clean script in my On_Click VBA code. Currently, I have everything posted in one VB line and it works fine. If I throw in some “& _ “ to break it up, I start getting errors. The line break works fine if I’m only using SELECT commands, but it doesn’t like the breaks when I add WHERE criteria.

Here is the working On_Click code:

	Private Sub Command9_Click()
Dim strSQL As String
Set db = CurrentDb
& "' and OU_T_ORG_UNIT_NM = '" & [Forms]![frm_Main_Form].[Team] & "' and C_CRNT_STAT_CD = '" &
[Forms]![frm_Main_Form].[CaseStatus] & "' and C_STAT_AID_STAT_CD= '" & [Forms]![frm_Main_Form].[FedAidStatus] & "' ;"
db.QueryDefs("PassThroughTest").SQL = strSQL
DoCmd.OpenQuery "PassthroughTest"
End Sub

Here is what I would like the strSQL section to look like:

“from CASE_CAS” & _ 
“WHERE” & _ 
“”C_MNG_CNTY_FIPS_CD = '" & [Forms]![frm_Select_Office].[Combo0]” & _ 
& "' and OU_T_ORG_UNIT_NM = '" & [Forms]![frm_Main_Form].[Team] & _ 
& "' and C_CRNT_STAT_CD = '" & [Forms]![frm_Main_Form].[CaseStatus] & _ 
& "' and C_STAT_AID_STAT_CD= '" & [Forms]![frm_Main_Form].[FedAidStatus] & "' ;"

I’m hoping this is a simple case of me leaving out parentheses or spaces somewhere.

I have a database which I am building to output html code to a .txt file for uploading to a site. The problem I face is, I cannot figure out how to force a line of text to export as continuous. As it sits, longer lines of code are forced a line break (carriage return) upon export.

Please see attached. Where am I going wrong?

Thank you for your time and help. Have a great day! Attached Files Sample.zip (82.3 KB, 2 views) Reply With Quote 09-28-2010, 02:39 PM #2 ajetrumpet Banned Windows Vista Access 2007 Join Date Mar 2010 Location N/A Posts 2,698 I did not look at your sample, but are you exporting the report to HTML by right clicking? by vba code? what do you want as an output? just the data? is the report based on a query? if it is, have you considered using ADO stream objects, or maybe even the I/O methods from DAO to stream the data right into a created text file?

That method would of course require you to intersperse the lines of data with relevant HTML tags, but a one-time setup function might be worth the time if the process is going to repeated more than once...


I have a MS Access database application split into a front-end/back-end database. The functionality all works fine on my computer but when my client tests it various pieces of functionality don't work. This is VERY frustrating and quite difficult to troubleshoot as I'm not sure what it is that's different between the client's and my pc that is causing the code to break.

Issue #1) The button on-click event: "DoCmd.OutputTo acOutputQuery, "Talent Data", acFormatXLSX, , True" works great on my computer my when my client runs this screen from her laptop, all of the memo fields convert to something that looks like chinese characters when it is exported to Excel.

Issue #2) I have a report that has a text box with a control source set to "IIf([l2p1] Is Null,"",[L2P1] & Chr(13) & Chr(10) & [l2p1jobtitle])" which in essence lists a persons name, forces a new line and then displays their job title on the next line of the text box. If that field is null, it won't display anything. This works Great in my version but of course, on my client's computer, the text box displays as "#Name?".

I'm not positive that this is the same issue - but it sort of seems that there is a missing library or something on my client's pc. I know that it is missing the DTS.dll (Microsoft DTS runtime 1.0) file... but when I take that reference out of my copy of access, all of my functionality still works - so I don't think that's it.




I am trying to write coding to enable the user to click on a button to send an email including fields from the active record.

I am currently experiencing issues with the lines highlighted in bold. It is coming up with the following error:

Compile Error
Expected: Line number or label or statement or end of statement.

Am not sure how to correct this as the references to the fields are correct and have tried different versions of line breaking without success.

Please advise what I am doing wrong...

Private Sub btnsendemail_Click()
If IsNull(Me.txtcount) Then
MsgBox "There are no appointments to email"
On Error GoTo handdler
Dim objOL As Outlook.Application
Set objOL = New Outlook.Application
Dim ObjOLMsg As Outlook.MailItem
Set ObjOLMsg = Olk.CreateItem(olMailItem)
With ObjOLMsg
Dim ObjOLRecip As Outlook.Recipient
Set ObjOLRecip = .Recipients.Add(joebloggs@google.com)
ObjOLRecip.Type = olCC
.Subject = "Appointment Reminder"
.Body = "Thank you for your referral regarding" & Nz(Me.Forename, "") & "& Nz (Me.Surname,"")_"
& vbCrLF & "We have booked an outpatient appointment for them to be seen in" & Nz (Me.Consultant,"")& "clinic on" & Nz(Me.1st_Appointment,"") "at insert time." _
& vbCrLF & "Kind Regards" _
& vbCrLF & "The Appointment Team."_
& vbCrLF & "This message has been automatically generated by the Booking Database System"

End If
If Err.Number = 2501 Then
MsgBox "You have cancelled the request", vbInformation
Exit Sub
End If
End Sub


Hi All,

So I have a access database and I have delevolped a code (actually borrowed a code) to send all of the information to a MS Word template. My question is - not all of my contacts have an e-mail address so in the event that they do not the fax will be inserted instead. I have that part set but I want to add in the text "E-mail:" or "Fax:" depending on the circumstance. I am not sure about how to go about it.

Any help is greatly appreciated!

Private Sub MergeBttn_Click()
'Declare variables for storing strings.
Dim AddyLineVar As String, SalutationVar As String
Dim DeliveryAdd As String

'Start building AddyLineVar, by dealing with blank last name fields.
If IsNull([sfrmContacts].[Form]![Last]) Then
AddyLineVar = [Company]
'Just set salutation to generic.
SalutationVar = "Sir or Madam"
AddyLineVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])

'Add Company on after name.
If Not IsNull([Company]) Then
AddyLineVar = AddyLineVar & vbCrLf & [Company]
End If

'Salutation will be customer's last name
SalutationVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![Last]) & ", "
End If

'Start building DeliveryAdd, by dealing with blank email fields.
If IsNull([sfrmContacts].[Form]![Email]) Then
DeliveryAdd = [sfrmContacts].[Form]![BusinessFax]
End If

'Add line break and Address lines.
AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![Address])
'Tack on line break then city, state, and zip.
AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![City]) & ", "
AddyLineVar = AddyLineVar & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])

'Declare an instance of MS Word.
Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")

'Specify the path and name to the word document.
Dim MergeDoc As String
MergeDoc = Application.CurrentProject.Path
MergeDoc = MergeDoc & "WordFormLetter.dotx"

'Open the word document template, make it visible.
Wrd.Documents.Add MergeDoc
Wrd.Visible = True

'Replace each bookmark with current data.
With Wrd.ActiveDocument.Bookmarks
.Item("ProjectDescription").Range.Text = ProjectDescription
.Item("AddressLines").Range.Text = AddyLineVar
.Item("Salutation").Range.Text = SalutationVar
.Item("Phone").Range.Text = sfrmContacts.Form!Phone
.Item("JobNumber").Range.Text = JobNumber
.Item("PMInitials").Range.Text = Manager
.Item("Typist").Range.Text = [Entered_By]
.Item("JobNumber2").Range.Text = JobNumber
.Item("Phone2").Range.Text = sfrmContacts.Form!Phone
.Item("BusinessFax2").Range.Text = sfrmContacts.Form![BusinessFax]
.Item("Delivery").Range.Text = DeliveryAdd
.Item("ProjectDescription2").Range.Text = ProjectDescription
End With


I have a complex application built in Access 2003.

I'm having an issue with the VBA code in a form module. The behavior occurs when I put a stop or breakpoint in the before_update event of the unbound combo box. What happens is this:

The code breaks ok.
I press F8
I get the "This expression is too complex to be evaluated..." error
I click ok
I get repeated errors saying "Object is no longer set" until I kill the app with Task manager.

I have tried everything I can think of short of rebuilding the form (which is pretty complex)
I have taken code out and stripped to the bare bones, but it triggers even on a line of code such as "CheckBatch = true".
If I don't put a break in the code the error sometimes occurs, but usually does not.

I've decompiled and recompiled multiple times.

I deleted and recreated the combo box but it seems when I add any code to the before_update event that calls a function it triggers this error

I verified none of the queries or lookups in the code are causing the error.

I recently imported the database objects into a new database to fix another corruption issue so I hate to have to do that again.

Appreciate any suggestions.
Thanks in advance,

Hi, I could really use some help here. I'm trying to tighten up a DB that has lots of queries that are fired in VBA by using some like this:

strQuery = "the query to be run"
DoCmd.OpenQuery strQuery

I might be wrong, but this seems rather inefficient and I'd like to use more SQL statements in VBA. It's easy enough to copy the SQL from the saved query, assign it to a string variable and run it. The obstacle I have is the SQL code is often very, very long and I'd like to use line breaks and table aliases to compress the code and neaten it up.

I've attached a text file to show the full extended version of the code.

Any help or suggestion would sincerely be appreciated.



Sometimes when I'm running a vba procedure in Access, the code breakes and throws me into the class module where the code is, and highlites a line in yellow. It acts just as though I put a breakpoint on the line (but there is none). This ususally happens after I've been debugging and maybe HAD one or more breakpoints in the module - except that I removed them but the code still breaks. Even happens if I do a compile (from the debug menu), close the db, reopen, and compact and repair. Even after all this, the code might break. Eventually - after a few more compact and repair operations, it stops. But it is vexing and inexplicable to me.
This happen to anybody else?

G'day folks,

I have a piece of code that I use for one of my Msgboxes that uses the "chr(13)" for line breaks. It works fine. When I try this same approach for text in a memo field on a form, it doesn't.

Does anyone know if this is possible? I've included my code below to give you an idea.

	Me.Historical_status = Me.Historical_status & Chr(13) & "--------------------" & Chr(13) & Me.Current_status

btw, the reason why I'm doing this is for asthetic purposes. There are two fields, one is current status, the other historical. I need copy the current into historical and use a series of dashes to separate.


I have a complex SQL query, which I need to put into my DB in some VBA code so that it can be dynamically changed.
As far as I know, Access does not have anything like stored procedures, which would have made this easier.

I'm planning to use this SQL query as the record source of a form but to get it neatly into the code I want to leave it in it's exisiting structure with line breaks, etc. for easy reading.

The only way I can think of is to do something like

strSQL = "SELECT Field1, " & _
" Field2, " & _
etc. but this is going to become very tiresome adding the & _ at the end of each line.

Isn't there an easier way ?

Hi All,

I am very new to Access and am looking to import a txt file into a table--
I have imported txt files into Access before, but I am having some problems:

1. Each row is a different type of record
2. Each record (row) needs to be spaced out differently (delimited fixed width)
3. Every 6 rows need to be pulled into one row when imported into the access table

I've attached an example that might help explain my problems with the file--the break between the 2 sets of records is only there is show how the data cycles every 6 lines as relates to the Sample named in the 2nd line. (The file itself it actually continuous)

I'd ultimtely like to go from this:
Line 1 (250 characters in length)
Line 2 (50-100 characters in length)
Line 3 (250 characters in length)
Line 4 (250 characters in length)
Line 5 (250 characters in length)
Line 6 (250 characters in length)
Line 7 (250 characters in length)
Line 8 (50-100 characters in length)

To this: (A tbl where each row at every 7th row in the txt file)

Line 1 | Line 2 | Line 3 | Line 4 | Line 5 | Line 6 |
Line 7 | Line 8 | Line 9 | Line 10 | Line 11| Line 12 |
Line 13|...etc.

But also be able to breakup the extremely long entries for each Line (so ultimately each row in the access table would have approximately 115 columns/fields having all 6 lines being split up).

Each row in the txt file has a number to determine the way the row itself should be split by width (it is easiest to look at the line that says SAMPLE and look at the number immediately preceeding the S--that character position denotes the type of format that the row should be split into, so every 6 records it repeats: 1,2,4,4,4,4,6 (So I have 1 record type 1, 1 record type 2, 4 record type 4s and 1 record type 6) I have the different character widths needed for each record type, but I haven't put it here--I'm hoping to be able to eventually put this in a VBA code of some sort--almost like an If Record Type A, Then delimit as such... If that's even possible...

I'm sorry this question is so long-winded--I hope it makes sense to some degree, at least. I have looked around on the other forums under Line Input and Importing Txt Files, but haven't found anything that deals with both importing different rows but also adjusting the width of the rows as well. (If this type of question has already been answered somewhere, please let me know!) Aside from this I must confess I know little to nothing about VBA and was told that the only way I might be able to do this would be to use it. Is there a way to do this? Even if it must be done in more than one step that would be great. Any help or suggestions would be greatly appreciated!

Thank you!

OK, I have a tool I've made in Access which spits out data from a form, into an excel spreadsheet and makes a line chart/Graph accordingly.

My issues is that I need to be able to chart a single point on the graph to denote price breaks for software we're purchasing.

Is there any way of coding a vertical line on a line graph in excel... or at least plotting one single point? (if you look at my example, it will be easier to see what it is that I need to do via VBA code)

You see in my chart example I have a vertical line and a balloon display to denote the price point break... but I just drew those in by hand... the rest of the chart was made programmatically.

Hi folks,

I currently am successfully getting control break to work using textBody:


Set objCDOMessage = CreateObject("CDO.Message")
With objCDOMessage
Set .Configuration = objCDOConfig
.Subject = "IRB: NIEHS Packet Due Date in +/- 10 weeks " & "-- " & strproject
'.TextBody = strTo & vbCrLf & strCC & vbCrLf & vbCrLf & "The NIEHS packet DUE DATE for " & strproject & _
" (protocol# " & strprotocol & ") is: " & vbCrLf & vbCrLf & " " & dtPacketDue _

I get the control breaks I desire.

However, when I change the above to .HTMLBody instead of .textBody, the "vbCrLf" does not line break, so I tried the html line break code:

.htmlbody = strTo &
& strCC &
& vbCrLf & "The NIEHS packet DUE DATE for " ....etc

VBA throws up an error message in both cases (using &vbCrLf, or

Anybody see my error?

Ok so we have a spreadsheet with like 15 tabs, oodles of code and triggers and subs that run when you change tabs etc to pull data from databases to populate drop down menus. At any rate...the file is 5mb and we will be generating hundreds of these. So to cut down on space i wrote a macro to run through the spreadsheet and delete all unused tabs which worked great. But the problem is we have code in the This.Workbook that now goes defunct cause most of the tabs done exist. I googled around a lot to try and find out how to delete code and i have one working. But there is a weird problem. It always errors out the first time run, then when you rerun it...it runs perfect. Im racking my brain and can't figure it out, it's stuff i have never dealt with before.

	Sub saveDeleteWorkbookCode()
Dim x As Integer
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines 
I get two different errors depending if i try to debug or just run it.
This line gives me a "Can't enter break mode at this time" when i try to F8
through it.  But it did delete the code from This.Workbook like it was 
supposed to.  But the error halts it every time.
If i insert the code and just run it.  I get a "compile error, expected end 
property" but once again, it does delete the code, then if i rerun it, it runs
perfectly to end because the code is gone.
End With
Call saveOptimization
End Sub
Sub saveOptimization()
Dim R As Integer, C As Integer
Dim theForm As String, formName As String
theForm = Sheets("Main Menu").Range("C3")
Application.DisplayAlerts = False
Worksheets("Form Info").Visible = True
Worksheets("Form Info").Activate
For R = 1 To ActiveSheet.UsedRange.Rows.count
    If Range("A1").Offset(R, 0) = "Form Selection on Main Menu" Then
        titleRow = R
    End If
    If Range("A1").Offset(R, 0) = theForm Then
        For C = 1 To ActiveSheet.UsedRange.Columns.count
        Worksheets("Form Info").Activate
        formName = Range("A1").Offset(titleRow, C)
            If Range("A1").Offset(R, C).Value  "X" And Range("A1").Offset(R, C).Value  "M" Then
                If formName = "Form Info" Then
                    Exit For
                End If
            End If
            If Range("A1").Offset(R, C).Value = "X" Or Range("A1").Offset(R, C).Value = "M" Then
                Selection.PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, _
                SkipBlanks:=False, _
            End If
        Next C
    End If
    If formName = "Form Info" Then
        Exit For
    End If
Next R


I'm populating a text box via code, pulling various fields from a table and displaying them in a textbox.

Quote: field1:
this field contains some data

This field also contains some data

This field contains lots of data, and will easilly spread over 4 or 5 lines in the textbox.
I have set the enter key behaviour in the textbox to 'New Line' and when I write data into the text box, I have tried seperating the fields with vbcr, but this just gives me a little square box, and then the data carries on on the same line.

Can anyone help with getting line breaks into a textbox when populating the textbox via vba?

Hey everyone, new question.

Is there any way possible to have row breaks in the imports specs of access 2000? While the data I'm importing is mostly in columns, it does change its "header" (if you will) for different customers. I suppose you could look at it like bank account transactions. See the attached file for an example similar to the data I'm using.

I need to organize the data to store the client and account as the identifiers for the trans id, trans type, amount, and so forth. The data would be imported from a text file. Because the column breaks won't line up I can't just put them in their own columns. Any ideas at all?


Fooled around with it some more and found a way to extract just the client and account from the file. I'm guessing now I'll have to do another import to match the data, but am presented with the same problem as before since the columns don't line up. A query or vba code may also be able to solve this problem, but in all honesty my coding skills are somewhat lacking and am not really sure where to start. I'll continue to hack away at it though and post any other progress I may make.

Not finding an answer? Try a Google search.