jimatqsi 1,277
Recognized Expert Top Contributor
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
19 3309 MMcCarthy 14,534
Recognized Expert Moderator MVP
Try this instead: -
-
Public Function CalcOnHand(ItemNbr As String) As Long
-
Dim db As Database
-
Dim rs As DAO.Recordset
-
Dim Onhandqty As Integer
-
-
Onhandqty = 0
-
-
Set db = CurrentDb
-
Set rs = db.OpenRecordset("ItemWarehouse")
-
-
rs.MoveFirst
-
Do until rs.EOF
-
If rs!Item = CStr(ItemNbr) Then
-
If rs![Warehouse = "9301" Then
-
If rs![Warehouse] = "3405" Then
-
If rs![Warehouse] <> "PCR" Then
-
Onhandqty = Onhandqty + rs![OnHand]
-
rs.MoveLast
-
End If
-
End If
-
End If
-
End If
-
rs.MoveNext
-
Loop
-
-
Loopend:
-
CalcOnHand = Onhandqty
-
rs.Close
-
Set rs = Nothing
-
Set db = Nothing
-
-
End Function
-
-
jimatqsi 1,277
Recognized Expert Top Contributor
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
MMcCarthy 14,534
Recognized Expert Moderator MVP
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
jimatqsi 1,277
Recognized Expert Top Contributor
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
jimatqsi 1,277
Recognized Expert Top Contributor
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
MMcCarthy 14,534
Recognized Expert Moderator MVP
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?
MMcCarthy 14,534
Recognized Expert Moderator MVP
BTW
Where and when are you calling the CalcOnHand function?
jimatqsi 1,277
Recognized Expert Top Contributor
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
MMcCarthy 14,534
Recognized Expert Moderator MVP
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 -
-
Private Form_OnLoad()
-
-
Me.OnHand=CalcOnHand(Me.OdItem)
-
-
End Sub
-
-
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Neil |
last post by:
I previously posted re. this, but thought I'd try again with a summary of
facts.
I have an Access 2000 MDB with a SQL Server 7 back end. There is a view that
is linked to the database via ODBC and has been in place for several years
without any performance problems.
Recently I added a couple of fields to the output of the view, and it became
very slow when scrolling. When just opened in the database window, the
linked view takes about...
|
by: Steve Hall |
last post by:
Dear All
We have a SQL Server 2000 BE on a Win 2K Server box. Access 2000
clients, running on Win 2000 Pro.
Front end design is fairly straight forward - 5 different "record
types" (split across approx 20 tables). For each "record type", there
is a datasheet view, listing the records of that type, and this can be
filtered by using a combination of text / combo / check box filters
above the subform datasheet. On clicking a record ID in...
|
by: Hernán Freschi |
last post by:
Hi all, I'm a completely newbie here. I'm having a problem. I've
programmed before in VB6 and such, and all the programs I've made run
fast. But .NET programs run very slowly. I use C# (for no particular
reason).
It's not the algorithms (that is, what the program * does *, that works
fine), but the UI responsiveness. This programs "hides" in the traybar
and sits there until the phone rings (It's a Caller ID program).
Everything works...
|
by: Andy_Khosravi |
last post by:
My problem:
I'm having trouble with a query taking much too long to run; a query
without any criteria evaluating only 650 records takes over 300 seconds
to run (over the network. On local drive takes 120 seconds).
The Setup:
I'm running Access 97 non-developer edition. I have exactly zero other
tools to use and no way to change that =(. My database is compiled and
resides on a network drive. The database has not been split into a...
|
by: Drwtsn32 |
last post by:
Hi Guys,
I'm ripping off all of my hair soon ;-)
I'm trying to do a very simple C# form accessible from COM client like
VBScript.
I would like to display the form and update the form during the
intepretation of the vbscript. Does anyone know of any good example that
could point me to the right direction.
| |
by: Ant |
last post by:
Hi,
I'm using the text changed event of as text box to display a datagrid of
search results dynamically. (The classic 'gets more specific the more you
type in' scenario ).
I find when I first start the app & type in something into the text box, it
stalls & won't display the text you've typed in. After a short while, the
text appears & the results are loaded into the data grid. After the first
time, it works perfectly; loading data in as...
|
by: Andy_Khosravi |
last post by:
I'm having a problem trying to optimize the performance of one of my
A97 databases. I have very slow record navigation after a change I made
to the table structure, and I'm not sure how best to correct it.
For purposes of explanation, lets say I have two tables: tblIssues and
tblLocationHistory. The tblIssues table contains 'incidents' along with
the incident header information. The tblLocationHistory table contains
a list of all...
|
by: Michael |
last post by:
I work with a highly programmed Access
database (some 15,000 lines of VBA code, much of it automating data
entry on forms -- and believe me, it's very tight code). In Access
97,
2000, 2002, and 2003, no performance problems. However, when I open
the same database in Access 2007, it's as slow as molasses. Data
trickles onto the form instead of an immediate display in Access
2003.
This happens regardless of whether I keep the Access...
|
by: eighthman11 |
last post by:
using Access 2003 and sql server version 8.0
Hey everyone. Created a text box where the user types in an Inventory
number and it takes them to that inventory number on the contimuous
form. The form is based on a link table to sql server. Here is the
code:
Dim rst As DAO.Recordset
Dim InventoryItem As String
InventoryItem = "'" & "TextBoxValue" & "'"
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |