PLease help,



I have an old Access 2000 database that is being converted to an Access 

2000 project (SQL Server 7.0 interface), which is what it should have been 

to begin with...



Many of my forms have DLookup function statements.



I can not get the Access 2000 Project to recognize the DLookup statement.  

The error returned is "There was a problem accessing a property or method 

of the OLE object."



How can I get this function to work?



The current function statement is:

***************************************************************

Private Sub cboOPID_AfterUpdate()

On Error GoTo cboOPID_Err

    Dim strPW, strLevel As String

    strPW = DLookup("[password]", "tblopid", "forms![frmlogin]![cboOPID] = 

[opid]")

    strLevel = DLookup("[Security_Level]", "tblOPID", "forms![frmlogin]!

[cboOPID] = [opid]")

    txtLevel = strLevel

    txtPW2 = strPW

    

cboOPID_Exit:

    Exit Sub

    

cboOPID_Err:

    MsgBox Err.Description

    Resume cboOPID_Exit

    

End Sub

******************************************************************



Any help would be appreciated.



I can be reached at tnbrom@n...



Thanks in advance....



Tim Norton

That function is a method of the Access.Application object.  Do the other

methods work?  If you type ?Access.Application.CurrentUser in the debug

window, do you get the same error?  If so, you might look for a missing

reference, or unregistered component.



If not, could it be that the domain aggregate functions don't work against

SQL server sources?  If that's it, you could probably come up with your own

substitute that would take the same args as DLookup, build up a SQL string

for the actual lookup & execute it on the server...  That's outside my

experience tho...



HTH,



-Roy



-----Original Message-----

From: TNBROM@n... [mailto:TNBROM@n...]

Sent: Monday, March 26, 2001 5:50 PM

To: Access

Subject: [access] DLookup in Access 2000 Project





PLease help,



I have an old Access 2000 database that is being converted to an Access 

2000 project (SQL Server 7.0 interface), which is what it should have been 

to begin with...



Many of my forms have DLookup function statements.



I can not get the Access 2000 Project to recognize the DLookup statement.  

The error returned is "There was a problem accessing a property or method 

of the OLE object."



How can I get this function to work?



The current function statement is:

***************************************************************

Private Sub cboOPID_AfterUpdate()

On Error GoTo cboOPID_Err

    Dim strPW, strLevel As String

    strPW = DLookup("[password]", "tblopid", "forms![frmlogin]![cboOPID] = 

[opid]")

    strLevel = DLookup("[Security_Level]", "tblOPID", "forms![frmlogin]!

[cboOPID] = [opid]")

    txtLevel = strLevel

    txtPW2 = strPW

    

cboOPID_Exit:

    Exit Sub

    

cboOPID_Err:

    MsgBox Err.Description

    Resume cboOPID_Exit

    

End Sub

******************************************************************



Any help would be appreciated.



I can be reached at tnbrom@n...



Thanks in advance....



Tim Norton

TNBROM,



    Domain aggregate functions within Access JET are not compliant with SQL.

So there are two ways I know of (perhaps more for performing similar

functionality.



One method is to re-crate a query for each lookup, this is very easy and

works in Access as well.



this query becomes the table/row source in the combo-box properties within a

form and Access will very nicely take care of the form level details



To try it simply create the select criteria within design mode for a new

query



then put that query as the table/row source on the combo box







The other more ugly ( to build initially), but more elegant method is to use

code.



You would use VB ADO code and create a generic lookup function that passes

the SQL statement directly to the server and populates a pick list based on

the criteria, Another field on the form or  variables either calculated or

input by the user.

Then you can make this a callable routine from any form or code in you

system.



The wrox Professional Access 2000 programming manual has examples of this

second method. It's advantage over the first

one is memory and speed.  But to walk before you run try the first one I

mentioned.



I usually prefix my sql dlookup queries as qrysqldlook_domainname_source

source being the point of use for the lookup say

frmCustomers for the



This helps manage a huge list of queries



Brian



















----- Original Message -----

From: <TNBROM@n...>

To: "Access" <access@p...>

Sent: Tuesday, March 27, 2001 1:51 AM

Subject: [access] DLookup in Access 2000 Project





> PLease help,

>

> I have an old Access 2000 database that is being converted to an Access

> 2000 project (SQL Server 7.0 interface), which is what it should have been

> to begin with...

>

> Many of my forms have DLookup function statements.

>

> I can not get the Access 2000 Project to recognize the DLookup statement.

> The error returned is "There was a problem accessing a property or method

> of the OLE object."

>

> How can I get this function to work?

>

> The current function statement is:

> ***************************************************************

> Private Sub cboOPID_AfterUpdate()

> On Error GoTo cboOPID_Err

>     Dim strPW, strLevel As String

>     strPW = DLookup("[password]", "tblopid", "forms![frmlogin]![cboOPID] 

> [opid]")

>     strLevel = DLookup("[Security_Level]", "tblOPID", "forms![frmlogin]!

> [cboOPID] = [opid]")

>     txtLevel = strLevel

>     txtPW2 = strPW

>

> cboOPID_Exit:

>     Exit Sub

>

> cboOPID_Err:

>     MsgBox Err.Description

>     Resume cboOPID_Exit

>

> End Sub

> ******************************************************************

>

> Any help would be appreciated.

>

> I can be reached at tnbrom@n...

>

> Thanks in advance....

>

> Tim Norton

>

>