Syntax på SQL

vanni>Not sure what you are using recordset for, can you explain in more detail or evne better send the db to me and I will take a look.
eksperten@santhell.dk

Hvad mener du med "Bruger"? Mener du at en værdi i Recordset1 bruges som kriterie i Recordset2 osv?

I så fald, ser det nogenlunde sådan ud:

Dim rs1 as ADODB.Recordset
Dim rs2 as ADODB.Recordset
Dim rs3 as ADODB.Recordset
Dim cn as ADODB.Connection
Set cn = Currentproject.Connection
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs3 = New ADODB.Recordset

rs1.Open "Select * From Tabel1", cn
rs2.Open "Select * From Tabel2 Where [Felt1] = " & rs1![Felt1], cn
rs3.Open "Select * From Tabel3 Where [Felt2] = " & rs2![Felt2], cn
...
...

Hi Terry

It's rather complex, but I will try in English.

I have to use queries based on queries for use in nested loops.

Al I want is the syntax for this test code:

strSQL1 = "SELECT bla, bla
FROM bla
WHERE bla
Set db1 = CurrentDb
Set recordset1 = db1.OpenRecordset(strSQL1)

srtSQL2 = "SELECT bla, bla
FROM bla, recordset1
WHERE bla
Set db2 = CurrentDb
Set recordset2 = db2.OpenRecordset(strSQL2)

Mvh. Vanni

Hej Thomas

Nu får jeg nok smæk, men jeg bruger gammeldavs DAO ikke ADO. :o)

Mvh. Vanni

Gode gamle DAO er nu også klart den nemmeste at bruge....

Du kan ikke bruge Recordsets på præcis den måde, som du ønsker.
Hvorfor bruger du ikke bare skrive SQL'en, så de relevante tabeller indgår i næste recordset?

Thomas: Øhh "skrive SQL'en"?

Hvis du har fået det til at virke ved at lave flere forespørgsler, så kan du jo bare benytte disse forespørgsler som grundlag for dine recordsets:

Set recordset1 = db.Openrecordset("Forespørgsel1", dbopendynaset)
Set recordset2 = db.Openrecordset("Forespørgsel2", dbopendynaset)
...
...

Vanni why not make ONE SQL which contains a SUB select

SELECT *, (SELECT fld FROM tbl2 AS T2 WHERE T2.ID = T1.ID) AS SomeField
FROM tbl1 AS T1
WHERE bla

Or something like that

Terry: I wrote this:

strSQL1 = "SELECT bla, bla;"

strSQL2 = "SELECT *, ( " & strSQL1 & " ) AS SomeField " _
& "FROM tbl1 AS T1 " _
& "WHERE bla; "

strSQL3 = "SELECT *, ( " & strSQL2 & " ) AS SomeField " _
& "FROM tbl1 AS T1 " _
& "WHERE bla; "

etc.

I can have it work this way. I'll just have to remove the semicolons from strSQLx.

Does that seem ok?

vanni, if you can give an example of your tables, fields and relationships, and what you are expecting from the result then it may be easier to give an example.

Terry: I am using "17/08-2003 21:42:44" and it is working fine. I just wanted your good old trained "SQL-eyes" to look at it and maybe make a remark.

Sorry, I wasnt sure if you meant that it was working or you were asking me if it looked OK!
Well if it works then thats just great :o)
No easy to see from here with bla, bla and all that :o)

Can you not paste strSQL3 here so we can see what it looks like?

If its working then you could actually paste it into a query and use the query instead of the SQL!

17/08-2003 22:01:14 - It was only a question about the syntax and principles.

Thomas: Jeg prøvede dit forslag og jeg tror også det kunne lade sig gøre, men jeg vil hellere bruge sub-sql.

I'll have to incorporate the strSQl in nested loops and will have read values and update fields according different conditions.

17/08-2003 22:02:26 - Yes I can, but I have to remove the semicolons.

Thanks for your help

selv tak!