Can't refresh/requery/repaint textboxes

[Leslie Isaacs]

Hello All

This is a continuation of a problem I posted last week.

I have a form with 6 unbound text boxes that show various calculated values
(using DLookups etc.). The only problem is that the values are not
displaying when the form is first opened: the only way I can get them to
display is either to click on them, or to tab through them.

I have tried using the following commands in the form's OnOpen event:

[text1].SetFocus
DoCmd.GoToControl ("[text1]")
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Following suggestions received to my earlier post, I have tried Me.Recalc in
both the form's OnLoad and its OnCurrent events, and I have also tried
Me.Repaint and DoEvents ... but unfortunately nothing worked!

I know that using the 6 DoCmd.GoToControl commands (1 for each textbox) does
work, because I put them as the OnClick event of a button, and that worked.
The problem is getting these events to 'fire' without having to click a
button.

The SetFocus and DoCmd.GoToControl commands both do work on the final
textbox specified - i.e. they cause the value in that final textbox to be
displayed: it's as if the commands haven't had time to
'act on' the previous texboxes though, as their values were not displayed. I
did try seperating the commands with a Beep command to see if that created
enough delay for the commands to 'act' on each textbox ... but it didn't!!

Finnaly, I tried just having 1 of the DoCmd.GoToControl commands in the
form's OnLoad event (i.e. DoCmd.GoToControl ("[weeklyrate]") ), and then
putting the other 5 in the OnGotFocus event of that first textbox
[weeklyrate], but it seems that you can't use code to go to a control that
has an OnFocus event!

What else can I do? There must be some way of getting the textboxes to
display their values without having to click a button or tab through them.

Hope someone can help.
Many thanks
Les

 

[PayeDoc]

Hello OssieMac

Thanks for your reply.
The Dlookups are all in the control source property.
I - like you - have had texboxes with Dlookups in the control source many
times before, with no problem. I don't know why there is a problem with this
form.
If you have any other ideas I would be really grateful.

Many thanks
Les

Hi Leslie,

Pls forgive me if this is a silly question but where are you inserting the
Dlookup. Are you putting it in the Control Source property of the text box or
is it in code that requires an event (like On enter) to run it and populate
the textbox with the result?

I have tested this by inserting the Dlookup in the control source property
and it simply displays the results when the form opens.

--
Regards,

OssieMac

Hello All

This is a continuation of a problem I posted last week.

I have a form with 6 unbound text boxes that show various calculated values
(using DLookups etc.). The only problem is that the values are not
displaying when the form is first opened: the only way I can get them to
display is either to click on them, or to tab through them.

I have tried using the following commands in the form's OnOpen event:

[text1].SetFocus
DoCmd.GoToControl ("[text1]")
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Following suggestions received to my earlier post, I have tried Me.Recalc in
both the form's OnLoad and its OnCurrent events, and I have also tried
Me.Repaint and DoEvents ... but unfortunately nothing worked!

I know that using the 6 DoCmd.GoToControl commands (1 for each textbox) does
work, because I put them as the OnClick event of a button, and that worked.
The problem is getting these events to 'fire' without having to click a
button.

The SetFocus and DoCmd.GoToControl commands both do work on the final
textbox specified - i.e. they cause the value in that final textbox to be
displayed: it's as if the commands haven't had time to
'act on' the previous texboxes though, as their values were not displayed. I
did try seperating the commands with a Beep command to see if that created
enough delay for the commands to 'act' on each textbox ... but it didn't!!

Finnaly, I tried just having 1 of the DoCmd.GoToControl commands in the
form's OnLoad event (i.e. DoCmd.GoToControl ("[weeklyrate]") ), and then
putting the other 5 in the OnGotFocus event of that first textbox
[weeklyrate], but it seems that you can't use code to go to a control that
has an OnFocus event!

What else can I do? There must be some way of getting the textboxes to
display their values without having to click a button or tab through them.

Hope someone can help.
Many thanks
Les

 

[Marshall Barton]

I have a form with 6 unbound text boxes that show various calculated values
(using DLookups etc.). The only problem is that the values are not
displaying when the form is first opened: the only way I can get them to
display is either to click on them, or to tab through them.

I have tried using the following commands in the form's OnOpen event:

[text1].SetFocus
DoCmd.GoToControl ("[text1]")
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Following suggestions received to my earlier post, I have tried Me.Recalc in
both the form's OnLoad and its OnCurrent events, and I have also tried
Me.Repaint and DoEvents ... but unfortunately nothing worked!

I know that using the 6 DoCmd.GoToControl commands (1 for each textbox) does
work, because I put them as the OnClick event of a button, and that worked.
The problem is getting these events to 'fire' without having to click a
button.

The SetFocus and DoCmd.GoToControl commands both do work on the final
textbox specified - i.e. they cause the value in that final textbox to be
displayed: it's as if the commands haven't had time to
'act on' the previous texboxes though, as their values were not displayed. I
did try seperating the commands with a Beep command to see if that created
enough delay for the commands to 'act' on each textbox ... but it didn't!!

Finnaly, I tried just having 1 of the DoCmd.GoToControl commands in the
form's OnLoad event (i.e. DoCmd.GoToControl ("[weeklyrate]") ), and then
putting the other 5 in the OnGotFocus event of that first textbox
[weeklyrate], but it seems that you can't use code to go to a control that
has an OnFocus event!

What else can I do? There must be some way of getting the textboxes to
display their values without having to click a button or tab through them.

Use SetFocus. The DoCmd and DoMenuItem are not 100%
reliable in some scenarios.

If that works for one text box, then I think the problem may
be, as you suspected, that DLookup takes more time than
Access can deal with while its trying to calculate all the
controls, display all the controls and load the form's data.

A quick thing to try would be to use one or two DoEvents
between each SetFocus.

OTOH, I suspect that you should remove the text boxes'
control source expression and set their value using code in
the Load event. If the DLookups are retrieving different
fields from a single record, then use a recordset ro get the
record once.

 

[OssieMac]

Marshall's idea of placing the Dlookup in code is certainly worth a try.
However, as you say that when you click in the textboxes, they update. How
about trying SendKeys in conjunction with set focus in the On Current Event.

Me.Text4.SetFocus
SendKeys "{ENTER}",True
Me.Text6.SetFocus
SendKeys "{ENTER}",True
Me.Text8.SetFocus
SendKeys "{ENTER}",True

You could possibly use the SendKeys without the SetFocus but you might also
inadvertantly operate a CommandButton if the focus just happens to be in the
wrong place. I also tested Tab but under test by placing a stop in the code
after each SendKeys, the form is not actually displayed by the time the
SendKeys Tab is processed. Using Enter, the form was always displayed after
the first Enter.

--
Regards,

OssieMac

Hello OssieMac

Thanks for your reply.
The Dlookups are all in the control source property.
I - like you - have had texboxes with Dlookups in the control source many
times before, with no problem. I don't know why there is a problem with this
form.
If you have any other ideas I would be really grateful.

Many thanks
Les

Hi Leslie,

Pls forgive me if this is a silly question but where are you inserting the
Dlookup. Are you putting it in the Control Source property of the text box or
is it in code that requires an event (like On enter) to run it and populate
the textbox with the result?

I have tested this by inserting the Dlookup in the control source property
and it simply displays the results when the form opens.

--
Regards,

OssieMac

Hello All

This is a continuation of a problem I posted last week.

I have a form with 6 unbound text boxes that show various calculated values
(using DLookups etc.). The only problem is that the values are not
displaying when the form is first opened: the only way I can get them to
display is either to click on them, or to tab through them.

I have tried using the following commands in the form's OnOpen event:

[text1].SetFocus
DoCmd.GoToControl ("[text1]")
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Following suggestions received to my earlier post, I have tried Me.Recalc in
both the form's OnLoad and its OnCurrent events, and I have also tried
Me.Repaint and DoEvents ... but unfortunately nothing worked!

I know that using the 6 DoCmd.GoToControl commands (1 for each textbox) does
work, because I put them as the OnClick event of a button, and that worked.
The problem is getting these events to 'fire' without having to click a
button.

The SetFocus and DoCmd.GoToControl commands both do work on the final
textbox specified - i.e. they cause the value in that final textbox to be
displayed: it's as if the commands haven't had time to
'act on' the previous texboxes though, as their values were not displayed. I
did try seperating the commands with a Beep command to see if that created
enough delay for the commands to 'act' on each textbox ... but it didn't!!

Finnaly, I tried just having 1 of the DoCmd.GoToControl commands in the
form's OnLoad event (i.e. DoCmd.GoToControl ("[weeklyrate]") ), and then
putting the other 5 in the OnGotFocus event of that first textbox
[weeklyrate], but it seems that you can't use code to go to a control that
has an OnFocus event!

What else can I do? There must be some way of getting the textboxes to
display their values without having to click a button or tab through them.

Hope someone can help.
Many thanks
Les

 

[PayeDoc]

Hello OssieMac

Many thanks for your further suggestions
Unfortunately using SendKeys with SetFocus didn't work: even though I had
the SetFocus commands immediately before each SendKeys command, it was still
tring to operate another CommandButton!

The record source for the form is:
SELECT sicknessabsences.employeename, Count(sicknessabsences.AbsenceID) AS
CountOfAbsenceID, staffs.[ssp days], [nml hourweek going]*[Hourly
rate]/[paydays] AS [workday rate], staffs.[nml hourweek going],
staffs.[Hourly rate], staffs.paydays
FROM staffs LEFT JOIN sicknessabsences ON staffs.name =
sicknessabsences.employeename
WHERE (((sicknessabsences.absencetype)="W" Or
(sicknessabsences.absencetype)="S" Or (sicknessabsences.absencetype)="L")
AND ((sicknessabsences.paid)=[Forms]![frm x main]![month name]))
GROUP BY sicknessabsences.employeename, staffs.[ssp days], [nml hourweek
going]*[Hourly rate]/[paydays], staffs.[nml hourweek going], staffs.[Hourly
rate], staffs.paydays
HAVING
(((sicknessabsences.employeename)=[Forms]![frmSicknessAbsence]![sickname]));

The OnCurrrent event I now have is:
(I have rem'd out the SendKeys and Me.Repaint and Me.Recalc commands for
now)
Private Sub Form_Current()
'Me.Repaint
'Me.Recalc
DoEvents
DoEvents
Me.weeklyrate.SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents
Me.[weeklyrate].SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents
Me.[normalpay].SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents
Me.[sspdays].SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents
Me.[ssprate].SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents
Me.[ssppay].SetFocus
'SendKeys "{ENTER}", True
Me.[Command29].SetFocus
SendKeys "{ENTER}", True
DoEvents
DoEvents
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub

The 8 texboxes on the form have the following record sources:
1. CountOfAbsenceID (called CountOfAbsenceID)
2. =[workday rate]*[paydays] (called weeklyrate)
3. paydays (called Text10)
4. =[CountOfAbsenceID]*[workday rate] (called normalpay)
5.
=DCount("[sicknessabsences]![absenceID]","[sicknessabsences]","[employeename
] = [forms]![frmsicknessabsence]![sickname] and [paid]=[forms]![frm x
main]![month name] and [absencetype] = 's'") (called sspdays)
6. =IIf([Forms]![frm x main]![number]<121,72.55,75.4) (called ssprate)
7. paydays (called Text16)
8. =[ssprate]*[sspdays]/[paydays] (called ssppay)

I know my naming convention (or lack of!) is less than ideal, but don't
think this can be causing the problem.

Any ideas?
Thanks as ever.
Les

Marshall's idea of placing the Dlookup in code is certainly worth a try.
However, as you say that when you click in the textboxes, they update. How
about trying SendKeys in conjunction with set focus in the On Current Event.

Me.Text4.SetFocus
SendKeys "{ENTER}",True
Me.Text6.SetFocus
SendKeys "{ENTER}",True
Me.Text8.SetFocus
SendKeys "{ENTER}",True

You could possibly use the SendKeys without the SetFocus but you might also
inadvertantly operate a CommandButton if the focus just happens to be in the
wrong place. I also tested Tab but under test by placing a stop in the code
after each SendKeys, the form is not actually displayed by the time the
SendKeys Tab is processed. Using Enter, the form was always displayed after
the first Enter.

--
Regards,

OssieMac

Hello OssieMac

Thanks for your reply.
The Dlookups are all in the control source property.
I - like you - have had texboxes with Dlookups in the control source many
times before, with no problem. I don't know why there is a problem with this
form.
If you have any other ideas I would be really grateful.

Many thanks
Les

Hi Leslie,

Pls forgive me if this is a silly question but where are you inserting the
Dlookup. Are you putting it in the Control Source property of the text

box
or

is it in code that requires an event (like On enter) to run it and populate
the textbox with the result?

I have tested this by inserting the Dlookup in the control source property
and it simply displays the results when the form opens.

--
Regards,

OssieMac


:

Hello All

This is a continuation of a problem I posted last week.

I have a form with 6 unbound text boxes that show various calculated values
(using DLookups etc.). The only problem is that the values are not
displaying when the form is first opened: the only way I can get them to
display is either to click on them, or to tab through them.

I have tried using the following commands in the form's OnOpen event:

[text1].SetFocus
DoCmd.GoToControl ("[text1]")
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Following suggestions received to my earlier post, I have tried Me.Recalc in
both the form's OnLoad and its OnCurrent events, and I have also tried
Me.Repaint and DoEvents ... but unfortunately nothing worked!

I know that using the 6 DoCmd.GoToControl commands (1 for each

textbox)
does

work, because I put them as the OnClick event of a button, and that worked.
The problem is getting these events to 'fire' without having to click a
button.

The SetFocus and DoCmd.GoToControl commands both do work on the final
textbox specified - i.e. they cause the value in that final textbox

to
be

displayed: it's as if the commands haven't had time to
'act on' the previous texboxes though, as their values were not displayed. I
did try seperating the commands with a Beep command to see if that created
enough delay for the commands to 'act' on each textbox ... but it didn't!!

Finnaly, I tried just having 1 of the DoCmd.GoToControl commands in the
form's OnLoad event (i.e. DoCmd.GoToControl ("[weeklyrate]") ), and then
putting the other 5 in the OnGotFocus event of that first textbox
[weeklyrate], but it seems that you can't use code to go to a

control
that

has an OnFocus event!

What else can I do? There must be some way of getting the textboxes to
display their values without having to click a button or tab through them.

Hope someone can help.
Many thanks
Les

 

[PayeDoc]

Hello Marshall

Thanks for your further suggestions.

Unfortunately using SetFocus commands, even with 2 DoEvents commands between
each, didn't work!
I have also tried OssieMac's idea of using SendKeys, but again that didn't
work!
I haven't yet tried to set the values with code, mainly because I don't know
how to use a recordset to get the record once: but in any case I think there
must be something 'peculiar' with my form/textboxes the bottom of which I
would like to get to!

Here are the form's and the textboxes's data sources and the form's
OnCurrent event:

The record source for the form is:
SELECT sicknessabsences.employeename, Count(sicknessabsences.AbsenceID) AS
CountOfAbsenceID, staffs.[ssp days], [nml hourweek going]*[Hourly
rate]/[paydays] AS [workday rate], staffs.[nml hourweek going],
staffs.[Hourly rate], staffs.paydays
FROM staffs LEFT JOIN sicknessabsences ON staffs.name =
sicknessabsences.employeename
WHERE (((sicknessabsences.absencetype)="W" Or
(sicknessabsences.absencetype)="S" Or (sicknessabsences.absencetype)="L")
AND ((sicknessabsences.paid)=[Forms]![frm x main]![month name]))
GROUP BY sicknessabsences.employeename, staffs.[ssp days], [nml hourweek
going]*[Hourly rate]/[paydays], staffs.[nml hourweek going], staffs.[Hourly
rate], staffs.paydays
HAVING
(((sicknessabsences.employeename)=[Forms]![frmSicknessAbsence]![sickname]));

The OnCurrrent event I now have is:
(I have rem'd out the SendKeys and Me.Repaint and Me.Recalc commands for
now)
Private Sub Form_Current()
'Me.Repaint
'Me.Recalc
DoEvents
DoEvents
Me.weeklyrate.SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents
Me.[weeklyrate].SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents
Me.[normalpay].SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents
Me.[sspdays].SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents
Me.[ssprate].SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents
Me.[ssppay].SetFocus
'SendKeys "{ENTER}", True
Me.[Command29].SetFocus
SendKeys "{ENTER}", True
DoEvents
DoEvents
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub

The 8 texboxes on the form have the following record sources:
1. CountOfAbsenceID (called CountOfAbsenceID)
2. =[workday rate]*[paydays] (called weeklyrate)
3. paydays (called Text10)
4. =[CountOfAbsenceID]*[workday rate] (called normalpay)
5.
=DCount("[sicknessabsences]![absenceID]","[sicknessabsences]","[employeename
] = [forms]![frmsicknessabsence]![sickname] and [paid]=[forms]![frm x
main]![month name] and [absencetype] = 's'") (called sspdays)
6. =IIf([Forms]![frm x main]![number]<121,72.55,75.4) (called ssprate)
7. paydays (called Text16)
8. =[ssprate]*[sspdays]/[paydays] (called ssppay)

I know my naming convention (or lack of!) is less than ideal, but don't
think this can be causing the problem.

Any ideas?
Thanks as ever.
Les

I have a form with 6 unbound text boxes that show various calculated values
(using DLookups etc.). The only problem is that the values are not
displaying when the form is first opened: the only way I can get them to
display is either to click on them, or to tab through them.

I have tried using the following commands in the form's OnOpen event:

[text1].SetFocus
DoCmd.GoToControl ("[text1]")
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Following suggestions received to my earlier post, I have tried Me.Recalc in
both the form's OnLoad and its OnCurrent events, and I have also tried
Me.Repaint and DoEvents ... but unfortunately nothing worked!

I know that using the 6 DoCmd.GoToControl commands (1 for each textbox) does
work, because I put them as the OnClick event of a button, and that worked.
The problem is getting these events to 'fire' without having to click a
button.

The SetFocus and DoCmd.GoToControl commands both do work on the final
textbox specified - i.e. they cause the value in that final textbox to be
displayed: it's as if the commands haven't had time to
'act on' the previous texboxes though, as their values were not displayed. I
did try seperating the commands with a Beep command to see if that created
enough delay for the commands to 'act' on each textbox ... but it didn't!!

Finnaly, I tried just having 1 of the DoCmd.GoToControl commands in the
form's OnLoad event (i.e. DoCmd.GoToControl ("[weeklyrate]") ), and then
putting the other 5 in the OnGotFocus event of that first textbox
[weeklyrate], but it seems that you can't use code to go to a control that
has an OnFocus event!

What else can I do? There must be some way of getting the textboxes to
display their values without having to click a button or tab through

them.


Use SetFocus. The DoCmd and DoMenuItem are not 100%
reliable in some scenarios.

If that works for one text box, then I think the problem may
be, as you suspected, that DLookup takes more time than
Access can deal with while its trying to calculate all the
controls, display all the controls and load the form's data.

A quick thing to try would be to use one or two DoEvents
between each SetFocus.

OTOH, I suspect that you should remove the text boxes'
control source expression and set their value using code in
the Load event. If the DLookups are retrieving different
fields from a single record, then use a recordset ro get the
record once.

 

[Marshall Barton]

PayeDoc wrote:


Well, I went and got confused by the two threads and replied
in your original thread. Go back there where I noted a
couple of possible performance issues and what I think could
be the crux of your problem.