Access VBA - Recordset Query

Posted on 2013-10-14
Medium Priority
Last Modified: 2013-10-14
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
    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
                Do Until rstScrapPct.EOF
                    Dim scrapPct As Double
                    scrapPct = rstScrapPct!pctScrap
            End If
            Set rstScrapPct = Nothing
End If
Set rstScrapType = Nothing
Question by:Tim313
  • 2
LVL 53

Accepted Solution

Gustav Brock earned 2000 total points
ID: 39572001
Try with:

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


Author Closing Comment

ID: 39572406
I thought I had tried that [" & scrapType & "] but probably did ['" & scrapType &"'].

Thanks for the quick solution!
LVL 53

Expert Comment

by:Gustav Brock
ID: 39572892
You are welcome!


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question