Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 19th, 2004, 03:48 AM
Authorized User
Join Date: Sep 2004
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using Find Method of ADO

Hello Expert,

I have attempted for two days to following codes. I am still unable to get the way out. Would you give me a help?

The field fund_cd is a combo box of transaction table to look up reference table called fund_type. Here is the structure of fund_type:

fund_cd text 3
fund_desc text 50
fund_currency text 5

My needs are to pull both values of fund_cd and fund_currency to transaction table, so 1 combo box cannot meet my needs. As such, I write following codes to base on selected fund_cd to find appropriate value of fund_currency in fund_type.

From the code below, I meet the run-time error ¡¥3001¡¦ and I am not sure can I finally get fund_currency using method of GetString.

Dim strSQL As String
Dim strccy As String

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select * from fund_type"

strSQL = "fund_cd = " & Me.fund_cd

rst.Find strSQL, 0, adSearchForward

strccy = rst.GetString(adClipString)

Thanks very much!

Old September 19th, 2004, 04:44 AM
joefawcett's Avatar
Wrox Author
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts

There are a number of ways to solve this, one problem appears to be that your database is not normalised as you are storing data twice, buts that's a different issue, alternatively store three columns in your combo and hide two but retrieve data from them when neded.

Instead of opening a recordset try using the DLOOKUP function which accepts a field to lookup, tablename and a criteria statement.
If you want to stick with a recordset you could use a full SQL statement originally or use the filter property or modify your find string. The main thing is that as fund_cd is a text string you need quotes around it so, assuming the current value of fund_cd is held in a variable called sFundCd (you can just use the current value of the combo) and you want to store the fund_currency in sFundCurrency:

1) Using DLOOKUP:
  sFundCurrency = DLOOKUP("fund_currency", "fund_type", "fund_cd = '" & sFundCd & "'")

2) Using full SQL:
  rst.Open "Select * from fund_type where fund_cd = '" & sFundCd & "'"
  sFundCurrency = rst.Fields("fund_currrency").value

3) Using recordset filter:
  rst.Open "Select * from fund_type"
  rst.filter = "fund_cd = '" & sFundCd & "'"
  sFundCurrency = rst.Fields("fund_currrency").value

4) Using find:
  rst.Open "Select * from fund_type"
  rst.Find "fund_cd = '" & sFundCd & "'", 0, adSearchForward
  sFundCurrency = rst.Fields("fund_currrency").value

I think you'll agree that if you stick to current design then DLOOKUP seems easiest.


Old September 19th, 2004, 08:48 AM
Authorized User
Join Date: Sep 2004
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts


Thanks very much. I am excited to get support from you. Both ways of DLookUp and Find are working. Once again, thanks for your help. Have a good day!


Old November 21st, 2007, 07:35 AM
Registered User
Join Date: Jul 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts

Yes, I use dlookup a lot. There is one point I would like to make though. If dlookup does not find a record matching the criteria you will end up with a null value which will cause an error.
Using: sFundCurrency = DLOOKUP("fund_currency", "fund_type", "fund_cd = '" & sFundCd & "'")

sFundCurrency will error (it's null) if dlookup does not find a record.
I use nz with every dlookup.

Example: sFundCurrency = nz(DLOOKUP("fund_currency", "fund_type", "fund_cd = '" & sFundCd & "'"),"The Value if not found")

Just an error trap suggestion.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using .Find with ADO phi kyo VB Databases Basics 2 January 16th, 2006 07:11 AM
find method in Hibernate sikharsaikia Java Databases 3 August 10th, 2005 05:09 AM
NextRecordset method in ADO.NET? Clay ADO.NET 6 April 20th, 2005 07:54 AM
ADO DELETE METHOD JENKINSACTIVE SQL Server 2000 4 November 4th, 2004 04:58 AM
ADO could ADO counot find the specified provider. Rob Collie Classic ASP Databases 2 June 9th, 2003 04:12 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.