Solved

add sequential number to field for query dataset

Posted on 2010-08-23
5
914 Views
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
                    .Edit
                    strFa = "FA" & dblCounter
                    !afanId = strFa
                    dblCounter = dblCounter + 1
                    .Update
                    .MoveNext
                Loop
                .Close
            End With
       
            Set rs = Nothing
   
           
0
Comment
Question by:craigdnc
  • 2
  • 2
5 Comments
 
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.
0
 

Author Comment

by:craigdnc
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
0
 
LVL 31

Accepted Solution

by:
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

   Else

      '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, _

      view:=acDesign

   Set frm = Forms(strFormName)

   frm.RecordSource = strSQL

   DoCmd.OpenForm FormName:=strFormName, _

      view:=acNormal

   

   'Use SQL string as the record source of a report

   strReport = "rptLoadSold"

   DoCmd.OpenReport ReportName:=strReport, _

      view:=acViewDesign, _

      windowmode:=acHidden

   Set rpt = Reports(strReport)

   rpt.RecordSource = strSQL

   DoCmd.OpenReport ReportName:=strReport, _

      view:=acViewNormal, _

      windowmode:=acWindowNormal

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

   'DoCmd.Close objecttype:=acReport, _

      objectname:=strReport



=========================



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

      .MoveFirst

      .MoveLast

      CreateAndTestQuery = .RecordCount

   End With

   

ErrorHandlerExit:

   Exit Function



ErrorHandler:

   If Err.Number = 3021 Then

      CreateAndTestQuery = 0

      Resume ErrorHandlerExit

   Else

   MsgBox "Error No: " & Err.Number _

      & " in CreateAndTestQuery procedure; " _

      & "Description: " & Err.Description

   End If

   

End Function

Open in new window

0
 

Author Closing Comment

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

Expert Comment

by:Helen_Feddema
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.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

705 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

18 Experts available now in Live!

Get 1:1 Help Now