By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 2,042 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

slow form display

Expert 100+
P: 1,221
I'm using Access 2000 (and also 2003 with same result) in a Windows XP Professional environment on a peer-to-peer network. Files in this system are relatively small. There are about 1,200 rows in the item/warehouse table, about 200 order headers, and about 2000 order detail rows.

I have a form with a sub-form in the detail of the main form. The sub-form is a continuous line display (not single item); In the heading I am getting a customer's order heading information, and in the subform I am listing all of the line items for that order.

Each line in the subform has a text box for inventory item number, quantity ordered, quantity shipped, on hand quantity, and on order quantity (all open orders combined).

If the order quantity for that item exceeds the (on hand - on order) then there are not enough available for that order and I use conditional formatting to turn the background of the on hand text box yellow.

The problem is, sometimes this form is VERY slow to complete. The on hand and on order boxes remain empty, filling down the screen slowly. And oddly, if you do an ALT-TAB to switch out of the screen, and ALT-TAB back immediately, then suddenly all the text boxes are filled in. Or, if you wave your mouse over an empty text box, the value for that text box will appear.

The on-hand quantity is not a single field, there can be on hand in more than one warehouse.Here is the code to return the on hand value for an item
Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function CalcOnHand(ItemNbr As String) As Long
  3. Dim db As Database
  4. Dim rs As DAO.Recordset
  5. Dim qd As QueryDef
  6. Dim Sql As String
  7. Dim strFind As String
  8.  
  9. Dim Onhandqty, Counter As Integer
  10. Counter = 0
  11.  
  12. Set db = CurrentDb
  13.  
  14.     Set rs = db.OpenRecordset("ItemWarehouse")
  15.     Onhandqty = 0
  16.     On Error GoTo Loopend
  17.     rs.MoveFirst
  18.  
  19.     strFind = "Item='" & CStr(ItemNbr) & "'"
  20.     rs.FindFirst (strFind) ' first occurrence of this item in itemwh file
  21.  
  22. LoopItems:
  23.     While (Not (rs.EOF))
  24.     If rs.NoMatch Then GoTo Loopend
  25.     If rs![Item] <> ItemNbr Then GoTo NextItem
  26.     ' If rs![Warehouse] <> "9301" And rs![Warehouse] <> "3405" Then GoTo NextItem
  27.     If rs![Warehouse] = "PCR" Then GoTo NextItem
  28.     Counter = Counter + 1
  29.     Onhandqty = Onhandqty + rs![OnHand]
  30.     If Counter > 1 Then GoTo Loopend  'don't bother looking after we found both warehouses
  31. NextItem:
  32.     rs.FindNext (strFind)
  33.  
  34.     'rs.MoveNext
  35.     Wend
  36.     'GoTo LoopItems
  37.  
  38. Loopend:
  39.    CalcOnHand = Onhandqty
  40.     rs.Close
  41.     Set rs = Nothing
  42.  
  43. End Function
The code to get the on order quantity comes from a query, and it is like this
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Orders.OHCustomerID, tbl_Orders.OHOrderDate, tbl_Orders.OHOrderNbr, tbl_Orders.OHPONumber, tbl_Orders.OHCasesPerPallet, tbl_Orders.OHMemo, tbl_Orders.OHStatus, tbl_Orders.OHCarrier, tbl_Orders.OHTotalWeight, tbl_Orders.OHPalletCount, tbl_Orders.OHShipDate, tbl_Orders.OHBOL, tbl_Orders.OHInvoice, tbl_Orders.OHTerms, tbl_Orders.OHCHSequence, tbl_Orders.OHCases, tbl_Orders.OHCartons, tbl_Orders.OHCigarettes, tbl_Orders.OHDiscPct, tbl_Orders.OHDueDate, tbl_Orders.OHMessage, tbl_Orders.OHFreightCharge, tbl_Orders.OHMiscCharge
  2. FROM tbl_Orders
  3. ORDER BY tbl_Orders.OHOrderDate DESC , tbl_Orders.OHOrderNbr DESC;
  4.  
What's going on and how to I correct it?
Thanks,
Jim
Oct 19 '06 #1
Share this Question
Share on Google+
19 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this instead:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function CalcOnHand(ItemNbr As String) As Long
  3. Dim db As Database
  4. Dim rs As DAO.Recordset
  5. Dim Onhandqty As Integer
  6.  
  7. Onhandqty = 0
  8.  
  9. Set db = CurrentDb
  10. Set rs = db.OpenRecordset("ItemWarehouse")
  11.  
  12. rs.MoveFirst
  13. Do until rs.EOF
  14.   If rs!Item = CStr(ItemNbr) Then 
  15.     If rs![Warehouse = "9301" Then 
  16.       If rs![Warehouse] = "3405" Then 
  17.         If rs![Warehouse] <> "PCR" Then
  18.           Onhandqty = Onhandqty + rs![OnHand]
  19.           rs.MoveLast
  20.         End If 
  21.       End If   
  22.     End If 
  23.   End If   
  24.   rs.MoveNext
  25. Loop
  26.  
  27. Loopend:
  28. CalcOnHand = Onhandqty
  29. rs.Close
  30. Set rs = Nothing
  31. Set db = Nothing
  32.  
  33. End Function
  34.  
  35.  
Oct 20 '06 #2

Expert 100+
P: 1,221
Thanks for the reply, MM. I've learned a lot by reading your responses to a lot of questions here. I've very glad there are some folks with so much knowledge and willingness to share it.

In this case, it appears you're suggesting I change to a sequential read through the ItemWarehouse table. Seems to me that will make it less fast. But I'll give it a shot, I don't have any better ideas.

Thanks,
Jim
Oct 20 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Jim

Because of the way the Jet Engine works this is not always the case.

With the code I have given you the sequential logic means that the search will jump to the next set of records once the required result is found.

If a condition is untrue it will automatically jump to the next record.

I think you'll be surprised at how fast it will run.

Thanks for the reply, MM. I've learned a lot by reading your responses to a lot of questions here. I've very glad there are some folks with so much knowledge and willingness to share it.

In this case, it appears you're suggesting I change to a sequential read through the ItemWarehouse table. Seems to me that will make it less fast. But I'll give it a shot, I don't have any better ideas.

Thanks,
Jim
Oct 20 '06 #4

Expert 100+
P: 1,221
MM,
Thanks, but the change you suggested did not make any difference. It's really odd, they way the text boxes in the "on hand" column will fill in if you wave the mouse over them. And if you Alt/Tab to another window, and Alt/Tab back immediately, they're all filled in.

I'm going to try to get the on hand value from a query and link the text box to the query result.

Jim
Oct 24 '06 #5

Expert 100+
P: 1,221
I have changed to get the on hand quantity from a query, but still I have this problem.

Here's a little more information. Hoping I can give enough info so that somebody will recognize what is going on here.

My form is an order entry form. The heading of the main form has customer data, the subform has order details. When I launch the form, the first customer appears and his most recent order. Usually orders consist of at least 4 or 5 line items. Each line item shows the item number, order quantity, ship quantity, and the quantity on hand for that item. Sometimes the first line item has the on hand value filled in, sometimes not, but the other lines always have a blank in the on hand text box. Eventually, slowly, they fill in. (All the other text boxes on all the lines filled in immediately upon entering the form, as is normal.)

You can go to other customers, other orders, and the problem persists.

If you wave your mouse over the top most blank text box, the quantity on order will fill in. If you wave at a lower blank text box, nothing happens. If you Alt/Tab out of that screen, and immediately Alt/Tab back, all the blanks fill instantly.

Whether you fill in the boxes by doing an alt/tab, or by progressively moving down the screen with your mouse, after all the boxes have been filled in for any one order, there is no longer any problem. You can navigate to other customers and other orders, and everything fills in very quickly.

Any ideas?

Thanks,
Jim
Oct 24 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Open the subform on its own in design view.

Go to the OnHand textbox and open the properties window.

Check the data tab and look at the control source and default value for this control. What are they set to.

Are there filter values set?

Then go to the Event tab and check if there are macros or event procedures running on any events, if so what are they?
Oct 25 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
BTW

Where and when are you calling the CalcOnHand function?
Oct 25 '06 #8

Expert 100+
P: 1,221
MM,
No events, no default and no filters. The countrol source for the text box is
=CalcOnHand([OdItem])
where OdItem is the Inventory Item number for the line item.

Jim
Oct 25 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
MM,
No events, no default and no filters. The countrol source for the text box is
=CalcOnHand([OdItem])
where OdItem is the Inventory Item number for the line item.

Jim


OK take that value out of there and leave it blank

Then in the OnLoad Event of the form


Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Form_OnLoad()
  3.  
  4. Me.OnHand=CalcOnHand(Me.OdItem)
  5.  
  6. End Sub
  7.  
  8.  
Oct 25 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
OK take that value out of there and leave it blank

Then in the OnLoad Event of the form

I mean the subform on load event by the way.
Oct 25 '06 #11

Expert 100+
P: 1,221
Thanks, MM, I can't wait to try that. Seems then that the entire recordset is loaded and each related text box calculate at the start of the form. What does that mean about updates to the data during processing ... if the user enters this order entry form at the start of the business day, and stays in it all day, does the form data need refreshed in order for the correct on hand quantities to show?

More specifically, when this screen is used to ship some units, will the form show the new on hand amount correctly? Or rather, what steps have to be taken to insure that it does show the right quantity on hand?

Thanks,
Jim
Oct 25 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
If any changes take place to data that will have an effect on the calculation of the OnHand control then it will have to be retriggered.

Are we talking about changes in the same form or elsewhere in the database.
Oct 25 '06 #13

Expert 100+
P: 1,221
Changes to on hand could be made from this form and from other activities by other users performing the same process, or other processes.

If any changes take place to data that will have an effect on the calculation of the OnHand control then it will have to be retriggered.

Are we talking about changes in the same form or elsewhere in the database.
Oct 26 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Changes to on hand could be made from this form and from other activities by other users performing the same process, or other processes.
Is OnHand a field bound to any table or just a calculation for display purposes.
Oct 26 '06 #15

Expert 100+
P: 1,221
On hand is calculated. There can be multiple records for one item in the item/warehouse file. An item in stock at 3 warehouses will have 3 records to be totaled to get the overall on hand value. I guess that could become bound to a query item.

Jim

Is OnHand a field bound to any table or just a calculation for display purposes.
Oct 26 '06 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
The best thing I can suggest is that you put a command button beside On Hand that will allow the user to prompt it to update.

Private Sub cmdUpdate()

Me.OnHand=CalcOnHand(Me.OdItem)

End If
Oct 27 '06 #17

Expert 100+
P: 1,221
Thanks a lot. If I do that, is it going to, at the moment the button is pressed, calculate every onhand textbox for every order in the system? Can this be done selectively? Can I make sure it only does the calculation for the particular order shown on the screen?

Seems to me there's a lot I don't know about events and the filling of text boxes on a form. Can you suggest a good reference on the web?

Thanks,
Jim


The best thing I can suggest is that you put a command button beside On Hand that will allow the user to prompt it to update.

Private Sub cmdUpdate()

Me.OnHand=CalcOnHand(Me.OdItem)

End If
Oct 27 '06 #18

PEB
Expert 100+
P: 1,418
PEB
Hi,

In fact, there is also the problem slow open and close of forms rather than they aren't with subforms and tabs and subordinated controls

The problem in those cases is that Access need to save the changes on the forms and on the respective recordsources...

You can speed a lot your forms if you leave their recordsource property empty when closing and opening the form

When loading the form you assign the recordset using on load property event procedure in the form properties

This is good to do also for all comboboxes - on open and close the reocordset sources to be empty... This will speeds up the open and close of your forms!

Best regards
Oct 27 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
the Me. means will only refer to the current record
Oct 27 '06 #20

Post your reply

Sign in to post your reply or Sign up for a free account.