Help Needed With OutputTo Function:
Aug 18, 2005
Hi,
I am having trouble with the OutputTo function. I woul like to Output a report in html format but I would like Access not to record the file under the same name each time. So if my file is named 1_report.html I would like the second time I run the OutputTo function that it is name 2_report.html. Or as an alternative have the name include data from a form/table (In my case an order number).
I have a sample of the code from the converted macro:
Code:'------------------------------------------------------------' Output order to file''------------------------------------------------------------Function EnvoyerCommande_EnvoyerCommande()On Error GoTo EnvoyerCommande_EnvoyerCommande_Err ' Envoi la commande DoCmd.OutputTo acReport, "EnvoiCommande", "HTML(*.html)", SaveAs, False, ""EnvoyerCommande_EnvoyerCommande_Exit: Exit FunctionEnvoyerCommande_EnvoyerCommande_Err: MsgBox Error$ Resume EnvoyerCommande_EnvoyerCommande_ExitEnd Function
Note: here I have used the 'SaveAs' instead of specifying a location. This is not really what I want as I would prefer no user interraction...
Thanx for any help you can provide
See ya
View Replies
ADVERTISEMENT
Mar 14, 2005
Hello, need help with the Trim() function. In a query I am using this SQL:
SELECT TRIM(tblAllMembers.First_Name), tblAllMembers.Middle_Name, TRIM(tblAllMembers.Last_Name), tblAllMembers.Address1, tblAllMembers.Address2, tblAllMembers.City, tblAllMembers.StateOrProvince, Trim(tblAllMembers.Postal_Code)
FROM tblAllMembers
WHERE (((tblAllMembers.City) Is Not Null) AND ((tblAllMembers.StateOrProvince) Is Not Null) AND ((tblAllMembers.Country)="USA"));
However when I run the query, the First_Name, Last_Name and Postal_Code doesn't trim leading and following blank spaces.
Help please
View 13 Replies
View Related
Mar 23, 2006
Hi all,
To export a file to MS excel from my form, i used the command
DoCmd.OutputTo acOutputQuery, langName & partName, acFormatXLS, exportPath, False
however, i realised that the format of the excel spreadsheet (excel version 5.0/95) that i have exported is slightly different from the format of the spreadsheet if i had exported by clikcing on FILE, EXPORT...
how can i specify the excel version for the exported file? i wan it to be in the latest excel version else i am unable to perform some of the marcos i have written in the latest excel format?
Thks FT
View 2 Replies
View Related
Apr 7, 2005
Hi guys,
I’m working on a report that I want to show on the web as a Snapshot file. I created a Macro using OutputTo method to export the report. If I type the file name and location on the Output File it works perfectly, but the problem is that I want Macros to read the file name from a combo box since the file name will change every day. Here is the code I got so far.
C:Test””&FORMS!frm_FORM2!cbo_Name&”.snp”
Every time I run this Macro I got the following message:
“The report snapshot was not created because you don’t have enough free disk space for temporary work files.”
Now!, when I type the file name to that specific location Macro does the job perfectly.
Is there any way I can get the File name from a combo box located in FORM2.
Thank you so very much in advanced. Your help is always a blessing.
Mosquetero
View 2 Replies
View Related
Dec 1, 2007
Hi guys & gals,
I recently came across this really strange behaviour and was wondering if anyone else knows this bug or knows a better workaround than mine.
I've attached a little Events DB, which has a search interface. I've included an export button, which runs a macro that does an OutputTo (xls) of the search query.
Now if you do an export once, everthing is fine. The bug (at least I think it's a bug) occurs if you export a *second* time and *overwrite* the file you created earlier. The search query (which sits under the "Queries") tab is now empty and you get an error message.
My workaround idea was to dump the query to a new table every time you export and then do an OutputTo with this table. Not very elegant and you also have to do the whole error handling (e.g. user presses cancel) manually.
Does anyone have a better idea? Or am I doing somethng completely wrong and this is actually "by design"?!
Thanks in advance for any ideas!
View 2 Replies
View Related
Nov 10, 2014
Some of my users do not have access to all of our servers. In trying to automate I've hit one user whose system hangs up when Access tries to write to a server folder she does not have write (or even read) permissions for (Error 52, Bad file name or number). See the "Me.CitationType > 500" line below.
Code:
Private Sub comboStatus_AfterUpdate()
Dim hDate As String
Dim sFile As String
[Code]....
It gets as far as "If Len(Dir(hDate, vbDirectory)) = 0" and then hangs with Error 52. Testing for Dir() sooner doesn't work either.
how to either test for read-write permissions or trap Error 52. "On Error GoTo ErrorHandler" never fires to even test for 52, unless I'm missing something.
View 6 Replies
View Related
Apr 2, 2013
I am trying to export the data from a form to a textfile by using the following simple piece of code:
Private Sub Command188_Click()
DoCmd.OutputTo acOutputForm, "CC_MAIN_PROJ"
End Sub
it works, but gives me the data like this:
| SPEC | SIZE_1 | SIZE_2 | C1_s | C2_s | APS1 | APS2 | BPS1 |
what I really want is for the data to be without all the bars "|" and dashes etc. and even without headers. I just want a straight text output, maybe delineated by spaces or commas only.
View 1 Replies
View Related
Sep 19, 2004
I want to run a report, I need to provide an option to export the output to an excel file or provide a print view. Where would I put the DoCmd.OutputTo command in the report to acheive this??
thanks
View 1 Replies
View Related
Jul 28, 2014
I have a series of reports that are built to replicate the order forms of our suppliers. On each I have a text box for Air Freight that I only want visible when the airfreight ID is ticked.
I have this working fine using an if statement on the Load Event of the reports. However when I use the OutputTo command in VBA it seems to bypass the Load Event. Any event that is triggered using OutputTo.
I have also tried setting the .visible in VBA but can't get the coding to work as I am using a variable report name depending on the SupplierName and when I try to use the Reports! nomenclature it will not accept the Report name being a variable (ie. Reports!ReportName![Airfreight].Visible where ReportName is a variable).
View 9 Replies
View Related
Aug 24, 2007
Hi all,
I have been using this command in VB to export Access tables to Excel. Everything works fine except when the table contains a large Character/Text field- it gets truncated to around 200 chars. Is this a known limitation or I am doing something wrong? DoCmd.TransferSpreadsheet command works correctly but there are some limitations to this command.
Can please somebody help
Thanks
Claude
View 4 Replies
View Related
Dec 11, 2013
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies
View Related
Jan 30, 2014
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
View 2 Replies
View Related
Jun 9, 2005
We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields.
160037
213137
224356
235716
235800
12341
21708
22732
Here is the date function we use:
Function f2Date(strDateOld As String)
Dim strDate As String, strMonth As String, strYear As String
strMonth = Mid(strDateOld, 5, 2)
strDate = Right(strDateOld, 2)
strYear = Left(strDateOld, 4)
f2Date = strMonth & "-" & strDate & "-" & strYear
f2Date = CDate(f2Date)
f2Date = Format(f2Date, "mmmm d yyyy")
End Function
View 9 Replies
View Related
May 25, 2006
Hi all,
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
Thanks in advance.
View 1 Replies
View Related
Apr 29, 2005
I need some help on a database that is set up for work use.
It's rather large and I have no technicial knowledge of access whatsoever so an answer in plain english would be appreciated.
I have attached a picture of the screen that i am having the problem with. The blank fields contain peoples names and addresses. On this page i have to click on "update service user" on the first user, the database opens a seperate page and i enter a few details, then i click to go back to this screen.
When I return back to the screen, the scroll bar is at the top of the list. This is OK when I am doing the first few letters of the alphabet (the users are listed alphabetically) but when i have too use the scroll bar and choose the letters later down the list, then click to update then return to the screen and the scroll barr is not where i left it...ie: it's at the top.
So what I am asking is, is there a way of freezing this scroll bar so it will stay there even when i have navigated away from the screen to update each user?
If someone could answer me this query then I would be most grateful as it's quite time consuming for me to use the scroll bar and find the next person that i need to update.
View 2 Replies
View Related
Mar 15, 2006
Hi, looking for help big time!! I am a novice at using MS Access and struggling like hell. I have been set a project at University that requires me to import data from Excel, sort the data (i.e the data imported will be for a whole year and I am only interested in looking at single months), use calculations on the data imported and display this graphically. Is this possible with Access?? Any help will be gladly received as Im desperate.:(
View 1 Replies
View Related
Sep 18, 2006
I have a multiple user access database on share drive with user-security level created. I created myself as administrator, and remove default Admin from Admin group. Removed permission from user group and joined workgroup file which I created as "MBS work group.mdw". I created shortcut on user's computer. I believe the syntax is correct
"C:Program FilesOffice 2003OFFICE11MSACCESS.EXE" /wrkgrp "\ServerNameMBS DatabaseMBS Work Group.mdw" "\ServerNameMBS DatabaseMBS.mdb"
Everything works fine. When I directly double click the database (MBS.mdb), it pops up a login box. However, when other user double clicks the database directly (not from shortcut) from their station, login window doesn't pop up. It allows them to access the database. I saw the work group file used default System.mdw
My question is how can I restrict them by double clicking the database without go through shortcut? Why my database security not protect user access without give user name and password. I tried by joined user’s work group file to MBS work group.mdw. It works, but I don’t want let user manually to do that one by one.
Is there anybody can help me go through this problem?
View 10 Replies
View Related
Dec 27, 2006
Sorry, i dont know where should i post this( under which category)
so i just post it here,
I want to create a login page using access,
and then i wanted to record down the name of the user and the time and date(login and log out) when he/she is using the database.
And then record it down in a table.
Any ideas fellow forumers :)
View 3 Replies
View Related
Jan 11, 2007
Hi,
I am fairly new to DB's and have been trying to get my head round a database I want to build. I have had a few pointers from other people on here which has helped etc.
I was wondering if anyone out there would mind helping me with this project. I will go into detail if anyone is interested. I am hoping that someone would let me add them to messenger so when I get stuck etc, I could drop them a quick note. I am not going to harrass you every minute of the day (I promise not to try to anyway :D ). I would like to work with someone so I dont have to keep explaining it to someone different each time.
If this is something that someone wouldnt mind doing, please, please, please let me know.
Many thanks
Jamie
View 2 Replies
View Related
Apr 21, 2007
So far I haven’t got help on my DB.
I am attaching my DB.
The DB has two tables: DiaryTable & RBCaseTable
I want to resolve two issues on this DB
First issue: The DyNo field will start from No.1 on new year (DMax function). Since there will be duplicate records with the same DyNo for every year, I want to filter the records year-wise. For the current year, I should be able to do data entry, edit, search, print etc. For the previous year’s records, no data entry should be allowed and only search and print to be allowed.
Second issue: Two fields in both the tables are same i.e. DyNo and Remarks. I want this to be linked. What I want to do is that after I open ‘frmRBCaseAdd’ when I type the DyNo, the form should show the ‘Remarks’ field of DiaryTable so that I could update/edit.
I know nothing about VBA coding etc. Whatever codes I put in this DB was obtained from this forum and I simply made changes to suit my requirements. However, I failed to get help on these two issues. I shall be grateful if you could kindly help me. Thanks in advance.
View 4 Replies
View Related
May 9, 2007
I need help please.
1. I have a [CmdBtn] to do some calculation on the first day of every month.(The Btn and calculation code are ready and working).
2. On clicking, the [CmdBtn] will look for [FieldTotal] entry. If no entry, it will execute.(This code is also fine and working).
3. While executing the command, it will populate the [FieldTotal] automatically so that second clicking of [CmdBtn] will take no action and the populated field will tell the user that calculation is already done.(Code for auto-populating is also fine and working)
4. On the last day of the month, the [FieldTotal] entry will be deleted.(Here also the code works fine.).
I have two tables Table1 & Table2. I have the form for Table1. The [FieldTotal] is in Table2. I inserted a TextBox in form and tried to link it to Table2.FieldTotal throu Data-ControlSource, but it is not working. When I open the form ‘#Name?’ appears in the [FieldTotal]. I tried changing the field property to Text and then Number. In both options, I get the same ‘#Name?’.
Interestingly, when I leave the text box as it is without linking it to Table2, the [FieldTotal] in my form appears blank. Here I am able to populate the [FieldTotal]. But when I exit and reopen the form, the text box is again blank.
Please guide me (1) in linking the Table2.[FieldTotal] to my text box in form (which is based on Table1) or (2) how to keep the text box entry permanent till the end of the month.
View 1 Replies
View Related
Oct 10, 2007
Hello, i need simple database for my artworks. i made a few tables, but now i have to connect them and add some features. Also i will need a few reports... i think i will not be able to do it mysef. so i am looking for help... does anybody interested in this project? i will pay with Paypal. please, let me know
thank you,
Elena.
View 4 Replies
View Related
Nov 4, 2007
i'm eric. joining a couple boards and looking
forward to participating. hehe unless i get
too distracted!
eric
View 6 Replies
View Related
Nov 8, 2007
I am a novice and need help, i have built a simple database and i need to find out how to automatically update files across a network as soon as possible it updates on a couple of machines strait away but on others i have to close the database and then reopen it to get the updates. In addition to this i want to be able to search for a reference at the click of a button.
Any help on this would be greatly appricated as i am a novice
Thanks
View 10 Replies
View Related
Feb 8, 2008
Ok i have a database works fine just run into a little porblem..
In My VBA Coding i have made a SQL quiery the code is..
Private Sub Form_AfterInsert()
DBEngine(0)(0).Execute "INSERT INTO availability (ID) " & _
" VALUES (" & Me.ID & ")", dbFailOnError
'---------------------- Below Is The Code That -----------------------
'-------------------------- Doesn't Work -----------------------------
DBEngine(0)(0).Execute "INSERT INTO availability (Name) " & _
" VALUES (" & Me.Name & ")", dbFailOnError
'----------------------------------------------------------------------
End Sub
ok so the insert of ID works but the insert of Name does not..
Am i doing something wrong?? can somebody help please i need it urgent..
P.S.
i have been using MySQL for about a Year working on PHP and it seems much different from this SQL here on access.. and my suggestion would look something like this
"INSERT INTO availability ('ID','Name'), VALUES ('Me.ID','Me.Name')
Something along Those Lines..
- Alan
View 1 Replies
View Related
Nov 16, 2004
:confused: Hi, am new here & i need some help with some access database. Its for a little project am using to learn access.
Ok am designing a website for an airline company using access as d database end & asp for coding & stuff. My problem is a table i called flight. I have to write out a flight schedule in d database but am not sure how to do it, i wrote a schedule down in words using days. let me explain that, what i did was i have about 10 countries my airline company flies to, so what i did was grp 5 countries to fly mondays,weds,fridays & sundays & d other 5 fly tuesdays,thursdays & saturday. I don't know how to put that in the table since in d frontend customers are supposed to be able to search by date. On the frontend is a drop down menu where they pick a date like 29th november 2004 & then it shows d flight details but my problem is i wrote it in days. what should i do?? :confused:
View 5 Replies
View Related
