Modules & VBA :: Code Runs OK Once But Error Next Time
Jun 26, 2015
The task is (1) output an Access query to Excel (2) overwrite that file if it already exists (3) apply specific formatting to the header row and the other rows in Excel. I have cobbled the code together from two sources. The beginning and end are adapted from code on btabdevelopment.com but the large insert in the middle is code I got form a project a former colleague had done. But he's no longer around.
The problem: I click the button and everything works OK. The file is created and formatted just how I want. If I click the button a second time though, it seems to run OK, but when I open the file it is NOT formatted. However, theres another window behind it called Book 1 which has all the data and all the correct formatting it just hasnt been saved. If I click it a third time I get an error message that says Object variable or With block variable not set. Im not even 100% all that is accurate because I have tried it a multitude of ways, closing and re-opening the form, closing and re-opening Access itself, starting with Excel open or closed, never with the destination excel file open though. I dont seem to get exactly the same behaviour any two times. But as far as I can see, if I close and re-open Access, it always works the first time. So I can live with it.
Code:
Private Sub cmdExport_Click()
On Error GoTo Errhandler
Dim rs As DAO.Recordset
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
[code]....
View Replies
ADVERTISEMENT
May 29, 2014
I have a little problem in creating a msg box that then activate a code. How can I do? I was doing
Private Sub Chiusura_Pratica_Click()
MsgBox "Bla Bla Bla " _
VbMsgBoxStyle.vbYesNo
If Response = vbYes Then
Me.Stato.Value = "A Scadere"
Me.Assegnato_a.Value = ""
Else
MyString = "No"
End If
End Sub
But it doesn't work...
View 3 Replies
View Related
Jul 21, 2015
Access 2007, Sql Server 2008 R2. Problem with refresh.
Form 1, (Single Form) Parent Form, contains Property Year Detail data. PK = PropYrDetID. This is a SINGLE FORM
Form 2, (Single Form) Child form, contains land square footage data from multiple records. PK = SPYDID FK = PropYrDetID. This is a SINGLE FORM.
The code below executes every time Form 1 opens, activates or whatever. The reason is that data in Form 2 changes frequently. Thus every time Form 1 opens the end user will see the latest data.
"Form 1" receives a series of values that it obtains from VBA code that runs when it opens or activates.
"Form 2" has the data being summarized in Form 1.
All of the code and queries below run fine and return the correct values from Form 2 to Form 1. My problem is Refresh on Form 1.
I've tried the code in the On Current, On Activate, On Load of Form 1. Mostly the data shows up in Form 1. Other times parts of the data are left out. I have to hit F5 or close and reopen the form, and then the data appears. When I don't see data on Form 1, I check the underlying table and the correct data exists. I don't know how to achieve a 100% refresh success. I have tried me.refresh and different Events all over the place.
All of the code below runs when Form 1 opens. Queries are fine, Equations are fine, Tests run fine. It's the results showing up that I am having the problem with.
I don't think the issue is with the code but with the Refresh. Here it is for your review.
The term "Equations" is the customers. There are 5 equations. Some have a series of tests after the Equation to determine the result.
Code:
Private Sub Form_Activate()
Dim rs As Variant
Dim varAOProp As Variant
Dim varAOIni As Variant
Dim varAOCert As Variant
Dim varBORIni As Variant
Dim varBORFinal As Variant
[Code] ....
View 5 Replies
View Related
Mar 17, 2014
I have a userform that pops up when I am implementing a VBA subroutine. The nature of the form is simply to update the user what progress through the operation the code is using a label called lblProgressText.
So, I have a form called frmProgress and in my loop I use:
Code:
DoEvents
Form_frmProgress.lblProgressText.Caption = Format(rsLongItems.PercentPosition / 100, "0.00%") & " - Long items"
Form_frmProgress.pbProgressBar = rsLongItems.PercentPosition
Form_frmProgress.Requery
Form_frmProgress.Refresh
Form_frmProgress.Repaint
I know I don't need the .requery, .repaint and .refresh lines but I put in there just to check it wasn't that causing the issue.
When my code runs, the form is opened using:
Code:
Form_frmProgress.Modal = False
DoCmd.OpenForm Form_frmProgress.Name, acNormal, , , , acWindowNormal
The form Popup property is set to Yes.
The lblProgressText control just wont update (but earlier today it was so maybe I have broken something).Btw, all this code is run from a Module, not in the form object.
View 3 Replies
View Related
Nov 5, 2014
I have the following code that creates a long string that works most of the time but sometimes gets cut short. I can't understand why it does this. When it cuts the string short it cuts it short in the same place. Everything gets in the string up to/or about the following code '</Practice Name>'This string is needed to upload info to a server.
Code:
dim msg as string
msg = "<?xml version=""1.0"" encoding=""utf-8""?>" & vbCrLf & _
"<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">" & vbCrLf & _
" <soap:Body>" & vbCrLf & _
" <UpdateDataForScreens xmlns=""http://mdtoolboxrx.com/"">" & vbCrLf & _
" <PatientObj>" & vbCrLf & _
[code]....
View 10 Replies
View Related
Oct 8, 2014
I have a function that formats minutes to "hh:mm:ss" but its doesnt work if minutes is equal to or greater than 1440 minutes/24 hrs.
Code:
Forms![frmTasksTimer]!estimate_time = FormatTime(Forms![frmTasksTimer]!amount_left, Forms![frmTasksTimer]!Rate)
Code:
Function FormatTime(Amount As Double, Rate As Double) As String
Dim x As String
x = CStr(Round(Amount / Rate * 60))
FormatTime = Format(x / 1440, "hh:mm:ss")
End Function
How can i modify this code to accommodate time that exceed 1440 minutes?
View 9 Replies
View Related
Aug 22, 2014
I got these three code routines for backing up an Access database file from two different forums. I installed them as is, on a test database, creating command buttons for each. The test database is not password protected. I intend (if I can get one of these working) to install it on simple, single file, password protected databases.The first two came from Access World Forums at: URL....
Code:
Function BACKUPS()
On Error GoTo Err_BACKUPS
Dim fso As Object
Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String
[code]....
I got a compile error "Invalid use of property." on the button sub. Then there was a simple On Click sub:
Code:
Private Sub Backup_Click()
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile CurrentProject.FullName, "C:", True
Set fs = Nothing
MsgBox "Database has been backed up successfully"
End Sub
Private Sub Command32_Click()
End Sub
On this one I got a "Permission denied." error on the CopyFile method. As I said before, my database is not password protected.I found this third bit of code on another forum: URL...
Code:
Public Function db_Backup()
On Error GoTo db_Backup_Err
Dim sourceFile As String, destinationFile As String
Dim aFSO As Variant
Dim path As String, name As String
[code]....
On this one I got another "Permission denied." error on the CopyFile method. Again, my database is not password protected. This code had appeared to me to be the most promising of the three.
View 14 Replies
View Related
May 12, 2015
I've got a form that takes the members from my members table and allows me to take attendance. I have it set up with toggle buttons in the footer (so we can see what class we're currently looking at) and I want to apply two filters when we click on a button. The two filters are "SS_Roll = Yes (or True)" and "SS_Class = AD1 (or whatever the class is)". I did some research and found one code for it, but now that I'm getting the missing object error and upon further research, I'm starting to think the code I found was only an excerpt. Below is the code I currently have. It highlights the first line when I hit debug.
Code:
Private Sub OptAD1_Click()
Table![MembersTable].FilterOn = True
Table![MembersTable].Filter = "[SS_Roll] = " & True And "[SS_Class] = " & AD1
End Sub
View 11 Replies
View Related
Mar 1, 2014
Previously my snapshot file was displaying thur this code but as 2010 Access, it produded subjected error and halt at below red color line.
Public Function LoadSnapshotFile(snpCtl As Object, strFilePath As String) As Boolean
Const conSnpFinishedDownload As Integer = 4
With snpCtl
.SnapshotPath = strFilePath
[Code]....
And it was runing well. But not now due to the produced error. I tried to make it pdf extension of the report file. It is updating the file at the path (where I kept the report) but problem is not displaying after click event on the form.
View 3 Replies
View Related
Aug 3, 2013
I have created a report in Access for around 800 uniquely identified line items. My end goal is to develop a script that will Print a PDF for each unique identifier and save them all in one folder and have each one named by unique identifier. Below is the script I have written, I cannot get through the Run-Time Error 91. The data is being derived from a SQL database.
Private Sub Command35_Click()
Dim rst As ADODB.Recordset
Set rst = Currentdb.OpenRecordset("SELECT DISTINCT [Unique_Identifier] FROM [tbl_questionnaire] ORDER BY [Unique_identifier];", dbOpenSnapshot)
[Code] ....
View 5 Replies
View Related
Sep 24, 2014
I am using the function below to calculate a field that excludes weekends and holidays. The weekends are excluded as it is now, but when I try to add in code to exclude holidays I am getting errors. The code for the holidays is in bold and a couple of the errors are Loop without Do so I remove the Loop then I get a Else without If.
Code:
Option Compare Database
Public Function WorkingDays(Due_Date As Date, Result_Date As Date) As Integer
'-- Return the number of WorkingDays between Due_Date and Result_Date
On Error GoTo err_workingDays
[Code] ....
View 6 Replies
View Related
Dec 20, 2013
I have the following code in a module that sends out various email updates to me when I'm off site. On my PC it works perfectly whether I have Outlook open or not. I've installed the database on several other machines, but I'm getting a Run Rime Error 287 on the .Send line if the user has Outlook closed. Any setting somewhere in Outlook or Access that needs changing? Or is there something that can be changed in the code to ensure the email sends whether Outlook is open or not?
Code:
Public Sub SendEmailNotification(strSubject as String, strMessage as String, strAddress as String)
Dim olEmailApp As Object
Dim olMsg As Object
Dim strEmailBody As String
Set olEmailApp = CreateObject("Outlook.Application")
Set olMsg = olEmailApp.CreateItem(0)
[Code] ....
View 1 Replies
View Related
Feb 13, 2014
I have some code that creates appointment that i can send to colleagues, when I run the code first time it work all ok but the second time i run it i get a run time error see pic below
But I don't get any error's if i leave outlook open have also try the code on 2 pc's but stiil same problem
1392336756_tmp_run_time_error[1].jpg
Code:
Shell ("Outlook.exe")
Dim outMail As Object
Set outMail = Outlook.CreateItem(olAppointmentItem)
outMail.Recipients.Add (Me.txtsupervisor)
[Code] .....
View 3 Replies
View Related
Jun 11, 2013
I'm writing to an excel file from two separate Access tables, for which I'm using two separate DAO.Recordsets in VBA.The first table has 190 columns, the second table has more than 66 columns, so more than 256 columns will be written to the excel file.So I'm not expecting any error due to any maximum of 256 columns, but still I'm getting an error while trying to populate the 257th excel-column:
Run-time error '1004': Application-defined or object-defined error.(The same VBA works fine when I use another table with less columns.)Even more strange: on another computer I did not get the error and I was able to populate more than 256 excel-columns.
Code:
Option Compare Database
Option Explicit
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlWks As Excel.Worksheet
Dim FileNameOut As String
Dim RowNr As Long
Dim ColNr As Long
[code]....
View 5 Replies
View Related
Jul 6, 2013
When I enter character in input box then show run-time error with this code.
Code:
Private Sub cmdDelete_Click()
Dim password As String
If Command49.Visible = False Then
password = CStr("0" & InputBox("Enter Password"))
[Code] .....
View 3 Replies
View Related
Jun 12, 2015
I am trying to create a txt file to import into our accounting software. I get the file (its blank), but it fails on the WriteLine and i get the run time error. I have a command button on a form that the user will click to export the file.
Private Sub cmdExport_Click()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strPath As String
Dim strPathGB As String
[Code] ....
View 6 Replies
View Related
Nov 27, 2013
I am trying to call a stored procedure from access ,but it is giving me this runtime error :
Code:
2147217900
Syntax error or access voilation
I am doing this first time so i dont know about how to pass parameters (IN and OUT)..
My code is
Code:
Public Sub createDataToAnalyze()
Dim objConnection As New ADODB.Connection
Dim objCom As ADODB.Command
[Code].....
View 1 Replies
View Related
Apr 21, 2015
I'm trying to export queries from access to excel using the DoCmd option. The code (see below) works to a point - it exports some of the queries before I get a run time error:
"31532: Microsoft Access was unable to export the data".
The worksheet tabs also do not pick up the query name but instead return what looks like a temporary ID (e.g. ~TMPCLP118431). Have tried different file locations and versions of excel but the same thing keeps happening.
Code:
Sub ExportAllQueries()
Dim qdf As QueryDef
Dim db As Database
[Code]....
View 3 Replies
View Related
Dec 4, 2013
I am trying to create an INSERT statement from a form to put unbound fields in a table. The challenge that I am a getting is that I am getting a
Run-time error '3075' Syntax error in date in query expression '#'
What is really perplexing and perhaps something that may guide in identifying the culprit is that I have an identifcal form that uses the identical code and it works.
Here is the code below:
Dim strSQL As String
Dim strCriteria As String
strSQL = ""
strSQL = strSQL & " INSERT INTO [tblTicket]"
[Code] ....
View 4 Replies
View Related
Aug 11, 2014
I am getting Run-time error '3464': Data type mismatch in criteria expression when i execute the code:
Option Compare Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Public verificacoes As Boolean
[code]....
View 5 Replies
View Related
Jun 16, 2014
I have a linked table to a DB2 database. this table contains key-pair values and has about 140k records.
I use a Sub to update the value of a specific record.
The sub starts by opening the needed DAO recordset
Then it uses the rs.Findfirst method
It checks if rs.Nomatch is not true (so the records exists!)
Then it starts updating the record with
rs.edit
rs!value1 = myvalue1,
rs!value2 = myvalue 2
rs.Update
There is where I get the '3021 No current record' error
I use the same sub on the same table to update to different parts. One part works the other gives me the error.
I have checked for typos.
View 5 Replies
View Related
Mar 14, 2014
On double click event in the listbox, code as below:
Code:
Private Sub ListBox_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmTransactions"
stLinkCriteria = "[BinNumber] =" & Me![ListBox]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
When I double click any row then it show Run time error 3464.
View 3 Replies
View Related
Sep 22, 2014
I have a button which runs a list of queries that take roughly 10 seconds to run, when another user clicks the button while the other one is running it gives and error message 3405, File already in use.
View 1 Replies
View Related
Dec 23, 2014
I have a series of 7 separate Access 2010 programs that are designed to run under the control of a scheduler, and they must always finish. Errors are trapped and logged but mustn't stop the processes completing.
What I'm trying to do now is to create a single master control program that runs each individual application, one after the other, in order. And each individual app cannot start until the previous one is finished.how to call a separate Access program and know when it has finished.
View 2 Replies
View Related
Aug 17, 2006
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
View 10 Replies
View Related
Jul 13, 2012
How I can get rid of Disk or network error with error code 3043? What this error indicates.
View 4 Replies
View Related
