Access VBA - Recordset Query

I have to following code where the first query returns ScrapType (its datatype is Text in tblScrap) that I want to use in the second query as the column name. For example:

First query returns ScrapType = "Scrap Total", which is assigned to string variable scrapType and the second query uses this variable scrapType, but running the code as is, I get the following error message:

Run-time error '3061':
Too few parameters. Expected 1.

at the line of the second query:

Set rstScrapPct = CurrentDb.OpenRecordset("SELECT [scrapType] As pctScrap FROM Query1")


If I type [Scrap Total] (the actual column name in Query1) into the second query the code runs and returns the result expected.

How can I pass the string "Scrap Total" to the column name in the second query?


Dim rstScrapType As DAO.Recordset
Set rstScrapType = CurrentDb.OpenRecordset("SELECT ScrapNo, ScrapType FROM tblScrap ORDER BY ScrapNo")
If rstScrapType.RecordCount > 0 Then
    rstScrapType.MoveFirst
    Do Until rstScrapType.EOF
           
        Dim scrapType As String
        scrapType = rstScrapType!ScrapType
           
            Dim pctScrap As Integer
            Dim rstScrapPct As DAO.Recordset
            Set rstScrapPct = CurrentDb.OpenRecordset("SELECT [scrapType] As pctScrap FROM Query1")
            If rstScrapPct.RecordCount > 0 Then
                rstScrapPct.MoveFirst
                Do Until rstScrapPct.EOF
                                                   
                    Dim scrapPct As Double
                    scrapPct = rstScrapPct!pctScrap
                       
                       
                       
                    rstScrapPct.MoveNext
               
                Loop
            End If
            rstScrapPct.Close
            Set rstScrapPct = Nothing
               
        rstScrapType.MoveNext
    Loop
End If
rstScrapType.Close
Set rstScrapType = Nothing