Same ADO Split Db

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ra - 12 Sep 2007 22:10 GMT

I am still wondering how to update my subform more consistently. Not to
mention when I delete a record it shows the row as "deleted". I have to close
and open to update the subform. Again, I use Me.sfrmABC.Requery to update. If
I use the DoCmd.RunSQL strSQL and then Me.sfrmABC.Requery, it updates all the
time. But that's the Jet, is it not??
Another issue is with a combo box on the same form. I have a list of names
in it, and when I delete one name it stays in the combo as "deleted" until I
close and open the form. I tried to "run" the RowSource after delete, with no
good result.
Any ideas?

Reply to this Message

Albert D. Kallal - 13 Sep 2007 00:52 GMT

>I am still wondering how to update my subform more consistently. Not to
> mention when I delete a record it shows the row as "deleted"

Are you deleting the record in code? Simply execute a requery on the
sub-form then

eg:

code to delete goes here:

me.frmMysubForm.Requery <--- add this after you delete code
(this assumed the
delete code is in the "main" form part..

> Another issue is with a combo box on the same form. I have a list of names
> in it, and when I delete one name it stays in the combo as "deleted" until
> I
> close and open the form

....code to delete goes here:
...

me.NameOfComboBox.Requery <-- add this after your delete code

As a general rule, you want to learn the use of

me.Refresh - force a disk write, and display of all updated records,
(record pointer does NOT move)

I use me.Refresh just about anything I launch another form (or report) from
the current record, as then the current record is written to disk. Note
that forcing disk writes is a by-product of using me.Refresh, but I have use
it for years to force disk writes.

A good many developers recommend the following code to force a disk write of
the *current* record to disk:

if me.Dirty = true then
me.Dirty = false
end if

me.Requery - forms record set is re-loaded, same as if you closed the
form
and then re-opened it. Note that bookmarks are
invalid after
you do this

me.Repaint - pending screen updates are shown. So, if you have a
loop, or
code running, and you update a control on he
screen, it will NOT
generally display the update UNTIL your code
is done. Repaint
will display pending screen updates right away

me.ReCalc when you change a value in a form via the GUI, most
fields are
recalculated for you. When you change a value
via code, then
often, you need to do a re-calc. However, you
don't need this
when running code in "after update" events
etc, since those
events are running as a result of changes..and
thus recalc
is fired for you anyway.

DoEvents.
All pending screen, mouse, and repaint, and recalc events are fired.
the event stack is run till empty. Use this to "release" processing
during a loop in which you have a stop buttion, or keypress to
stop the loop...

So, after a bit of time, you instantly know *exactly* which of the above
commands you need to call. In your cause, requery of the sub-form, or combo
box will re-load the record set....exactly what the doctor ordered...

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message

Ra - 13 Sep 2007 02:40 GMT

Thank you for your answer. I am deleting in code, and after the "delete sub"
I run the Me.sfrmABC.Requery. All the code is in the main form. I tried to
run the Me.cboExample.Requery as well. All the adding and deleting is done
over a connection on tables located on another drive, over a LAN. What am I
doing wrong??

> >I am still wondering how to update my subform more consistently. Not to
> > mention when I delete a record it shows the row as "deleted"
[quoted text clipped - 72 lines]
> commands you need to call. In your cause, requery of the sub-form, or combo
> box will re-load the record set....exactly what the doctor ordered...

Reply to this Message

Albert D. Kallal - 13 Sep 2007 03:25 GMT

> Thank you for your answer. I am deleting in code, and after the "delete
> sub"
> I run the Me.sfrmABC.Requery. All the code is in the main form.

Hum, that should work.

if you using dao, you code will look like:

Dim strSql As String

strSql = "delete * from contactchild where id = 144"

currentdb.Execute strSql

If you using ADO, then use:

Dim strSql As String

strSql = "delete * from contactchild where id = 144"

CurrentProject.Connection.Execute strSql
Me.cChild.Requery

Remember, you should use the currentProject connection object, and not one
you create from scratch, as you might experience some sync problems.

You *must* use the same connection object.

If you for some from another planet using your own connection object, then
your going to have to tell the jet database engine
that data could be pending..

This is explained here:

http://support.microsoft.com/kb/q200300/

But, really, it begs the question as why one would use diffent connection
objects here....

If the above is not your problem, you could also trying using:

me.cChild.form.Requery

You should not have to reference the "form" property of a sub-form control,
but try it just in case just calling for a re-query on the sub-form control
does not work... (ie: try me.cChile.Form.Requery).

And, if you binding recordsets to the form in code, then you might try a
query on the recordset

eg;

rst.Requery

My best guess is if the simple requery don't work, then you using the
"wrong" connection object, and you have to take a read of the above KB
article...

And, we are assuming bound forms here.....

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message

Pieter Wijnen - 13 Sep 2007 23:39 GMT

Two minor points
Me.sfrmABC.Form.Requery (you want to requery the form, not the control)
strSql = "delete from contactchild where id = 144" (is the Correct Ansi SQL,
Delete * Won't was with say Oracle)

Pieter

>> Thank you for your answer. I am deleting in code, and after the "delete
>> sub"
[quoted text clipped - 55 lines]
>
> And, we are assuming bound forms here.....

Reply to this Message