When I execute the below function I get an error. If I use the commented out "Open" line it works fine. I get Run-time error '52'. Bad file name or number.
Private Sub GenerateSSA_Click()
Dim strRA As Variant, strFileNew As String, strFileBlank As String
Dim intFileOut As Integer
Dim rstWork As DAO.Recordset
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
[Code] ....
I have a variable (dtDueDate as Date) showing as 6/28/2013. I want to append a time value to it (dtMaxTime as Date) which is 5:00 PM so dtDueDate reads 6/28/2013 05:00:00 PM. I have been going in circles trying to figure this out. My goal is to append this date to a table field which has a datetime (General Date).
View 6 Replies View RelatedIs there a quick way to export ALL VBA code to a text file/word document?
View 5 Replies View RelatedI need to extract a specific number of records into a table using a MakeTable or Append command using a temp variable, e.g. TempK&SA. Previously on the forum I was shown how code could be added to the OnOpen function to use a temp variable to select a specific number of records to report. ACCESS does not have the OnOpen function in the design view of a query like in the report. It does allow a SELECT TOP but only with fixed variables or percents (e.g. 25 in the code below).
The beginning code for the make table query (where 25 is the number of records added) is:
INSERT INTO [Output] ( RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] )
SELECT TOP 25 TestBank.RndNo, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.Exam1, TestBank.Status, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.[NCCPAKnowledge&Skills], *
FROM TestBank
WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And .....
how to modify the code to allow a temp variable to determine the number of records to append to another table would be gratefully received. (This process then is repeated for a total of 7 append tables with different temp variables.)
I currently have a working model which opens up a word document template, fills the bookmarks with info from access, saves the file as a preset name and then send the document on Outlook.
What I would like to do is save the document as a variable, preferably as something like " Climbing Request for site (and then to lift the site name from Access form field and insert here) "
E.G " Climbing Request - Newcastle Central Station.doc"
The location of the site name on access is...
Code:
objWord.Selection.Text = Forms![Front Page]![Site 2 Owner]
Currently I save the document using the below code...
Code:
objWord.ActiveDocument.SaveAs2 FileName:="C:UsersmedesktopFrom Colino2test.docx"
This is the last step before "launching" my database automation.
I might be able to get "super easy mail merge" to work, but the problem is i want clicking the "merge" button to launch a file chooser box- so i can navigate to the word doc i want to use for the merge (destination doc will change with the currently selected record- i'm not merging a single record, however).
Basically i want to do exactly what right clicking on a table/query, then choosing "export" then choosing "merge with word" does (access 2007).
Can't do as a report because we may have "complex" letters- with graphics and/or tables.
why is something that is a simple right-click menu option so difficult to automate??
i'm using a form with an unbound text box to temporarily hold data that i want to use in several other places (all those functions work great). last step is to save the newly created table with a different name (will be used for a mail merge later so must be a separate table or else we'd have to filter thousands of records during each mail merge) and i want to use the data i've temporarily held in my unbound text box within my form as the name in the save as function.
can i specify a variable as a file name within the save as function? is there a better way?
hi,
Is there a way to store the text of a word document in a variable without opening the document?
I explain:
I have document1.doc opened
I want to copy and paste the contain of document2.doc at the end of document1.doc without opening document2.doc
I ve tried to use
Open "document2.doc" For Input As #1
Do While Not EOF(3)
Line Input #3, temp
tempstr = tempstr & temp
Loop
Close #3
but I don t get the result
Any idea?
I have a folder which holds 1000s of pictures organised in subfolders, for example:
D:~AI Database Print Scans2009family and
D:~AI Database Print Scans2009holiday
And so forth. In total at the moment 17 main subfolders, each of which hold another 2-3 subfolders.
I am putting together a database to bring pictures together with all sorts of details. I import the picture via hyperlink and complete the various fields. All that works fine but there are a lot of pictures! And it gets confusing to see which ones have already been entered into the database and which ones haven’t.
One solution for this is to copy the pictures that have been “completed” to another folder. I have found a way to do that:
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = Forms!frmPrintDetails.txtPath1
newPath = "D:~AI Database Print ScansCompleted_Entries"
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath, newPath
Set fs = Nothing
Works fine, but the problem is that I loose the subfolder structure, it copies all pictures to the same main folder. I’d like to maintain the subfolder structure and add code to create the correct subfolders and next copy the picture. With the different paths I’m lost. I don’t know how to extract the correct path in code. I’d also like to either rename or remove the picture once I have completed entry of the details. The problem I have again is that I don’t know how to code for a changing path.
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...
I have a form which i use for a user to select an excel file they want to import and then click a cmd button to import the file into a table which works fine, however i want to append a date into a date field from an unbound txtbx before the file is imported so it will look something like;
id;date;excel info;excel info;excel info.
i have created a form that has data in a field as a referance, i want to use that ref to open a page or PDF file to that referance ,
The idea is that the form is the data input for hard copy documents, i will scan the documents and store them on a hard drive i then want to open the documents as word, or PDF files from the form to the ref of the field in that form
can anyone help
how to manage attachments (Document or File Links).
View 1 Replies View RelatedI'm trying to figure out at what point an error message occurs while a database is closing. This problem only occurs if someone clicks on the red close button on the top right of the screen when there are several forms open. If the user exists normally there is NO problem.
I am attempting to use the print statement to write to a text file to track what is happening as the database shuts down. Unfortunately, the open (print) statement, when issued, zaps any prior contents which precludes having a full printed record. I am hoping for a print syntax that would allow appending.
Code:
Open "ErrorReport.txt" for Output as #1
Appended text to the file to show what is happening in that event.
Close #1
The program, in closing, does stop with an error message that a certain file can't be found. But when I click on debug, the program simply closes. I have also commented out VBA references to the file that can't be found, but the error still persists .
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer
LR = 5
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wbRef = xlApp.Workbooks.Add
With wbRef
wbRef.Activate
.Worksheets("Sheet1").Activate
With ActiveSheet
.Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
end With
I have a query that combines few different tables in order to create a View (Query) that is then used to by and Excel sheet to update a list. The Excel is dynamical updated when new data is inputted in the Access Tables.
But one of the fields in a query is combination of path name and report name (another filed in one of the tables) that crate a complete path to a file that contains some additional information.
Now since the Excel is updated dynamical and users of this Excel sheet are not very advanced. I waned to make it easy for them to just click on the location of the file and the file opens up. But I am not able to make the Query that contains the file destination hyperlinked.
I am using Access 2007
I am trying to write an append query which is based on a table (PA0041)having muliple columns. The columns are DAR01, DAT01, DAR02, DAT02, etc etc.
The data for DARnn will contain values such as S1, S2, S3 etc. For each of these values, there will be a corresponding date in the corresponding field DATnn.
The table PA0041 looks something like this:
Employe Number, DAR01, DAT01, DAR02, DAT02, DAR03, DAT03 ...
1, S1, 21/02/2006, S2, 13/01/2006, S3, 10/01/2006
2, S1, 15/02/2006, S2, 09/03/2006, S3, 18/02/2006
3, S1, 06/01/2006, S2, 16/02/2006, S3, 01/03/2006
4, S2, 03/03/2006, S3, 26/01/2006
There may be up to 10 columns for each - i.e. DAR01 up to DAR10.
I can append them into 1 append table if I write 10 queries. What I would like to know whether it is possible to do this using 1 query. I need to write both the date type and the date into the table.
In addition I need to know whether any employee is missing any date types. If I have a table called tblDateTypes which holds the date types that I would expect the employee to have as a minimum, how can I check for any employees who do not have a required date type.
If the data contained in tblDateTypes is as follows:
DateType
S1
S2
S3
In the above example I would expect the end result to flag the fact that employee 4 does not have date type S1.
Let us assume in the above example that the employees all exist in the table PA0041 - even if they don't have all the required date types.
Let me know if you need more information.
Thanks,
Evan
I work in a school where teachers have to archive their assessments and other documents monthly. Currently they email them to a set email address but I was wondering if it would be possible for me to make something with access whereby they can do this. I'd like to make a form where they selected their name and then attached a file, this file would then be saved in an area I would have assigned that persons name to, does this make sense? I want it to be very simple so just a name section from a dropdown then a file upload section and done.
View 11 Replies View RelatedI look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..
Code:
Public fDate As Variant
Public vField As String
Public vFile As String
'GTSdata
vField = "txt_gts_data"
[Code] ....
What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate
What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .
Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)
How do I say update the contents of the variable, not the variable itself?
Is it possible to format a PDF document created in MS Access i.e. set the document to landscape instead of portrait as in the following code;
DoCmd.OutputTo acOutputQuery, "Query Name", acFormatPDF, "Document Name", False
This PDF document is attached to an MS Outlook e-mail and sent without any intervention from the user. This is accomplished using further VBA code. This code is working at the moment sending both Excel spread sheets and PDF documents but the PDF's require the page formatting before sending. I want to be able to accomplish this without having to open another application such as Adobe Acrobat.
Excuse me!
i would like to know how to use browse function (like in windowXP) to select the excel file i want instead of using transferspreadsheet code.
thx!
I use the following code to achieve following
a) Open a word document from MS access / VBA,
b) Connect to data from a query
c) mailmerge it
d) Save it as a new document containing mailemerged letters.
The code does open the required document as I can see it open.
I put a message box to recheck that filename/path is what I mean, and to to know till which line the code runs. Code runs till that point and that word doc is seen open. I get the error message before
Then I get error message.
I was fooling around and was getting message " This command is not available because no document is open" I tried to remove few lines thinking redundancy. Before getting the message, I was also getting the error trapping message "No documents opened."
Now I am getting different message.
I am getting message " The remote Server Machine does not Exist or is unavailable"
Following is the code I have written.
Dim xlApp As Object
Set xlApp = CreateObject("word.Application")
xlApp.Visible = True
fn = FormPathName("941 2014 AutoOpen.doc")
[Code] .....
We need to replicate an Access report we have in Microsoft Word. The report has a fixed, small image in the header and so we embedded it in the report (it is not in an external file). To put this image in the Word document the only way we have come up with is shown in the code below.
Code:
Dim apWord As Word.Application
Dim doc As Word.Document
Set apWord = CreateObject("Word.application")
doc.Shapes.AddPicture "G:ImagesSinful Banner.bmp", False, True, 0, 0, 540, 42
Which requires an external image file. We really would like to avoid this. We could make a template Word document, but that too would be an external file. We know how to put this image in a table as an OLE object, but can't find any way to get it from the table into the Word document.
I have built a simple database (by no means complete) but I am after adding some further code into it. Basically on the form [FrmMain] I have a button that allows me to send a document to a relevant CSU contact. It finds the file name based on the info in the table TblResident. What I want to do is open the word document before sending and add a password to it using vba.
View 1 Replies View RelatedI have been trying to populate a merge document from access. I have the dotx files organised and the recipient file (Query) within my main access project. The following code opens word, opens the selected file (dotx) but can't find the db file.
I am using access 2010 and .accdb database format. I have temporarily put in MsgBox lines to display the running content of fields and they are producing the right info.
Private Sub DocumentsCbo_Click()
' load precedent template as a dotx, merge fields into precedent document, allow user to modify if needed, save it as a pdf
On Error GoTo ErrTrap
Dim MyPath As String ' Path of the source template
Dim DestPath As String ' Path of destination document
[Code] ....