How to use dlookup in Vb

vargas_dieg
vargas_dieg used Ask the Experts™
on
Hi.

I have been trying to use the dlookup function in VB. I added MS Access 10 in the references already...

I connected VB to an ODBC database... I started by creating a data report in VB. It gives me the Dataenvironment and it works fine. I can see the data and everything seems OK with the database connection...

My problem is that I am trying to use the dlookup function and i am getting a run-time reserved error '2950'

Why am I using dlookup?

Because I want to find out the telephone of a customer from a table of the ODBC database, and assign it the number to a variable in my code...Something like this...

number = dlookup("cust_phone", ODBCtable, "where cust_id=504")

I guess the problem is that it is not reaching the ODBC table... I have typed dataenvironment1.customer, dataenvironment1.rscustomer, dataenvironment1.recordsetcustomer, etc...

I learned here that I could use the dlookup function and it has been excellent for my Access applications, but it doesn't work with VB.

What can I use to get the phone number of the customer into a variable? Is there any other function I could use...

Thank you very much,

Diego Vargas
(A novice VB user)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try dlookup("cust_phone", "ODBCtableName", "cust_id=504")

Cheers, Andrew

Author

Commented:
Thanks, but it doesn't work... Maybe I did not explain myself good... I have tried the dlookup but it doesn't work out at all... Do you know of any other function that I can use to get to an ODBC database table?

I have been able to see the recordset, but I am not knowledgeable to make that *&*(#*^& recordset thing get me data from a table... What else can I use, instead of dlookup?

Thank you very much

Diego Vargas
Commented:
Domain aggregate functions such as Dlookup, Dfirst etc. are native to access and other office applications - does not work in VB.

easy to create functions like them though...  (note: need a reference to DAO 3.x)

Public Function DLookup(ByVal sQuery As String, DBName as string) As
Variant
  On Error Resume Next

Dim db as Database  
Dim rs As Recordset

Set db = opendatabase(DBName)

  DLookup = Null

  Set rs = db.OpenRecordset(sQuery, dbOpenSnapshot, dbForwardOnly)
  If Err = 0 Then
    If Not rs.EOF Then
      DLookup = rs(0)
    End If
    rs.Close
  End If
  set db=nothing

End Function
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks Funke. I don't understand some of the stuff you wrote but i am trying to make some sense out of it... Sorry I am not a skillfull programmer...

In your code you've got a variable Err...
(If Err =0)

What is it doing?

Thank you very much,

Diego Vargas

Author

Commented:
I have tried to make the thing work but I haven't been able. I am trying to get to an ODBC database (Informix)

I am having problems with the Opendatabase(Dbname)... I think I am getting the name, but when I am debugging and place the mouse over 'set db' it says "db = nothing" So my guess is that I am unable to provide the right name of the database...

To get the name I've tried:
- dataenvironment.connection1.defaultdatabase
- currentdb.name
- and the actual name of the database... "production"

But I can't get nothing... I guess if 'db = nothing', then i can't open a recordset...

What can i do?

Thanks.

Author

Commented:
I got it!

Well, there might be a more efficient way to get it but it is working now. I got it from my ASP book, the section about recordsets.. Thanks...

Dim objConn As ADODB.Connection, objRS As ADODB.Recordset

RS_Source = "select cus_phone from customers"
RS_field = "cus_phone"

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset
objConn.Open DataEnvironment1.connection1
objRS.Source = RS_source
objRS.Open , objConn

Do While Not objRS.EOF
    find_item = Trim(objRS(RS_field))
    objRS.MoveNext
Loop


objRS.Close
Set objConn = Nothing

Author

Commented:
Thanks Funke, you gave me the answer... I had to redo the recordset code to make it work, but you definitely pointed me to the right direction.

Thanks

Diego Vargas

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial