How do I read a Table by the Primary Key?

PRD

Registered User.
Local time
Today, 15:35
Joined
May 18, 2011
Messages
72
I have another embarrassingly simple question - how do I read a Table using the Primary Key?

I have a Table (called ‘Sciflat’) which has a two-field Primary Key (‘Genus’ and ‘Species’). I want to retrieve the ‘Family’ of a specific genus and species. I thought the SELECT statement would do the trick and so I coded the following…

SELECT Sciflat.Family FROM Sciflat
WHERE Sciflat.Genus = “Thrinax” AND Sciflat.Species = “alba”

However when I run the code I get the following debug error…

Compile Error: Expected Case

Can you tell me what I am doing wrong? As a temporary work-around I am reading the entire Sciflat Table using a DO LOOP until I get a hit on the genus and species but this is obviously pretty ugly and inefficient. Thx.

P.S. The version of Access I am running is Access Professional (2002) V 5.1.2600 under Windows XP.
 
Howzit

Do you have the sql statement encased in double quotes. It may be that seeing the Select part vba wants and expects to see the case part as in SELECT CASE

You may want something like

Code:
Dim sSQL as string

sSQL = "SELECT Sciflat.Family FROM Sciflat "
sSQL = SSQL & "WHERE Sciflat.Genus = 'Thrinax' AND Sciflat.Species = 'alba'"

...
 
Thanks but this did not work, there was no debug error but the Family name was not retrieved. For what its worth the following code works perfectly...

Sciflat.MoveFirst
Do Until Sciflat.EOF

If Sciflat![Genus] = Me.LAGE Then
If Sciflat![Species] = Me.LASP Then
Me.LAFA = Sciflat![Family]
Sciflat.MoveLast
End If
End If

Sciflat.MoveNext
Loop
 
Howzit

Glad you got a solution but you may want to do a dlookup to get the results a little quicker, as opposed to looping through the table until you find a match

Code:
me.LAFA = Dlookup("[Family]","Sciflat","[Genus]='" & me.Me.LAGE & "' AND [Species]='" & me.LASP  & "'")
 
Kiwiman -

AWESOME tip! The Dlookup works like a charm. Forget the SELECT statement, thanks again!
 
Howzit

The select statement would work just fine - but you would need to assign it to and work with a recordset to return a value.

As an example this should also work - but I would stay with the Dlookup in this instance

Code:
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sSQL as string

sSQL = "SELECT Sciflat.Family FROM Sciflat "
sSQL = sSQL & "WHERE Sciflat.Genus = '" & me.Me.LAGE & "'  AND Sciflat.Species = '" & me.LASP  & "'"

set db = currentdb
ser rs = db.openrecordset(sSQL)

if rs.eof and rs.bof then
  ' There is nothing there
else
  me.LAFA = rs!Family
end if

rs.close
set rs = nothing
set db = nothing
 
I will try this on Monday as it would be nice to get the SELECT statement working for future use. My Tables are not SQL tables, will that make a difference?

I used to write programs in the ancient language of COBOL and we had two simple statements to read a file (table). One was a 'MOVE' statement to load the Key with the desired value and the other was a 'READ' statement to retrieve the record (there was an INVALID KEY clause in the event the record was not found). As impressed as I am with Access I'm surprized there isn't an easier way to read a table by Key than the SELECT clause which seems kind of cumbersome. I will use the Dlookup in this case as you suggest. Thanks again for your help.
 
Howzit

No it will not matter that your tables are not SQL tables. You still use SQL to query the tables. Good luck.
 

Users who are viewing this thread

Back
Top Bottom