Dsum result to textbox (do I run the code on the textbox?)

[shabbaranks]

Hi all,

Im trying to output to a textbox the result of a dsum, also Im wondering if this is possible to have some sort of dynamic control so a user changes the selection in the combo box and the date ranges in the "From" and "To" dates and the result is updated based on this.

My current code is

DLookup (DSum("sHours", "TimesheetTable", "sUser = '" & Forms!SubmittedTimesheet_frm!AdminSelect_Combo.Value _
& " AND [Task Date] between '" & Forms!SubmittedTimesheet_frm!FromDateAdmin_TXTBox & "'" _
& " AND '" & Forms!SubmittedTimesheet_frm!ToDateAdmin_TXTBox & "'"))

But Im not sure firstly how to output this to a textbox on the same form which is called TotalHoursAdmin_TXTBox

Or how to make the textbox update dynamically if the combo and date ranges change on this form?

Help is appreciated as always

 

[pr2-eugin]

Hello shabbaranks, If you need to assign the result to a Text box just do something along the lines of..

Me.theTextBoxControlName = "This will be on the Text box, literally"

Where theTextBoxControlName is the name of the control you are trying to assign the value to.. But looking at your code.. Either I am missing something vital or you have used DLookUp when it is not required at all.. I think the latter is right..

Any domain function DCount, DSum, DLookUp etc. Would take in three Arguments.. Two mandatory and one optional..The only function required is DSum.. So the code should be..

Me.TotalHoursAdmin_TXTBox = DSum("sHours", "TimesheetTable", "sUser = '" & [COLOR=Blue]Me.[/COLOR]AdminSelect_Combo _
& "[COLOR=Blue][B]' [/B][/COLOR]AND [Task Date] BETWEEN [COLOR=Red][B]#[/B][/COLOR]" &[COLOR=Blue] Format(Me.[/COLOR]FromDateAdmin_TXTBox[COLOR=Blue], "mm/dd/yyyy")[/COLOR] & "[COLOR=Red][B]#[/B][/COLOR]" _
& " AND [COLOR=Red][B]#[/B][/COLOR]" & [COLOR=Blue]Format(Me.[/COLOR]ToDateAdmin_TXTBox[COLOR=Blue], "mm/dd/yyyy")[/COLOR] & "[COLOR=Red][B]#[/B][/COLOR]")

I have added Format function to make sure the proper dates are picked.. Also you passed Dates as String, which is not right..

If you wish to change the values dynamically put the same code in the After update events of FromDateAdmin_TXTBox and ToDateAdmin_TXTBox

Hope this helps..

 

[shabbaranks]

Worked like a charm, thank you