Modules & VBA :: Access Not Responding After Procedure
Mar 21, 2014
This is a real conundrum. The following procedure works well the first time through but on subsequent times the database just goes into "Not Responding" mode and has to be restarted. I have tried putting delays in but with little success.
The idea of the procedure is to rename a query, "Daily Logs" to include a specific date "TransDate" so that when the query is sent via Outlook the Excel file name appears for example as Daily Logs 21-03-14.
DoCmd.CopyObject "", "Daily Logs " & Format(Me![TransDate], "dd-mm-yy"), acQuery, "Daily Logs"
DoCmd.SendObject acQuery, "Daily Logs " & Format(Me![TransDate], "dd-mm-yy"), "Excel97-Excel2003Workbook(*.xls)", "someone@dorsetglidingclub.co.uk", "", "", "Daily Logs " & Format([TransDate], "dd-mm-yy"), "", True, ""
DoCmd.DeleteObject acQuery, "Daily Logs " & Format(Me![TransDate], "dd-mm-yy")
This procedure might seem clumsy but was the only way I could think of the achieve the objective.
View Replies
ADVERTISEMENT
Dec 24, 2013
I'm using the following code:
Code:
Private Sub Form_Timer()
Dim LocalTime, StartWindow, EndWindow As Date
LocalTime = Format(TimeValue(Now()), "hh:mm")
StartWindow = #11:46:00 AM#
EndWindow = #11:48:00 AM#
Dim Response
[Code] ....
The intent is to run automated back end maintenance activities while giving the user the option to delay it if they're in the middle of something.
My logic is as follows:
If it's between these two times, ask the user if the maintenance can run.
If user says yes, run maintenance.
If user says no shift the two times to the right and ask again in a few minutes.
As it is written, when I chose no I get an hourglass icon and if I try to do anything else Windows tells me Access isn't responding. I'm guessing that Access is hanging since I've initiated the Do Until and then stalled it by telling it to wait 3 minutes before it goes to the next step. I suspect that if I waited it out, when the new Startwindow hit I would be asked about running the maintenance again.
My issue is that I need the database to remain responsive to whatever the user needs to do while I wait to ask about running maintenance again. Therefore, I don't think Do Until is my answer - at least not the way I've implemented it.
The will ultimately be set on a 10 minute timer interval and start/end window will be 01:00 and 01:15 AM respectively. The times you see above are there for testing so I can see what happens.
View 3 Replies
View Related
Jul 31, 2014
I have just made a change to one of the forms by adding a button (by copying the only other button on the form) to cancel any changes and close the form. However, as soon as I added it I started getting the error message in the title. Please attachment LA Err1 for the full message. I also changed the caption on the other button on the form from "Close Form" to "Save && Close Form" this button is now giving the same error.
I have Compacted and repaired the DB on several occasions to no avail. I have deleted the procedures from the module and recreated them using the properties window - still get the error. I have deleted the buttons from the form and recreated the both via the object wizard and without it. Nothing I have tried has made any effect.
View 5 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
Nov 21, 2007
Hi
Does anyone know how to overcome MS Access not responding error. I am trying to change a Data Type on Access 2003 but the data is large and stops responding. There are about 550 000 records in one of my tables and when i try to change the data type after importing the data, it crashes.
I am using a pentium 4 with 1GB Memory
Thanks
View 4 Replies
View Related
Jun 28, 2012
in this query, I have about 25 small queries, 3 fields each, each linking to this one big table where one field of each query links to one field of the big table. Whenever I run the query, my program hangs up and I'm eventually forced to force-quit it. Is it because my computer isn't strong enough (it's your typical desk top computer for an office job) or could it be because the servers in which all the access databases are stored on aren't very good networks?
View 2 Replies
View Related
Jan 16, 2013
I am relatively new to Access and I was wondering if there is a way to automatically respond to delete table prompts when running a macro.
Currently I am running a macro that takes about 2 hours, and sporatically asks 'are you sure you want to delete table x'. I have to check on it every 10min or so to respond 'yes' or it will pause the macro. Quite a hassle if I am busy with other tasks and forget to check it.
View 4 Replies
View Related
Sep 17, 2014
In Windows 8, when we try to use the filter option in Microsoft Access 2010 using the keyboard option (arrow keys), the application stops responding.
The error occurs only if the rows are more than 300. However, using the mouse pointer works fine to filter content.
This error doesn't occur in Windows XP for the same Access database and for same version of access.
We have also tested using windows 8.1 with access 2010.Same error occurs even for windows 8 pro with access 2010.
The error is shown in the screenshot below.
acc.png
access12.png
View 2 Replies
View Related
Jan 8, 2015
For some reason our database keeps "not responding" ...
The application stops responding when:A field is selected that has a input mask
A combo box is highlighted (forcing the drop-down menu to open)
Certain buttons are pressed (in fact all but two trigger not responding, why the two buttons are immune)
if I put a break point on a section of code that runs when I press a button (any button, it doesnt matter) and I then press the button which takes me to VBA, to the break point. Then I press F5 to continue to run the code until the function ends (essentially doing nothing, just running the code to the soonest break point I can in the function and then running the rest of the code as normal...) it all works fine again!!!!
I fixed the issue by creating the function:
Code:
Sub aiuewjgaop()
End Sub
saved the database, deleted the function and then saved the database again.
View 1 Replies
View Related
Apr 28, 2014
I have a form with about 100 labels and would like something to happen when I mouseover. So I have a following code:
Code:
Private Sub Label1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Label1.BackColor = vbBlue
End Sub
[Code]....
View 7 Replies
View Related
May 11, 2015
I'm having an issue getting a return value from a stored procedure that I'm calling from VBA. This is what I have at the moment:
Code:
Dim strDate As String
Dim strWOStatus As String
Dim CurrentConnection As ADODB.Connection
Dim adoCMD As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim ParamReturn As ADODB.Parameter
[Code] .....
The problem I am having is this error:
Error: 424
Description: Object Required
The line of code it errors on is:
Code:
Set .Parameters("@PartsUSedMTD").Value = ParamReturn
And the value of ParamReturn is always Null after it hits the line before it.
So it seems like it's not really creating the parameter variable SQL Server needs to run
View 4 Replies
View Related
Aug 6, 2013
I've been stumped for the past couple of days trying to discern a method for a form to determine that it's a new month and run a procedure and only run it on month change.
My initial thought is:
Open recordset
move to the last record
compare the month in a date field within the last record to the current month
and run the procedure if the month is different.
I can't get this to work however and I'm not sure what I'm doing wrong. To be honest I typically fumble my way through VBA when it comes to opening recordsets. Here's what I got:
Code:
Dim rs As DAO.Recordset
Dim db as CurrentDB
Dim strSQL As String
Set rs = CurrentDB.OpenRecordset("FlightLog")
strSQL = SELECT [txtDate] FROM [FlightLog]
[Code] ....
txtReqNumb and txtFltNumb are the values to be reset at the beginning of each month.
Am I on the right track and just have the VBA wrong?
View 4 Replies
View Related
Jun 26, 2013
I'm trying to put multiple IF statements into one procedure like the below:
Dim Answer As Integer
Answer = MsgBox("Have You Selected The Correct Outcome?", vbCritical + vbYesNo, "Continue Request?")
If Answer = vbYes Then 'Works fine here
'The bit I can't get right
[Code] .....
View 2 Replies
View Related
Aug 15, 2015
I have 3 event procedure with 3 buttons to make them run.I would like to create another button that can run all procedures togehter. if I copy one of the procedures how do I tell it to run the other 2.
View 2 Replies
View Related
Jun 27, 2014
I have a form with a field called duration.
The user selects AM,PM or All Day from a list
If the user chooses All Day I want to create a duplicate record
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
However I would like the duration in the first record to be AM and the Second record to be PM
So something like Iif [Duration], "All Day" run procedure.
Else IIF do nothing.
View 2 Replies
View Related
Nov 21, 2013
On my form I have a combo box where the user can select the sex of a person. I had set the row source of the combo to a table, tblSex and this works fine. Now I want to programatically add the table entries ( simply male or female) to the row source, having set the combobox to value list. I cant see how to pass the combobox and tablename parameters and I am getting an error on form load "The expression you entered as the event property setting produced the following error: UserDefined type not defined" The calling procedure from frmPeople is
Code:
'---------------------------------------------------------------------------------------
' Procedure : Form_Load
' Author : Administrator
' Date : 21/11/2013
[Code].....
I think the way the parameters combobox and tablename are passed is incorrect. I want to write a robust procedure which I can use for each of the comboboxes on my form.
View 7 Replies
View Related
Dec 2, 2013
I have code for calling stored procedure with parameters,which is as follows
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Long
IdValueToProcess = 221177 ' test data
Debug.Print (IdValueToProcess)
Set qdf = CurrentDb.CreateQueryDef("")
[Code] ....
And my stored procedure is
Code:
ALTER PROCEDURE [dbo].[spItemDesc]
@ItemNo varchar(200) ,
AS
BEGIN
set nocount on ;
select ProductDesc1,ProductDesc2 from ProductDatabase.dbo.tblProductInfo where ProductNumber = @ItemNo
END
The error comes on debug.print(rst!ProductDesc1)
And it says "Item not found in the collection"
View 2 Replies
View Related
Jul 13, 2014
I need to stop already ran procedure or function with vba code . So I use END command ans everything stops.. in some cases that is useful, but some times need I to do something else after I use END command, but after END everything stops...
LIKE THIS :
SUB ()
END
ME.CTL.SETFOCUS
'OR
CALL function
EXIT SUB
View 5 Replies
View Related
Feb 23, 2014
I have a form with a button to print preview a report. This report needs one input parameter before executing. The computer this will run on is a touch screen and does not have a keyboard. Windows 7 has a "on screen keyboard" program. I want this to run first so that my user can input the parameter.
I have the following which throws an "Invalid procedure Call or Argument"
Sub CallTeclado()
Dim RetVal
RetVal = Shell("c:windowssystem32osk.exe", vbNormalNoFocus)
End Sub
View 4 Replies
View Related
Jan 15, 2015
Following concerns about someone accidentally deleting the access database we have been using to crunch performance numbers, I have successfully moved the data on to an SQL server
While the database works as it is, several of the queries are running extremely slow. I therefore decided to see if a stored procedure could run the number crunching on the server instead of passing the data back and forth all the time.
I have taken the series of queries and converted them into a stored procedure that runs too fast for me to blink while giving the same results as before.
The problem I face is that I can trigger the stored procedure from the server management studio manually while supplying the variables needed thus providing the data I need to export to excel in a table for this purpose.
What I want to do is to have a form in access supply the chosen variables (like I could before) and run the stored procedure at the click of a button as part of a series of other queries.
I have looked at pass-through queries but apparently they do not take kindly to variables unless they are hardcoded. The other solution would be to trigger it from VBA but I have not been able to find a solution I could get to work.
How to run a stored procedure on an SQL server from access while also giving it the variables it needs?
Stored procedure name: spNearMissCalculation
Variables:
@SelectedDate (date format) (taken from a form field)
@SelectedVessel (nvarchar(max) format) (taken from a form field)
@SelectedVesselGroup (nvarchar(max) format) (taken from a form field)
View 4 Replies
View Related
Apr 11, 2014
I have two forms:
frmOpeartions
frmManagers
frmOperations allows the user to assign a manager to an operation by selecting the manager record from a combobox. Occasionally the user may need to setup a new Manager record if one hasn't been setup already. In this case there is a "New" "button" (it's actually a label with an on click event) that the user can click to open frmManagers and add the new manager record.
The code to open frmManagers is:
Private Sub lblNewManager_Click()
DoCmd.OpenForm "frmManagers", acNormal, , , acFormAdd, acDialog
Forms!frmManagers!cboMoveTo.Visible = False
Forms!frmManagers!lblManagers.Visible = True
End Sub
Once frmManagers is open the user creates the new Manager record and then closes the form using a similar label with an on click event:
Code:
Private Sub lblClose_Click()
DoCmd.Close acForm, "frmManagers", acSaveNo
End Sub
frmMangers also has an OnClose event that will refresh any comboboxes on other forms that refer to tblManagers to make sure that new Manager records will be available immediately for the user to choose from:
Code:
Private Sub Form_Close()
If CurrentProject.AllForms("frmPlants").IsLoaded Then
Forms!frmPlants!cboPlantManager.Requery
Forms!frmPlants!cboQCManager.Requery
[Code] .....
So the problem comes when the user clicks the Close label (acting like a button) on the frmManagers. The code successfully closes the form and the on close event successfully refreshes any comboboxes on forms that may be open, but then for some reason it attempts to run again or perhaps continue running the onClick event that opens frmManagers. Since the form is already closed it gets hung up on trying to change the visible properties of the controls and the code fails.
View 6 Replies
View Related
Jul 13, 2015
I am trying to pass a recordset to a sub procedure as follows
Code:
Private Sub AddNewSProdStatus(rsTemp As DAO.Recordset)
rsSProdStatus.AddNew
rsSProdStatus![ProdID] = Me.Recordset![ProdStatusID]
rsSProdStatus![TitleID] = Me.Recordset![Title]
rsSProdStatus![ProdDealType] = 1
[Code] ....
However, whenever I call the procedure as below
Code:
AddNewSProdStatus (rsCSales)
I get a type mismatch error.
All Recordsets are declared as DAO and contain data...
View 1 Replies
View Related
Feb 6, 2015
I'm developing an application where I want to call the keyboard up on the screen when a user enters a field. This is my setup:
Windows 8.1 32 Bit, Access Runtime 2010.
Exact lines of code are:
Dim RetVal
RetVal = Shell("C:Program FilesCommon Filesmicrosoft sharedinkTabTip.exe")
These lines of code work perfectly fine on my development PC which is running Windows 7 64 bit, Access/Office 32 bit.
I know the path to the exe is good. I can navigate and double click it and it works great. but the shell command is resulting in the invalid procedure.
View 4 Replies
View Related
Feb 5, 2014
I tried to dynamically set image OnClick property to function with couple of attributes. Everything works well, but... it launches the function automatically without any mouse click! What is causing this? I want to execute the function only on the result of mouse click.
Piece of code:
Forms!Form1.Controls("Image" & i).OnClick = fclick(PiltID)
Private Function fclick(u)
DoCmd.OpenForm "Form2"
Forms!Form2.Image0.Picture = u
End Function
View 6 Replies
View Related
Oct 28, 2004
Hello,
can we create a stored prodecure in Access 2000 and call it from VB. I've created in SQL Server with ASP but have no idea in Access. Can anyone please tell me how to do it. If so kindly specify some resources from where i can learn more related to this. thank you.
View 5 Replies
View Related
Oct 6, 2003
I have a MS SQL 2000 stored procedure that acepts a parameter and returns a recordset.
I want to run this Stored procedure from an Access 2000 report and use the recordset for the report.
I want to pass a field off of a form that is user entered to the stored procedure.
Is this possible. Any help is appreciated.
I can figure out how to attach the stored procedure to the report as the dataset but cannot seem to figure out how to pass the dynamic parameter to the Pass-Through Query.
Thanks in advance,
Knight.
View 8 Replies
View Related
