requery & stay on record

[Guest]

MainForm: DailyData
SubForm: MinData

I have a control on The MainForm that i requery so that the subform is
updated.
After data is entered the Form returns to the first record. How can i keep
the focus on the current record?

Thanks for any help.

patti

 

[Dirk Goldgar]

MainForm: DailyData
SubForm: MinData

I have a control on The MainForm that i requery so that the subform is
updated.
After data is entered the Form returns to the first record. How can i
keep the focus on the current record?

Are you requerying a control, or the form itself? The Requery method
only really applies to some controls (list/combo boxes, subforms,
calculated controls that use SQL aggregate or domain aggregate
functions), but requerying those controls shouldn't cause the form to be
requeried. If you requery a control that isn't in that group, though,
the form is requeried.

What is the exact code you're executing? If you really need to requery
the main form, then you can save the primary key of the current record
and move back to that record after requerying. However, I'm not sure
from your description that you need to, or want to, requery the form.

 

[kingston via AccessMonster.com]

Is there an ID on the current subform record before the requery? If so, save
the ID to a variable and find the record with that ID after the requery via
DoCmd.FindRecord.

 

[Guest]

Thanks for the help.

I had been working with the subform and it would go back to the first record.
Using the subform on the main form keeps the focus.
so i guess that is fixed (or acting as it should)

But i have a question regarding the requery.
MainForm: DailyData

Control: HGB

Private Sub HGB_AfterUpdate()

Requery
PLT.SetFocus

and then

Private Sub PLT_AfterUpdate()

Requery
Iron.SetFocus

This updates:
Subform: MinData
Control: MinHGB
Control: MinPLT

I requeried to update the subform that is based on total queries (so that i
can find the minimum of these two fields and their corresponding dates)

What is the best way to get the subform to reflect immediate changes?


Thanks.

patti

 

[Guest]

The use of the unqualified Requery could be a problem. You need to associate
the the method with the object to avoid any ambiquity. If you want to
requery the form, it should be
Me.Requery

Now, the technique for keeping the form on the same record (The code below
assumes the field in your form's record source you will be using is numeric,
the syntax will be different if it is text or date):

Dim lngMyKey As Long
Dim rst As Recordset

lngMyKey = Me.txtMyKey
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[MyKey] = " & lngMyKey
If rst.NoMatch Then
MsgBox "Error Find Record " & lngMyKey
Else
Me.Bookmark = rst.Bookmark
Endf If
Set rst = Nothing

 

[Dirk Goldgar]

Thanks for the help.

I had been working with the subform and it would go back to the first
record. Using the subform on the main form keeps the focus.
so i guess that is fixed (or acting as it should)

But i have a question regarding the requery.
MainForm: DailyData

Control: HGB

Private Sub HGB_AfterUpdate()

Requery
PLT.SetFocus

and then

Private Sub PLT_AfterUpdate()

Requery
Iron.SetFocus

This updates:
Subform: MinData
Control: MinHGB
Control: MinPLT

I requeried to update the subform that is based on total queries (so
that i can find the minimum of these two fields and their
corresponding dates)

What is the best way to get the subform to reflect immediate changes?

I suspect that you're requerying the main form unnecessarily, and I
don't know why you're doing all this "SetFocusing" -- what's wrong with
setting the form's tab order?

It sounds like all you need to do is save the current main form record,
and then requery the subform. You can do that like this:

Me.Dirty = False
Me!MinData.Requery

(assuming "MinData" is the name of the subform control on the main form,
as well as the name of the form that is its Source Object). You'd do
that in the AfterUpdate event of each of the main form controls that you
need to see reflected immediately in the subform result. Be aware that
you'll get an error if the current record can't be saved at the moment,
for some reason.

 

[Guest]

Thanks for the help.
subform updates as main form controls filled in.

re: setfocus. that was me trying to keep focus on current record. i have
removed that code.

i really appreciate all the advice.