[dba-VB] Command Objects within Data Environments

Arthur Fuller artful at rogers.com
Fri Jun 20 06:22:05 CDT 2003

I like to handle this type of task using static functions, rather than
rebuilding the SQL. Write a static function called CurrentEmployee() or
somesuch, then write the SQL something like:

SELECT someColumns FROM tblEmps WHERE EmployeeID = CurrentEmployee()

Then all you need to do is assign a new value to the static function and

Here's the template I use when creating static functions. Just follow the

Static Function CurrentX(Optional lngNew As Long) As Long
'Copy this template each time you need a new set/get function
'Then Replace "X" with the name of your object, i.e.,"Employee"
'Replace all in current proc and you're done.

    Dim lngCurrent As Long
    Select Case lngNew
        Case Is < 0
            lngCurrent = 0
        Case Is > 0
            lngCurrent = lngNew
        Case Else
            'just return the current value
    End Select
    CurrentX = lngCurrent
    #If conDebug = 1 Then
        Debug.Print "Current X: ", CurrentX
    #End If
End Function

To assign a value, call it like this:


To obtain the current value elsewhere, write:

? CurrentX()


-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of
paul.hartland at fsmail.net
Sent: June 16, 2003 11:06 AM
To: dba-vb at databaseadvisors.com
Subject: [dba-VB] Command Objects within Data Environments

To all,

I have a DataEnvironment (DEEmploy), which has a command tblEmps which uses
a SQL Statement to get all the records from tblEmps like so :


Now I have reached a point where I only want to bring one employee back from
the back-end, so what I need to know is can I build a SQL statement on the
fly (something like (SELECT TOP 1 * FROM tblEmps) or (SELECT * FROM tblEmps
WHERE (Criteria)) ) and assign that select statement to the Command object
tblEmps so that it only returns employees that meet the criteria thats been
built on the fly....

Thanks in advance for any help


Join Freeserve http://www.freeserve.com/time/

Winner of the 2003 Internet Service Providers' Association awards for Best
Unmetered ISP and Best Consumer Application.

dba-VB mailing list
dba-VB at databaseadvisors.com

More information about the dba-VB mailing list