Refresh unbound textboxes on form

[Leslie Isaacs]

Hello All

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

.... but none of these work! 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!!

What can I do?

Hope someone can help.
Many thanks
Les

 

[Douglas J. Steele]

If the calculations rely on bound fields, the Open event is likely too soon,
as the bound fields won't be populated yet. Try putting the calculations
into the Load event, or the Current event.

 

[Leslie Isaacs]

Hello Douglas

Thanks for your suggestion, but unfortunately neither the Load or the
Current event worked either!

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.
So - as you say - the problem seems to be a matter of timing (isn't
everything?).
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!

Is there another way to do this?
I'm sure there must me ... but I can't get it.

If you have any other suggestions I would be really grateful.

Thanks again
Les

If the calculations rely on bound fields, the Open event is likely too soon,
as the bound fields won't be populated yet. Try putting the calculations
into the Load event, or the Current event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello All

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

... but none of these work! 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!!

What can I do?

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

... but none of these work! 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!!

Instead of all that, try using
Me.Repaint
and/or
DoEvents
in the Load event or maybe the first time the Current event
fires.

If that doesn't take care of it, try using Me.Recalc

 

[Klatuu]

Try using
Me.Recalc
In the Load or current event.
The Recalc method forces all calculated controls on the form to recalculate.
Sort of like using F9 in Excel.

Hello Douglas

Thanks for your suggestion, but unfortunately neither the Load or the
Current event worked either!

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.
So - as you say - the problem seems to be a matter of timing (isn't
everything?).
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!

Is there another way to do this?
I'm sure there must me ... but I can't get it.

If you have any other suggestions I would be really grateful.

Thanks again
Les

If the calculations rely on bound fields, the Open event is likely too soon,
as the bound fields won't be populated yet. Try putting the calculations
into the Load event, or the Current event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello All

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

... but none of these work! 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!!

What can I do?

Hope someone can help.
Many thanks
Les

 

[Leslie Isaacs]

Hello 'Klatu'

Many thanks for your suggestion.
Unfortunately I won't be in the office now until Monday, but I'll post back
then with the results!

Thanks again
Les

Try using
Me.Recalc
In the Load or current event.
The Recalc method forces all calculated controls on the form to
recalculate. Sort of like using F9 in Excel.

Hello Douglas

Thanks for your suggestion, but unfortunately neither the Load or the
Current event worked either!

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.
So - as you say - the problem seems to be a matter of timing (isn't
everything?).
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!

Is there another way to do this?
I'm sure there must me ... but I can't get it.

If you have any other suggestions I would be really grateful.

Thanks again
Les

If the calculations rely on bound fields, the Open event is likely too soon,
as the bound fields won't be populated yet. Try putting the calculations
into the Load event, or the Current event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello All

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

... but none of these work! 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!!

What can I do?

Hope someone can help.
Many thanks
Les

 

[Leslie Isaacs]

Hello Marshall

Many thanks for your suggestion.
Sorry about this, but how do I use Me.Repaint or DoEvents - I'm afraid I
don't know the syntax!

Thanks again
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

... but none of these work! 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!!

Instead of all that, try using
Me.Repaint
and/or
DoEvents
in the Load event or maybe the first time the Current event
fires.

If that doesn't take care of it, try using Me.Recalc

 

[Marshall Barton]

The syntax is exactly what I posted.

You can always check syntax and details by looking in VBA
Help.

 

[Leslie Isaacs]

Hi Marshall

Thanks for your reply.
I will try that when I'm next in the office, on Monday, and will post back
with the results!

Thanks again
Les

The syntax is exactly what I posted.

You can always check syntax and details by looking in VBA
Help.
--
Marsh
MVP [MS Access]

Sorry about this, but how do I use Me.Repaint or DoEvents - I'm afraid I
don't know the syntax!


"Marshall Barton" wrote

 

[PayeDoc]

Hello Marshall

OK: it's now Monday morning, I'm back in the office, and have tried
Me.Repaint and DoEvents in both the form's OnLoad and its OnCurrent events
.... but unfortunately nothing worked!

What else can I try?
Hope you can help.

Thanks again.
Les

The syntax is exactly what I posted.

You can always check syntax and details by looking in VBA
Help.
--
Marsh
MVP [MS Access]

Sorry about this, but how do I use Me.Repaint or DoEvents - I'm afraid I
don't know the syntax!


"Marshall Barton" wrote

 

[PayeDoc]

Hello "Klatuu"

OK: it's now Monday morning, I'm back in the office, and have tried
Me.Recalc in both the form's OnLoad and its OnCurrent events. I have also
tried Marshall's suggestions Me.Repaint and DoEvents ... but unfortunately
nothing worked!

What else can I try?
Hope you can help.

Thanks again.
Les

Try using
Me.Recalc
In the Load or current event.
The Recalc method forces all calculated controls on the form to recalculate.
Sort of like using F9 in Excel.

Hello Douglas

Thanks for your suggestion, but unfortunately neither the Load or the
Current event worked either!

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.
So - as you say - the problem seems to be a matter of timing (isn't
everything?).
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!

Is there another way to do this?
I'm sure there must me ... but I can't get it.

If you have any other suggestions I would be really grateful.

Thanks again
Les

If the calculations rely on bound fields, the Open event is likely too soon,
as the bound fields won't be populated yet. Try putting the calculations
into the Load event, or the Current event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello All

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

... but none of these work! 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!!

What can I do?

Hope someone can help.
Many thanks
Les

 

[Klatuu]

Post the calculations in the text boxes and the code where you do the
recalc, please.
The Recalc will cause a form's calculated values to be recalculated. There
has to be something else you are doing or not doing causing the problem.

Hello Marshall

OK: it's now Monday morning, I'm back in the office, and have tried
Me.Repaint and DoEvents in both the form's OnLoad and its OnCurrent events
... but unfortunately nothing worked!

What else can I try?
Hope you can help.

Thanks again.
Les

The syntax is exactly what I posted.

You can always check syntax and details by looking in VBA
Help.
--
Marsh
MVP [MS Access]

Sorry about this, but how do I use Me.Repaint or DoEvents - I'm afraid I
don't know the syntax!


"Marshall Barton" wrote
Leslie Isaacs wrote:
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.

 

[Marshall Barton]

OK: it's now Monday morning, I'm back in the office, and have tried
Me.Repaint and DoEvents in both the form's OnLoad and its OnCurrent events
... but unfortunately nothing worked!

I thought you had abandoned this thread in favor of your
"continuation" thread. Since I have already replied in the
other thread, let's try to keep the conversation there, in
just one place.

 

[PayeDoc]

Kello Klatuu

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 - as they have failed to work!)
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?
Many thanks for your help.
Les

Post the calculations in the text boxes and the code where you do the
recalc, please.
The Recalc will cause a form's calculated values to be recalculated. There
has to be something else you are doing or not doing causing the problem.

Hello Marshall

OK: it's now Monday morning, I'm back in the office, and have tried
Me.Repaint and DoEvents in both the form's OnLoad and its OnCurrent events
... but unfortunately nothing worked!

What else can I try?
Hope you can help.

Thanks again.
Les

The syntax is exactly what I posted.

You can always check syntax and details by looking in VBA
Help.
--
Marsh
MVP [MS Access]


Leslie Isaacs wrote:
Sorry about this, but how do I use Me.Repaint or DoEvents - I'm afraid I
don't know the syntax!


"Marshall Barton" wrote
Leslie Isaacs wrote:
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.

 

[Marshall Barton]

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]));

I have some comments while we're waiting for Dave to analyze
all this. First, unless you really need to count the non
Null AbsenceIDs, use Count(*) It's much faster.

Remove the HAVING clause and put the name criteria in the
WHERE clause. The WHERE clause is used before the grouping,
nut HAVING is used after the grouping. This might be very
significant in some circumstances.

Probably won't make a noticable difference, but you can
chorten the absencetype criteria to:
absencetype IN("W","S","L")

The OnCurrrent event I now have is:
(I have rem'd out the SendKeys and Me.Repaint and Me.Recalc commands for
now - as they have failed to work!)
Private Sub Form_Current()
'Me.Repaint
'Me.Recalc
DoEvents
DoEvents
Me.weeklyrate.SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents [snip]
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub

SendKeys can be a disaster and should never be used!
DoMenuItem is an archaic hangover from A2 and should never
be used (even when a stupid wizard creates it).

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 think your whole problem might(?) be caused by the DCount
using the form references inside the quotes. This
effectively hides the form references from the expression
service so Access does not realize that it needs to be
recalculated when the control values change. Try changing
it to:
=DCount("*","sicknessabsences","employeename='" & sickname &
"' and paid= '" & [month name] & "' and absencetype='s' ")

 

[Leslie Isaacs]

Hello Marshall

I tried using your expression
=DCount("*","sicknessabsences","employeename='" & sickname & "' and paid= '"
& [month name] & "' and absencetype='s' ")
as the data source for the textbox called sspdays, but this returned a blank
(= null?) value (is this the problem?).

I tried having the Count(*) in the form's data source query, but I got a
message that you can't group on the *.

I will try to set the values with code, and I will remove the HAVING clause
and put the name criteria in the WHERE clause, and I will shorten the
absencetype criteria to absencetype IN("W","S","L"): but I really would like
to know what's going on with this form!

If you have any other suggestions I would be really grateful.

Thanks again
Les

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]));

I have some comments while we're waiting for Dave to analyze
all this. First, unless you really need to count the non
Null AbsenceIDs, use Count(*) It's much faster.

Remove the HAVING clause and put the name criteria in the
WHERE clause. The WHERE clause is used before the grouping,
nut HAVING is used after the grouping. This might be very
significant in some circumstances.

Probably won't make a noticable difference, but you can
chorten the absencetype criteria to:
absencetype IN("W","S","L")

The OnCurrrent event I now have is:
(I have rem'd out the SendKeys and Me.Repaint and Me.Recalc commands for
now - as they have failed to work!)
Private Sub Form_Current()
'Me.Repaint
'Me.Recalc
DoEvents
DoEvents
Me.weeklyrate.SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents [snip]
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub

SendKeys can be a disaster and should never be used!
DoMenuItem is an archaic hangover from A2 and should never
be used (even when a stupid wizard creates it).

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 think your whole problem might(?) be caused by the DCount
using the form references inside the quotes. This
effectively hides the form references from the expression
service so Access does not realize that it needs to be
recalculated when the control values change. Try changing
it to:
=DCount("*","sicknessabsences","employeename='" & sickname &
"' and paid= '" & [month name] & "' and absencetype='s' ")

 

[Marshall Barton]

I tried using your expression
=DCount("*","sicknessabsences","employeename='" & sickname & "' and paid= '"
& [month name] & "' and absencetype='s' ")
as the data source for the textbox called sspdays, but this returned a blank
(= null?) value (is this the problem?).

I tried having the Count(*) in the form's data source query, but I got a
message that you can't group on the *.

I will try to set the values with code, and I will remove the HAVING clause
and put the name criteria in the WHERE clause, and I will shorten the
absencetype criteria to absencetype IN("W","S","L"): but I really would like
to know what's going on with this form!

If you have any other suggestions I would be really grateful.

I don't have any "other" suggestions, but I do wonder why
the DCount is not returning a value. AFAICT, your DCount
and what I posted should be equivalent unless [frm x main]
is not the form under discussion. Id there more than one
form (or subform) involved in all this?

I think that Count(*) message is because you used Group By
for a field named *
Try setting up the field this way:

CountOfAbsenceID: Count(*)
with the Totals row set to
Expression
--
Marsh
MVP [MS Access]

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]));

I have some comments while we're waiting for Dave to analyze
all this. First, unless you really need to count the non
Null AbsenceIDs, use Count(*) It's much faster.

Remove the HAVING clause and put the name criteria in the
WHERE clause. The WHERE clause is used before the grouping,
nut HAVING is used after the grouping. This might be very
significant in some circumstances.

Probably won't make a noticable difference, but you can
chorten the absencetype criteria to:
absencetype IN("W","S","L")

The OnCurrrent event I now have is:
(I have rem'd out the SendKeys and Me.Repaint and Me.Recalc commands for
now - as they have failed to work!)
Private Sub Form_Current()
'Me.Repaint
'Me.Recalc
DoEvents
DoEvents
Me.weeklyrate.SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents [snip]
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub

SendKeys can be a disaster and should never be used!
DoMenuItem is an archaic hangover from A2 and should never
be used (even when a stupid wizard creates it).

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 think your whole problem might(?) be caused by the DCount
using the form references inside the quotes. This
effectively hides the form references from the expression
service so Access does not realize that it needs to be
recalculated when the control values change. Try changing
it to:
=DCount("*","sicknessabsences","employeename='" & sickname &
"' and paid= '" & [month name] & "' and absencetype='s' ")

 

[PayeDoc]

Hello Marshall

Many thanks for continuing with this!
I now have Count(*) working as you suggest. In fact the original expression
wasn't returning a blank - it's just that its value didn't display until I
clicked in the field (just like the other textboxes) - derr!!

I have removed the HAVING clause as you suggested. The sql of the query is
now:
SELECT sicknessabsences.employeename, Count(*) 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])) AND
(((sicknessabsences.employeename)=[Forms]![frmSicknessAbsence]![sickname]))
GROUP BY sicknessabsences.employeename, staffs.[ssp days], [nml hourweek
going]*[Hourly rate]/[paydays], staffs.[nml hourweek going], staffs.[Hourly
rate], staffs.paydays;

I now have your expression:
=DCount("*","sicknessabsences","employeename='" & [sickname] & "' and paid=
'" & [month name] & "' and absencetype='s' ")
as data source for the textbox called 'sspdays', but this returns #Name?.
Might this point to my problem?

Thanks again
Les

I tried using your expression
=DCount("*","sicknessabsences","employeename='" & sickname & "' and paid= '"
& [month name] & "' and absencetype='s' ")
as the data source for the textbox called sspdays, but this returned a blank
(= null?) value (is this the problem?).

I tried having the Count(*) in the form's data source query, but I got a
message that you can't group on the *.

I will try to set the values with code, and I will remove the HAVING clause
and put the name criteria in the WHERE clause, and I will shorten the
absencetype criteria to absencetype IN("W","S","L"): but I really would like
to know what's going on with this form!

If you have any other suggestions I would be really grateful.

I don't have any "other" suggestions, but I do wonder why
the DCount is not returning a value. AFAICT, your DCount
and what I posted should be equivalent unless [frm x main]
is not the form under discussion. Id there more than one
form (or subform) involved in all this?

I think that Count(*) message is because you used Group By
for a field named *
Try setting up the field this way:

CountOfAbsenceID: Count(*)
with the Totals row set to
Expression
--
Marsh
MVP [MS Access]

PayeDoc wrote:
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] ));

I have some comments while we're waiting for Dave to analyze
all this. First, unless you really need to count the non
Null AbsenceIDs, use Count(*) It's much faster.

Remove the HAVING clause and put the name criteria in the
WHERE clause. The WHERE clause is used before the grouping,
nut HAVING is used after the grouping. This might be very
significant in some circumstances.

Probably won't make a noticable difference, but you can
chorten the absencetype criteria to:
absencetype IN("W","S","L")


The OnCurrrent event I now have is:
(I have rem'd out the SendKeys and Me.Repaint and Me.Recalc commands for
now - as they have failed to work!)
Private Sub Form_Current()
'Me.Repaint
'Me.Recalc
DoEvents
DoEvents
Me.weeklyrate.SetFocus
'SendKeys "{ENTER}", True
DoEvents
DoEvents
[snip]
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub

SendKeys can be a disaster and should never be used!
DoMenuItem is an archaic hangover from A2 and should never
be used (even when a stupid wizard creates it).


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]","[employeen ame
] = [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 think your whole problem might(?) be caused by the DCount
using the form references inside the quotes. This
effectively hides the form references from the expression
service so Access does not realize that it needs to be
recalculated when the control values change. Try changing
it to:
=DCount("*","sicknessabsences","employeename='" & sickname &
"' and paid= '" & [month name] & "' and absencetype='s' ")

 

[Marshall Barton]

Many thanks for continuing with this!
I now have Count(*) working as you suggest. In fact the original expression
wasn't returning a blank - it's just that its value didn't display until I
clicked in the field (just like the other textboxes) - derr!!

I have removed the HAVING clause as you suggested. The sql of the query is
now:
SELECT sicknessabsences.employeename, Count(*) 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])) AND
(((sicknessabsences.employeename)=[Forms]![frmSicknessAbsence]![sickname]))
GROUP BY sicknessabsences.employeename, staffs.[ssp days], [nml hourweek
going]*[Hourly rate]/[paydays], staffs.[nml hourweek going], staffs.[Hourly
rate], staffs.paydays;

I now have your expression:
=DCount("*","sicknessabsences","employeename='" & [sickname] & "' and paid=
'" & [month name] & "' and absencetype='s' ")
as data source for the textbox called 'sspdays', but this returns #Name?.
Might this point to my problem?

Well, that certainly is a problem, but it may or may not be
the only problem ;-)

#Name means that Access can not find [sickname] and/or
[month name] in either the form's record source query's
Fields collection or the form's Controls collection.

I am still concerned about how many forms/subforms are
involved in all this confusion. While reviewing your
original DCount, I finally noticed that the where condition
referred to two different forms and this will almost
certainly cause the #Name in the way I rewrote it.. If your
problem is occurs when you change a value on one form and
the value of a text box on another form, then that would be
expected and need something to be requiried. I really need
more details about your forms.

 

[Leslie Isaacs]

Hello Marshall

You are right - there are two forms from which criteria are taken. [frm x
main] is in fact the mdb's startup form, and has various texboxes the values
of which are use for various process. One such texbox is 'month name', and
this is used as a parameter for the data source of the other form
[frmSicknessAbsence]. It is [frmSicknessAbsence] that has the textboxes that
are not automatically displaying their values. There are no problems with
[frm x main], which is definitely open all the time - including when
[frmSicknessAbsence] is loaded. The non-displaying problem occurs
immediately when [frmSicknessAbsence]is opened - not just if the value of
'month name' on [frm x main] is changed. I would add that 'month name' on
[frm x main] is used by other processes (e.g. as filter for reports) without
any problems.

I hope I have given you all the relevant info. Unfortunately (from the point
of view of solving this problem!) I am now away on holiday for 5 days, and
so will not be able to try any suggestions you may make until after that. (I
may sneak a quick look at this thread if I have the opportunity - but wife
will not aprove!). I will definitely check back here on my return. If no-one
posts anything more on this thread while I'm away I guess I'll have to start
another post!

Very many thanks for all your help.
Les

Many thanks for continuing with this!
I now have Count(*) working as you suggest. In fact the original
expression
wasn't returning a blank - it's just that its value didn't display until I
clicked in the field (just like the other textboxes) - derr!!

I have removed the HAVING clause as you suggested. The sql of the query is
now:
SELECT sicknessabsences.employeename, Count(*) 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])) AND
(((sicknessabsences.employeename)=[Forms]![frmSicknessAbsence]![sickname]))
GROUP BY sicknessabsences.employeename, staffs.[ssp days], [nml hourweek
going]*[Hourly rate]/[paydays], staffs.[nml hourweek going],
staffs.[Hourly
rate], staffs.paydays;

I now have your expression:
=DCount("*","sicknessabsences","employeename='" & [sickname] & "' and
paid=
'" & [month name] & "' and absencetype='s' ")
as data source for the textbox called 'sspdays', but this returns #Name?.
Might this point to my problem?

Well, that certainly is a problem, but it may or may not be
the only problem ;-)

#Name means that Access can not find [sickname] and/or
[month name] in either the form's record source query's
Fields collection or the form's Controls collection.

I am still concerned about how many forms/subforms are
involved in all this confusion. While reviewing your
original DCount, I finally noticed that the where condition
referred to two different forms and this will almost
certainly cause the #Name in the way I rewrote it.. If your
problem is occurs when you change a value on one form and
the value of a text box on another form, then that would be
expected and need something to be requiried. I really need
more details about your forms.