getting data from a linked sql

[Jean-Paul]

I use 2 tables:
Leerlingen
and
Diverse

Both have a field ID

I want the value stored in the field ID to store in the interger lln_nr
but I can't find the correct syntax

This is the code

sql_meet = "SELECT leerlingen.*, Diverse.* FROM leerlingen INNER JOIN
Diverse ON leerlingen.ID = Diverse.ID WHERE Diverse.Meetlabo= False AND
leerlingen.[Nu nog leerling]=True And leerlingen.Klas='1';"
Set TB_meet = db.OpenRecordset(sql_meet)
lln_nr = TB_meet!ID

Anybody any idea?

Thanks

 

[John Spencer (MVP)]

Many questions.
Which ID do you want? Leerlingen.ID or Diverse.ID? You are returning both in
the query.
Which record do you want the ID from (assuming that your query returns more
than one record? Could it return zero records?

You might try the following.
IF Tb_Meet.RecordCount> 0 then
lln_nr = TB_meet.Fields("Leerlingen.Id")
END IF

If all you are interested in is one of the ID then change the select clause to
SELECT LeerLingen.ID FROM ...

Then you should be able to use
lln_Nr= Tb_Meet!ID
with no problem.

Another method that should work would be to modify the query slightly to
SELECT leerlingen.*, Diverse.*, leerLingen.ID as L_ID FROM ...

Then use this expression for the assignment.
lln_nr=Tb_Meet!L_ID

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County