access 97 problem with VBA and records

[phil cunningham]

Hi there

I had a project that works great under Access 2 but have updated to Access
97 (I know it's 2003)

I have a form with a subform and a button when the button is pressed the
code adds a record into the sub-form but this does not appear immediately as
it used to.

If I press F9 to requery the form it will eventually appear but it can take
a few seconds each time.

Is there a setting to speed something up as I am lost with this one

Any help will be much appreciated
Regards
Phil Cunningham

 

[Allen Browne]

Presumably your code appends the data to the table, rather than to the
RecordsetClone of the form.

If so, you could requery the form programatically:
Forms![MyForm].Requery

 

[Van T. Dinh]

It sounds like the new Record is still in cache and not actually in the
Table. You can try using the Idle Method with dbRefreshCache option to
ensure that the (new) Record is actually updated into the Table. Something
like:

'(code to add Record)...
DBEngine.Idle dbRefreshCache
Me.Requery


(NOT Me!Requery. Is it a typing mistake in the post? I don't think
Me!Requery would work???)

 

[phil cunningham]

please ignore my last posting. Your suggestion has worked, I forgot to
requery afterwards.

Many thanks for this as it was driving me nuts
Phil

 

[phil cunningham]

sorry but it seems to be happening again.

Really cannot understadn why the records are not appearing straight away.

If you have any more ideas I'd very apprecitiative
Phil

 

[Van T. Dinh]

Post relevant details of your Table, Form / Subform, wether the
CommandButton in is the Main Form or the Sub Form and the code for the
CommandButton.

 

[Van T. Dinh]

I don't have A97 to test the OpenTable Method you use on the Database object
and A2K doesn't show the OpenTable Method for the Database Object. In A2K,
there is a OpenTable Method of the DoCmd Object but this only open the Table
in the Access GUI, NOT a Table in memory for manipulation or data entry.

Try the OpenRecordset in lieu like:

****Untested code snippet only****
Dim myRS As DAO.Recordset

set myRS = myDb.OpenRecordset("BOM")
myRS.AddNew
...
myRS.Update
DBEngine.Idle dbRefreshCache
'(Subform requery - see previous post)
***

 

[phil cunningham]

thanks for the help but...

using RecordSet in the code the record is still added but will not appear
unless something forces the form to requery. (pressing F9 doesn't seem to
make much difference).

In the record navigator the total number of records doesnt change.

If I close the form and re-open it - the record is there)

also, if the table BOM is open when the code is executed the new record
doesnt appear there either (even with F9) but if I close and re-open the BOM
table the record is now visible.

It's as if Access is not recognising that records are being added.

Is there a way of forcing it to re-load the query records?

This is a real puzzle as I expected the records to appear straight away

If you can give any more help I'd really appreciate it
Phil

 

[phil cunningham]

The fact that sorting the records seems to make them appear I've added the
line

Forms![BOM Summary].OrderBy="[Bill ID]"

and it works!

There is also a MS-knowledge base article on this
http://support.microsoft.com/default.aspx?scid=kb;en-us;175188

still seems weird to me though
Phil

 

[Van T. Dinh]

You got me confused on where the added Record should appear. Is it the Main
Form or the Subform? Your earlier post seemed to indicate Subform but your
last post made me wonder.

You need to re-query (by code) the main Form or the Subform as appropriate
to your set-up. You added the Record *by code* so the Form will not be
aware of the added Record automatically and therefore you need to issue a
command to re-query the Form or Subform.