Last Edited record on Subform.

[gmenon100 via AccessMonster.com]

Dear Friends,

I have a problem with staying on the last edited record. I will summarize my
project as under.
I have a main form "Shipment" which has 10 fields, 3 editable and the rest 7
derive totals from the undelying 3 subforms. This main form has a subform
"Entry Reg" which has 7 fields 3 editable and the rest deriving totals from
the underlying 2 subforms. Entry Reg has a subform "Invoice" which has 12
editable fields and 3 deriving totals from the underlying last subform
"Product".
In short the user first starts with creating a shipment and enters
information, he then enters data on Entry Reg. There can be multiple entries
for each shipment. Each entry can have one invoice or multiple invoices and
each invoice can similarlly have one or multiple products. After update,
products form, it runs a code to update all totals on the invoice subform,
Entry Ref subform and finally the Shipment main form. But as a result of the
code, it takes me back to the first record on the entry form with the code I
am using. I am sure I am missing some thing. I cannot do without putting the
totals on the forms as they are used in other reports. My code is as under
where OrdDetID is the product ID.

Private Sub Form_AfterUpdate()
With Me.RecordsetClone
Dim lngorddetId As Long
lngorddetId = Me.OrdDetID
Me.Requery
Me.Parent.Parent.Parent!ShpTotPcs = Me.ShiptotQrysub!shppcs
Me.Parent.Parent.Parent!ShpDuty = Me.ShiptotQrysub!shpdty
Me.Parent.Parent.Parent!ShpAch = Me.ShiptotQrysub!ShpAch
Me.Parent.Parent.Parent!ShpInvVal = Me.ShiptotQrysub!InvoiceValue
Me.Parent.Parent.Parent!ShpTotDty = Me.Parent.Parent.Parent!ShpDuty + Me.
Parent.Parent.Parent!ShpAch
Me.Parent.Parent!EntPcs = Me.EntrytotQrysub!EntPcs
Me.Parent.Parent!EntDuty = Me.EntrytotQrysub!entdty
Me.Parent.Parent!EntInv = Me.EntrytotQrysub!InvoiceValue
Me.Parent.Parent!EntTotDuty = Me.Parent.Parent!EntDuty + Me.Parent.Parent!
EntAch
Me.Parent.Parent!EntLoc = Me.Parent.Parent.Parent!ShpTotLoc
Me.Parent.Parent!EntInt = Me.Parent.Parent.Parent!ShpTotInt
Me.Parent.Parent.Refresh
Me.Parent!INVVAL = Me.InvtotQrysub!InvoiceValue
Me.Parent!POVal = Me.InvtotQrysub!POVal
Me.Parent!Totpcs = Me.InvtotQrysub!Invpcs
Me.Parent!TotDty = Me.InvtotQrysub!Invdty
Me.Parent!Freight = Me.InvtotQrysub!Frtppd
Me.Parent!TotAch = Me.EntrytotQrysub!AchAmt
Me.Parent.Refresh
.Find "orddetid=" & lngorddetId
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With


End Sub


Any help, comments or advise is appreciated.
Thank you,
G Menon

 

[Sylvain Lafontaine]

First, I'm not sure if your post is about ADP and SQL-Server so maybe you
should post your question in a more appropriate newsgroup such as mpa.forms
or mpa.formscoding.

In your case, doing a requery (or a refresh?) reset the recordset at its
beginning. You have code to reset its position using the lngorddetId
variable but it's not clear from your post if the bug is located at this
level. The best thing you could do would be simply to debug your code in
order to find where the error is located; for example, is the value
Me.OrdDetID OK? Does the find call function properly? Does the .bookmark
call function properly? What effect have the Me.Parent.Parent.Refresh
followed later by a Me.Parent.Refresh? You have your code right in front of
you, so it's much more easier for you to debug your code than for us to do
it.

Second, I'm not sure what you are trying to achieve with these two calls to
Me.Requery and to Me.Refresh. It's also not clear what kind of control are
ShpTotPcs and the like: are they bound or unbound controls? Are you trying
to change the values in bound controls and update the tables? Shouldn't you
try to update the tables instead and requery/refresh the forms/subforms?

Finally, with things like Me.RecordsetClone, it's better not to use them
directly with a WITH statement and instead store the value of
Me.RecordsetClone in a variable. If you are using ADP, it's also better to
use Me.Recordset.Clone instead of Me.RecordsetClone because there have been
report of bugs with Me.RecordsetClone and ADP.

 

[gmenon100 via AccessMonster.com]

Hi Sylvain,

Appreciate your response. I am not sure as to how to go around debuging this.
Do you have any suggenstions. As regards me.requery/refresh, I am refresing
the last subform, whihc has 2 subforms which updates information into 3
different tables. I will try using the Me.Recordset.Clone. Thanks for your
suggestion.

G Menon

First, I'm not sure if your post is about ADP and SQL-Server so maybe you
should post your question in a more appropriate newsgroup such as mpa.forms
or mpa.formscoding.

In your case, doing a requery (or a refresh?) reset the recordset at its
beginning. You have code to reset its position using the lngorddetId
variable but it's not clear from your post if the bug is located at this
level. The best thing you could do would be simply to debug your code in
order to find where the error is located; for example, is the value
Me.OrdDetID OK? Does the find call function properly? Does the .bookmark
call function properly? What effect have the Me.Parent.Parent.Refresh
followed later by a Me.Parent.Refresh? You have your code right in front of
you, so it's much more easier for you to debug your code than for us to do
it.

Second, I'm not sure what you are trying to achieve with these two calls to
Me.Requery and to Me.Refresh. It's also not clear what kind of control are
ShpTotPcs and the like: are they bound or unbound controls? Are you trying
to change the values in bound controls and update the tables? Shouldn't you
try to update the tables instead and requery/refresh the forms/subforms?

Finally, with things like Me.RecordsetClone, it's better not to use them
directly with a WITH statement and instead store the value of
Me.RecordsetClone in a variable. If you are using ADP, it's also better to
use Me.Recordset.Clone instead of Me.RecordsetClone because there have been
report of bugs with Me.RecordsetClone and ADP.

Dear Friends,

[quoted text clipped - 62 lines]

Thank you,
G Menon

 

[Sylvain Lafontaine]

Debugging is easy: you display the values and take a look to see if they are
OK or not. You also made some quick checks to be sure that the code is
doing what's supposing to do.

For example, before setting the .Bookmark, does the Find call worked
properly?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi Sylvain,

Appreciate your response. I am not sure as to how to go around debuging
this.
Do you have any suggenstions. As regards me.requery/refresh, I am
refresing
the last subform, whihc has 2 subforms which updates information into 3
different tables. I will try using the Me.Recordset.Clone. Thanks for your
suggestion.

G Menon

First, I'm not sure if your post is about ADP and SQL-Server so maybe you
should post your question in a more appropriate newsgroup such as
mpa.forms
or mpa.formscoding.

In your case, doing a requery (or a refresh?) reset the recordset at its
beginning. You have code to reset its position using the lngorddetId
variable but it's not clear from your post if the bug is located at this
level. The best thing you could do would be simply to debug your code in
order to find where the error is located; for example, is the value
Me.OrdDetID OK? Does the find call function properly? Does the .bookmark
call function properly? What effect have the Me.Parent.Parent.Refresh
followed later by a Me.Parent.Refresh? You have your code right in front
of
you, so it's much more easier for you to debug your code than for us to do
it.

Second, I'm not sure what you are trying to achieve with these two calls
to
Me.Requery and to Me.Refresh. It's also not clear what kind of control
are
ShpTotPcs and the like: are they bound or unbound controls? Are you
trying
to change the values in bound controls and update the tables? Shouldn't
you
try to update the tables instead and requery/refresh the forms/subforms?

Finally, with things like Me.RecordsetClone, it's better not to use them
directly with a WITH statement and instead store the value of
Me.RecordsetClone in a variable. If you are using ADP, it's also better
to
use Me.Recordset.Clone instead of Me.RecordsetClone because there have
been
report of bugs with Me.RecordsetClone and ADP.

Dear Friends,

[quoted text clipped - 62 lines]

Thank you,
G Menon