add sequential number to field for query dataset
Posted on 2010-08-23
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)
' Set db = CurrentDb
' Set rs = db.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
'loop through records increasing number by one
Do While Not .EOF
strFa = "FA" & dblCounter
!afanId = strFa
dblCounter = dblCounter + 1
Set rs = Nothing