Use DLookup in Access VBA

If you want to use the Dlookup in VBA.

Download Sample Database

Here is the code-

Sub use_dlookup()
Dim sumfield As String
Dim tblname As String
lookupid = "[rep Id]"
tblname = "sales_detail"
MsgBox DLookup(lookupid, tblname, "[Rep Name] = 'a'"), vbOKOnly
' with query
Dim SQLresult As String
Dim rs As Recordset
SQLresult = "SELECT ([sales_detail].[REP ID]) AS Filtersales FROM [sales_detail] WHERE ([sales_detail].[rep name]='a')"
Set rs = CurrentDb.OpenRecordset(SQLresult)
rs.MoveFirst
MsgBox rs(0).Value, vbInformation
End Sub