Refresh unbound textboxes on form

[Marshall Barton]

Ok, I think I understand it better now.

Assumong that employeename is a Text field, the Dcount
should then look more like:

=DCount("*","sicknessabsences","employeename='" & [sickname]
& "' And paid=Forms]![frm x main]![month name] And
absencetype='s' ")

Note that if [month name] is changed while
frmSicknessAbsence is open, you will need to at least
Requery the DCount text box and ony other item that uses
[month name] as a criteria. Actually, looking back at the
form's record source, is see that frmSicknessAbsence needs
to be requeried, which should also take care of the text
box.

On further reflection, I suspect that it's possible that you
might be able to duck the whole issue by ignoring everything
else you've tried and just using:
Me.sspdays.Requery
instead of the SetFocus, etc.
--
Marsh
MVP [MS Access]

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.

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?.

 

[PayeDoc]

Hello Marshall

I'm back!!
I hope you are still watching this thread, but if not I will re-post.

I have now tried Me.sspdays.Requery, but unfortunately this didn't work.
employeename is a Text field as you assumed, so I now have the DCount
expression as
=DCount("*","sicknessabsences","employeename='" & [sickname] & "' And
paid=Forms]![frm x main]![month name] And > absencetype='s' ")
.... but this still returns #Name?

My original DCount expression is:
=DCount("[sicknessabsences]![absenceID]","[sicknessabsences]","[employeename
] = [forms]![frmsicknessabsence]![sickname] and [paid]=[forms]![frm x
main]![month name] and [absencetype] = 's'")
.... and this does return the expected value - but only displays it after the
textbox has been manually selected!!!

I am not changing [month name] while frmSicknessAbsence is open.

Does any of this make any sense to you?

Many thanks for your continued help.
Les

Ok, I think I understand it better now.

Assumong that employeename is a Text field, the Dcount
should then look more like:

=DCount("*","sicknessabsences","employeename='" & [sickname]
& "' And paid=Forms]![frm x main]![month name] And
absencetype='s' ")

Note that if [month name] is changed while
frmSicknessAbsence is open, you will need to at least
Requery the DCount text box and ony other item that uses
[month name] as a criteria. Actually, looking back at the
form's record source, is see that frmSicknessAbsence needs
to be requeried, which should also take care of the text
box.

On further reflection, I suspect that it's possible that you
might be able to duck the whole issue by ignoring everything
else you've tried and just using:
Me.sspdays.Requery
instead of the SetFocus, etc.
--
Marsh
MVP [MS Access]

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.

PayeDoc wrote:
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?.

 

[Marshall Barton]

I have now tried Me.sspdays.Requery, but unfortunately this didn't work.
employeename is a Text field as you assumed, so I now have the DCount
expression as
=DCount("*","sicknessabsences","employeename='" & [sickname] & "' And
paid=Forms]![frm x main]![month name] And > absencetype='s' ")
... but this still returns #Name?

My original DCount expression is:
=DCount("[sicknessabsences]![absenceID]","[sicknessabsences]","[employeename
] = [forms]![frmsicknessabsence]![sickname] and [paid]=[forms]![frm x
main]![month name] and [absencetype] = 's'")
... and this does return the expected value - but only displays it after the
textbox has been manually selected!!!

I am not changing [month name] while frmSicknessAbsence is open.

When you post a query, expression or VBA code, please use
Copy/Paste from your program instead of retyping it. This
way we won't have to waste time going back and forth
discussing typing errors.

After trying to unravel the newsreader line wrapping, both
of your expressions appear to have typos with extra and
missing characters. The one I suggested appears to have
several syntax errors that could result in #Name or several
other errors.

 

[Leslie Isaacs]

Hello Marshall

Thanks for still watching this!
I am a little mystified though, as I always do copy/paste expressions etc.
to/from the newsgroup postings.
Certainly with regard to the DCount expressions this was the case.
What made you think it wasn't?

Thanks again
Les

I have now tried Me.sspdays.Requery, but unfortunately this didn't work.
employeename is a Text field as you assumed, so I now have the DCount
expression as
=DCount("*","sicknessabsences","employeename='" & [sickname] & "' And
paid=Forms]![frm x main]![month name] And > absencetype='s' ")
... but this still returns #Name?

My original DCount expression is:
=DCount("[sicknessabsences]![absenceID]","[sicknessabsences]","[employeename
] = [forms]![frmsicknessabsence]![sickname] and [paid]=[forms]![frm x
main]![month name] and [absencetype] = 's'")
... and this does return the expected value - but only displays it after
the
textbox has been manually selected!!!

I am not changing [month name] while frmSicknessAbsence is open.

When you post a query, expression or VBA code, please use
Copy/Paste from your program instead of retyping it. This
way we won't have to waste time going back and forth
discussing typing errors.

After trying to unravel the newsreader line wrapping, both
of your expressions appear to have typos with extra and
missing characters. The one I suggested appears to have
several syntax errors that could result in #Name or several
other errors.

 

[Marshall Barton]

=DCount("*","sicknessabsences","employeename='" &
[sickname] & "' And paid=Forms]![frm x main]![month name]
And > absencetype='s' ")

I would expect multiple errors from that expression. Right
off the top, I see an extra > and a missing [

If that was copied and pasted from the text box's control
source, it may be the cause of what you are seeing, but I
would have expected something other than #Name

Your original expression may be the victim of line wrapping,
but there appears to be an extra space after the field name
employeename.
--
Marsh
MVP [MS Access]

Thanks for still watching this!
I am a little mystified though, as I always do copy/paste expressions etc.
to/from the newsgroup postings.
Certainly with regard to the DCount expressions this was the case.
What made you think it wasn't?

I have now tried Me.sspdays.Requery, but unfortunately this didn't work.
employeename is a Text field as you assumed, so I now have the DCount
expression as
=DCount("*","sicknessabsences","employeename='" & [sickname] & "' And
paid=Forms]![frm x main]![month name] And > absencetype='s' ")
... but this still returns #Name?

My original DCount expression is:
=DCount("[sicknessabsences]![absenceID]","[sicknessabsences]","[employeename
] = [forms]![frmsicknessabsence]![sickname] and [paid]=[forms]![frm x
main]![month name] and [absencetype] = 's'")
... and this does return the expected value - but only displays it after
the
textbox has been manually selected!!!

I am not changing [month name] while frmSicknessAbsence is open.

When you post a query, expression or VBA code, please use
Copy/Paste from your program instead of retyping it. This
way we won't have to waste time going back and forth
discussing typing errors.

After trying to unravel the newsreader line wrapping, both
of your expressions appear to have typos with extra and
missing characters. The one I suggested appears to have
several syntax errors that could result in #Name or several
other errors.

 

[Leslie Isaacs]

Marshall

I'm sure the > is a line wrapping issue. Not sure about the missing [ of
the extra space: I will check when I'm back in the office tomorrow.

Many thanks
Les

=DCount("*","sicknessabsences","employeename='" &
[sickname] & "' And paid=Forms]![frm x main]![month name]
And > absencetype='s' ")

I would expect multiple errors from that expression. Right
off the top, I see an extra > and a missing [

If that was copied and pasted from the text box's control
source, it may be the cause of what you are seeing, but I
would have expected something other than #Name

Your original expression may be the victim of line wrapping,
but there appears to be an extra space after the field name
employeename.
--
Marsh
MVP [MS Access]

Thanks for still watching this!
I am a little mystified though, as I always do copy/paste expressions etc.
to/from the newsgroup postings.
Certainly with regard to the DCount expressions this was the case.
What made you think it wasn't?

PayeDoc wrote:
I have now tried Me.sspdays.Requery, but unfortunately this didn't work.
employeename is a Text field as you assumed, so I now have the DCount
expression as
=DCount("*","sicknessabsences","employeename='" & [sickname] & "' And
paid=Forms]![frm x main]![month name] And > absencetype='s' ")
... but this still returns #Name?

My original DCount expression is:
=DCount("[sicknessabsences]![absenceID]","[sicknessabsences]","[employeename
] = [forms]![frmsicknessabsence]![sickname] and [paid]=[forms]![frm x
main]![month name] and [absencetype] = 's'")
... and this does return the expected value - but only displays it after
the
textbox has been manually selected!!!

I am not changing [month name] while frmSicknessAbsence is open.


When you post a query, expression or VBA code, please use
Copy/Paste from your program instead of retyping it. This
way we won't have to waste time going back and forth
discussing typing errors.

After trying to unravel the newsreader line wrapping, both
of your expressions appear to have typos with extra and
missing characters. The one I suggested appears to have
several syntax errors that could result in #Name or several
other errors.

 

[PayeDoc]

Hello Marshall

OK: I set the expression for the DCount to:
=DCount("*","sicknessabsences","employeename='" &
[Forms]![frmsicknessabsence]![sickname] & " And paid=[Forms]![frm x
main]![month name] And absencetype='s' ")
(this is pasted from the textbox's control source property)
.... but it now returns #Error (- but only after the textbox has been
manually selected!!)

I think the #Name? as neing returned because I had a space before the first
= - i.e. the first character of the expression was a space!

If I change the expression to
=DCount("*","sicknessabsences","employeename=
[forms]![frmsicknessabsence]![sickname] And paid=[Forms]![frm x main]![month
name] And absencetype='s' ")
.... it returns the expected value - but again only after the textbox has
been manually selected!

I guess therefore I need to keep this expression as the one that works: but
I still have the original problem of this and some other textboxes not
displaying their values until they are selected.

I realise you may well be getting tired/bored of this puzzle(?), but if you
do have any other ideas about what may be going on I would be really
grateful.

Many thanks once again.
Les

Marshall

I'm sure the > is a line wrapping issue. Not sure about the missing [ of
the extra space: I will check when I'm back in the office tomorrow.

Many thanks
Les

=DCount("*","sicknessabsences","employeename='" &
[sickname] & "' And paid=Forms]![frm x main]![month name]
And > absencetype='s' ")

I would expect multiple errors from that expression. Right
off the top, I see an extra > and a missing [

If that was copied and pasted from the text box's control
source, it may be the cause of what you are seeing, but I
would have expected something other than #Name

Your original expression may be the victim of line wrapping,
but there appears to be an extra space after the field name
employeename.
--
Marsh
MVP [MS Access]

Thanks for still watching this!
I am a little mystified though, as I always do copy/paste expressions etc.
to/from the newsgroup postings.
Certainly with regard to the DCount expressions this was the case.
What made you think it wasn't?


PayeDoc wrote:
I have now tried Me.sspdays.Requery, but unfortunately this didn't work.
employeename is a Text field as you assumed, so I now have the DCount
expression as
=DCount("*","sicknessabsences","employeename='" & [sickname] & "' And
paid=Forms]![frm x main]![month name] And > absencetype='s' ")
... but this still returns #Name?

My original DCount expression is:
=DCount("[sicknessabsences]![absenceID]","[sicknessabsences]","[employee name
] = [forms]![frmsicknessabsence]![sickname] and [paid]=[forms]![frm x
main]![month name] and [absencetype] = 's'")
... and this does return the expected value - but only displays it after
the
textbox has been manually selected!!!

I am not changing [month name] while frmSicknessAbsence is open.


When you post a query, expression or VBA code, please use
Copy/Paste from your program instead of retyping it. This
way we won't have to waste time going back and forth
discussing typing errors.

After trying to unravel the newsreader line wrapping, both
of your expressions appear to have typos with extra and
missing characters. The one I suggested appears to have
several syntax errors that could result in #Name or several
other errors.

 

[Marshall Barton]

OK: I set the expression for the DCount to:
=DCount("*","sicknessabsences","employeename='" &
[Forms]![frmsicknessabsence]![sickname] & " And paid=[Forms]![frm x
main]![month name] And absencetype='s' ")
(this is pasted from the textbox's control source property)
... but it now returns #Error (- but only after the textbox has been
manually selected!!)

If I change the expression to
=DCount("*","sicknessabsences","employeename=
[forms]![frmsicknessabsence]![sickname] And paid=[Forms]![frm x main]![month
name] And absencetype='s' ")
... it returns the expected value - but again only after the textbox has
been manually selected!

I guess therefore I need to keep this expression as the one that works: but
I still have the original problem of this and some other textboxes not
displaying their values until they are selected.

The way I understand this situation, the text box with this
expression is in frmsicknessabsence. If so, using :
... & sickname & ...
is just a long winded way of using:
... & [Forms]![frmsicknessabsence]![sickname] & ...

You dropped the apostrophe before the And and after
sickname, which would cause #Error

=DCount("*","sicknessabsences","employeename='" &
sickname & "' And paid=[Forms]![frm x main]![month name] And
absencetype='s' ")