Control requery problem

  • Thread starter Thread starter Michael Allen
  • Start date Start date
M

Michael Allen

I have a main form containing a sub-form. The main form includes a text
box with the control source being a Dlookup expression. The value of
this control is dependant on what is selecetd in a combo box in the
subform. I therefore need the control in the main form to update when
the value in the subform combo box changes. I therefore have an
AfterUpdate Event Procedure as follows:-

Me.Parent![txtDailyScore].Requery

This does update the txtDailyScore value in the main form but my
problem is that the focus keeps moving to the first record in the
sub-form when the event occurs.

So say I am adding data in the 10th record in the sub-form and I tab to
the field following the combo box, focus immediately goes to the first
field/record in the subform. How can I prevent this.

Thanks,
Mike
 
Hi Michael

I would use the 'form refresh' rather than the requery. Sometimes this works
the same and other times it can be tempromental!!!?!?!

Instead of
'Me.Parent![txtDailyScore].Requery
try

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

' just paste this as it shows and it will work on any form that has a
controlsource.

Try it and let me know if works!! :)

Rich



Michael said:
I have a main form containing a sub-form. The main form includes a text
box with the control source being a Dlookup expression. The value of
this control is dependant on what is selecetd in a combo box in the
subform. I therefore need the control in the main form to update when
the value in the subform combo box changes. I therefore have an
AfterUpdate Event Procedure as follows:-

Me.Parent![txtDailyScore].Requery

This does update the txtDailyScore value in the main form but my
problem is that the focus keeps moving to the first record in the
sub-form when the event occurs.

So say I am adding data in the 10th record in the sub-form and I tab to
the field following the combo box, focus immediately goes to the first
field/record in the subform. How can I prevent this.

Thanks,
Mike
Click to expand...
 
Thanks Rich but I have tried this. Any form of refreshing seems to do
the same, even pressing F9.

Mike
 
Hi Mike

Have you tried setting the ID of the record (I don't know what your using as
your primary key) ie autonumber or idexed field. Use it before your current
code is used thenn "Find the record" once the requery has been run ie...

Dim MyAct as Object
set MyAct = Me.ActiveControl
Dim MyO As Object
Dim MyNum as integer ' If your key field is a number
Set MyO = Me.Recordset.Clone
MyNum = me.MyKeyField


'====do your code

'====then

MyO.FindFirst "[ID] = " & MyNum
Me.Bookmark = MyO.Bookmark
MyAct.setfocus

This should make sure you always return to the record that you were on ie 10
instead of 1

Also may need to set the focus back to the active control which this shows
too.

Let me know if this is ok :)

Rich
 
Michael said:
I have a main form containing a sub-form. The main form includes a text
box with the control source being a Dlookup expression. The value of
this control is dependant on what is selecetd in a combo box in the
subform. I therefore need the control in the main form to update when
the value in the subform combo box changes. I therefore have an
AfterUpdate Event Procedure as follows:-

Me.Parent![txtDailyScore].Requery

This does update the txtDailyScore value in the main form but my
problem is that the focus keeps moving to the first record in the
sub-form when the event occurs.

So say I am adding data in the 10th record in the sub-form and I tab to
the field following the combo box, focus immediately goes to the first
field/record in the subform. How can I prevent this.
Click to expand...


Generally, as long as Access can figure out the dependency,
you should not have to do anything. I suspect that the
control source expression with the DLookup is somehow
obscuring the dependency. Post the expression and we'll see
if we can get it to work.

If we can't get that sorted, try using Recalc instead of
Requery.
 
The statement is as follows:-

=DSum("[DailyScore]","[qryAnalData]","[StaffID]=Forms!frmCert![StaffID]")

Mike
 
Sorry Marshall but I forgot to say in my last post that Recalc has the
same behaviour as Requery.
 
Michael said:
The statement is as follows:-

=DSum("[DailyScore]","[qryAnalData]","[StaffID]=Forms!frmCert![StaffID]")
Click to expand...


I don't see where that references the subform's combo box.
You did say it works when you requery the main form, but
that doesn't seem to jibe with what you said you wre trying
to do???

The way I read your situation, this might(?) allow Access to
see the dependency:

=DSum("[DailyScore]","[qryAnalData]","[StaffID]=" &
subformcontrol.Form.combobox)
 
Michael said:
Sorry Marshall but I forgot to say in my last post that Recalc has the
same behaviour as Requery.
Click to expand...


What? I am unfamiliar with a situation where Recalc changes
the form's current record. Are you sure you used:

Me.Parent![txtDailyScore].Recalc

and removed any Requery and Refresh?
 
My apologies Marshall. The behaviour is not the same. Using Recalc
instead of Requery, the control in the main form does update correctly
but what is now happening is that in the subform when the Recalc event
occurs, the records scroll to the last record, hiding all previous
records. They can be viewed by using the vertical scroll bar but it
would be preferable if this didn't happen.

There is no direct dependency between the control in the main form and
the combo box. However the Dsum statement is looking at the query
qryAnalData which contains a calculated field DailyScore. The
DailyScore value is dependent on what is selected in the combo box in
the subform.

Mike
 
Michael said:
My apologies Marshall. The behaviour is not the same. Using Recalc
instead of Requery, the control in the main form does update correctly
but what is now happening is that in the subform when the Recalc event
occurs, the records scroll to the last record, hiding all previous
records. They can be viewed by using the vertical scroll bar but it
would be preferable if this didn't happen.

There is no direct dependency between the control in the main form and
the combo box. However the Dsum statement is looking at the query
qryAnalData which contains a calculated field DailyScore. The
DailyScore value is dependent on what is selected in the combo box in
the subform.
Click to expand...


With no direct dependency, I think you'll have to calculate
the DSum in the combo box's AfterUpdate event procedure
instead of using a control source expression.

Remove the DSum expression from the main form text box.
Then add the a line of code to the event procedure:

Me.Parent!txtDailyScore = Dsum( . . .
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

After Update Requery Code 2
Requery combo box in subform 5
calculated control event order 2
requery subform combo afterupdate of main form combo 5
requery not working 7
"save" button not requerying as coded 3
Requery a control on a subform 5
Requerying combo box in sub sub form 6

Back
Top