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
Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted. Others take a little more time and effort and often providing a sample database is very helpf…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works
The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…