One of our vendors needs data from a table with a header (as HD) and a footer (as FT). The header is easy but short of converting the query to a report, how do I send CSV file with a footer as the last line.
View RepliesI 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.
I'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] .....
In Access 2010 how to add a file path in the report footer?
View 4 Replies View RelatedI need to create a text file that is exported from access to txt. I know how to do this; however, I need to have a header and footer setup in this file that dynamically changes with the day. How do I create these two required objects?
View 1 Replies View RelatedManaged to export a table to csv file. The problem I now have is I need to add the following Text at the start of the file:-
H,LKJ85485524,DE
and the new last line :- T,whatever the record count is,2
Whether this info is added before or after export is not important.
I have even resorted to using VBA to create an Excel spreadsheet and saving to csv.
Thought this had cracked it however I had surplus commas at head and footer where the original csv file had additional columns (ok manually delete these comas) ,then found that the end of each csv line was missing a final comma that is needed by the recipient of the file.
I want to open a txt file through my access database and append a line to it. When I use the opentextfile method for appending but it always appends my new text line to the end of the last line. I want it to append the new text to a new line. I have tried object.skipline but this is not supported when appending?
This is what I get:
"oldline" "newline"
but I want:
"oldline"
"newline"
Here is my code
sub ConcatenateTxtFile ()
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fd.OpenTextFile(dpath, ForAppending)
'Need a line here to skip to next line
f.writeline ("newline")
End Sub
I am trying to run a powershell file from command line. What i need to do in command line is
cd D:ScriptsWTV-MetaRenamer
powershell -command "& 'D:ScriptsWTV-MetaRenamerwtv-metarenamer.ps1' "
I use SendObject to send automatic emails from my application. Can I attach a table or an MDB file to the email? How do I do that? Or do I attach a table and it sends it as a text file?
View 4 Replies View RelatedI 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...
In Access 2007 there a way to send a report to a file you create from the information in the form?
Right now my code for the command button is:
Dim strMyPath As String
Dim strMyName As String
strMyPath =" EBEBMainDigital Sender "
strMyName = "Permit " & Me.[Permit Number].Value
DoCmd.OpenReport "File Current Permit", acViewPreview, , "[ID] = " & Me.[ID]
DoCmd.OutputTo acOutputReport, "File Current Permit", acFormatPDF, strMyPath + strMyName, False
DoCmd.Close acReport, "File Current Permit"
And that works perfect for sending the current report to the Digital Sender Folder... but I'd like to be able to send it to a certain county folder within the digital sender folder, based on what is listed for the county on the form. same for municipalities. and then, have it create a folder based on the name inside the form..... how to lead it through the path of the folder through the given information on the form, or how to make it create a folder.
I have some reports which lay my data out perfectly.For example,Report headerPage headerDetailsPage footerReport footerNow, I'd like to have exactly the same layout on some forms but with various buttons, etc. for manipulating data.The only problem is that when I put something on the form footer, it appears at the bottom of the viewable screen area no matter whether the detail area should overrun or not ?Is this due to form size or something else?Ideally I'd like to have it appear right at the bottom of the form and allow the user to scroll down the form to see the footer.
View 6 Replies View RelatedI 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 have a query that runs based on a parameter a user selects on a form.
I want to dynamically create the sendobject file name based on the parameter the user selects.
Example:
My form has a parameter named medicine. When the user checks the checkbox next to medicine it runs a query for records labeled as medicine but sends the e-mail output as the query name. I want something like medicine.txt etcc...
thanks
:confused:
I want to send an e-mail with a report as attachment.On the internet i have found two options, and both are not the best solutions.
1. option
Code:
DoCmd.SendObject acSendReport, sReport, acFormatPDF, sMailAdres
With this construction I don't have enough control over the mail, and worse, I get an Outlook message when the mail is sending which I have to answer.
2. option
Code:
Dim oMail As MailItem
Dim oAtt As Attachment
Set oApp = CreateObject("Outlook.application")
Set oMail = oApp.CreateItem(olMailItem)
DoCmd.OpenReport ReportName:=sReport, View:=acViewPreview, WindowMode:=acHidden
Set rpt = Reports(sReport)
[code]...
with this construction I have to enter an existing report.pdf on my filesystem (bold line), but I want to pass the rpt object straight to the mail as an attachment.
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 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 am trying to create an invoice report. I want to add all the costs from the detail section in my footer. I have accomplished this in either the Report footer and/or the page footer, but the problem is that the report footer doesn't go to the bottom of the page, and the page footer doesn't go to just the last page. I have tested a bunch of suggestions that don't seem to work.
1. Print page footer with Report header = NO - only works if your report is two pages or more.
2. Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
If Page = Pages Then
Me.[TextBoxName].Visible = True
Else
Me.[TextBoxName].Visible = False
End If
End Sub
The Page functions worked, but it did not change the results from page to page. If it was a two page report, then the if statement is false and the text box was not visible on page 1, but it also was not visible on page 2.
How do I print a different page footer on different pages? Please help.
View 1 Replies View RelatedHello. I think is going to be very challenging for someone to assist with. In Access 2003. I hope this makes sense.
In a grouped(?) footer of a report I have the following calculations in text boxes, and they are working well:
Column1: =Sum([pays])
Column2: =Sum([fuel])
Column3: =Sum([osa])
Column4: =(Count([fit(i)])+Count([ag(i)])+Count([azg(i)])-Count([loc_id]))/4
Column5: =Sum([pays])+Sum([fuel])+Sum([osa])+(Count([fit(i)])+Count([ag(i)])+Count([azg(i)])-Count([loc_id]))/4
Column5 is the grand total of columns 1-4
In the report footer I have the following calculations going on, and they are working well:
Column1: =Sum(Abs([main Zone]=1)*([pays]))
Column2: =Sum(Abs([main Zone]=1)*([fuel]))
Column3: =Sum(Abs([main Zone]=1)*([osa]))
Column4: (blank)
Column5 (blank)
My problem is this: I would like Columns 4&5 of the grouped area to carry to Columns 4&5 of the report footer. I can get Columns 1-3 to carry to the report footer, but not Columns 4&5. Again, Column5 being the grand total for that zone. If I try to combine the count expression with Sum(Abs(... expression, I get 'Cannot Aggregate...
Thanks in advance.
I apologise if this question has been answered elsewhere but I have tried looking and can't find anything that helps.
I'm in the process of upsizing an access database to an adp front end and a sql server back end. One of the forms in my original database was a continuous form with some calculated text boxes in the form footer. One for example had the formula
=Sum(IIf([field1]="valueA",1,0))
which worked quite happilly.
I've now changed my forms recordsource to a stored procedure which works fine, however none of the calculated text boxes in the footer of the form work. I have read that complex expressions might not work in adp's and that I should use CASE instead of IIF, which I've done. I've taken the iif part of the formula and added a calculated field to my underlying query so all I need to do in the calculated text box is an =sum([aField]) but I still get a #Error.
Even if I do a =count(1) or something really simple like that I still get #Error.
Can anyone see where I'm going wrong?
i have a subform that has a field that has this (=Round([TotalHoursPoss]*[PercentField])*0.27) in the control source. the form is set to Continuous Forms. it then have a field in the form footer that i need a total of what ever numbers show up in the field from above. for example.
field=41
field=41
field=32
form footer:
total=114
but every time i try to put this "=sum([BudgetedNumberofSales])" in the field in the form footer it gives me an error.
any ideas of what i am doing wrong.
thanks
chad
I have a report that prints the report header and footer on seperate pages. The only problem is that the page footer and page header show on the pages with the report. Since the report header and footer show on seperate pages I don't need the page footer to show on these pages. How do you suppress the page header and footer on the pages with the report header and footer?
Thanks
I have a report that is several pages long, the page footer I want to show only on the last page, how can I work out a counter/indicator to know which is the last page and be able to set the footer fields to visible
Jabez