Running two SQL queries in VBA MS Access. The second recordset depends on the first recordset

Ruva

I am currently using VBA access and I want the information from strSQL to come from strSQL0. Please help me correct my select statement for strSQL.

The logic for strSQL is I want to select the minimum iD in from the recordist produced by strSQL0

strSQL0 = "SELECT Promis_lt_generating_numbers_SubjectID_step2.* " _
            & "FROM Promis_lt_generating_numbers_SubjectID_step2 LEFT JOIN PROMIS_LT ON Promis_lt_generating_numbers_SubjectID_step2.[Id] = PROMIS_LT.[DadosID]" _
            & "WHERE (((PROMIS_LT.DadosID) Is Null));"

    Set rs0 = dbs.OpenRecordset(strSQL0)
    

    strSQL = "SELECT MIN(rs![Id]) AS New_SubjectID" _
            & "FROM rs0![*];"
Gustav

You could trim the first SQL to deliver the value without a second SQL:

strSQL = "SELECT TOP 1 Id " & _
    "FROM Promis_lt_generating_numbers_SubjectID_step2 " & _
    "LEFT JOIN PROMIS_LT ON Promis_lt_generating_numbers_SubjectID_step2.[Id] = PROMIS_LT.[DadosID] " & _
    "WHERE PROMIS_LT.DadosID Is Null " & _
    "ORDER BY Id ASC;"

    Set rs0 = dbs.OpenRecordset(strSQL)
    SubjectID = rs0(0).Value
    rs0.Close

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Running two SQL queries in PHP where the first statement creates a temporary table to be used by the second one

Access VBA: What is the difference between dao.recordset vs recordset?

Excel VBA SQL recordset doesn't refresh

Datatype mismatch when reading recordset in vba access

Populating multicolumn listbox with SQL recordset in Excel VBA

VBA in MS Access: how to accelerate a loop operation on a recordset

Why is a loop needed to change recordset in Access VBA?

Modify a query recordset to complete information (MS Access)

MS ACCESS VBA recordset search criteria type mismatch

access vba array by looping through recordset

vba get value from sql server recordset

Access 2013 - VBA - Recordset Insert getting ID

Run-time 3464 in Access VBA. Trying to open a recordset with SQL select statement

Access VBA - Error on checking recordset

How to open recordset from SQL Server in MATLAB via MS Access front-end

Copy attachments from records in one recordset into records in another using VBA in MS Access

Access 2016 VBA .FindFirst is not finding record in recordset

Access VBA: Editing a RecordSet in a continuous subform

Export Access VBA Recordset To Single Row In Excel

Access VBA set recordset of lists not working

Filtering a MS-Access recordset based on records in another recordset

Ms Access, ADODB, Recordset, Set Primarykey

Update MS access table using VBA recordset with 2/more inner join sub statements

Open Recordset with nested queries

MS Access - how can i aggregate two queries in one recordset?

MS access copying recordset using vba

VBA ADOBD.Recordset won't open if first line in .sql file is a comment

How to load a multiline recordset into an Access form with VBA

Access - VBA/SQL Recordset loop through not updating