slow form display

[jimatqsi]

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

 
Public Function CalcOnHand(ItemNbr As String) As Long
Dim db As Database
Dim rs As DAO.Recordset
Dim qd As QueryDef
Dim Sql As String
Dim strFind As String
 
Dim Onhandqty, Counter As Integer
Counter = 0
 
Set db = CurrentDb
 
    Set rs = db.OpenRecordset("ItemWarehouse")
    Onhandqty = 0
    On Error GoTo Loopend
    rs.MoveFirst
 
    strFind = "Item='" & CStr(ItemNbr) & "'"
    rs.FindFirst (strFind) ' first occurrence of this item in itemwh file
 
LoopItems:
    While (Not (rs.EOF))
    If rs.NoMatch Then GoTo Loopend
    If rs![Item] <> ItemNbr Then GoTo NextItem
    ' If rs![Warehouse] <> "9301" And rs![Warehouse] <> "3405" Then GoTo NextItem
    If rs![Warehouse] = "PCR" Then GoTo NextItem
    Counter = Counter + 1
    Onhandqty = Onhandqty + rs![OnHand]
    If Counter > 1 Then GoTo Loopend  'don't bother looking after we found both warehouses
NextItem:
    rs.FindNext (strFind)
 
    'rs.MoveNext
    Wend
    'GoTo LoopItems
 
Loopend:
   CalcOnHand = Onhandqty
    rs.Close
    Set rs = Nothing
 
End Function

The code to get the on order quantity comes from a query, and it is like this

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
FROM tbl_Orders
ORDER BY tbl_Orders.OHOrderDate DESC , tbl_Orders.OHOrderNbr DESC;
 

What's going on and how to I correct it?
Thanks,
Jim

[MMcCarthy]

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.

[jimatqsi]

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

[MMcCarthy]

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.

[jimatqsi]

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.

[MMcCarthy]

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.

[jimatqsi]

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.

[MMcCarthy]

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=CalcO nHand(Me.OdItem )

End If

[jimatqsi]

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=CalcO nHand(Me.OdItem )

End If

[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

[MMcCarthy]

the Me. means will only refer to the current record