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.N umber, 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
8  1736 
On Thu, 2 Sep 2004 16:20:49 +1200, "WindAndWav es" <ac****@ngaru.c om>
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.N umber, 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
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******@bigpon d.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(Crite ria) 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.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
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******@bigpon d.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(Crite ria) 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.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
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******@bigpon d.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("Client ID", "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(Crite ria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(Order Clause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
End If
Resume Exit_ELookup
End Function
"WindAndWav es" <ac****@ngaru.c om> wrote in message
news:Md******** ************@ne ws.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.N umber, 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
Brilliant
Thanks a million!
"Phil Stanton" <di********@sta ntonfamily.co.u k> wrote in message
news:41******** *************** @mercury.nildra m.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******@bigpon d.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(Crite ria) 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.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
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******@bigpon d.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(Crite ria) 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.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
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******@bigpon d.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("Client ID", "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(Crite ria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(Order Clause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
End If
Resume Exit_ELookup
End Function
"WindAndWav es" <ac****@ngaru.c om> wrote in message
news:Md******** ************@ne ws.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.N umber, 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
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.c om... On Thu, 2 Sep 2004 16:20:49 +1200, "WindAndWav es" <ac****@ngaru.c om>
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.N umber, 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
is there also one for ecount or should I work that out myself?!
"Phil Stanton" <di********@sta ntonfamily.co.u k> wrote in message
news:41******** *************** @mercury.nildra m.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******@bigpon d.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(Crite ria) 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.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
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******@bigpon d.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(Crite ria) 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.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
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******@bigpon d.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("Client ID", "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(Crite ria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(Order Clause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
End If
Resume Exit_ELookup
End Function
"WindAndWav es" <ac****@ngaru.c om> wrote in message
news:Md******** ************@ne ws.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.N umber, 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
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(Crite ria) And Not Criteria = "" Then
SqlS = SqlS & " WHERE " & Criteria
End If
SqlS = SqlS & ";"
'---'Lookup the value.
Set Dbs = DBEngine(0)(0)
Set RST = Dbs.OpenRecords et(SqlS, dbOpenForwardOn ly)
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********@sta ntonfamily.co.u k> wrote in message
news:41******** *************** @mercury.nildra m.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******@bigpon d.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(Crite ria) 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.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
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******@bigpon d.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(Crite ria) 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.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
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******@bigpon d.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("Client ID", "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(Crite ria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(Order Clause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
End If
Resume Exit_ELookup
End Function
"WindAndWav es" <ac****@ngaru.c om> wrote in message
news:Md******** ************@ne ws.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.N umber, 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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Darren Smith |
last post by:
I am having a great deal of difficulty accessing individual fields
generated from a Sql Server 7 view.
When I specify the actual field name, I get the error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
The column prefix
'inventory_hardware' does not match with a table name or alias name
used in the query.
|
by: Jennifer Smith |
last post by:
I want to be able to display my recordset as follows:
a e
b f
c g
d h
Instead of :
a b
c d
|
by: Dominic Tocci |
last post by:
When I submit a form to an asp page, the request.form is not getting the
data. This only happens on my local copy of IIS, and not on my web host.
It's a simple request.form, so I know it's not a coding issue.
The pages all load fine and access the MSAccess database perfectly. However,
when I submit a form
(even the login form) to another asp page, the data is not received by the
target page.
Why would this happen locally and not on...
|
by: Chris Styles |
last post by:
Dear All,
I've been using some code to verify form data quite happily, but i've
recently changed the way my form is structured, and I can't get it to work
now.
Originally :
The form is called "form1", and I have selects called "PORTA", "PORTB" ...
etc...
|
by: prodirect |
last post by:
Hi all,
I hope someone can help me. I've recently created a database and wanted to
put it up on an ftp sight so that multiple people could access the same
tables at the same time from different geographical locations. I have been
completely unsucessful in acheiving this goal so far however.
Things I have tried:
Create a shortcut to ftp sight via browser then tried to map local drive to
| | |
by: Eugen Walcher |
last post by:
Hello all, I've tried posting this same question on other newsgroups
with no luck. This group seems to have a lot more activity so I
apologize if you have seen it before.
I'm trying to create a web service in c# using paradox data. The data
is stored locally on my development machine with IIS also installed.
Web services are working to the point where I can return strings and
ints using Access2000 data but whenever I try and...
|
by: CrazyAtlantaGuy |
last post by:
I'm having a strange problem and I was hoping someone could give me
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server are on the
same network.
This code used to work, and started acting strangely after I moved it
to a new webserver and SQL server (from testing
environment->production). Previously the web server and sql database
were running on the same...
|
by: Jimmy Reds |
last post by:
Hi,
I have a blood glucose meter (a Lifescan OneTouch Ultra in case anyone
was wondering) which I connect to my PC using a USB cable and I would
like to have a go at accessing the data on this device without having
to use the software provided by Lifescan. The software is free and is
okay but it's not very good at manipulating the data.
Although I connect my meter using a USB cable, it can be connected by a
serial cable (there is a...
|
by: djhulme |
last post by:
Hi,
I'm using GCC. Please could you tell me, what is the maximum number of
array elements that I can create in C, i.e.
char* anArray = (char*) calloc( ??MAX?? , sizeof(char) ) ;
I've managed to create arrays using DOUBLE data types, but when I try
to access the array, the compiler complains that the number is not an
INT, i.e.
|
by: hugh welford |
last post by:
Hi
Have just installed IIS7 on Vista and am trying to access a .mdb file
through ASP. Getting server error.
I think the problem is in the file permission. Under XP Pro/IIS6 is used to
have to set the .mdb file security via windows explorer to give
IUSR_machinename full permissions on this file for anonymous web access.
Cant seem to do this with Vista explorer - says object cannot be found.
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
