I'm wondering if anyone has any idea how I can speed this form up. I have similar forms that don't calculate as much but are much faster when updating the date.
Basically, I have a make table query that pulls all units due to be delivered between a certain time period, that are a specific priority and we input how many days we want to push the delivery date back.
A form opens that shows the Job Code, Run, Door Style, Special Color, Lot Delivery Date, W/O Due Date, W/O Start Date and a Change check box. If the Change check box is selected the W/O Due Date moves back however many days entered. Based on the new W/O Due Date and whether or not a Raised Panel door or Special Color was selected the WOSD resets as well.
Here is the code I have used. It takes a little over a minute to update 1 line and we may need to move several lines. After all the units needed to move are selected, an update query is run updating the information in the actual tables used in the make table query.
Private Sub Change_Exit(Cancel As Integer)
If Me.Change = True Then
Call UpdateWODDChange
Call UpdateWOSDChange
End If
End Sub
Private Sub Form_Close()
DoCmd.OpenQuery "qryUpdateWODDPriorityChange"
DoCmd.OpenForm "frmMenu"
I'm doing the simplest little 'INSERT INTO' type operation here with recordsets in Access 97 (SR2). Inserting into a table whose name is stored in the Const TABLE_CANDIDS.
I have a little piece of code that doesn't move on until the changes are committed to the table (usually this is just a single long integer being added).
The transaction takes up to a second to commit, and sometime doesn't commit at all. I tried putting begintrans and committrans around the loop but it seems to make the problem worse.
Can anyone spot my error?
Thanks, Rob
' Empty the TABLE_CANDIDS table then start filling it with the current ' list of Candidates DoCmd.RunSQL "DELETE * FROM " & TABLE_CANDIDS Set rstCandIDs = CurrentDb.OpenRecordset(TABLE_CANDIDS)
' Find the CandIDs for all selected items (default to select all) For Each objListItem In objListView.ListItems If objListItem.Selected Or Not blnSelectionOnly Then rstCandIDs.AddNew rstCandIDs!CandIDFiltered = CLng(objListItem.Text) rstCandIDs.Update lngCount = lngCount + 1 End If Next objListItem rstCandIDs.Close
' Wait for up to 1 second until the new value(s) are committed. Screen.MousePointer = 11 datStart = Now() Do While IsNull(DLookup("CandIDFiltered", TABLE_CANDIDS, "CandIDFiltered <> 0")) If DateDiff("s", datStart, Now()) > 100 Then Exit Do End If DoEvents Loop Screen.MousePointer = 0
I wonder if anyone could offer some advice on a problem with a form & subform?
I have a main form, based on table PublicationOrders: POrderID (pk) EventID(fk)
And a subform, EventDisplay, based on table Events: EventID (pk) EventName
The main form and subform are linked on the field EventID and my master and child links are correct, as far as I can tell.
The field EventID on the main form is a combobox, bound to field EventID in the table PublicationOrders. It allows the user to select which event an order is being sent to. In the Afterupdate property of this combobox, I have the code: Forms!PublicationOrders!EventDisplay.Requery
I would like the subform to update to show the details of the event that has just been selected from the combobox. However, for some reason, on afterupdate, the subform displays whichever event was selected when the previous record was entered. If I close and reopen the form, it will right itself and show the correct event information that corresponds with the order.
Any ideas why it's doing this? I have searched here for a solution but can't seem to find anything that relates.
UPDATE (tblAFFIRMATION_REC_TOOL LEFT JOIN tbl_TZero_Spns ON tblAFFIRMATION_REC_TOOL.CptySPN = tbl_TZero_Spns.SPN) INNER JOIN Entity ON tblAFFIRMATION_REC_TOOL.ReferenceEntity = Entity.ReferenceEntity
SET tblAFFIRMATION_REC_TOOL.[Scope Reason] = IIf(IsNull(tbl_TZero_Spns!SPN) Or (Entity!Test='EM'),tblAFFIRMATION_REC_TOOL![Scope Reason],'TZero Trade')
WHERE ((([tblAFFIRMATION_REC_TOOL]![Scope Reason])="Affirmation Eligible"));
the way it right now, it's running for about half hour.. Could that be IIF statement that slows it down ?
I have a big application, which we updated to Access 2010 a month ago. It ran fine. Then we also just updated the data from 2003 to 2010. Now we have very slow searches of big tables/queries, and the tech says that 'cpu usage on the server has gone to 100%'.
What can be happening? Nothing else has been changed except that the data has been updated to 2010 accdb.
I'm having trouble with access. Basically it has been very slow to load (over 30 seconds when not opening a database) and when opening a database it just crashes.
Has anyone any idea what could cause this as it worked fine before ?
Hello All, Yesterday only i designed my first form in VB.I designed a tab form as i have to place two kinds for data from the same table separately (for user convience).i was succesfull in that.But the problem is the form is very slow to load........very very slow......
i want to know whether Tab form are slow or i have made any mistake..
Hi guys. I am stumped with a 45 second delay when I startup my application. I get the delay on networked workstations only when another workstation is up showing the Main "switchboard" form. Each workstation has their own copy of VBA (not compiled) with networked versions having linked tables to the server's .mdb
It seems like the workstations know that the main form is up somewhere else and need to negotiate with it. All have the (Access 2000) Default open mode to Shared, Default record locking to No Locks, and Open database using record-level locking On. :confused:
I have developed a small database with 3 or 4 forms which works ok on most PC's. But when I put it on a certain PC, it almost works properly except for one form which is very slow accessing the table! Eg. It could take 2 minutes to use a combobox.
All PC's have Access 2000. Would there be a reason for this? Is this an access problem or a PC problem? Are there special settings that I'm unaware of?
I have a form. It has about 75+ bound text boxes, and about 25+ unbound text boxes that calculate values. And about a dozen command buttons. The code behind all this mess prints off in about a dozen pages.
Everything works. BUT, it takes around two or three minutes from clicking to open the form before the form appears. (There is no delay in moving between records.)
If I go into design mode, it takes anywhere from 10 to 20 minutes for it to save changes.
I noticed things really slowed down as I started copying controls.
Is there anything I can do to speed up this monster?
i searched for slow loading and found some fast internet packages on <web address removed by moderator>. By following the tips i just purchased now fast broadband but now i am facing the problem of slow loading on my local computer, is it due to any bugg with microsoft package or my system is infected by virus.
I have a Continuous form that is used to place orders in my Purchase order Database. I have a combo box with a Textbox over laid, so the users can type in the Textbox and it filters the combo box as they type. This is to search Nominal ledger codes that the current user has set up.
This has been working fine, but now finding issues as people have 400+ Nominal coeds set up. So there is a lag when the user is typing.Is there a better way of doing this? i have a similar setup to search a supplier but this is not a continuous form and the majority of objects are un bound.
Code:
Private Sub lstSelect_AfterUpdate() Me.lstSelect.RowSource = "qryCurrentNominalNew" End Sub Private Sub lstSelect_BeforeUpdate(Cancel As Integer) Me.lstSelect.RowSource = "qryCurrentNominalNew" End Sub
i am using a form like form customer orders in northwind 2003 database. in my form there are 3 sub forms.data of subform1 will be filtered by one of the field in the main form.subform2 will be filtered by one of the field in the subform1 and ...
also i have added some code for highlighting current row of each form/sub form , now my problem is when navigate thorough records of main form by using navigation button , showing each record after pressing navigation button is a bit slow (it takes 5 seconds), problem is from subforms or codes for highlighting current row?
I have a database with a table that contains 360,000 rows. I built a form with four boxes where a user can specify values to limit the result set. And instead of having a new window open with the results, I built a subform and placed it on the main form to display the results.
Here is how it flows: Main Form -> user enters search criteria Search Criteria -> feed as criteria in query Query Results -> display on subform Subform -> shows on main form
The query is setup to take the values from the main form and either use it if it's not null, or return all values if the field is null.
The problem I am having is that on opening the main form, Access is taking the four null values from the main form search fields, feeding them to the query, which is then feeding the subform. So 10 minutes later when the main form finally opens, I have 360K records displaying in my subform.
What I would like is to be able to open the main form instantly, specify my search criteria, then run the query, then have the query results populate the subform.
I've got a DB, which has 3 forms ( all 'under' 3 navigation buttons/tabs ). The main form ( Client's form ) is set to show first, and currently has a recordsource of about 1350 records. I think the size of the recordsource, and the fact it 'loads' the other two forms all 'in one go', is slowing it down ( between 5 and 10 secs. to load ).
I read I should set the Visible properties of forms, not shown immediately, to false, only setting them to True in the On_Load event - when Tab is selected. Ideal I thought. However, I can't find this property in the Form's property sheet.
I have a database i have been working on (access 2007). In the past I have been using a list box to select records. Now I would like to use the datasheet view or split form view. Everything seemed fine until I moved to linked tables.
My problem is, with the same query, the listbox is lightning fast at changing the recordsource.
When I change the recordsource on a datasheet view or split form, it takes... ages and shows "Calculating.." On larger recordsets it freezes access.
I am using John Big Bootys Dynamic Search Form (built in Access 2003) as it totally met my search requirements, however when typing anything into the search box it is realy slow, taking several seconds for each letter to show in the box.
This has always been a problem now and again, however some of us have upgraded to Office 2010, (although still using the 2003 built Db as we won't move to a 2010 version Db untill March next year) and now the problem is even worse that ever. Everything else seems to work really well except for this search function, however it is the search function that is used more that anything else.
My DB is very slow when it's used by more than 1 person at a time. It's divided into FE/BE. The BE is in a shared server and a copy of the FE is in each person's machine.
I don't know why it takes ages to respond. Any help will be very much appreciated.
I have an Access 2003 db that works extremely well as a stand-alone application but I am keen to “split” it so that many users can operate it together.
Accordingly I did the split and placed the tables on our server, and tried running the front end locally.
Much to my horror, it takes about a minute to open, and many seconds to do anything after a button press etc.
I have been testing it on my wireless home network and the same thing happens, except now I can monitor network activity from the “network working” light.
What I see baffles me and would welcome comments, in particular, what I can do to speed it up by a factor of 100!
1.It takes forms about 50 seconds to open; however, this is due to about 40 seconds of network activity BEFORE the form’s load event fires! 2.In DESIGN mode, any minor change to a control for example, results in about 40 seconds network activity. 3.All queries upon which forms are based take well under a second to run over the network. 4.Making an mde file has no effect.
I thought the idea of splitting was to keep the forms/queries/code etc, local to a machine so I don’t understand what all the network activity is about, particularly in design mode. Also, given the actual queries are so fast, just what is Access doing??
I have a form with a subform (there is no child parent relationship) , when I open the mainform it causes the subform to slow down.
ie If I open the suform by itself - it opens quickly If I then open the main - it runs quickly
If I then open the main again it runs slow or if I open the subform again - it runs slow - it continues to run slow - until I have opened the sunform by itself.
So something in the mainform is causing the subform to slow down.
i'm create a aplication in ms access 2000 developer edition. this app comunicate with sql 2000.
we have problem with slow connection provided by access. in headquater wher people use access app it working quickly. sql 2000 is localted at headquarter too. but if i run app in warehouse it working very slowly.
VPN connection doesn't slow. but access can't use all vpn connection capacity but only 1/5 from this capacity?
how can i do something that acces can use all capacity that i have?
example: link is 512 kbps but access use only 100kbps and work very slowly.
I developed a sales database that contains around twenty forms, (in addition to tables, queries, reports and 2 modules). In these forms, I'm setting recordsets, select statements and filters. We recently hired a person to set up a Virtual Private Network. After spending a couple of thousands on a server and countless hours reprogramming the database to have multiple users access the database out in the field, (via Verizon broadband network card), we were unsuccessful to use the database since it was taking too long to download the data or perform any calculations. The database is split. The users have a front end.
The person setting the VPN has suggested that I get rid of all the queries that I have in the forms (which is impossible), or use Visual Basic for the front end instead of Access with VBA.
When I set up the recordsets I use the following setting: rs.CursorLocation = adUseClient rs.CursorType = adOpenDynamic rs.LockType = adLockOptimistic
I don't know what to do. This program is very complicated and I don't want to have to reprogram the whole thing with Visual Basic, and I don't know how the front end is going to work if I eliminate all the queries, select statements and filtering.
Any ideas, comments or suggestions would be appreciated.
At the moment, I have a very simple Access 2007 database with 3 small tables, and 4 small forms.
In two of the forms' headers, I have a text box with contents '=Nz([FirstName] & " " & [MiddleName] & " " & [LastName],"Untitled")' which I took from Microsoft's Marketing Projects sample database.
When I open the two forms with this text box, the forms displays instantaneously except for this text box -- this text box doesn't show its contents for almost a full second. The part of the form that displays right away includes the fields FirstName, MiddleName, and LastName, so it's not that Access is taking forever to pull the row of data...
The database isn't too large. It's about 800K.
It isn't the computer, it's an Intel Core2 Duo E6600 with 4GB memory.
Any ideas on how to speed this up? Is there a totally different way I should be doing this?
As you can problaby tell, I'm relatively new to Access. I've read a few books, but am at a loss why this function is running so slowly. Before, I've focused on web development however this client requres Access for this project. Any help would be much appreciated!