Interesting Problem with Requerying a form (me.requery) 
Author Message
 Interesting Problem with Requerying a form (me.requery)

I have a line item form whose recordsource is a temporary table, created in
a "work.mdb" using ADO and then linked to the main.mdb using ADO.
As is usual for Access forms, when the user enters the first field in the
line item, a "pen" appears to the left of the line item, indicating that the
line item is not yet "written" to the underlying table. Once the user fills
out all the fields in the line item, the "pen" changes to a "triangle"
indicating that the data has been written to the underlying table.

I have provided the user with a Save button which begins a posting routine
based on data in the underlying table. In the past, I have first issued a
me.requery to make sure the current record's data on the form has been
posted to  the underlying table.  This always has worked.

I find now that it does not work unless I requery twice! (me.requery,
me.requery) Also, if I single step thru the code it works. Otherwise it
doesn't.

Can anyone suggest an explanation for this. It is as if the access module is
moving too fast and has to be slowed down!

Thanks for any suggestions.

Paul Kreingold
Leesburg, VA



Tue, 03 Jun 2003 03:01:07 GMT  
 Interesting Problem with Requerying a form (me.requery)
Hi,

Have your tried Me.Dirty = False ?

Me.Requery should be used when it is required to see changes made by others
(other users but also other instances and in some case, your VBA code), and
it invalidate bookmarks. You didn't added VBA code behind your form that
make "two" requeries now "required" to achieve the side-effect result you
expect (and not necessary described here)? I will try to remove them first,
using a simple Me.Dirty=False instead.

Hoping it may help,
Vanderghast, Access MVP


Quote:
> I have a line item form whose recordsource is a temporary table, created
in
> a "work.mdb" using ADO and then linked to the main.mdb using ADO.
> As is usual for Access forms, when the user enters the first field in the
> line item, a "pen" appears to the left of the line item, indicating that
the
> line item is not yet "written" to the underlying table. Once the user
fills
> out all the fields in the line item, the "pen" changes to a "triangle"
> indicating that the data has been written to the underlying table.

> I have provided the user with a Save button which begins a posting routine
> based on data in the underlying table. In the past, I have first issued a
> me.requery to make sure the current record's data on the form has been
> posted to  the underlying table.  This always has worked.

> I find now that it does not work unless I requery twice! (me.requery,
> me.requery) Also, if I single step thru the code it works. Otherwise it
> doesn't.

> Can anyone suggest an explanation for this. It is as if the access module
is
> moving too fast and has to be slowed down!

> Thanks for any suggestions.

> Paul Kreingold
> Leesburg, VA



Tue, 03 Jun 2003 20:47:16 GMT  
 Interesting Problem with Requerying a form (me.requery)
Michel,

Thank you for your answer but....
Me.Dirty will tell me if the record has been changed, but the problem is
that I know of no way to make sure the underlying table (recordsource of the
form) is updated unless I require the user to tab through all the fields of
the line item or to use me.requery. I would have thought that
DoCmd.RunCommand acCmdSaveRecord would force an update of the underlying
table but it does not seem to.

Perhaps there is another method to accomplish what I need to accomplish
which is outlined below:

1. My form is a line item form with multiple rows (records) displayed and
each row has many fields.
3. Underlying the form is a table which is a temporary table, created and
loaded in the Open_form event.
4. The user wants to delete a line item so he selects the line and clicks
the Delete button.
5. A field on the row called the PostFl now displays a "D" indicating that
this record will be deleted when the user clicks
   the SAVE button. At this point the old value of PostFl is blank, the new
value is "D" and the value of the field in the
   recordsource is also blank.
6. The user clicks the Save button and processes.  Because the PostFl in the
recordsource is blank, the record is not
   deleted!
7. In the past I have used me.requery which resulted in an update of the
PostFl in the recordsource.

Any suggestions.

Paul Kreingold
Leesburg, VA


Quote:
> Hi,

> Have your tried Me.Dirty = False ?

> Me.Requery should be used when it is required to see changes made by
others
> (other users but also other instances and in some case, your VBA code),
and
> it invalidate bookmarks. You didn't added VBA code behind your form that
> make "two" requeries now "required" to achieve the side-effect result you
> expect (and not necessary described here)? I will try to remove them
first,
> using a simple Me.Dirty=False instead.

> Hoping it may help,
> Vanderghast, Access MVP



> > I have a line item form whose recordsource is a temporary table, created
> in
> > a "work.mdb" using ADO and then linked to the main.mdb using ADO.
> > As is usual for Access forms, when the user enters the first field in
the
> > line item, a "pen" appears to the left of the line item, indicating that
> the
> > line item is not yet "written" to the underlying table. Once the user
> fills
> > out all the fields in the line item, the "pen" changes to a "triangle"
> > indicating that the data has been written to the underlying table.

> > I have provided the user with a Save button which begins a posting
routine
> > based on data in the underlying table. In the past, I have first issued
a
> > me.requery to make sure the current record's data on the form has been
> > posted to  the underlying table.  This always has worked.

> > I find now that it does not work unless I requery twice! (me.requery,
> > me.requery) Also, if I single step thru the code it works. Otherwise it
> > doesn't.

> > Can anyone suggest an explanation for this. It is as if the access
module
> is
> > moving too fast and has to be slowed down!

> > Thanks for any suggestions.

> > Paul Kreingold
> > Leesburg, VA



Tue, 03 Jun 2003 21:35:37 GMT  
 Interesting Problem with Requerying a form (me.requery)
Michel didn't tell you to test whether me.Dirty = False, he told you to set me.Dirty = False.

If your form is dirty, and you set me.Dirty = False, it forces access to update the current record.

HTH,
Dale

Michel,

Thank you for your answer but....
Me.Dirty will tell me if the record has been changed, but the problem is
that I know of no way to make sure the underlying table (recordsource of the
form) is updated unless I require the user to tab through all the fields of
the line item or to use me.requery. I would have thought that
DoCmd.RunCommand acCmdSaveRecord would force an update of the underlying
table but it does not seem to.

Perhaps there is another method to accomplish what I need to accomplish
which is outlined below:

1. My form is a line item form with multiple rows (records) displayed and
each row has many fields.
3. Underlying the form is a table which is a temporary table, created and
loaded in the Open_form event.
4. The user wants to delete a line item so he selects the line and clicks
the Delete button.
5. A field on the row called the PostFl now displays a "D" indicating that
this record will be deleted when the user clicks
   the SAVE button. At this point the old value of PostFl is blank, the new
value is "D" and the value of the field in the
   recordsource is also blank.
6. The user clicks the Save button and processes.  Because the PostFl in the
recordsource is blank, the record is not
   deleted!
7. In the past I have used me.requery which resulted in an update of the
PostFl in the recordsource.

Any suggestions.

Paul Kreingold
Leesburg, VA


Quote:
> Hi,

> Have your tried Me.Dirty = False ?

> Me.Requery should be used when it is required to see changes made by
others
> (other users but also other instances and in some case, your VBA code),
and
> it invalidate bookmarks. You didn't added VBA code behind your form that
> make "two" requeries now "required" to achieve the side-effect result you
> expect (and not necessary described here)? I will try to remove them
first,
> using a simple Me.Dirty=False instead.

> Hoping it may help,
> Vanderghast, Access MVP



> > I have a line item form whose recordsource is a temporary table, created
> in
> > a "work.mdb" using ADO and then linked to the main.mdb using ADO.
> > As is usual for Access forms, when the user enters the first field in
the
> > line item, a "pen" appears to the left of the line item, indicating that
> the
> > line item is not yet "written" to the underlying table. Once the user
> fills
> > out all the fields in the line item, the "pen" changes to a "triangle"
> > indicating that the data has been written to the underlying table.

> > I have provided the user with a Save button which begins a posting
routine
> > based on data in the underlying table. In the past, I have first issued
a
> > me.requery to make sure the current record's data on the form has been
> > posted to  the underlying table.  This always has worked.

> > I find now that it does not work unless I requery twice! (me.requery,
> > me.requery) Also, if I single step thru the code it works. Otherwise it
> > doesn't.

> > Can anyone suggest an explanation for this. It is as if the access
module
> is
> > moving too fast and has to be slowed down!

> > Thanks for any suggestions.

> > Paul Kreingold
> > Leesburg, VA



Sat, 07 Jun 2003 21:15:04 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. subform disapppears after requerying main form

2. Progress Bar - whilst Access form's Recordsource is requerying

3. requery parent form when user adds a record to child form

4. Record Requerying Problem?

5. requery current record on the form???

6. help with requery command on form

7. Form Requery

8. Form requery

9. Forms requery

10. Requery all open forms

11. Requery a Combo Box on a Subform When Filtering the Main Form in Access 7.0

12. Requery stops the form updating

 

 
Powered by phpBB® Forum Software