2nd form with recordset of 1st

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 forms. the first will show a filtered list of items. The 2nd will
show details of 1 item selected from the first form. I am currently using
docmd.OpenForm on the first form.

The problem is the recordset used for the forms is a quary and do to the
size of the quary the 2nd form take a long time to load as it has to run the
quary and searh the entire result everytime. The question is: Can I make the
2nd form use the filtered recordset from the first form for the detail view?
or can I make the 2nd form reuse the quary data it already has in future
searches?

Any suggestions?

thanks john
 
Hi, John.
I have 2 forms. the first will show a filtered list of items. The 2nd will
show details of 1 item selected from the first form.
Click to expand...

Use the WHERE clause criteria to filter the 2nd form's records when it
opens. However, the Record Source of the form must have a primary key in
order to uniquely identify which record is needed.

In the following example, CurrRatesBtn is the name of the button that is
clicked to open the frmRates form (the 2nd form), ID is the primary key of
the Record Source of the second form, txtID is the name of the text box on
the current form displaying this primary key value. This example shows a
numeric value. A string value has slightly different syntax, so if you don't
know the syntax for a string, please post back.

Private Sub CurrRatesBtn_Click()

On Error GoTo ErrHandler

DoCmd.OpenForm "frmRates", acNormal, , "ID = " & Me!txtID.Value

Exit Sub

ErrHandler:

MsgBox "Error in CurrRatesBtn_Click( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Hi Gunny,

thanks for the reply! I get that. your code is alomost exactly what I have
and it works but slow. The quary has about 12000 records in it and the 1st
form filters it down to 20-30 records. Can I use the 20-30 records from the
1st form in the 2nd form and use the WHERE clause on it? So set the 2nd foms
record source to be the 1st forms recordset?

John
 
Hi, John.
your code is alomost exactly what I have
and it works but slow.
Click to expand...

Do you have an index on the field that is being used as the criteria for the
WHERE clause? That can speed things up immensely, especially when the data
set is being retrieved from a back end file across the network. Are other
processes using this table using optimistic locking? Optimistic locking
retrieves records faster for the user than pessimistic locking does. Are all
processes using this table using record-level locking or page level locking?
Record-level locking retrieves records faster for the user than page level
locking does.

If this is a split database, is there a persistent connection to the back
end? Preventing the user from establishing a new connection to the database
and creating a new .LDB file every time the tables are queried can save lots
of time. If this is a split databse, is the back end file as close to the
root directory as possible? Every directory traversed requires Windows to
use the Windows Networking API to check whether the user has Windows security
permissions to access that directory. Lots of directories in the path means
the Windows Networking API gets called lots of times. Does the file name and
the complete path to it use the old DOS 8.3 naming convention or the Windows
long name convention? Again, the Windows API for resolving long names must
be called to resolve the path and file name if the DOS 5.3 naming convention
isn't adhered to. It takes time for all of these things to happen,
especially on a network with lots of traffic.
Can I use the 20-30 records from the
1st form in the 2nd form and use the WHERE clause on it? So set the 2nd foms
record source to be the 1st forms recordset?
Click to expand...

Unfortunately, no. The second form is a separate entity and must query the
database itself when it opens. The second form can't "share" the same
Recordset Object as the first form, but it can retrieve the exact same
records from the database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Back
Top