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
Tim313Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Try with:

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

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tim313Author Commented:
I thought I had tried that [" & scrapType & "] but probably did ['" & scrapType &"'].

Thanks for the quick solution!
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.