dcount

PatientTable contains thousands of patient records. subform current record
selection should for a given clinic ID, update the parent totalpatients text
box with total number of patients in good health. But the
update is slow. like to speed it up so that a current record selection,
updates the text box almost immediately.

Private Sub Form_Current()
Me.Parent.totalpatients = DCount("patient", "PatientTable", "patient='Good'
And ClinicID='" & Me.Parent.Clinic_ID & "'")
End Sub

--
Message posted via http://www.accessmonster.com

hi,

igg via AccessMonster.com wrote:
update is slow. like to speed it up so that a current record selection,
updates the text box almost immediately.

Private Sub Form_Current()
Me.Parent.totalpatients = DCount("patient", "PatientTable", "patient='Good'
And ClinicID='" & Me.Parent.Clinic_ID & "'")
End Sub
The most important step:
Create a combined index in your table [PatientTable] and add the two
fields [Patient] and [ClinicID] (in this order).

Is your parent form bound or unbound? Does Me.Parent.Clinic_ID reference
a bound field or an unbound control?


mfG
-- stefan --

parent form is unbound. And, Me.Parent.Clinic_ID is also unbound.

Stefan Hoffmann wrote:
hi,

update is slow. like to speed it up so that a current record selection,
updates the text box almost immediately.
[quoted text clipped - 3 lines]
And ClinicID='" & Me.Parent.Clinic_ID & "'")
End Sub
The most important step:
Create a combined index in your table [PatientTable] and add the two
fields [Patient] and [ClinicID] (in this order).

Is your parent form bound or unbound? Does Me.Parent.Clinic_ID reference
a bound field or an unbound control?

mfG
-- stefan --

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

hi,

igg via AccessMonster.com wrote:
The most important step:
Create a combined index in your table [PatientTable] and add the two
fields [Patient] and [ClinicID] (in this order).
Is your parent form bound or unbound? Does Me.Parent.Clinic_ID reference
a bound field or an unbound control?
Okay. Adding the index did work?



mfG
-- stefan --

original remote table is being used and I can not modify it. i copied the
original database to a different remote folder with no user connected to it
and noticed significant improvement in speed. By adding a combined index you
do not mean adding a new column to the table, right?. just go to design view
and create an combined index of the two, right?.

Stefan Hoffmann wrote:
hi,

The most important step:
Create a combined index in your table [PatientTable] and add the two
fields [Patient] and [ClinicID] (in this order).
Is your parent form bound or unbound? Does Me.Parent.Clinic_ID reference
a bound field or an unbound control?
Okay. Adding the index did work?

mfG
-- stefan --

--
Message posted via http://www.accessmonster.com

hi,

igg via AccessMonster.com wrote:
original remote table is being used and I can not modify it. i copied the
original database to a different remote folder with no user connected to it
and noticed significant improvement in speed.
This should only be the case if the new remote location is on another
server/drive.

But there is another optimization you may try: A permanent recordset.

Create a form with this events filled:

Option Compare Database
Option Explicit

Private m_Recordset As DAO.Recordset

Private Sub Form_Load()

Set m_Recordset = CurrentDb.OpenRecordset( _
"anyRemoteTable", dbOpenDynamic)

End Sub

Private Sub Form_Close()

m_Recordset.Close
Set m_Recordset = Nothing

End Sub

ACE/Jet will create the lock file (.ldb) once. So this saves file I/O.

By adding a combined index you
do not mean adding a new column to the table, right?. just go to design view
and create an combined index of the two, right?.
Yes. No new columns, only the index.


mfG
-- stefan --

your first suggested optimization to create combo index worked very well. for
the second optimization that you have suggested, just use the form already i
am using or should i create a new form?. Just making sure. these should get
added to the form i use to do queries, right?. Thanks.

Stefan Hoffmann wrote:
hi,

original remote table is being used and I can not modify it. i copied the
original database to a different remote folder with no user connected to it
and noticed significant improvement in speed.
This should only be the case if the new remote location is on another
server/drive.

But there is another optimization you may try: A permanent recordset.

Create a form with this events filled:

Option Compare Database
Option Explicit

Private m_Recordset As DAO.Recordset

Private Sub Form_Load()

Set m_Recordset = CurrentDb.OpenRecordset( _
"anyRemoteTable", dbOpenDynamic)

End Sub

Private Sub Form_Close()

m_Recordset.Close
Set m_Recordset = Nothing

End Sub

ACE/Jet will create the lock file (.ldb) once. So this saves file I/O.

By adding a combined index you
do not mean adding a new column to the table, right?. just go to design view
and create an combined index of the two, right?.
Yes. No new columns, only the index.

mfG
-- stefan --

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

hi,

igg via AccessMonster.com wrote:
your first suggested optimization to create combo index worked very well. for
the second optimization that you have suggested, just use the form already i
am using or should i create a new form?. Just making sure. these should get
added to the form i use to do queries, right?. Thanks.
No, explicitly a new form.

Open it as soon as possible after your application has started using
WindowMode := acHidden, so that the users doesn't see it.

mfG
-- stefan --

is it possible to have the main form to start this new form and close when it
exits?. if yes, how?.

Stefan Hoffmann wrote:
hi,

your first suggested optimization to create combo index worked very well. for
the second optimization that you have suggested, just use the form already i
am using or should i create a new form?. Just making sure. these should get
added to the form i use to do queries, right?. Thanks.
No, explicitly a new form.

Open it as soon as possible after your application has started using
WindowMode := acHidden, so that the users doesn't see it.

mfG
-- stefan --

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

hi,

igg via AccessMonster.com wrote:
is it possible to have the main form to start this new form and close when it
exits?. if yes, how?.
Use the main forms Load event, e.g.:

Private Sub Form_Load()

DoCmd.OpenForm FormName := "formWithPermanentRecordset", _
WindowMode := acHidden

End Sub


mfG
-- stefan --