DLookup in Access and VB6 (not)

[PALman]

I have been making extensive use of DLookup in my programs running under Access. However I need to tranfer all my coding over to VB6 and when I run them, the first occurrence of DLookup causes the error... "Sub or Function not defined". Can anyone explain why this is the case? Also is there an alternative to DLookup for VB6 or how do I define it?

 

[zevw]

Check your references, that would be my first shot with newer versions.

 

[PHV]

Can anyone explain why this is the case?
DLookUp is not a VB function nor a VBA function but a method of the Access.Application object.

In VB6, I'm afraid you have to write your own function playing with Recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PALman]

Thanks zevw,
I checked/ticked all the references that were in use in Access and it passed the DLookup method OK. However it stopped at Me.JobNo with another error... "Method or Data Member not found".

Thanks PHV,
I had a feeling I would have to write my own function. As I said a few weeks ago (before holiday), I need to teach myself more on the Recordset function and use it to look up fieldvalues in one table and compare against another table as I used DLookup in this example...
PartNo = DLookup("[PartNo]", "Enquiry Desk", "[JobNo]='" & Forms![QA-Contract Review]![JobNo] & "'")

Also to look up paths for finding files in subfolders as per...
Path = DLookup("[Path]", "[Folder Paths]", "[PathNo] = 'Path02'")

Thanks guys, any further help as always is much appreciated.

 

[abaldwin]

Conversions from vBA to vb do not happen simply by cut and paste (as you have found). The me.jobno is probably in access referring to the contents of a field on your form called jobno.

Using an ado recordset object and ado connection you can replication basically what you want by doing a sql statement of
"SELECT PartNo from [Enquiry Desk] Where JobNo = " & jobno.text & ";"

the open the recordset using this statement. There are plenty of references in way of books on the ADO objects. This is something I think it would be wise to wrap your arms around earlier rather than later.



Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[PALman]

Thanks abaldwin,
I shall do as you suggest and get back as soon as I can, although this is all new territory to me. (not a programmer but keen to learn).
Thanks again.

 

[PHV]

As a starting point to write your own Dxxx functions:

http://easyweb.easynet.co.uk/~trevor/downloads/baslookup.zip

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PALman]

PHV,
You've given me a great start with baslookup.zip.
Many Thanks,