If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Refresh unbound textboxes on form
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] Leslie Isaacs wrote: 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?. |
#22
|
|||
|
|||
Refresh unbound textboxes on form
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 "Marshall Barton" wrote in message ... 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] Leslie Isaacs wrote: 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?. |
#23
|
|||
|
|||
Refresh unbound textboxes on form
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. -- Marsh MVP [MS Access] |
#24
|
|||
|
|||
Refresh unbound textboxes on form
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 "Marshall Barton" wrote in message ... 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. -- Marsh MVP [MS Access] |
#25
|
|||
|
|||
Refresh unbound textboxes on form
=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] Leslie Isaacs wrote: 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? "Marshall Barton" wrote 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. |
#26
|
|||
|
|||
Refresh unbound textboxes on form
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 "Marshall Barton" wrote in message news =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] Leslie Isaacs wrote: 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? "Marshall Barton" wrote 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. |
#27
|
|||
|
|||
Refresh unbound textboxes on form
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 "Leslie Isaacs" wrote in message ... 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 "Marshall Barton" wrote in message news =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] Leslie Isaacs wrote: 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? "Marshall Barton" wrote 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. |
#28
|
|||
|
|||
Refresh unbound textboxes on form
PayeDoc wrote:
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' ") -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|