DSUM VBA Code Help (1 Viewer)

[Coldsteel]

Hello all,


I need some help with my code. I have this code on the after update in a text box called Text0. What I am trying to do is have the user type in their ID number and the code will run summing up certain fields and display them in other text boxes in on the form. The problems I am having include i keep getting the overload error and if the ID number does not exist I get another error any help will be appreciated.
Thanks



Dim Sum2Calc As Integer
Dim Sum3Calc As Integer
Dim Sum4Calc As Integer



Sum2Calc = DSum("[New $ Amt]", "tbl_referral", "[ID]= " & Forms!ProductSpecialistSearch!Text0 & " And [STATUS]='Closed - Funded' And [Time Closed] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# and [DATE REFERRED] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# ")


Sum3Calc = DSum("[New $ Amt]", "tbl_referral", "[ID]= " & Forms!ProductSpecialistSearch!Text0 & " And [STATUS]='Closed - Funded' And [Time Closed] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# and [DATE REFERRED] Not Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# ")


Sum4Calc = DSum("[PS Payout]", "tbl_referral", "[ID]= " & Forms!ProductSpecialistSearch!Text0 & " And [STATUS]='Closed - Funded' And [Time Closed] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# and [DATE REFERRED] Not Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# ")



Text92 = Sum2Calc
Text94 = Sum3Calc
Text96 = Sum4Calc


End Sub

 

[KenHigg]

This is a bunch going on in domain function. How many records are you trying to go through in tblReferral? I would suggest building a query to do the filter then maybe dsum the query recordset - ?

 

[Coldsteel]

Its about 8000 records. Do I build the query in VBA and then have the Dlookup from that?

 

[KenHigg]

Hum... That's what I was thinking. Not really better except maybe it'd make it a little faster and at least for me, a little easier to put together.

 

[Coldsteel]

Thanks for the help

 

[KenHigg]

Thanks for the help

Glad to help - Lets us know how it turned out.