Modules & VBA :: Do Until Causes Access To Stop Responding
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 Replies
ADVERTISEMENT
Aug 5, 2013
I ran some code to number old records in a column to order items in a datasheet subform however after updating the column in the inventory transactions table when opening the main form that relies on it. It runs a query to determine the customers balance due and the query for that is taking forever to run like 2-3 minutes or more however reverting to an old copy of the data it runs fine and all I did was renumber records in one column most of which had 1s in them the code to re-number the records:
Code:
Dim rst As DAO.Recordset
Dim i As Long
Dim OID As Long
Dim stringSQL as String
i = 1
[Code] .....
I continued playing with this today and the query that is breaking the 2nd level subquery that actually references the inventory transactions table that I changed runs fine but go up a level to subquery1 which references subquery2 and a sum of payments query and it chokes however sum of payments only refernces the payments table.
I did some more testing today Subquery2 and sum of payments query both run faster than what I can time with a stopwatch but when combined they take approximately 1:45 there are only 5 columns in subquery1 four from subquery2 and 1 from the sum of payments 3 of the first 4 are sum and the last is group by and then the one from sum of payments is group by
Subquery 2:
Code:
SELECT
CLng((nz([UnitsSold])*nz([UnitPrice]))*(1-nz([Discount]))*100)/100 AS [Line Total],
CLng([Line Total]*(1+nz([SalesTaxRate]))*100)/100 AS [Line Total With Tax],
[Line Total With Tax]-[Line Total] AS [Line Tax],
[Inventory Transactions].*
[Code] .....
View 3 Replies
View Related
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 10 Replies
View Related
Nov 11, 2013
i am running some code from vba to add a linked table and the do some lookups, then delete the linked tables.
When the linked table gets added, the navigation pane gets displayed. I have set it to do not display in the settings and it does not when it opens but when this code runs it opens and then stays open until the db is closed and reopened.
I am trying to use my db as a software and I really do not want this to show.
I have also noticed this in a database I have which has update queries and was fine in access 2000 but does what i described above when using in access 2010.
View 2 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
Aug 2, 2013
I tried this but dont know how to stop the timer...I want to run the code that changes the Form color but want to be able to stop the Timer and reset it to start over..Will this timer work? Almost like an auction timer. But want to be able to reset the code at any stage...
Code:
Public Sub ExcelTimer()
Dim PauseTime, Start, Finish
[code]..
View 2 Replies
View Related
Feb 22, 2015
This code puts pdf reports into a folder . I am prompted for a number via the query I run because I have more than one report the query asks me 3 times. Could this be modified so I only get prompted once for all the reports in the code
On Error GoTo Err_Handler
Const FOLDER_EXISTS = 75
Const MESSAGE_TEXT1 = "No current invoice."
Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
Dim strFullPath As String
[Code] .....
View 6 Replies
View Related
Mar 23, 2015
I'm using an append query that needs to add the records from another database into a table. I can get it to get the data and add the records. There are other columns in the database that are not in the one the data is pulled from. If I would run the append query again, it would add the same records again at the end. How can I avoid this? I only need to add new records that are not yet in the database I want to pull the records to.
View 4 Replies
View Related
Jul 18, 2014
I was wondering if there's a way to set objects so that they don't get pushed down if an object above is set as Yes in the CanGrow property?can a report be split into columns?
I have been asked to replicate a form (previously written up on Word) which has two columns of information. As luck would have it all the fixed fields are on the Left hand side, so that part doesn't need (or want) to grow, However, the Right hand side has several sub-reports which can display 1-5 records each.
So if any of the sub reports show more than 1 record, they push down everything below it, but this includes the Left hand objects, creating gaps and often pushing the report onto 2 pages.
View 3 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
Aug 12, 2014
Is there a way to stop the controls from flickering when the timerevent fires?
What doesn't work:
Code:
Application.Echo False
Docmd.Echo False
TimerEvent on the current form
TimerEvent on a different form
Using Windows API:
Code:
Private Declare Function SetTimer Lib "user32" etc
Private Declare Function KillTimer Lib "user32" etc
Each of these events flicker the controls on my forms. The effect is worse when the form background contains a picture.
View 13 Replies
View Related
Jul 16, 2015
I completed the Op[en form and find specific data wizard and it works.
Here is my issue: If there is no data to find, I get a "Stop Macro" error.
How to change this?
View 1 Replies
View Related
Jan 15, 2015
I have a Combo box which contains a set of numbers divided by "."
e.g.: 1.342567.24433
When looking for a certain code, I would like to use the number pad to enter the first few e.g. 1.34 to move to the Position. In Previous Versions of Access I had a function on the key
If KeyAscii = 44 Then
DoCmd.CancelEvent 'stop the comma keypress being updated
SendKeys ".", True 'put a full stop in the field
End If
This worked fine, but in Access 7 and up, it turns off the number lock.
Is there an alternative to sendkeys to achieve the above or a way of directly turning the number lock on again?
View 3 Replies
View Related
Jun 21, 2014
I have a simple code for an access form button to send a email message.
It looks like this:
Quote:
DoCmd.SendObject , , , [emailaddress], , , [CustomerId], "Dear " & [CustomerName] & "We inform you that (...)"
When I click the button an email message window pops up, and if I do SEND it, everything ends well.
The problem:
If choose NOT to send it, and I close the email message window, an error pops up causing my database to close (I use access runtime).
Do you think I can add anything to my vba so this error doesnt occur?
View 13 Replies
View Related
Nov 12, 2014
Any code that stops the vba from running if an 'output to' function is null. I've found some code using the DCount function but I'm struggling to adapt this to multiple excel outputs.
I'm sending 5 excel files via E mail to several addresses and I'm wanting to identify if the first file has records, currently using access 2010.
View 5 Replies
View Related
Sep 22, 2013
I have a sales sytem in Access 2010.New customers have to be imported to MYOB daily. I have a query that finds new customers and appends them to a table for importing at a later stage. I export the records in that table if the field "imported" is set to false. The results are exported to Excel so they can be imported into MYOB after some additional data is added. If there are no records to append to the import table I want to stop the export from happening. I am new to VBA and don't know how to express that the select query "000 Append New Customers...." should only run if the append query has records. If there are no records found, I want to put a message saying "no records found". Here is the code I have behind the command button:
Private Sub PrepCustcmd_Click()
'Turn warnings off
DoCmd.SetWarnings False
DoCmd.OpenQuery "000 Append New Customers to MYOB Customers", acViewNormal, acEdit
[code]....
View 2 Replies
View Related
Mar 3, 2015
I am writing the following piece of code to be called again and again after 30 min. But I need to do invisible mouse move to stop screen saver occurring so i want to sendkeys after every 5 minutes. The Sub abc is in module and everything works except stopping screensaver from occurring.
Code:
Private Sub UserForm_Initialize()
'Update the Barcodes printed today
Call CommandButton1_Click
'Update batches to be scanned / batches scanned today
Call CommandButton3_Click
[Code] ....
View 14 Replies
View Related
May 25, 2007
Hi
I need to know how to stop access from saving the record when the
subform query is on focus?
Thanks
View 5 Replies
View Related
Sep 19, 2013
I have a form that allows a user to complete a stock take. I would like to stop other users from receiving or despatching stock while a stock take is in progress.
Is there a way I can lock a table, or stop users adding or altering records that match certain criteria. i.e. don't let users receive or despatch stock from with a locationID of 'A'.
View 4 Replies
View Related
Aug 16, 2007
HI All,
Can anyone tell me is there a way to stop access automatically deleting 00's from the front of numebrs after they've been entered into a form.
I need to keep the 00's to ensure to different DB's match.
All help aprciated
-Elfman
View 2 Replies
View Related
Jul 3, 2013
I have an excel file, and am trying to import it, and once when I have imported it, am checking for a condition with While Not rs.EOF, it works fine for the purpose of looping, but it does not stop when the records are over., it is additionaly printing 19 null statements, where I beleive it, should not be.
View 9 Replies
View Related
Feb 14, 2013
I have an Access 2010 db on a shared network drive. All users accessing the db are running Windows 7 OS and have a licensed corporate version of Access 2010. The advanced options "Shared", "Edited record" and "Open dabases by using record-level locking" are turned on. Additionally, the locking time limits are set to their default levels. If a user selects the save icon after making additions or updates, the following waring message appears:
Costar Job Database can't save design changes or save to a new database object because another user has the file open. To save you design changes or to save to a new object, you must have exclusive access to the file.Since I developed the db, I am the only one that makes design or object changes. I know that users don't need to select the save icon to update the db, but I don't want them to "freak out" if they select save and see the message.
Is there any way to prevent the message from being displayed?
View 6 Replies
View Related
