Update On Form Is Extemely Slow

Sep 30, 2005

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"

End Sub

Private Sub UpdateWODDChange()

Dim intNumDays As Integer

intNumDays = Forms!frmUpdateWODDbyPriority.Form.PushForwardDays

Me.WODD = AddWorkdays(Me.LotDelDate, intNumDays)

End Sub


Private Sub UpdateWOSDChange()

Dim AddColor As Boolean
Dim intNumDays As Integer

If Not (Forms!frmUpdateWODDbyPriority.Form.SpecialColor = 0) Then
AddColor = True
Else
AddColor = False
End If

Select Case Me.DoorStyle

Case "Eagle", "H/E", "F/E", "RP-9", "RP-22", "RP-23"
If AddColor Then
intNumDays = 7
Else
intNumDays = 6
End If

Case Else
If AddColor Then
intNumDays = 7
Else
intNumDays = 5
End If
End Select

Me.WOSD = MinusWorkdays(Me.WODD, intNumDays)


End Sub

View Replies


ADVERTISEMENT

Very Slow Update Query

Nov 21, 2007

Hi all,

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

View 2 Replies View Related

Subform Slow To Update

Jul 27, 2005

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.

View 2 Replies View Related

Update Query Is Running Too Slow

Feb 13, 2008

What would be a better way to do below query --



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 ?

View 1 Replies View Related

General :: Slow Searches After Update To 2010 Data?

Apr 16, 2013

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.

View 3 Replies View Related

Access Slow To Load [really Really Slow!]

Dec 14, 2007

Hi All,

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 ?

also would a reinstall help ??

-Elfman

View 3 Replies View Related

Is Tab Form Very Slow

Dec 6, 2005

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..

any solution please.....

View 1 Replies View Related

Closing Form Slow After Sorting

Apr 26, 2005

Closing a form with a large amount of records is slow after sorting the records, is there a way to avoid this.

Its slower to close after sorting than the the sum of the time to close the form and sort the records, which doesn't seem to make much sense.

Can anyone help?

Ta - Paul

View 4 Replies View Related

Startup Form Very Slow Over Network

Jan 16, 2007

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:

View 6 Replies View Related

Form Accessing Table - Very Slow!!?

Feb 17, 2005

Hi,

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?

Thanks in advance

View 1 Replies View Related

Slow Form Open/save

Sep 15, 2005

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?

David

View 3 Replies View Related

Forms :: Slow Loading Of Form On Internet

May 2, 2013

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.

View 1 Replies View Related

Forms :: Slow Combo Box On Continuous Form

Feb 13, 2015

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

[code]...

View 3 Replies View Related

Forms :: Navigate Records On Form Is Slow

Sep 27, 2013

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?

View 3 Replies View Related

Large Table Causing Slow Opening Form

Mar 21, 2006

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.

What do I need to do?

View 3 Replies View Related

Forms :: Recordsource - Slow Form Load Event

Mar 9, 2013

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.

View 1 Replies View Related

General :: Slow Datasheet View / Split Form

Nov 14, 2012

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.

View 3 Replies View Related

Forms :: Access 2003 / Dynamic Search Form Very Slow?

Nov 8, 2013

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.

View 1 Replies View Related

Very Slow DB

Nov 8, 2006

Hi,

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.

Regards,
B

View 8 Replies View Related

Why Is Split Db So Slow?

Apr 29, 2005

Hi all

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??

Any comments please?

Skeletal

View 3 Replies View Related

Slow Subform

Jun 2, 2005

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.

Help! Thanks

View 2 Replies View Related

Slow Running

Jun 24, 2005

I am using Access 2003 but it is to slow, what can I do?

View 1 Replies View Related

Slow Database? HELP!

Apr 4, 2006

Hi,

I have a database for booking equipment in and out.

I have the backend stored on a shared drive on our LAN, and the front end on each client machine.

The database has been working fine until just now, it runs very slow and my DCount functions have stopped displaying data.

I have no idea why this has happened, please help?

View 1 Replies View Related

Slow Connection

Aug 24, 2006

hi all,

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.

View 1 Replies View Related

VPN - Process Are Too Slow!

May 30, 2007

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.

Maritza

View 2 Replies View Related

Text Box With Nz() Very Slow

Feb 25, 2008

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!

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved