craigdnc
asked on
add sequential number to field for query dataset
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(dbOpenDy naset)
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
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(dbOpenDy
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
"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.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Helen. not sure why qry defs didn't work as documented in this case. thanks for workaround.
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.