add sequential number to field for query dataset

Posted on 2010-08-23
Last Modified: 2012-05-10
HI experts.  access 2007.  I've got a set of queries which all feed off one seed query.  the seed query has parameters set from a form.  I'm trying to add a sequential number to each of the set of queries using ado.  my tables are all on sql server.  problem is when i try to loop through the records using the query names I get "too few parameters expected 3" error.  code all works sweet if i change the sql to tables so the issue is the query parameters fed off the form - there are 3.  I've tried using querydefs to pass the parameters but keep getting no records - the code quits out on the if eof then close line before looping through records.  I've hard coded the query and parameters to ensure they are not the problem at this stage.  if i mouse over qdf line as i step through code i get qdf = nothing.  if i run this query with those parameters i get 2,500 records.  am i doing something obviously wrong?  is there a different solution? any help appreciated.  

here is code with querydefs and hardcoded query name and parameters

            Dim db As DAO.Database
            Dim qdf As DAO.QueryDef
            Dim prm As DAO.Parameter
            Dim rs As DAO.Recordset
            Dim strSql As String
            Dim strWhere As String
            Dim dblCounter As Double
            Dim strFa As String
            dblCounter = Me.txtFaStart 'number to start with
            'strWhere = Me.cboAssetSubGroup 'this feeds the rest of the sql string
            'strSql = "SELECT " & strWhere & ";"
            Set db = CurrentDb
            Set qdf = db.QueryDefs("qryRdsValRbs")
            qdf.Parameters(0).Value = "2010"
            qdf.Parameters(1).Value = "*"
            qdf.Parameters(2).Value = Null
            Set rs = qdf.OpenRecordset(dbOpenDynaset)
            DoCmd.SetWarnings False
'            Set db = CurrentDb
'            Set rs = db.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
            'loop through records increasing number by one
            With rs
                Do While Not .EOF
                    strFa = "FA" & dblCounter
                    !afanId = strFa
                    dblCounter = dblCounter + 1
            End With
            Set rs = Nothing
Question by:craigdnc
  • 2
  • 2
LVL 84
ID: 33499436
"Too Few Parameters" generally means that you've got a Field or Control misspelled. Check the spelling of all field/control names in your code.

Author Comment

ID: 33500076
I originally had 5 but fixed 2 through method you suggested.  I suspect the other 3 are those that the seed query is looking for from the form which i have hardcoded in the posted code.  it seems that my problem is with the line Set qdf = db.QueryDefs("qryRdsValRbs") as this returns null if i hover over in step through.  The query name is correct. Will double check though. thanks
LVL 31

Accepted Solution

Helen_Feddema earned 500 total points
ID: 33502082
You might want to use an alternate method, creating a filtered query or SQL statement as in the following code.  I use this method to prevent errors with queries that get values from forms.

[sample code fragment using the procedure]

   Dim dbs As DAO.Database

   Dim lngCount As Long

   Dim lngID As Long

   Dim rpt As Access.Report

   Dim rst As DAO.Recordset

   Dim strPrompt As String

   Dim strQuery As String

   Dim strRecordSource As String

   Dim strReport As String

   Dim strSQL As String

   Dim strTitle As String


   strRecordSource = "tblInventoryItemsComponents"

   strQuery = "qryTemp"

   Set dbs = CurrentDb

   'Numeric filter

   lngID = Nz(Me![ID])

   If lngID <> 0 Then

      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _

         & "[ID] = " & lngID & ";"

   End If

   'String filter

   strInventoryCode = Nz(Me![InventoryCode])

   If strInventoryCode <> "" Then

      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _

         & "[InventoryCode] = " & Chr$(39) & strInventoryCode & Chr$(39) & ";"

   End If

   'Date range filter from custom database properties

   dteFromDate = CDate(GetProperty("FromDate", ""))

   dteToDate = CDate(GetProperty("ToDate", ""))

   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _

      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _

      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   'Date range filter from controls

   If IsDate(Me![txtFromDate].Value) = True Then

      dteFromDate = CDate(Me![txtFromDate].Value)

   End If

   If IsDate(Me![txtToDate].Value) = True Then

      dteToDate = CDate(Me![txtToDate].Value)

   End If

   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _

      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _

      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   Debug.Print "SQL for " & strQuery & ": " & strSQL

   lngCount = CreateAndTestQuery(strQuery, strSQL)

   Debug.Print "No. of items found: " & lngCount

   If lngCount = 0 Then

      strPrompt = "No records found; canceling"

      strTitle = "Canceling"

      MsgBox strPrompt, vbOKOnly + vbCritical, strTitle

      GoTo ErrorHandlerExit


      'Use this line if you need a recordset

      Set rst = dbs.OpenRecordset(strQuery)

   End If

   'Use SQL string as the record source of a form

   strFormName = "fpriLoadSoldPackingSlip"

   DoCmd.OpenForm FormName:=strFormName, _


   Set frm = Forms(strFormName)

   frm.RecordSource = strSQL

   DoCmd.OpenForm FormName:=strFormName, _



   'Use SQL string as the record source of a report

   strReport = "rptLoadSold"

   DoCmd.OpenReport ReportName:=strReport, _

      view:=acViewDesign, _


   Set rpt = Reports(strReport)

   rpt.RecordSource = strSQL

   DoCmd.OpenReport ReportName:=strReport, _

      view:=acViewNormal, _


   'DoCmd.Save objecttype:=acReport, objectname:=strReport

   'DoCmd.Close objecttype:=acReport, _



Public Function CreateAndTestQuery(strTestQuery As String, _

   strTestSQL As String) As Long

'Created by Helen Feddema 28-Jul-2002

'Last modified 6-Dec-2009

On Error Resume Next


   Dim qdf As DAO.QueryDef


   'Delete old query

   Set dbs = CurrentDb

   dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler


   'Create new query

   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)


   'Test whether there are any records

   Set rst = dbs.OpenRecordset(strTestQuery)

   With rst



      CreateAndTestQuery = .RecordCount

   End With



   Exit Function


   If Err.Number = 3021 Then

      CreateAndTestQuery = 0

      Resume ErrorHandlerExit


   MsgBox "Error No: " & Err.Number _

      & " in CreateAndTestQuery procedure; " _

      & "Description: " & Err.Description

   End If


End Function

Open in new window


Author Closing Comment

ID: 33539915
Helen. not sure why qry defs didn't work as documented in this case.  thanks for workaround.
LVL 31

Expert Comment

ID: 33542086
There are always severals ways to do any particular task in Access, and sometimes you just have to try and see which one works in a given situation.

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now