How do i refresh text boxes that have dlookup in

[Benwatson]

Hello

i have a form it has a combo box that allows you to choose between shifts then i have 12 text boxes that have a dlookup. the dlookups return the number of errors in a month from a query. i need the dlookups to refresh and display the new values when they choose a different shift. I AM COMPLETELY STUCK i have tried making a button that would refresh each text box didn't work. tried making a sub form of the query and have the dlookup look for the results on the sub form so i could re-query the sub form but the dlookup wont look for records on the sub lol HELP

or if you can help me by telling me what is wrong with this DLOOKUP it just keeps returning value name#? but i have looked and looked and i swear this is correct

=DLookUp("[CountofDATE]","MonthTotals","[month] = " & "10" & " AND [Shift] = '" & ![cmbShift] & " '")




regards

 

[Minty]

You need to add a requery to the after update event of the combo box. Refresh is a different thing.

Assuming [Month] is a numeric field try this (Month is a reserved word I would change it to something else)

=DLookUp("[CountofDATE]","MonthTotals","[month] = 10 AND [Shift] = '" & Me.[cmbShift] & "'")

 

[Benwatson]

i dont know what im doing wrong but i cant seem to get it to work for some reason:/ :banghead:

 

[Benwatson]

hey i managed to fix it so i created a event after update and used this code

If Me.cmbShift = "Blue" Then
Me.txtJanuary = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 1 AND [Shift] = '" & "Blue" & "'")
Me.txtFebuary = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 2 AND [Shift] = '" & "Blue" & "'")
Me.txtMarch = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 3 AND [Shift] = '" & "Blue" & "'")
Me.txtApril = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 4 AND [Shift] = '" & "Blue" & "'")
Me.txtMay = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 5 AND [Shift] = '" & "Blue" & "'")
Me.txtJune = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 6 AND [Shift] = '" & "Blue" & "'")
Me.txtJuly = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 7 AND [Shift] = '" & "Blue" & "'")
Me.txtAugust = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 8 AND [Shift] = '" & "Blue" & "'")
Me.txtSeptember = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 9 AND [Shift] = '" & "Blue" & "'")
Me.txtOctober = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 10 AND [Shift] = '" & "Blue" & "'")
Me.txtNovember = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 11 AND [Shift] = '" & "Blue" & "'")
Me.txtDecember = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 12 AND [Shift] = '" & "Blue" & "'")
ElseIf Me.cmbShift = "Red" Then
Me.txtJanuary = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 1 AND [Shift] = '" & "Red" & "'")
Me.txtFebuary = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 2 AND [Shift] = '" & "Red" & "'")
Me.txtMarch = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 3 AND [Shift] = '" & "Red" & "'")
Me.txtApril = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 4 AND [Shift] = '" & "Red" & "'")
Me.txtMay = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 5 AND [Shift] = '" & "Red" & "'")
Me.txtJune = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 6 AND [Shift] = '" & "Red" & "'")
Me.txtJuly = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 7 AND [Shift] = '" & "Red" & "'")
Me.txtAugust = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 8 AND [Shift] = '" & "Red" & "'")
Me.txtSeptember = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 9 AND [Shift] = '" & "Red" & "'")
Me.txtOctober = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 10 AND [Shift] = '" & "Red" & "'")
Me.txtNovember = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 11 AND [Shift] = '" & "Red" & "'")
Me.txtDecember = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 12 AND [Shift] = '" & "Red" & "'")
ElseIf Me.cmbShift = "Green" Then
Me.txtJanuary = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 1 AND [Shift] = '" & "Green" & "'")
Me.txtFebuary = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 2 AND [Shift] = '" & "Green" & "'")
Me.txtMarch = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 3 AND [Shift] = '" & "Green" & "'")
Me.txtApril = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 4 AND [Shift] = '" & "Green" & "'")
Me.txtMay = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 5 AND [Shift] = '" & "Green" & "'")
Me.txtJune = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 6 AND [Shift] = '" & "Green" & "'")
Me.txtJuly = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 7 AND [Shift] = '" & "Green" & "'")
Me.txtAugust = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 8 AND [Shift] = '" & "Green" & "'")
Me.txtSeptember = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 9 AND [Shift] = '" & "Green" & "'")
Me.txtOctober = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 10 AND [Shift] = '" & "Green" & "'")
Me.txtNovember = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 11 AND [Shift] = '" & "Green" & "'")
Me.txtDecember = DLookup("[CountofDATE]", "Monthtotals", "[monthno] = 12 AND [Shift] = '" & "Green" & "'")
End If

thanks for your help

 

[Galaxiom]

That is horrid.

Instead of the Dlookups and separate textboxes, get all the information in a single aggregate query (Group By Shift and MonthNo). Use it as a subform RecordSource with all those textboxes on the subform bound to the RecordSource.