I have a query and I am prompted for a " nco " number. This could produce any amount of lines.
What I would like to do is have some code that will ask me the NCO number then print of a report for each line item from the query so if I have 10 lines I need 10 reports .
My query name is "internalworksorder" ...
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG)
Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer
Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String
Dim Lim As String, ITNO As Integer
[code]....
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code:
Dim strLine As String
Dim intLineNum As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Open "C:TestTest.txt" For Input As #1
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1
Line 2 is placed in Row 2 Column 1
Line 3 is placed in Row 3 Column 1
.
.
.
Line X is placed in Row X Column 1.
Here's the statement
Code:
sqlfinal = "SELECT Employees.ID, Employees.Name "
sqlfinal = sqlfinal & "FROM ((qryDeptVBA INNER JOIN qrySkillVBA ON qryDeptVBA.ID = qrySkillVBA.ID) "
sqlfinal = sqlfinal & "INNER JOIN Employees ON qryDeptVBA.ID = Employees.ID) "
sqlfinal = sqlfinal & "INNER JOIN qryAreaVBA ON Employees.ID = qryAreaVBA.EmpID "
sqlfinal = sqlfinal & "GROUP BY Employees.ID, Employees.Name;"
If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.
I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate()
DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
I need to insert(append) first and last line in xml file...
So I have xml file and I need to insert one line to the top and at the of the text... Xml file contains Cyrillic characters and method to recreate xml file is going wrong with characters conversions... The last line a can append easy but the first line is a problem...
My issue is when there are 4 records (store numbers) for any "row" on the load diagram, the last store's information is duplicated when the report is printed. It only appears once in the table, it only appears once in the query, but when the report prints it prints two lines. It also does not include the duplicate line in the totals at the bottom of the report. This leads me to believe that the issue is in my PrintLines function which is in the modPrintLoadDiagram but I am only a beginner when writing code and have not been able to figure it out on my own.
I am attaching a version of the original database. Please open rptLoadDiagram in Print Preview and look at row 3 on the right side. Store 5449 is showing up twice but should only be on there one time.
This is the only issue holding up my roll out of the database.
how to set focus in first or last line, but I need set to second.
View 11 Replies View RelatedWhen I build the statement I want to email and view it in a MsgBox the lines space correctly with the code vbCrLf.However when I send that text out as the body of the email, the spacing is no longer there and its just one big pile of nonsenses. In the MsgBox I see it correctly as:
Tool Box 5
Drawer 3 Tag Number 4
Drawer 2 Tag Number 6
Tool Box 7
Drawer 8 Tag Number 2
But in the Email it comes out as:
Tool Box 5 Drawer 3 Tag Number 4 Drawer 2 Tag Number 6 Tool Box 7 Drawer 8 Tag Number 2
How do I make Outlook correctly space?
I have a textbox on continues form which contains multiple lines and I d like to set it up so it ll be scrolled down completely when it displays the form. I ve already found a few so called solutions but nothing worked for me.
View 5 Replies View RelatedI am using "INSERT INTO tablename(a1,a2)" function but i have to many "a" objecjts. How to carry over to a new line without having error?
View 10 Replies View Relatedmanaged to get some code up and running but when trying to enhance it I have hit a rut.
I have a function that is looking up a query called Optimisation - Auto Optimise with operational data in it. I only need 2 key fields; consolcode and volume. There are 106 records with different consolcodes each with different volume. e.g consolcode: Chittagong to Rotterdam201452 (Chittagong to Rotterdam by year "2014" by week "52") and volume 161 (cbm)
I then run a code that allocates the volume into specific sea freight containers and returns the values into a different output table.
The allocation code works fine but when I run the loop function for the recordset rsttradelane it runs for the correct amount of records (106) but always returns the first record of Chittagong to Rotterdam201452 and 161 cbm and not the other 105 consolcodes with the different cbm. Giving me an output table with Chittagong to Rotterdam201452 and 161 cbm repeated 105 times!!
Code:
Set rsttradelane = dbsEPIC.OpenRecordset("Optimisation - Auto Optimise")
consollane = rsttradelane!consolcode
ConsolVol = rsttradelane!Volume
Do Until rsttradelane.EOF
'Optimisation code' then
Code:
rsttradelane.MoveNext
Loop
rsttradelane.Close
How do I ensure that each consolcode and its associated cbm is recognised individually and flushed through the optimisation code?
how to write a vba msgbox with two lines i access 2013
i try to put chr(13) and alse i used & vbCrLf& but nothing is worked
they seems to be for an earlier version ...
I'm trying to write a line of VBA code (in an existing Access 2010 DB) that changes the Subject Line of all selected emails in my Outlook Inbox to today's date.
View 1 Replies View RelatedOur 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 function which takes a recordset and writes the contents to a text file. One of the fields in the recordset is a free format text field, the contents of which originate from a text box on a form. Occasionally, a user can hit the 'Enter' key to add a new line (carriage return) to the note within the text box.
The problem is, when the output text file is subsequently imported to Excel, the carriage returns within that text field serve to move the next piece of data to the next row (even though the CR is within the double-quote text-qualifier in the text file)I want line breaks within the text field to be retained within the cell.
One option would very simply be to replace the carriage returns with simple spaces in the write function. I could do that quite easily. However, I would prefer to retain whatever the user has entered into the text box, including line breaks, if at all possible?So if the user broke his free format 'note' into multiple lines (for whatever reason), it would similarly appear as multiple lines in the same cell (and in the same row / record) in the Excel worksheet.
I could replace the carriage return with some obscure character during the write, then switch it back during the Excel import, but that seems a little clunky (plus Murphy's Law dictates that no matter how obscure a character I choose, somebody, at some point, will genuinely use it, and it will get switched out. Which means I'd have to use a non-printing character?could I simply replace the carriage return with a line feed? (Or is it the other way around? I always get carriage returns and line feeds mixed up!)there's a simple way around this but I always get confused as to precisely which ASCII character the 'Enter' key produces when keying into a text box.
i'm trying to send an Outlook e-mail from Access. Everything works fine except for one thing:
When generating the HTMLBody I use vbNewLine for starting a new line in the string that works fine as long as being in Access. However after displaying it in Outlook there are no any new lines in the mail text; the complete text is one mass.
I tried to use Chr(13) but had the same result.
I can change multiple things on a line graph with VBA.
Me.Graph47.chartType = GraphType ' take 65 for line
with me.graph47
.SeriesCollection(1).border.Color = vbblue ' change the line color
.SeriesCollection(1).border.Weight = LineWeight ' change the line weight to for example 3
.SeriesCollection(2).MarkerSize = MarkerWeight ' Change the marker weight, for example 4
.SeriesCollection(2).MarkerBackgroundColor = vbblue ' Change the marker color,
.SeriesCollection(2).axisgroup = 2 ' put this series on the secondary axis
end with
SeriesColection(1) is line with markers. This is correct.But now I want the seriescollection(2) without line, so only the markers. I cheched the MSDN site from Microsoft. The Excel trick with the macro does not work for me.how to hide the line with VBA for only SeriesCollection(2) in Access?
I have a database that I've split and have deployed to 7 persons.
The thing is, I have three (3) forms:
Form 1
Form 2
Form 3
...that require changing a portion of a line in each form's respective VBA codes. I need to change the directories to where an excel workbook is stored after exporting, and another is where same excel is moved to after being uploaded.
At the moment, during deployment I have to manually change the directories.
I was thinking of maybe a pop-up form with four (4) fields that allows the user to select his/her name at the start-up of their frontend app, and paste the directories in the other text boxes provided.
This information will then be used to replace the directories in the VBA.
If I were to make changes and have to redeploy, I would have to do everything manually again.
I have to count the number of occurrences of Line feed (vbLf) in a long string. The code I am using is:
MAX = TLine.Split(vbLf).Length - 1
It does not work.
i have set up an update query. is there a way of looping through a continuous form record set and running the update on each line?
View 2 Replies View RelatedI'm trying to have a command button send info on the form straight to Outlook as a meeting. So far, the code below works apart from the address bit. I need the code to automatically add the same 2 email addresses as recepients everytime.
Error message highlights the red line below with the following message:
Runtime error '438'
Object doesn't support this property or method
I'm new to VBA, so don't understand what is wrong here. The reference is set, I've tried different permutations of the "To:" & "Recipients.Add" for the email address with no joy.
Code:
Private Sub cmdSend_Click()
Dim outApp As Object
Set outApp = CreateObject("Outlook.application")
Set outmail = outApp.CreateItem(olappointmentitem)
[Code] .....
I need to print @100 envelopes, but the printer physically only holds 10 envelopes at a time, then has to be reloaded, print 10, reload, print 10 more and so on until EOF. I need to have a Msgbox for each reload to tell the users to reload the printer. I need a good approach on how to. I have tried, Do Whiles, Loops, and some Counts.
View 7 Replies View RelatedI am trying to capture a newly arrived mail in outlook with respective subject line. The below code works for me on 2010 outlook but when new mail arrive in outlook 2013 ,Mrthod Items_ItemAdd do not get called.
Code:
Option Explicit
Private WithEvents Items As Outlook.Items
Private Sub Application_Startup()
Dim olApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Set olApp = Outlook.Application
Set objNS = olApp.GetNamespace("MAPI")
' (1) default Inbox
[Code]...
Users of the db I am working on print using a regular network printer 95% of the time. 5% of the time however, they need to change that printer to another one, or to a PDF printing program. All of the printers they need are installed in their Printers folder.I can think of two possible courses of action here:
1. load the printer dialog box (preferred)
2. provide them with a custom interface to select a temporary printer
I don't want them to have to load the printers folder and switch the printer temporarily.how to load the printer dialog.
I have report that I open in reportview. On this report there is a button wich would allow the user to print the report. I have put the following code behind the button:
If MsgBox("Are you sure you want to print the report?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.RunCommand acCmdPrint
End If
The printdialog is appearing on the screen but if I click print nothing is happening.
Is it not possible to print a report like this?
As all the ribbons are hidden I am not able to use the access print button.