'Seek' function problem

[John F.]

Hi. There must be a simple error here but I just can't see it.

I have a table wih two columns, "Diagnosis" and "DiagnosisCode". The
PrimaryKey is the column "Diagnosis".

I'm trying to get the DiagnosisCode associated with a "Diagnosis" using Seek:

Set db = CurrentDb
Set rs = db.OpenRecordset("tblDiagnosisList", dbOpenTable)

'Set the index -- you have to do this to use Seek method
rs.Index = "PrimaryKey"
rs.Seek "=", Me.Diagnosis1
If rs.NoMatch Then
msgboxResult = MsgBox("Diagnosis not on list. Add it manually.",
vbOKOnly)
Else
'Now return the diagnosis code corresponding to that diagnosis
Me.Diagnosis1Code = rs.DiagnosisCode
End If

I get the error message "Method or data member not found", highlighting
rs.DiagnosisCode. I never get the 'NoMatch' msgBox if I comment out the
rs.DiagnosisCode, so I know the Seek is finding the record but I just can't
see why this doesn't return the rs.DiagnosisCode.

Can anyone spot the error?

Thank you.

johno

 

[Allen Browne]

You must use a bang (not dot) with fields of recordsets, i.e.:
rs!DiagnosisCode

For this kind of thing, I prefer to use a SQL statement that gets the fewest
fields and records you need, e.g.:
strSql ="SELECT DiagnosisCode FROM tblDiagnosisList WHERE [ID] = " &
Me.Diagnosis1 & ";"
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
...
The main advantage is that the code still works if you decide to split the
database.

For just a single value, you could use DLookup():
http://allenbrowne.com/casu-07.html

 

[David W. Fenton]

rs.Seek "=", Me.Diagnosis1

Why are you using .Seek? I just don't see much justification for
using it, especially as it's a Jet-specific command and will break
if you upsize to a different back end.

I've never once in 12 years of professional Access database
development had any need to use Seek.