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 |
#11
|
|||
|
|||
Refresh unbound textboxes on form
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 "Klatuu" wrote in message ... 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. "Leslie Isaacs" wrote in message ... 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 "Douglas J. Steele" wrote in message ... 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 http://I.Am/DougSteele (no e-mails, please!) "Leslie Isaacs" wrote in message ... 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 |
#12
|
|||
|
|||
Refresh unbound textboxes on form
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. "PayeDoc" wrote in message ... 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 "Marshall Barton" wrote in message ... 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. |
#13
|
|||
|
|||
Refresh unbound textboxes on form
PayeDoc wrote:
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. -- Marsh MVP [MS Access] |
#14
|
|||
|
|||
Refresh unbound textboxes on form
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 "Klatuu" wrote in message ... 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. "PayeDoc" wrote in message ... 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 "Marshall Barton" wrote in message ... 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. |
#15
|
|||
|
|||
Refresh unbound textboxes on form
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]","[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' ") -- Marsh MVP [MS Access] |
#16
|
|||
|
|||
Refresh unbound textboxes on form
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 "Marshall Barton" wrote in message ... 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]","[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' ") -- Marsh MVP [MS Access] |
#17
|
|||
|
|||
Refresh unbound textboxes on form
Leslie Isaacs wrote:
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] "Marshall Barton" wrote 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]","[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' ") |
#18
|
|||
|
|||
Refresh unbound textboxes on form
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 "Marshall Barton" wrote in message news Leslie Isaacs wrote: 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] "Marshall Barton" wrote 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' ") |
#19
|
|||
|
|||
Refresh unbound textboxes on form
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?. 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. -- Marsh MVP [MS Access] |
#20
|
|||
|
|||
Refresh unbound textboxes on form
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 "Marshall Barton" wrote in message ... 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?. 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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|