By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,807 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,807 IT Pros & Developers. It's quick & easy.

accessing data quicker

P: n/a
Hi Gurus

I have a query that contains reference to the following function:

Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function

THe WHOM() function is a function that reads the a value in the main form
(the person who is logged in).

Is there anyway (I am sure there is), that the userstatus function can be
faster?

The userstatus is determined by a button that the user presses in the main
menu and that is then stored in the M-WOR table.

TIA

- Nicolaas
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
On Thu, 2 Sep 2004 16:20:49 +1200, "WindAndWaves" <ac****@ngaru.com>
wrote:

Replace DLookup by an equivalent select query.
-Tom.

Hi Gurus

I have a query that contains reference to the following function:

Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function

THe WHOM() function is a function that reads the a value in the main form
(the person who is logged in).

Is there anyway (I am sure there is), that the userstatus function can be
faster?

The userstatus is determined by a button that the user presses in the main
menu and that is then stored in the M-WOR table.

TIA

- Nicolaas


Nov 13 '05 #2

P: n/a
Here are some useful functions from Allen Browne.

I always use ELookup(........) etc Much faster and does not load the
database so much

HTH

Phil

Function EMin(expr As String, domain As String, Optional Criteria)

On Error GoTo Err_EMin
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S

Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMin = Null
Else
EMin = rs(0)
End If
rs.Close

Exit_EMin:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_EMin:
' MsgBox Err.Description, vbExclamation, "EMin Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMin = CVErr(5) 'Out of range.
Else
EMin = CVErr(Err.Number)
End If
Resume Exit_EMin

End Function

Function EMax(expr As String, domain As String, Optional Criteria)

On Error GoTo Err_EMax
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S

Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr & " DESC"
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMax = Null
Else
EMax = rs(0)
End If
rs.Close

Exit_EMax:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_EMax:
' MsgBox Err.Description, vbExclamation, "EMax Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMax = CVErr(5) 'Out of range.
Else
EMax = CVErr(Err.Number)
End If
Resume Exit_EMax

End Function

Public Function ELookup(expr As String, domain As String, Optional Criteria,
Optional OrderClause)

On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup

End Function
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:Md********************@news.xtra.co.nz...
Hi Gurus

I have a query that contains reference to the following function:

Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function

THe WHOM() function is a function that reads the a value in the main form
(the person who is logged in).

Is there anyway (I am sure there is), that the userstatus function can be
faster?

The userstatus is determined by a button that the user presses in the main
menu and that is then stored in the M-WOR table.

TIA

- Nicolaas

Nov 13 '05 #3

P: n/a
Brilliant

Thanks a million!

"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message
news:41***********************@mercury.nildram.net ...
Here are some useful functions from Allen Browne.

I always use ELookup(........) etc Much faster and does not load the
database so much

HTH

Phil

Function EMin(expr As String, domain As String, Optional Criteria)

On Error GoTo Err_EMin
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S

Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMin = Null
Else
EMin = rs(0)
End If
rs.Close

Exit_EMin:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_EMin:
' MsgBox Err.Description, vbExclamation, "EMin Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMin = CVErr(5) 'Out of range.
Else
EMin = CVErr(Err.Number)
End If
Resume Exit_EMin

End Function

Function EMax(expr As String, domain As String, Optional Criteria)

On Error GoTo Err_EMax
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S

Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr & " DESC"
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMax = Null
Else
EMax = rs(0)
End If
rs.Close

Exit_EMax:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_EMax:
' MsgBox Err.Description, vbExclamation, "EMax Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMax = CVErr(5) 'Out of range.
Else
EMax = CVErr(Err.Number)
End If
Resume Exit_EMax

End Function

Public Function ELookup(expr As String, domain As String, Optional Criteria, Optional OrderClause)

On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup

End Function
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:Md********************@news.xtra.co.nz...
Hi Gurus

I have a query that contains reference to the following function:

Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function

THe WHOM() function is a function that reads the a value in the main form (the person who is logged in).

Is there anyway (I am sure there is), that the userstatus function can be faster?

The userstatus is determined by a button that the user presses in the main menu and that is then stored in the M-WOR table.

TIA

- Nicolaas


Nov 13 '05 #4

P: n/a
Hoi Tom

Basically, Phil in the other answer is recommending the same thing, using
the Elookups. That seems brilliant to me.

Thank you for your reply.

Nicolaas

"Tom van Stiphout" <no*************@cox.net> wrote in message
news:dn********************************@4ax.com...
On Thu, 2 Sep 2004 16:20:49 +1200, "WindAndWaves" <ac****@ngaru.com>
wrote:

Replace DLookup by an equivalent select query.
-Tom.

Hi Gurus

I have a query that contains reference to the following function:

Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function

THe WHOM() function is a function that reads the a value in the main form
(the person who is logged in).

Is there anyway (I am sure there is), that the userstatus function can be
faster?

The userstatus is determined by a button that the user presses in the mainmenu and that is then stored in the M-WOR table.

TIA

- Nicolaas

Nov 13 '05 #5

P: n/a
is there also one for ecount or should I work that out myself?!
"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message
news:41***********************@mercury.nildram.net ...
Here are some useful functions from Allen Browne.

I always use ELookup(........) etc Much faster and does not load the
database so much

HTH

Phil

Function EMin(expr As String, domain As String, Optional Criteria)

On Error GoTo Err_EMin
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S

Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMin = Null
Else
EMin = rs(0)
End If
rs.Close

Exit_EMin:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_EMin:
' MsgBox Err.Description, vbExclamation, "EMin Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMin = CVErr(5) 'Out of range.
Else
EMin = CVErr(Err.Number)
End If
Resume Exit_EMin

End Function

Function EMax(expr As String, domain As String, Optional Criteria)

On Error GoTo Err_EMax
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S

Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr & " DESC"
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMax = Null
Else
EMax = rs(0)
End If
rs.Close

Exit_EMax:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_EMax:
' MsgBox Err.Description, vbExclamation, "EMax Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMax = CVErr(5) 'Out of range.
Else
EMax = CVErr(Err.Number)
End If
Resume Exit_EMax

End Function

Public Function ELookup(expr As String, domain As String, Optional Criteria, Optional OrderClause)

On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup

End Function
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:Md********************@news.xtra.co.nz...
Hi Gurus

I have a query that contains reference to the following function:

Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function

THe WHOM() function is a function that reads the a value in the main form (the person who is logged in).

Is there anyway (I am sure there is), that the userstatus function can be faster?

The userstatus is determined by a button that the user presses in the main menu and that is then stored in the M-WOR table.

TIA

- Nicolaas


Nov 13 '05 #6

P: n/a
Anyway, I came up with :

Public Function Ecount(expr As String, domain As String, Optional Criteria)
'replacement for dCount
Const ProEro = 3: 'On Error GoTo err
Dim Dbs As Database
Dim RST As Recordset
Dim SqlS As String
'--- Build the SQL string.
SqlS = "SELECT count(" & expr & ") as C FROM " & domain
If Not IsMissing(Criteria) And Not Criteria = "" Then
SqlS = SqlS & " WHERE " & Criteria
End If
SqlS = SqlS & ";"
'---'Lookup the value.
Set Dbs = DBEngine(0)(0)
Set RST = Dbs.OpenRecordset(SqlS, dbOpenForwardOnly)
Ecount = RST.Fields("C")
RST.close
xit:
Set RST = Nothing
Set Dbs = Nothing
Exit Function
ERR:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
Ecount = 0
Resume xit
End Function

"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message
news:41***********************@mercury.nildram.net ...
Here are some useful functions from Allen Browne.

I always use ELookup(........) etc Much faster and does not load the
database so much

HTH

Phil

Function EMin(expr As String, domain As String, Optional Criteria)

On Error GoTo Err_EMin
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S

Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMin = Null
Else
EMin = rs(0)
End If
rs.Close

Exit_EMin:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_EMin:
' MsgBox Err.Description, vbExclamation, "EMin Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMin = CVErr(5) 'Out of range.
Else
EMin = CVErr(Err.Number)
End If
Resume Exit_EMin

End Function

Function EMax(expr As String, domain As String, Optional Criteria)

On Error GoTo Err_EMax
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S

Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr & " DESC"
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMax = Null
Else
EMax = rs(0)
End If
rs.Close

Exit_EMax:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_EMax:
' MsgBox Err.Description, vbExclamation, "EMax Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMax = CVErr(5) 'Out of range.
Else
EMax = CVErr(Err.Number)
End If
Resume Exit_EMax

End Function

Public Function ELookup(expr As String, domain As String, Optional Criteria, Optional OrderClause)

On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup

End Function
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:Md********************@news.xtra.co.nz...
Hi Gurus

I have a query that contains reference to the following function:

Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function

THe WHOM() function is a function that reads the a value in the main form (the person who is logged in).

Is there anyway (I am sure there is), that the userstatus function can be faster?

The userstatus is determined by a button that the user presses in the main menu and that is then stored in the M-WOR table.

TIA

- Nicolaas


Nov 13 '05 #7

P: n/a
"WindAndWaves" <ac****@ngaru.com> wrote in
news:ce********************@news.xtra.co.nz:
Basically, Phil in the other answer is recommending the same
thing, using the Elookups. That seems brilliant to me.


Trevor Best wrote his tLookup functions a long time ago (the code I
have is dated 1996). He recently updated them, and they can be found
here:

http://www.mvps.org/access/modules/mdl0012.htm

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #8

P: n/a
It looks like you have things well in hand, but if not, try Trevor
Best's replacement functions. Located at
http://easyweb.easynet.co.uk/~trevor/AccFAQ/. Look under downloads,
then domain aggregate function replacements.

Tom
Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.