This message is in MIME format. Since your mail reader does not understand

this format, some or all of this message may not be legible.



------_=_NextPart_001_01C0C432.CC8BD960

Content-Type: text/plain;

	charset="iso-8859-1"



 Brian,



I wanted to thank you for the suggestion below regarding the Dlookup

function.  I had already started the crawl technique and it is up and

running fine.  However, I can't find any reference to the second method you

mentioned in the "Professional Access 2000 programming".  Could you

elaborate on it.  I have the "Professional SQL Server with Access 2000" book

by Wrox. and I can't find it in there either.



Thanks again,



Tim Norton



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

From: Brian Paniccia [mailto:bpanicci@n...]

Sent: Wednesday, March 28, 2001 11:48 AM

To: Access

Subject: [access] Re: DLookup in Access 2000 Project





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

>

>