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.MoveFirst

rst.Find strSQL, 0, adSearchForward

strccy = rst.GetString(adClipString)

Thanks very much!
martinaccess:D

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
  rst.close

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

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

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

--

Joe

Joe,

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!

Martin

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.