creating a search box

[Nanuaraq]

I am making a form to show a query of data to my users.
My users work with cases, each assigned a case identification number.
The idea with this particular form is to show the user relevant data about any given case, identified by the case number of user's choice. The user will type in their case number, and be given a view of relevant data on the case (particular to a crucial step in the case, where the case worker has to do some complicated calculations).

The data is stored on an SQL server somewhere. I use Access merely as a pipeline to do queries.

I have a problem with speed. Access seems to take a very long time indeed to process a request to show a given case.
I have tried to solve it by creating a separate database where only a select few and relevant tables are shown. Still speed problems persist. I do not know why this database has so many issues with speed, since I am perfectly well able to access another and much larger database with no problems.



I also wish to make a form where only one case is shown to the user. I can do this, but I would like a better searchfunction, such as a button on the form where the user may type in a case number and be shown that case.

Any suggestions?

[ranman256]

put this code in the txtbox afterupdate event an give it the single record form for it to open:

sub txtBox_Afterupdate()
vRet = Dlookup("[Case#]","tCases","[Case#]='" & me.txtBox & "'")
if Isnull(vRet) then
   msgbox "Case '" & me.txtBox & "' not found."
else
   docmd.openform "frm1Case",,,"[Case#]='" & me.txtBox & "'")
endif
end sub

[Missinglinq]

The data is stored on an SQL server somewhere...I have a problem with speed.

I wonder if the latter is related to the former. How is this server...located 'somewhere,' being accessed? Access works fine over a LAN...but terribly over a WAN/VPN...an estimated 100 times slower!

Albert Kallal is a well known authority on the networking of MS Access, and has an often quoted article on the subject of attempting to run Access across WANs/VPNs:

http://www.kallal.ca/Wan/Wans.html

To make matters worse, connections frequently get dropped across WANs/VPNs, and Access simply does not tolerate dropped connections, the end result of which is usually a corrupted database!

But take heart; Albert does offer some possible workarounds!

Good Luck!

Linq ;0)>