Having problems with Dlookup (1 Viewer)

I am working a school project which is a simple split database with form that performs many calculations. Currently, I am using Dlookups but it is very slow in processing, is there a quicker way to perform these calculations or a better approach at stream line my code.

Here is my code:

Private Sub Text114_AfterUpdate()
Dim SumCalc1 As Long
Dim SumCalc2 As Long
Dim SumCalc3 As Long
Dim SumCalc4 As Long
Dim CountProd1 As Long
Dim CountProd2 As Long
Dim CountProd3 As Long
Dim CountProd4 As Long
Dim CounterOffer As Long
Dim PrevCounterOffer As Long
Dim CountProd6 As Long
Dim CountApproved As Long
Dim Payoutsum As Long
Dim RatioCalc As Single
Dim RatioCalc1 As Integer


If Me.Text114 <> DLookup("[Prod spec name]", "[tbl prod specialist]", "[prod initials] = Forms!PSstats.Text114") Then

'Current Month Calc
CurrentMonthFunded = Nz(DCount("[APPROVED/DENIED]", "tbl_referral", "[Prod Specialist #]=" & [Forms]![PSstats]![Text114] & " And [STATUS]='Closed - Funded' And [Time Closed] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# "), 0)

PrevMonthFunded = Nz(DCount("[Product 1 Referred]", "tbl_referral", "[Prod Specialist #]=" & Forms!PSstats!Text114 & " And [STATUS]='Closed - Funded'And [APPROVED/DENIED]= 'Approved' 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 & "# "), 0)

CountProd3 = Nz(DCount("[Product 1 Referred]", "tbl_referral", "[Prod Specialist #]= " & Forms!PSstats!Text114 & " And [STATUS]='Closed - Funded' And [Product 1 Referred] like '*CHECKING*' And [Time Closed] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# and [DATE REFERRED] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# "), 0)

CountProd4 = Nz(DCount("[Product 1 Referred]", "tbl_referral", "[Prod Specialist #]= " & Forms!PSstats!Text114 & " And [STATUS]='Closed - Funded' And [Product 1 Referred] like '*Direct Deposit*' And [Time Closed] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# and [DATE REFERRED] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# "), 0)

CounterOffer = Nz(DCount("[APPROVED/DENIED]", "tbl_referral", "[Prod Specialist #]= " & Forms!PSstats!Text114 & " And [STATUS]='Closed - Funded' And [APPROVED/DENIED]= 'Counter Offer' And [Time Closed] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# "), 0)

CountProd6 = Nz(DCount("[Product 1 Referred]", "tbl_referral", "[Prod Specialist #]= " & Forms!PSstats!Text114 & " And [STATUS]='Closed - Funded' And [Product 1 Referred] like '*PLAY*' And [Time Closed] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# and [DATE REFERRED] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# "), 0)

CountApproved = Nz(DCount("[APPROVED/DENIED]", "tbl_referral", "[Prod Specialist #]=" & [Forms]![PSstats]![Text114] & " And [APPROVED/DENIED]='Approved' And [DATE REFERRED] Between #" & DateSerial(Year(Date), Month(Date), 1) & "# And #" & Date & "# "), 0)

SumCalc1 = Nz(DSum("[New $ Amt]", "tbl_referral", "[Prod Specialist #]= " & Forms!PSstats!Text114 & " 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 & "# "), 0)

SumCalc4 = Nz(DSum("[PS Payout]", "tbl_referral", "[Prod Specialist #]= " & Forms!PSstats!Text114 & " 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 & "# "), 0)

'Prev Month Calc
SumCalc2 = Nz(DSum("[New $ Amt]", "tbl_referral", "[Prod Specialist #]= " & Forms!PSstats!Text114 & " 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 & "# "), 0)

SumCalc3 = Nz(DSum("[PS Payout]", "tbl_referral", "[Prod Specialist #]= " & Forms!PSstats!Text114 & " 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 & "# "), 0)

'Calc
SumTotalFunded = SumCalc1 + SumCalc2
SumPayout = SumCalc3 + SumCalc4
RatioCalc1 = (PrevMonthFunded + CountApproved + CounterOffer)
TotalClosedFunded = CurrentMonthFunded
RatioCalc = TotalClosedFunded / RatioCalc1

Else
MsgBox ("No Data Available")
Me.Text114 = ""
Me.Ratio = "0"
End If
Select Case SumTotalFunded
Case 0 To 499999
Me.Text58 = 0
Payoutsum = 0
Case 500000 To 599999
Me.Text58 = 50
Payoutsum = 50
Case 600000 To 699999
Me.Text58 = 100
Payoutsum = 100
Case 700000 To 799999
Me.Text58 = 150
Payoutsum = 150
Case 800000 To 899999
Me.Text58 = 200
Payoutsum = 200
Case 900000 To 999999
Me.Text58 = 250
Payoutsum = 250
Case Is >= 1000000
Me.Text58 = 300
Payoutsum = 300
End Select

PSPayoutSum = CLng(SumCalc1)
Text92 = CLng(SumCalc2)
Text94 = CLng(SumCalc3)
Text96 = CLng(SumCalc4)
Text81 = CLng(TotalClosedFunded)
Text103 = CLng(CountProd3)
Text105 = CLng(CountProd4)
Text108 = CLng(CounterOffer)
Text110 = CLng(CountProd6)
Text200 = CLng(PrevMonthFunded)
Text201 = CLng(RatioCalc1)
Text128 = CLng(RatioCalc1)
txtTotalFundedPS = Payoutsum + SumPayout
CurrentApprovedDisplay = CLng(CountApproved)
Ratio = RatioCalc
End Sub

Instead of the DLookups, you can make an SQL and open that in a recordset... This will be much faster than your dlookups.

Good luck !

The DLookups slow down the process of data search and very slow, instead of DLookups, using of SQL queries are much faster.
DLookups are good only for small databases or searchs

This line of code itself has too many criterias:
If Me.Text114 <> DLookup("[Prod spec name]", "[tbl prod specialist]", "[prod initials] = Forms!PSstats.Text114") Then

This could be:
set rs=CurrentDb.OpenRecordset ("SELECT * FROM [tbl prod specialist] WHERE [prod initials] = '" & Me.Text114 &"'")

[FONT=&quot]Thanks for the help, I have been working on converting my dlookups to SQL, but I am new to using record sets. Here is some of my SQL. Will you point me in the right direction in converting this to VBA and using record sets.[/FONT]
[FONT=&quot]SELECT Count(1) AS Total, tbl_referral.[Prod Specialist #][/FONT]
[FONT=&quot]FROM tbl_referral[/FONT]
[FONT=&quot]WHERE (((tbl_referral.[DATE REFERRED]) Between GetDateLower() And GetDateUpper()) AND ((tbl_referral.[APPROVED/DENIED])="Approved") AND ((tbl_referral.[Prod Specialist #])=[Forms]![PSstats]![Text114]))[/FONT]
[FONT=&quot]GROUP BY tbl_referral.[Prod Specialist #];[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]SELECT Count(*) AS Total, tbl_referral.[Prod Specialist #][/FONT]
[FONT=&quot]FROM tbl_referral[/FONT]
[FONT=&quot]WHERE (((tbl_referral.[DATE REFERRED]) Between GetDateLower() And GetDateUpper()) AND ((tbl_referral.[Time Closed]) Between GetDateLower() And GetDateUpper()) AND ((tbl_referral.[APPROVED/DENIED])="Counter Offer") AND ((tbl_referral.STATUS)="Closed - Funded") AND ((tbl_referral.[Prod Specialist #])=[Forms]![PSstats]![Text114]))[/FONT]
[FONT=&quot]GROUP BY tbl_referral.[Prod Specialist #];[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]SELECT Count(1) AS Total, tbl_referral.[Prod Specialist #][/FONT]
[FONT=&quot]FROM tbl_referral[/FONT]
[FONT=&quot]WHERE (((tbl_referral.[Time Closed]) Between GetDateLower() And GetDateUpper()) AND ((tbl_referral.STATUS)="Closed - Funded") AND ((tbl_referral.[Prod Specialist #])=[Forms]![PSstats]![Text114]))[/FONT]
[FONT=&quot]GROUP BY tbl_referral.[Prod Specialist #];[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]SELECT Count(1) AS Total, tbl_referral.[Prod Specialist #][/FONT]
[FONT=&quot]FROM tbl_referral[/FONT]
[FONT=&quot]WHERE (((tbl_referral.[DATE REFERRED]) Not Between GetDateLower() And GetDateUpper()) AND ((tbl_referral.[Time Closed]) Between GetDateLower() And GetDateUpper()) AND ((tbl_referral.[APPROVED/DENIED])="Approved") AND ((tbl_referral.STATUS)="Closed - Funded") AND ((tbl_referral.[Prod Specialist #])=[Forms]![PSstats]![Text114]))[/FONT]
[FONT=&quot]GROUP BY tbl_referral.[Prod Specialist #];[/FONT]

No No No, your still doing seperate queries on the same table which is the cause for the slowness

Notice how parts of your SQL are duplicated, in particular:
FROM tbl_referral
WHERE (((tbl_referral.[DATE REFERRED]) Between GetDateLower() And GetDateUpper())
AND ((tbl_referral.[Prod Specialist #])=[Forms]![PSstats]![Text114]))
GROUP BY tbl_referral.[Prod Specialist #];

This you want to make into one query, then use IIF function to make different counts... or sums...
I.e.
This will then run ONE query on your table returning all counts needed.

For information on opening SQL in code, do a google/search on the forum for DAO.Recordset that should get you plenty of samples.

Probably no use to this, but.... You really shouldnt use special characters like spaces # / etc in any of your field names,
Your using the _ in table names, which isnt great but much better than spaces... so you know this, why only apply it half way??