Modules & VBA :: Recordsets - One Access Report To Multiple Files
Sep 9, 2014
I have parameter form that ask for state and begining date. on click it runs a query and creates a report by state that has multiple pages.. I want to create a separate pdf file for is page (which would be by delegate_name).
This is the coding I have so far and am gettting the error '424' object required. This is the coding I currently have. I haven't worked with access in over 9 yrs so am a little rusty with it.
StateAbb = Me.State
PeriodStartDate = Format(Me.Period_start_date, "MM-DD-YYYY")
Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Dim strRptName As String
[Code] ....
View Replies
ADVERTISEMENT
Apr 24, 2014
I have 3 tables; tblProjects, tblTesting, and tblEmployees.
The issue I'm having is that I've created a form which collects testing information from testers that test a particular project. Each project can have multiple testers. tblProject and tblTesters have a one to many relationship and the form (frmTesting) consists of a main form for project info and a subfrom for testing info.
For each project tested there are 4 possible outcomes. 1st not all testers have tested to project and that case project status is "InTesting", 2nd all testers pass testing in that case project staus is "Waiting Final Approval", 3rd all testers fail testing in that case project status is "Maintenance" and last some testers pass and some fail in that case project status is "IPR".
Here is my code for a button I've placed on the sub form.
Private Sub BtnTest_Click()
Dim db As DAO.Database
Dim rsSQL As DAO.Recordset
Dim intMax As Integer
Dim strSQL As String
Dim csSQL As String
Set db = CurrentDb()
[Code] ....
My select statement for my recordset isn't working and I've tested it by putting a specific project number in the statement and I still only pulling one recordset.
View 4 Replies
View Related
Sep 20, 2014
I made a database that in one of the forms, I like by clicking on a button the user be able to select 5 excel files with different file names (in the same directory) and then based on the imported file's names, it be stored in 5 different tables.
At the moment by using the bellow code, I can import multiple files (with the same formats) only into one table . My vba code comes as follow:
Function GetAllFiles()
Dim fd As Object
Dim strFilter As String
Dim lngItems As Long
Const msoFileDialogOpen As Long = 3
Const msoFileDialogViewDetails As Long = 2
[Code] ....
View 4 Replies
View Related
Aug 1, 2013
I am trying to build a newer database 2010, based on an older one,2000, that has been locked tight and I cannot see the modules to kinda get a reference of where to start. I am trying to find a VBA code that will allow me to import a several text files to one table. The text files are all in the same format but I cannot remove the page headers and footers to get the table to look right. I have attached an example of the text file i am trying to import but it is a stripped down version for information protection.
Also, it appears in the old Database Table once imported as:
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
If I could import the text files and end up with a table like this, it would be all i need as i could run all the queries i need from this.
View 3 Replies
View Related
Jun 14, 2014
I created report "rpt" with a few subrpts. I would like to export this report to word by value in column M_AGENDA_KOD.
M_AGENDA_KOD column has about 370 rows, so i do not want to export each manually.
So I would like to have each M_AGENDA_KOD value as separated word file named by that M_AGENDA_KOD value.
Is that even possible?
View 14 Replies
View Related
May 15, 2014
Basically what I am trying to accomplish is uploading multiples file automatically into server. The code I am trying to use works great if the user wants to upload a single file manually because the code prompts you to choose the file and I am wondering if there is a way to tweak the code. here is the code I am using
Code:
Private Sub Form_Load()
Dim objFTP As FTP
Dim strfile As String
[Code].....
View 3 Replies
View Related
Jul 8, 2013
My report has tens of pages and I need to save each page as separate file with ID as filename. PDF prints with ID but all in one file. I use this code.
Option Compare Database
Private Sub tisk()
Dim cesta As String
Dim kod As String
Dim Sql As String
Dim rs As Recordset
[Code] ....
View 5 Replies
View Related
Oct 29, 2013
My code is creating a pdf file that includes everybody's reports instead of just that faculty member's reports.
Once I get it to generate the correct reports, I then have to figure out how to automatically email them.
Public Sub something3()
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
[Code] ....
One other thing is that I can't get the &".pdf" to work on the end of the filename.
View 10 Replies
View Related
Dec 17, 2013
Currently I have a code that imports a single text file into a table without problems.
I need to be able to selecte multiple text files and import them at once.
Here is my current working code.....
Code:
Private Sub Import_Click()
Dim strFilter As String
Dim strInputFileName As String
strFilter = ahtAddFilterItem(strFilter, "Text File (*.txt)", "*.txt")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
Filter:=strFilter, _
DialogTitle:="Please select new file...", _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
DoCmd.TransferText acImportDelim, "Import Specs", "Data Table", strSaveFileName, True, ""
View 14 Replies
View Related
Jul 25, 2013
I have a few hundred access databases all with the same tables and structure, and want to import all the data in a 'mother' database (which i created with the same tables and structure).
I am using a sub that feeds a string to another public sub as follows:
Code:
Private Sub cmdImport_Click()
Dim strFolder As String 'source folder path for import
Dim strFile As String 'individual file path
strFolder = GetFolder(Environ$("USERPROFILE") & "Documenti")
If Len(strFolder) <= 1 Then Exit Sub
[code]....
View 3 Replies
View Related
Apr 24, 2014
I have a requirement to create a piece of vba that will open all xlsx files in a folder one at a time then import the data in a range (sheet1!A1:G14) into a table named Weekly Input.
View 4 Replies
View Related
Jul 21, 2015
I have contract notes files in .xls format towards sale of equity shares from the brokerages. I have to do some editing in these files like deleting some rows, adjusting width of columns and inserting date of contract.
a) Contract-A.xls shows original file.
b) Contract-B.xls shows modified file
c) VBA macro code used for deleting rows and modifying column width is shown in modify_contractnotes.xlsm file.
d) Presently I open the file and copy and paste the contents on the macro-enabled workbook sheet . After macro operation I transfer back the contents by copy and paste to a master file(Contract-C.xlsx) and clear the contents in the macro enabled workbook to make it ready for another operation.
e) It is time consuming process for around 60 files per month.
f)After this operation I run RDBMerge add-in to consolidate and normalize all these files in a directory. RDBMerge smoothened out merged cells also.
Is it possible to have a macro-enabled workbook open all the time and the contract files be opened one after other and macro contained in macro enabled workbook by modifying its code suitably operate on these contract files.
Files are attached in modify_contractnotes zipped folder.
View 4 Replies
View Related
Feb 8, 2006
We've had a problem for quite a while with regards to multiple users being able to view/run, queries/reports at the same time.
If someone is running a query/report, and then someone attempts to use any other query/report that’s already using the linked .txt file they get the error message:
The Microsoft Jet database engine cannot open the file ". It is already opened exclusively by another user, or you need permission to view its data.
I've read various posts with regards to user rights to the folder which the DB is stored, which isn't the problem in this case (we have tested and set user access to all read, write, delete etc).
I've also read that .txt files are managed in a different way than most other file types, in that DOS controls user access rather than jet?
So my question is:
Is there a way round this issue with linked tables that use .txt file as there source?
Thanks
Darren
View 2 Replies
View Related
Mar 3, 2006
Hi I have a few .csv files that i would like to upload into an access db. I saw an example for DTS, but since i dont have sql server i can't go that route.
Since i have some .csv Files where the file name changes on a daily basis, is it possible to upload them all with some wild card function. there is already a table with the fields already predetermined, and all i basically have to do is just load them all up at once.
please let me know if you have a solution.
View 2 Replies
View Related
Nov 20, 2013
i have a database which has new customers added daily (currently we post all documentation)
when a new record is filled in the customer receives:
cover letter/invoice(report), book page(report) and a book (pdf, always the same) the reports are created from the record
rather than post all these documents i would like to email them to each customer in 1 email with the reports in pdf format.
Example:
CC (email feild from the record)
Subject (pre filled with "Stop Bullying Now! Books 4 Schools order")
Body (pre filled with "Dear ([Contact]if possible) Please find enclosed your documentation for your recent telephone order bla bla bla and our logo.jpg if possible)
then i would like to attach to the email the cover letter/invoice(report), book page(report) and the book (pdf) from the hard drive (r.eports changed to pdf)
if i can press a button on the form to send it that would be good.
if i could link it to a query to send a batch of them that would excellent.
The Database is Named: b4s.mdb
The table is called: Main Table
The input form is called: Client
The letter/invoice report is called: b4sletinv
The Book page report: b4sbook
The pdf book is located at c:Documents and SettingsAdministrator4sfinal.pdf
The query for the batch is called: coverlet&proforma
View 2 Replies
View Related
Jul 12, 2015
some code I've come up to in order to import multiple excel files each of them with a different number of worksheets into an access table. The procedure is called from an Access database. The problem I have with the code is that when it encounters a workbook with only one worksheets (e.g. Sheet 1) it gives the error that "Sheet 2$" is not a valid name. When geting to a workbook with 2 sheets it says that "Sheet 3$" is not a valid name and so on and so forth. Is there a way to "check" the number of sheets in the workbooks and when it has only one sheet to transfer it and go to the next file?
Below is the code:
Code:
Sub ImportExcelFiles()
Dim strFile As String 'Filename's
Dim strFileList() As String ' File Array
Dim intFile As Integer 'Number of files
Dim filename As String
Dim path As String
DoCmd.SetWarnings False
path = "D:Tranzactii"
[Code]...
View 3 Replies
View Related
May 21, 2015
I have a basic access query (MACs Report Template) that I need to export into multiple excel files based on the 1st field (Plan ID).
Example:
PLAN ID
Number
Amount
Status
AM141
12345
100
Disconnected
AM141
54321
5000
Active
AM142
11122
2000
Disconnected
AM155
22334
500
Disconnected
I need this to create a spreadsheet for each unique PLAN ID. Ideally i want it to export the following:
MACs Report AM141 20150521.xls (both records above should be in this report)
MACs Report AM142 20150521.xls
MACs Report AM155 20150521.xls
View 3 Replies
View Related
Mar 25, 2014
I have a lot of Excel files and each of them has 3 sheets that I would like to import in Access 2010. How can I import them without having to do one by one?
I always get error on
Code : Application.FileSearch
View 1 Replies
View Related
Mar 20, 2014
Access2007
At this point, FE db has 4 linked tables, 3 are located in FileBE1, and the 4th is located in FileBE2.
The production BE is located on a server, the test (when working remotely) is on a local computer that is not able to connect to the server. Also, the test BE files (but not the table names) have different names than the production.
I have the idea of creating a table in the Front End that contains a list of users, filename and location (server or local drive).
Upon startup, how could I set up the vba to check the links and if they need to be changed will go and change them without the user having to click on link table manager or be prompted for names and path.
For now, I envision three rows in the table, one for the test user, one for local user and a default UNC for everyone else. (or would it be six rows since there are two files that need link checking and relinking).
View 2 Replies
View Related
Apr 20, 2015
I am running Access 2013. I have created a report where the tables are linked to a SQL DB
I am trying to run a sales report that includes an stored image.
I am adding an Image and then using the control source to look up the 'filepath' stored in the DB. When I run the report on screen all the images are showing but when I try to print it (including exporting to PDF or XPS) and Print Preview only 2 of the 9 images print per page. The images are around 2-4 Mb.
I'm guessing its to do with the files sizes but cannot do anything about the size.
View 1 Replies
View Related
Jan 23, 2008
Hello,
I have some data in excel which I am importing it into MS Access 2002. Each excel file (one worksheet per file) is imported into separate tables.
I want to combine all my imported tables into one table. Is that possible, if yes then how and if no then what can be done to get single table after impoting data from various excel files.
Cheers,
Mandeep
View 14 Replies
View Related
Oct 20, 2014
I have a form based on an underlying query in a .mdb database. I want to be able to navigate backwards and forwards in the form using .movenext and .moveprevious etc.
Which sort of recordset should I use? I've seen discussions about ADO and DAO, and have come across the term "forward-only", which I'd not been aware of before.
Having made the choice, what is the correct syntax for opening the appropriate recordset, and also for .movenext and .moveprevious etc?
View 6 Replies
View Related
Sep 2, 2013
When I try to compile the code below I get the error message 'Sub or Function not defined'. I'm using Access 2007.
Code:
For x = 0 To 20
If Nz(rec1(x), "") <> Nz(rec2(x), "") Then
DoCmd.SetWarnings False
strSQL = "INSERT INTO tblResults ([Version], CUSTOMER_ID, CUSTOMER_NAME, CUST_LONG_NAME,
[Code] ....
What I'm trying to do is compare two recorsdsets and if it finds a difference, record the result line from both recordsets in a new table. This worked fine until I tried to go beyond more than 21 records from recordset 2 to publish to the Results table.
The problem arises in the 2nd INSERT INTO... statement. I get the error and it highlights the rec2(20) value. I can enter anything after it (it doesn't have to be a recordset value) but the error still occurs and it wont compile. If I close the statement at Rec2(20) it works fine.
View 6 Replies
View Related
Apr 22, 2015
Is there any way of opening recordsets as public or global in a form? E.g. if i do something like
Set rs = db.OpenRecordset("TblCustomers", dbOpenSnapshot, dbReadOnly)
the variable rs should be available in all the private sub i have on that form, and i should be able to access records without opening database/ record sets for individual subs.
View 6 Replies
View Related
Nov 11, 2013
I have to write a code for my database,i have folder with files "pending Review" and a table with column "tblExcelLocation". when i run my database all the files from pending review folder goes to "tblExcelLocation" on a click of button.But,if the files already exists it should not insert those files and insert the rest.For this i tried to write a code but i think i m unable to do that .
Code:
Loop through files in folder
folderspec = "O:QA FilesQC ReportingPending Review"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.files
[code]...
View 7 Replies
View Related
Apr 29, 2015
i want that if any powerpoint application is open after closing my access app all of these powerpoint files will be closed. i found this code for excel file and i changed it for powerpoint application. but i have an error message in this pink line?
error 438: object doesn't support this property or method
Code:
Public Function test()
Dim oApp As Object
Dim workbook1 As Object
[Code].....
View 2 Replies
View Related
